Have you tried a compound index, assuming Daly_proc_view is a table?
CREATE INDEX SearchDalyProcView ON DALY_PROC_VIEW (PRODDATE,GRADE,SMRYFLAG)
If Daly_proc_view is a view, restructuring the view syntax can work wonders at times.
"Ben Petersen" <[EMAIL PROTECTED]> wrote:
>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/
>
--
Albert Berry
Full Time Consultant to
PSD Solutions
350 West Hubbard, Suite 210
Chicago, IL 60610
312-828-9253 Ext. 32
__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now!
http://channels.netscape.com/ns/browsers/download.jsp
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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/
