Path: news.easynews.com!easynews!hub1.nntpserver.com!hub1.nntpserver.com!171.64.14.102.MISMATCH!headwall.stanford.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: bbadour@golden.net (Bob Badour)
Newsgroups: comp.databases,comp.databases.theory,comp.databases.oracle,comp.databases.oracle.misc,comp.databases.oracle.server
Subject: Re: Optimization of query finding related products for a certain product
Date: 9 Sep 2001 17:15:54 -0700
Organization: http://groups.google.com/
Lines: 36
Message-ID: <cd3b3cf.0109091615.41062c6e@posting.google.com>
References: <47c6b9be.0109070232.2196e3ea@posting.google.com> <newscache$7vpcjg$sz9$1@tower.foi.hr>
NNTP-Posting-Host: 63.68.16.100
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1000080954 1129 127.0.0.1 (10 Sep 2001 00:15:54 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 10 Sep 2001 00:15:54 GMT
Xref: easynews comp.databases:89458 comp.databases.theory:18316 comp.databases.oracle:10309 comp.databases.oracle.misc:68507 comp.databases.oracle.server:117645
X-Received-Date: Sun, 09 Sep 2001 17:45:45 MST (news.easynews.com)

Vucko <mvucic@barok.foi.hr> wrote in message news:<newscache$7vpcjg$sz9$1@tower.foi.hr>...
> In comp.databases.oracle.misc Jacob Nordgren <jacob_nordgren@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.