Re: replace subquery with left join

On Wed, 06 Nov 2002 17:14:27 +0100, Hans Sieghart <Hans.Sieghart_at_frischeis.at.nospam> wrote:

>I've tested all solutions. Isaac's solution doesn't work
>on DB2 (maybe the reference to P is not allowed in the
>subquery).

Isaac and I posted essentially the same thing. The reference to P should be allowed; P is in an outer scope. I don't think you can eliminate the subquery.

>The performance is still very poor. Maybe it's because
>the prices-table is too big and complicated. (But I
>can't change it: it's from our ERP). Let me explain:

How long does it take to fetch the current price for a single item?

For testing, can you replace the expression (DAYS(CURDATE())-693594) with a constant? (I'd expect DB2 to evaluate that expression only once, but maybe I'm being foolishly optimistic.) If you can create indexes, can you create an index on each column used in the WHERE clauses?

[snip]
>It's awfully slow:
>There are about 24000 records in the items-table and about
>170000 records in the prices-table and a query lasts circa
>50 min!

I loaded 24000 consecutive numbers into Items, and generated 170000 rows of random data for prices. (Using the tables I created earlier--they don't have the columns PrList, Scale, and so on.) Then I created a view based on the query I posted earlier. Using Batch SQL, I can get the current price for a single item in less than a second. Using a qraphical query builder, it takes about a second. (Mimer SQL, no indexes, no real load on the db server.)

Using a QBE interface, I fetched the entire 24000 row result set in about 1.5 minutes. Of course, I have only two conditions in my subquery. If I get time, I'll try it with the structure you just posted.

-- 
Mike Sherrill
Information Management Systems