sam wun <[EMAIL PROTECTED]> 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
