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]] 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]] 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]]
