Re: replace subquery with left join

Hi, Igor!

Igor Korolyov <k1i2v3_at_km.ru> wrote:
> Such kind of queries MUST be slow - your server must execute subquery for
> each record in main query. So in your case - server must execute subquery
> 24000 times. Of course it IS slowly. If you can create cursor (or maybe
> temporary table) in remote side (on SQL server) you can improve performance
> very much. Just select appropriate records from pricelist table on first
> stage, and then make left join with items table.
> something like this.
>
> 1) SELECT ItemId, MAX(ValDate) AS dLastDate
> From PriceList
> WHERE (ValDate limit condition and other PriceList limit conditions)
> GROUP BY ItemId
> INTO CURSOR tmp1
>
> 2) SELECT Item.ItemId, Item.OtherData, PriceList.Price
> FROM Item
> LEFT JOIN tmp1
> ON Item.ItemId = tmp1.ItemId
> LEFT JOIN PriceList
> ON PriceList.ItemId = tmp1.ItemId AND
> PriceList.ValDate = tmp1.dLastDate
> WHERE (Item limit condition if have such)

I think that would be fast. Unfortunaly I am not able to to anything on the DB2-server cause I simply don't know anything about it.

I've tried it now the whole day. I can create a view on the server, but only with fixed values and not with parameters. That would help me with exactly 1 branch/pricelist/etc.-combination (or I'll create a view for each combination ;-)).

It would help me, if I could create the view temporarely via ODBC, but it seems to me that the Visual FoxPro-command SQLPREPARE() (and SQLEXEC()) only works with SELECT-statements. Because if i put the same statement (CREATE VIEW...) in the DB2-terminal it works.

-Hans Received on Thu Nov 07 2002 - 18:05:56 CET