Mike, I think that generally the query optimizer does a good job, so I leave manopt off by default. But whenever a view or table select doesn't seem to perform the way I think it should I try setting manopt on and compare. Sometimes the difference is enormous.
Ben Petersen On 11 Oct 2002, at 17:59, Ramsour Mike wrote: > Ben: > > That does the trick. Very interesting. Does that mean that I should > run with MANOPT ON most or all of the time? If not, when will I know > which way to go? In general, the way I have most of my views > constructed seems (at least to me) to be the most efficient. > > Thanks for a very helpful reply. > > Mike > > -----Original Message----- > From: Ben Petersen [mailto:benpetersen@;softcom.net] > Sent: Friday, October 11, 2002 9:54 AM > To: [EMAIL PROTECTED] > Subject: Re: SELECT clause performance variations > > > > > > > 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/
