List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 19 2005 3:04pm
Subject:Re: sub query is extermely slow
View as plain text  
sam wun <sam.wun@stripped> wrote on 01/19/2005 07:02:37 AM:

> Hi list,
> 
> The following sql statement takes 3 mintues to complete the query. How 
> can I improve its speed?
> select DISTINCT i.basename from inventory i, transaction t, customer c 
> where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename 

> is not NULL and i.prodname is not NULL and ((date(t.date) >= 
> "2004-01-01" and date(t.date) <= "2004-01-31") and i.basename IN (select 

> DISTINCT ii.basename from inventory ii, transaction tt, customer cc 
> where ii.prodcode = tt.prodcode and cc.custcode = tt.custcode and 
> ii.basename is not NULL and ii.prodname is not NULL and(date(tt.date) >= 

> "2005-01-01" and date(tt.date) <= "2005-01-31"))) order by i.basename
> 
> Thanks
> Sam
> 
> 

I would simplify it by converting everything to us explicit (not implicit) 
JOIN statements,skipping unnecessary type conversions,  and logically 
merging your conditions. Here is your original query, slightly 
reformatted.

SELECT DISTINCT i.basename 
FROM inventory i, transaction t, customer c 
WHERE i.prodcode = t.prodcode 
        and c.custcode = t.custcode 
        and i.basename is not NULL 
        and i.prodname is not NULL 
        and (
                (date(t.date) >= "2004-01-01" 
                and date(t.date) <= "2004-01-31"
        ) 
        and i.basename IN (
                select DISTINCT ii.basename 
                from inventory ii, transaction tt, customer cc 
                where ii.prodcode = tt.prodcode 
                and cc.custcode = tt.custcode 
                and ii.basename is not NULL 
                and ii.prodname is not NULL 
                and(
                        date(tt.date) >= "2005-01-01" 
                        and date(tt.date) <= "2005-01-31")
                )
        ) 
order by i.basename

Here is my proposal:

SELECT DISTINCT i.basename 
FROM inventory i
INNER JOIN transaction t
        ON i.prodcode = t.prodcode
        AND t.date >= '2004-01-01' 
        AND t.date <= '2004-01-31'
INNER JOIN transaction tt
        ON i.prodcode = tt.prodcode
        AND tt.date >= '2005-01-01' 
        AND tt.date <= '2005-01-31'
INNER JOIN customer c 
        ON c.custcode = t.custcode
        AND c.custcode = tt.custcode
WHERE i.basename is not NULL 
        and i.prodname is not NULL 
order by i.basename

This should give you a list of inventory basenames for all current 
customers (their names are still in the customer table) that "had 
transactions" (ordered products?) during both JAN 2004 and JAN 2005. This 
list will show only the products that were ordered during BOTH time 
periods BY THE SAME CUSTOMER at least once (at least one repeat sale, year 
to year, in JAN). Is this what you were after or was there a different 
question you were trying to answer?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
sub query is extermely slowsam wun19 Jan
  • RE: sub query is extermely slowClint Edwards19 Jan
    • Re: sub query is extermely slowsam wun19 Jan
      • Re: sub query is extermely slowClint Edwards19 Jan
  • Re: sub query is extermely slowSGreen19 Jan
    • RE: sub query is extermely slowAndy Eastham19 Jan
    • Re: sub query is extermely slowsam wun20 Jan
    • Re: sub query is extermely slowsam wun20 Jan
      • Re: sub query is extermely slowSGreen20 Jan
Re: sub query is extermely slowClint Edwards19 Jan
  • Re: sub query is extermely slowsam wun19 Jan
RE: sub query is extermely slowArtem Koltsov19 Jan