SQL Query Search

[jtaljaard]

Hi

Just wondering if someone could assist with a large database sql search that either takes incredibly long or gives an error:

Capture.PNG (19.92 KiB) Viewed 46699 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]

Hi Jared,

This error is related to the CONNECT_FILE parameter 'Selection Limit':

CONNECT_FILE.PNG (24.07 KiB) Viewed 46692 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 (32.84 KiB) Viewed 46692 times

Kind regards,
René

[jtaljaard]

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]

Hi Jared,

Correct !

[caseywhite]

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.