Dan, That might be a possibility. However they like to have three years worth to have access to. Maybe create the temp table with just the six fields they want to look at? That might help. What do you think?
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 12:10 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: increase speed of lookup in form Glad you got it fixed. You can always create a temporary table to search against which should be faster. Dan Goldberg -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle Sent: Friday, February 06, 2015 10:06 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: increase speed of lookup in form Dan, I may have found the problem. What I forgot to take into account was the code previous to the code we have been dealing with. Using TRACE (once again comes to the rescue) I realized the .vSearchString had a quote in front. So adding the % made the entries look like this %'amer%' That is why it was not working. I change the vSearchString from SET VAR vsearchstring = (.vquotes+.vkeyword+.vmany+.vquotes) to SET VAR vsearchstring = (.vquotes+.vmany+.vkeyword+.vmany+.vquotes) The vmany variable was already giving me my wildcard. The speed is still not what I would like but it has improved. I have gone from 15 to 20 seconds to 5 - 7 seconds. That is on a table with 15,000 to 20,000 records. Not exactly like searching the web but going in the right direction. 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 11:47 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: increase speed of lookup in form Sorry should have been a + and not an & (.vSearchWhat+' LIKE ''%'+ .vSearchString + '%') Dan Goldberg -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Jim Belisle Sent: Friday, February 06, 2015 9:17 AM To: RBASE-L Mailing List 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]] 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]]
