Karen, Dan, Once again guilty of not reading everything that was sent. Yes! I no see the % and understand what you are saying, Dan. I figure it can't hurt to see if that helps. Duh! Why didn't I think of that?
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 9:36 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: increase speed of lookup in form Jim: look closer at his statement. There's a "%" both before and after. That turns it into a contains 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 9:34 am Subject: [RBASE-L] - RE: increase speed of lookup in form Does not the like start at the first letter of the information? If they get that wrong, it will not bring anything up. Or am I mistaken about the LIKE command? 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]>]
