Re: Optimization of query finding related products for a certain product

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?

Yuck! Take a weak optimizer and render it completely useless? Do you really think that's helpful?

First, Jacob needs to verify that someone has declared appropriate indexes etc. to the dbms. After that, Jacob needs to verify that the database statistics are up-to-date.

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 Sun Sep 09 2001 - 19:15:54 CDT