puzzling problem with query
A user on IDS 7.30.UC3 had two almost identical GROUP BY queries on the same ~216k-row table, differing only in the filter col3='N' vs col3='Y' (no index on col3); the 'N' query returned in seconds while the 'Y' query ran for hours. Suggestions included checking for NULL/other values, running UPDATE STATISTICS, looking for stray characters in the saved SQL, adding col3 to the existing col1,col2 index, and rebuilding possibly corrupt indexes. Another poster noted dbaccess shows a first screenful early, so the 'N' query only appeared faster. The poster reported that creating an index on col3 eliminated the hang, though he never found out why one query behaved differently.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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.
Thanks for the help!
Ty O'Kelly
tokelly@tcac.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.
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
Jonathan Leffler wrote:
> 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.
Actually, all rows will contain either N or Y. The problem isn't the totals.
The problem is that the second query seems to hang up or something. Thanks
for the input, though.
Ty
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.
Grasping at straws here:
1) Have you run update statistics?
2) If this is a saved sql query, use your system editor to check for
extraneous characters (if this is a unix system, ':set list' in vi or
od -xc sql_file should show any bogus characters)
Admittedly 2) should by all rights give a syntax error and update stats
shouldn't help if there is no index on col3 as the query must do a
sequential scan for both queries.
Have you been doing a lot of adds and deletes on this table?
Good problem! I hope you'll post the answer.
Duane
Sent via Deja.com http://www.deja.com/
Before you buy.
Thanks to all for the help with this problem. I had indeed updated stats on the table. I even dropped and recreated it. I wound up putting an index on the filtering column (col3) and this eliminated the hang up. I was hesitant to do this because there are only two possible values for the column and it seemed an inappropriate candidate for an index. I am still not sure why one query worked and the one didn't, but it is working now so I will leave well enough alone. I will continue to investigate this strange problem and share whatever information I find with the group. Thanks again, Ty O'Kelly tokelly@tcac.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: Try adding the col3 field to the index on col1, col2. Then UPDATE STATISTICS, and try again.
If you are running the query in "dbaccess", it may appear that Query 1 is
executing faster than Query 2. That's because dbaccess displays results as
soon as the query has returned a "screenful" of information, even though
the query is still running.
Query 1 is returning a lot of information and can use an index (col1,
col2) to start returning rows before completing the query - this could
explain why the results are being displayed on your screen fairly quickly
giving you the impression that its running faster.
You need to run both queries to completion to compare their performance -
you could do this by unloading the results to a file (add "unload to junk"
to the top of the query).
You could also be "suffering" from index corruption of your col1,col2
index. Rebuild your indexes on tab1 by disabling and enabling them ("set
indexes for tab1 disabled;" , then "set indexes for tab1 enabled;").
An index on col3 will NOT help.
Rudy
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.