Mike,
These variations
> This works well: SMRYFLAG IN ('M')
> and
> This works well: (ICHAR(SMRYFLAG)) = 77
> and
> This works well: SMRYFLAG = ('M')
don't use indexes, which seems counter intuitive, I know.
As a test, try "set manopt on" before selecting with = 'M' and see
what happens. Sometimes the query optimizer doesn't get it.
Ben Petersen
On 11 Oct 2002, at 17:23, Ramsour Mike 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/
>
================================================
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/
