Troy: The SMRYFLAG column is not indexed whereas PRODDATE is. My 1 gig database gets reloaded each morning and I usually do an UNLOAD ALL and rebuild the database at least once a week. I tried Ben Peterson's suggestion about setting MANOPT ON and that did the trick.
Thanks for the tips, though. Mike -----Original Message----- From: Troy Sosamon [mailto:tsosamon@;eapintl.com] Sent: Friday, October 11, 2002 6:02 PM To: [EMAIL PROTECTED] Subject: RE: SELECT clause performance variations Mike, >> 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: This tells me that the index on SMRYFLAG is a bad index because the multiple occurance table has a lot of duplicates. The opomizer is making the wrong decision on which index to use. Have you reloaded your database reciently? The index on PRODDATE is obviously the one you want to use. By doing something like (SMRYFLAG) = 'M' will not use the index on SMRYFLAG. Normally the optomizer will see that SMRYFLAG is a bad index and use the other one first. If it has been a long time since the database was reloaded, the values in the sys_indexes table that the query optomizer uses to decide which index it should use may be old and causing it to make bad decisions. Troy Sosamon ===== Original Message from [EMAIL PROTECTED] at 10/11/02 7:53 am >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/ ================================================ 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/
