Re: Count query taking too long vs identical query with columns returned

I'll try to comment on both responses...

Anurag:
The explain plan for the two queries is very similar but the cost of the count query is slightly higher (neither do a full table scan and both use the same indexes).

When I referred to total time I did mean the time to actually retrieve the data, we use disconnected recordsets as our transport mechanism so I know the data has been fetched when it is returned to the client (to speed up testing I test the queries in a sql editor of course).

Reid:
I'm not sure where you see a cartesian product because all the tables are joined, that being said the query I posted was just to give an idea how the real query is constructed, the actual query I'm using doesn't have a cartesian product problem (which is proven by the 2 second response time for the normal query). The explain plan for the query demonstrates that nothing unexpected is happening (i.e. cartesian product).

Thanks to both of your for your replies to my message, I'm still having troubles but I'll continue to hack away at it.

Thanks,
Andreas

Reid Lai <reidlai_at_netvigator.com> wrote in message news:<3B9A6318.4D6567BC_at_netvigator.com>...
> Andreas,
>
> You are generating catesian product coz you missed join key between table
> B and C as well as D and E. Review your business logic to match your
> query.
>
> Cheers,
>
> Reid Lai
>
>
> Andreas Ritzer wrote:
>
> > We're currently running Oracle 8.i and have 5 tables I'm querying with
> > approx. 1.5 millions records in each.
> >
> > When I perform the following type of query (with joins between the
> > tables and filtering):
> > SELECT A.col1, B.col2, C.col3, D.col4, E.col5
> > FROM table1 A, table2 B, table3 C, table4 D, table5 E
> > WHERE A.col = B.col
> > AND A.col = C.col
> > AND C.col = D.col
> > AND C.col = E.col
> > AND A.anotherColumn = 'SOMEARG'
> >
> > the response time is in the neighorbood of 3-4 seconds.
> >
> > When I try to perform the EXACT query trying to use the count
> > function, e.g.:
> > SELECT count(A.primaryKey)
> > FROM table1 A, table2 B, table3 C, table4 D, table5 E
> > WHERE A.col = B.col
> > AND A.col = C.col
> > AND C.col = D.col
> > AND C.col = E.col
> > AND A.anotherColumn = 'SOMEARG'
> >
> > the response time is well over 2 minutes. I have analyzed the tables
> > and indexes to death and tried count(1), count(*), decode(count(*), 0,
> > 0, 1), count(rowid), all to no avail.
> >
> > Can anyone help me understand why the same query would perform so
> > poorly when I'm only trying to retrieve the count rather then the
> > underlying data. In my naive head I thought with all things being
> > equal the count would perform equally if not better.
> >
> > Thanks for all your help,
> > Andreas
Received on Sat Sep 08 2001 - 16:54:50 CDT