Re: puzzling problem with query

From: Jonathan Leffler <jleffler@earthlink.net> > >Ty O'Kelly wrote: > > > I am something of an Informix newbie and would appreciate any advice > > offered for this rather perplexing problem. I have two queries against > > the same table. The first query runs in a matter of seconds. The > > second query will run for hours until I finally kill it, despite the > > fact that the number of rows it should return is significantly fewer > > than the first query. Sqexplain did not seem to provide any useful > > information. I am at a loss for what the problem may be. Has anyone > > had similar trouble? We are running Informix Dynamic Server Version > > 7.30.UC3. Following are the queries and some info. on the table: > > > > select col1,col2,count(*) > > from tab1 > > where col3='N' > > group by col1, > > col2> > > > select col1,col2,count(*) > > from tab1 > > where col3='Y' > > group by col1, > > col2> > > > Total records in tab1: 215974 > > Total records in tab1 where col3='N': 207868 > > Total records in tab1 where col3='Y': 8106 > > There is no index on col3. > >You've left out two sets of rows from your counting, which should account >for the >difference betwee the total records in the table and the number you've >counted. > >SELECT col1, col2, COUNT(*) > FROM tab1 > WHERE col3 IS NULL > GROUP BY col1, col2;> >SELECT col1, col2, COUNT(*) > FROM tab1 > WHERE col3 != 'N' AND col3 != 'Y' > GROUP BY col1, col2;> >If the 4 queries with group by clauses don't add up to the total number of >rows >and no-one is inserting data behind your back, then there's a bigger >problem. Unless my math is shot, the count isn't the problem, performance is. UPDATE STATISTICS? ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

Obnoxio The Clown wrote: > Unless my math is shot, the count isn't the problem, performance is. UPDATE > STATISTICS? Correct, performance is the problem. I have updated statistics to no avail. I think I have a real problem on my hands. :( Thanks, Ty

Given that the one query retrieves a lot more rows than the other, could this be a problem with the temp db space used for the group by temp table? -- --------------------------------------- Tony Flaherty aef@mfs.misys.co.uk Analyst Programmer Misys Financial Systems All statements and opinions are my own, Misys don't pay me enough to have opinions on their behalf . Ty O'Kelly wrote in message <38AD39F4.5F60EB8C@spammers.suck.tcac.net>... >Obnoxio The Clown wrote: > >> Unless my math is shot, the count isn't the problem, performance is. UPDATE >> STATISTICS? > >Correct, performance is the problem. I have updated statistics to no avail. I >think I have a real problem on my hands. :( > >Thanks, >Ty > >

Tony Flaherty wrote: > Given that the one query retrieves a lot more rows than the other, could > this be a problem with the temp db space used for the group by temp table? The problem query should return a smaller result set, so I don't know if that is the problem. The other query, which runs fine, returns a much larger result set without trouble. Ty