Re: Optimization of query finding related products for a certain product
Date: 9 Sep 2001 17:15:54 -0700
Message-ID: <cd3b3cf.0109091615.41062c6e_at_posting.google.com>
Vucko <mvucic_at_barok.foi.hr> wrote in message news:<newscache$7vpcjg$sz9$1_at_tower.foi.hr>...
> In comp.databases.oracle.misc Jacob Nordgren <jacob_nordgren_at_hotmail.com> wrote:
> >Hi everybody,
> >
> >This query finds the related products for a certain product based on
> >user visits.
> >
> >Today this query takes 3 seconds. Is it possible to make it faster
> >with the same result?
> >
> >SELECT PRODUCT_ID FROM VISIT WHERE
> >USER_ID IN (SELECT DISTINCT USER_ID FROM VISIT WHERE PRODUCT_ID =
> >1883577330)
> >AND PRODUCT_ID <> 1883577330 GROUP BY PRODUCT_ID ORDER BY
> >COUNT(DISTINCT USER_ID) DESC
> >PRODUCT_ID INT *
> >VISITDATE DATE *
>
> You don't like to use cursors?
If, after all of that, the optimizer simply handles "IN" clauses poorly, I would suggest trying logical equivalents using "= ANY" or "EXISTS", which some dbmses handle better. If logically correct, replace the whole subquery with a join. If the dbms supports subqueries in the "FROM" clause, rewrite the query using a join between VISIT and the subquery.
I wouldn't recommend using cursors under any circumstances. Using procedural methods prevents the optimizer from ever doing its job. Received on Mon Sep 10 2001 - 02:15:54 CEST