Jim, I don't know if this will help but have you tried creating a command for the entire expression and not just the where clause and then doing an &vcommand? Looking back at your original example I don't see where the "where clause" is used but you might my try my suggestion. This often gives me better results.
Just my 2 cents worth. Paul Buckley -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Javier Valencia Sent: Friday, February 06, 2015 1:04 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: increase speed of lookup in form Jim, You might want to look at the resulting variable SHOW VAR YourrVariable and verify that it shows what it is supposed to be. Sometime when constructing expressions, subtle errors, such as using "&" instead of "+" or a typo or a missing "." will result in a variable that does not execute properly. Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle Sent: Friday, February 06, 2015 11:46 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: increase speed of lookup in form Karen, Still a no go. It does not matter if I have extra spaces or no spaces. The code when using the % does not bring up anything. Now if I keep my old code and manually enter the % as the first entry into the field, that brings up my info. But it is still very slow. It takes 15 - 20 seconds to bring up the information to choose from. That is a long time while a customer is on the phone. James Belisle Making Information Systems People Friendly Since 1990 [cid:[email protected]] From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef Sent: Friday, February 06, 2015 11:26 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: increase speed of lookup in form I see one error with your code. Can't use the & or else it will put a space between. Should be: (.vSearchWhat + ' LIKE %' + .vSearchString + '%') Karen -----Original Message----- From: Jim Belisle <[email protected]<mailto:[email protected]>> To: RBASE-L Mailing List <[email protected]<mailto:[email protected]>> Sent: Fri, Feb 6, 2015 11:18 am Subject: [RBASE-L] - RE: increase speed of lookup in form Dan, I tried you specific example but that did not work. I then tried combinations of the wildcard in front and back of the vsearchstring with no success. Some varieties were: (.vSearchWhat+' LIKE %'&.vSearchString + '%') your suggestion (.vSearchWhat+' LIKE %'&.vSearchString) (.vSearchWhat+' LIKE ''%'&.vSearchString + '%') James Belisle Making Information Systems People Friendly Since 1990 -----Original Message----- From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]?>] On Behalf Of Dan Goldberg Sent: Friday, February 06, 2015 9:31 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: increase speed of lookup in form Just curious to see if you change this from to see if it is faster: SET VAR vWhereClause = (.vSearchWhat+' CONTAINS '&.vSearchString) TO SET VAR vWhereClause = (.vSearchWhat+' LIKE %'&.vSearchString + '%') Dan Goldberg -----Original Message----- From: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]?>] On Behalf Of Jim Belisle Sent: Friday, February 06, 2015 6:58 AM To: RBASE-L Mailing List Subject: [RBASE-L] - increase speed of lookup in form We use a form with a var radio group as the first step to editing orders. 1. The radio group items are used just to determine what field in the header table to look at. 2. The var edit control is where we enter the information so a var lookup list view shows the lines to choose from. 3. The EEP code for the var edit control is as follows on keystroke: GETPROPERTY Keyword TEXTVALUE vKeyword IF vKeyword = '*' OR vKeyword = '%' THEN -- Define Where Clause SET VAR vWhereClause = ('Control# IS NOT NULL') PROPERTY LV_Quotes REFRESHLIST 'TRUE' PROPERTY LV_Quotes SET_FOCUS 'TRUE' PROPERTY ExportResultsAs ENABLED 'TRUE' GOTO Done ENDIF -- Pre-define variables IF (SLEN (.vKeyword )) < 4 THEN GOTO Done ENDIF SET VAR vSearchString = (.vQuotes+.vKeyword+.vMany+.vQuotes) -- Define Where Clause -- Added this part for name fields (2/12/2010). IF vSearchWhat = 'DistName' OR vSearchWhat = 'ShipName' THEN SET VAR vWhereClause = (.vSearchWhat+' CONTAINS '&.vSearchString) PROPERTY LV_Quotes REFRESHLIST 'TRUE' ELSE SET VAR vWhereClause = (.vSearchWhat+' LIKE '&.vSearchString ) PROPERTY LV_Quotes REFRESHLIST 'TRUE' ENDIF LABEL Done --CLEAR VAR iv% RETURN The part that is super slow (though other fields can be slow at times) is when we are looking for the customer name or the ship to name. These columns are text fields 25 spaces long. I use "contains" since sales will not always know the exact spelling at the beginning of the field. a. The table it is looking at is only 15,000 to 20,000 rows. b. The fields Distname and Shipname are not indexed. c. There are a total of 8 fields in this table that are either PK, FK or indexed fields. Do you have any suggestions on how to rectify the slowness? James Belisle Making Information Systems People Friendly Since 1990 [cid:[email protected]<mailto:[email protected]>]
