G'day Mike, Thanks for your post - I learned to put parenthesis round straight text matches to improve performance.
In thinking about your problem the thought crossed my mind, "People like text, databases like numbers." Now I would state that as a global truth or anything profound but it probably stems from two incidents. The first incident was the second commercial database I built which was, in my opinion, an absolute disaster. It was based on user definable and editable, alphanumeric primary keys. Ugly. But I digress. I found another combination terribly slow, that was testing a date column to see if it was null or not. My workaround for that was to add a computed column to that table that was 0 if the date was null, 1 if the date col had a value. Speeded lookups enormously. So, four points: 1. Your post:
SELECT * FROM DALY_PROC_VIEW WHERE PRODDATE BETWEEN 07/01/2002 AND 09/30/2002 AND GRADE='1233' AND SMRYFLAG = 'M' ORDER BY PRODDATE,CUR_CC,GRADE
does not have the dates in single quotes. I would code this as:
SELECT * FROM DALY_PROC_VIEW WHERE PRODDATE BETWEEN '07/01/2002' AND '09/30/2002' AND GRADE= '1233' AND SMRYFLAG = 'M' ORDER BY PRODDATE,CUR_CC,GRADE
Don't know if would make a difference or not. Be interested to know. 2. Do you have a multi-column index on PRODDATE, GRADE and SMRYFLAG? 3. Do you have a multi-column index on PRODDATE,CUR_CC,GRADE? 4. Is it feasible (in a view or table) to add a computed col that would set a numeric value based on the text in SmryFlag and base your query on the computed col? Hope this helps. At 17:23 11/10/02 -0400, you wrote:
Greetings:
I am trying to understand the difference in the performance of a SELECT
statement by using variations when referencing a column with an AND
qualifier.
Note: SMRYFLAG is a TEXT 1 field and has 4 possible values: E, M, N or O.
Specifically if I issue the following query and state SMRYFLAG = 'M'
performance of the SELECT statement is dismal:
SELECT * FROM DALY_PROC_VIEW WHERE PRODDATE BETWEEN 07/01/2002 AND
09/30/2002 AND GRADE='1233' AND SMRYFLAG = 'M' ORDER BY
PRODDATE,CUR_CC,GRADE
However, if I use any of the following variations I get almost instant
results which, of course, is what I'm after.
This works well: SMRYFLAG IN ('M')
and
This works well: (ICHAR(SMRYFLAG)) = 77
and
This works well: SMRYFLAG = ('M')
I don't understand why simply stating SMRYFLAG = 'M' gives very poor
performance while all the other variations give almost instant response.
I'm looking for insight. I have rebuilt all the tables and indexes involved
and scanned the data looking for something unusual. Nothing turned up. I
even moved the location of the SMRYFLAG column in its table and tried
changing its data type. Still nuttin'.
What's up with this? This may provide useful insight into other possible
implications and applications.
Platform: Windows 2000 Pro
R:Base: Windows 6.5++ w/latest patch
Pentium III, 550 mHz, 256 MB RAM.
Thanks for any input.
Mike Ramsour
Voice: 740-829-4340
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
Tom Grimshaw
coy: Just For You Software
tel: 612 9552 3311
fax: 612 9566 2164
mobile: 0414 675 903
post: PO Box 470 Glebe NSW 2037 Australia
street: 3/66 Wentworth Park Rd Glebe NSW 2037
email: [EMAIL PROTECTED]
web: www.just4usoftware.com.au
"... the control of impulse -- is the first principle of civilization."-- Will Durant,
Pulitzer Prize winning philosopher, writer and historian
the most needed product in the world can be found at
www.thewaytohappiness.org
This email and any files transmitted with it are confidential to the intended recipient and may be privileged. If you have received this email inadvertently or you are not the intended recipient, you may not disseminate, distribute, copy or in any way rely on it. Further, you should notify the sender immediately and delete the email from your computer. Whilst we have taken precautions to alert us to the presence of computer viruses, we cannot guarantee that this email and any files transmitted with it are free from such viruses.
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
