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/
