List:General Discussion« Previous MessageNext Message »
From:sam wun Date:January 20 2005 4:45pm
Subject:Re: sub query is extermely slow
View as plain text  
SGreen@stripped wrote:

>
>
> 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?
>
Hi, while this is much faster than subquery, I found there is "cumsy" 
way faster than this series inner joins.
I dicovered that if I create two different temporary tables with "create 
view as select ..." for 2 differnet period, the join between this temp 
tables is also much faster than this series inner joins approach.  With 
160000 records in the transaction table, the series inner joins takes  
very long time (more than 15 minutes) give out a result.

Any comment?
Thanks
Sam

> 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