Visual LANSA Forum

Hi

Just wondering if someone could assist with a large database sql search that either takes incredibly long or gives an error: 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

Hi Jared,

This error is related to the CONNECT_FILE parameter 'Selection Limit': 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:
Kind regards,
René

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?

Hi Jared,

Correct !

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.