Mike, A couple of thoughts. You should be really careful when setting manopt on. You might cut your nose off to spite your face. You may get 1 query to run fast and bring a lot of others to their knees. I have been dealing with large database for over 10 years, and I can only remember twice where it was necessary to set manopt on. Both cases involved tables w/ millions of rows and more than 5 table joins.
If SMYRFLAG is not indexed, there is something else going on to confuse the optomizer. A straight 3 table view shouldn't be a problem. You might want to help the optomizer out by moving the PRODDATE part of the where clause in front of the SMRYFLAG. The thing that is confusing me about this if there is not an index on SMYRFLAG, then it souldn't effect the optomizer. There is another setting, and I can't think of what it is off the top of my head, that effects how many tables the optomizer will handle. My inside R:base book is at the office, so I can't look it up right now. Troy >===== Original Message From [EMAIL PROTECTED] ===== >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/ Troy Sosamon Denver Co [EMAIL PROTECTED] ================================================ 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/
