Re: puzzling problem with query
Ty O'Kelly had a query that performed badly although it should return a smaller result set than a similar query that ran fine. Obnoxio The Clown suggested UPDATE STATISTICS, which Ty had already done without effect. Tony Flaherty suggested temp dbspace for the group by temp table, but Ty doubted it. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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