SQL Query Search

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
jtaljaard
Posts: 36
Joined: Thu Sep 21, 2023 9:29 pm

SQL Query Search

Post by jtaljaard » Tue Nov 21, 2023 5:47 pm

Hi

Just wondering if someone could assist with a large database sql search that either takes incredibly long or gives an error:
Capture.PNG
Capture.PNG (19.92 KiB) Viewed 16951 times
How would one make the search faster? Here is my code so far:


Evtroutine Handling(#btnSearch.Click #edtSearch.Enter)
Clr_List Named(#lvSearch)
Execute Subroutine(#LoadTable)
Endroutine

Subroutine Name(#LoadTable)
#STD_COUNT := 1
Define Field(#wx_code) Reffld(#MPKODE)
#wx_code := 140
Check_For In_File(CDXSEN) With_Key(#wx_code)
If Cond(#IO$STS = "EQ")
Select Fields(*ALL) From_File(CDXSEN) Where(#CXBSKE.Contains( #edtSearch.Value.UpperCase )) With_Key(#wx_code)
#STD_COUNT += 1
Add_Entry To_List(#lvSearch)
Leave If(#STD_COUNT = 10)
Endselect
Else
Use Builtin(MESSAGE_BOX_ADD) With_Args('No item was found with that description.')
Use Builtin(MESSAGE_BOX_Show)
Endif
Endroutine

René Houba
Posts: 195
Joined: Thu Nov 26, 2015 7:03 am

Re: SQL Query Search

Post by René Houba » Tue Nov 21, 2023 9:51 pm

Hi Jared,

This error is related to the CONNECT_FILE parameter 'Selection Limit':
CONNECT_FILE.PNG
CONNECT_FILE.PNG (24.07 KiB) Viewed 16944 times
Do you have a CONNECT_FILE builtin in your program?

If not, how do you execute your VL application?
Is this the VLF? Then you should change the 'Selection Limit' in the VLF server definition:
VLF SERVER DEFINITION.PNG
VLF SERVER DEFINITION.PNG (32.84 KiB) Viewed 16944 times

Kind regards,
René

jtaljaard
Posts: 36
Joined: Thu Sep 21, 2023 9:29 pm

Re: SQL Query Search

Post by jtaljaard » Tue Nov 21, 2023 11:59 pm

Thanks Rene

I dont suppose there is a TOP or LIMIT parameter I can use to stop the SQL query once it reaches like 20 records that match the criteria?

René Houba
Posts: 195
Joined: Thu Nov 26, 2015 7:03 am

Re: SQL Query Search

Post by René Houba » Wed Nov 22, 2023 1:29 am

Hi Jared,

Correct !

caseywhite
Posts: 169
Joined: Thu May 26, 2016 1:17 am

Re: SQL Query Search

Post by caseywhite » Thu Nov 30, 2023 10:55 am

Jared. I am assuming this is VLF Windows since there is a CONNECT_FILE. In a use case like this you should consider calling a server function using the CALL_SERVER_FUNCTION BIF. Pass in the parms you need and execute that query on the server directly. This will likely be way faster because you will avoid each record being pulled down to the client to be compared.

Post Reply