Jim,
Below is the relevant part of my:
-- On Key Press Custom EEP ... Date of last amendment: 23/07/2009
-- Obtain letters as key pressed:
GETPROPERTY IDV_SelName TEXTVALUE vSelName
SET VAR vCount = (SLEN(.vSelName))
IF vCount < 2 THEN
GOTO lDone
ENDIF
SET VAR vSearchString = (.vQuotes + .vSelName + .vMany + .vQuotes)
-- Define Where Clause:
SET VAR vWhere_Clause = NULL
SET VAR vWhere_Clause = +
('First_Name LIKE' & .vSearchString & 'OR Main_Name LIKE' & .vSearchString)
PROPERTY DBLV_AllNames REFRESHLIST 'TRUE'
LABEL lDone
-- Adjust display:
IF vCount BETWEEN 2 AND 8 THEN
-- This then sets up the display before returning...
And this is the relevant table data:
No. Column Name Attributes
1 First_Name Type : TEXT 40
Index : SINGLE-COLUMN
Comment: First or Christian name
2 Main_Name Type : TEXT 40 NOT NULL
Index : SINGLE-COLUMN
Comment: Group or Surname
3 IDNum Type : INTEGER NOT NULL AUTONUMBER
Consrnt: PRIMARY KEY REFERENCED
Comment: System Identity Number from All_Name
4 Full_Name Type : TEXT 80 NOT NULL
Consrnt: UNIQUE
Compute: (First_Name& Main_Name)
Comment: Combination of FIRST & MAIN names
5 Name_Type Type : TEXT 1 NOT NULL
Default: C
Comment: Artist, Musician, Writer, Publisher,
Label, Venue
6 Sex Type : TEXT 1 NOT NULL
Default: N
Comment: Male/Female - Not Applicable/Unknown
Current number of rows: 41434
I'm using v7.6 and the speed is - as I've said before - fast enough not to
notice any waiting despite an "OR" in my where clause.
I limit the response to two letters or more simply because fewer than two
returns all the rows. To start with I had a minimum of three letters but then
discovered people with only two letters in their name! (More than eight tends
to become too specific to be of use in selecting from likely choices.)
I'm not sure how Larry would re-write it as a select statement...
Regards,
Alastair.
From: Lawrence Lustig
Sent: Sunday, January 24, 2010 2:24 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: locked out
<<
SET VAR vWhereClause = +
('Control# LIKE'&.vSearchString&'OR ' + +
'Invoice# LIKE'&.vSearchString&'OR ' + +
'OrderNumber LIKE'&.vSearchString&'OR ' + +
'ShipZip LIKE'&.vSearchString)
>>
The problem is that the ORs in your search criteria make this a
non-optimizable search and you'll pay for a full table scan.
To turn this into an optimizable search, you must rewrite it as four SELECT
statements UNIONed together.
--
Larry
------------------------------------------------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.730 / Virus Database: 271.1.1/2640 - Release Date: 01/23/10
07:33:00
