The Following runs in 0.016 seconds:

SELECT
 P.ProdCode,
 P.Name,
 CP.CustSuppProdCode

FROM CustomerProduct CP
LEFT JOIN Product P ON (CP.ProductID = P.ID)

WHERE CP.CustID =3231

--

And the following runs in "0" seconds (according to DBSys)

SELECT CustID FROM StockPurchase WHERE ID = 13586

--

BUT the following takes 7.5 seconds to run:

SELECT
 P.ProdCode,
 P.Name,
 CP.CustSuppProdCode

FROM CustomerProduct CP
LEFT JOIN Product P ON (CP.ProductID = P.ID)

WHERE CP.CustID IN
(
SELECT CustID FROM StockPurchase WHERE ID = 13586
)

--

Even though it is really only a concatenation of the first 2.

I am running DBISAM 3.30

--

* Is there a better way of writing the final SQL?
... given that I have to use the StockPurchase ID field (i.e. at run time I don't know
the CustID, and can only find it via the StockPurchase ID).

* Is this something that is better managed in v4 (v5?)

Adam

"adam" <adam@nospamplease.fmfoods.co.uk> wrote in message
news:789EC481-129E-496C-97A8-9491498FFE9B@news.elevatesoft.com...
> The Following runs in 0.016 seconds:
>
> SELECT
>  P.ProdCode,
>  P.Name,
>  CP.CustSuppProdCode
>
> FROM CustomerProduct CP
> LEFT JOIN Product P ON (CP.ProductID = P.ID)
>
> WHERE CP.CustID =3231
>
> --
>
> And the following runs in "0" seconds (according to DBSys)
>
> SELECT CustID FROM StockPurchase WHERE ID = 13586
>
> --
>
> BUT the following takes 7.5 seconds to run:
>
> SELECT
>  P.ProdCode,
>  P.Name,
>  CP.CustSuppProdCode
>
> FROM CustomerProduct CP
> LEFT JOIN Product P ON (CP.ProductID = P.ID)
>
> WHERE CP.CustID IN
> (
> SELECT CustID FROM StockPurchase WHERE ID = 13586
> )
>

Can't you just JOIN StockPurchare?
> SELECT
>  P.ProdCode,
>  P.Name,
>  CP.CustSuppProdCode
>
> FROM CustomerProduct CP
> LEFT JOIN Product P ON (CP.ProductID = P.ID)

JOIN StockPurchase SP ON SP.CustID = CP.CustID
WHERE SP.ID = 13586

My experience is that in most cases, subqueries in V 3 are absolute dogs.
One always ends up recoding as JOINS or scripts.

Robert

Adam,

<< Even though it is really only a concatenation of the first 2. >>

Not exactly - you're adding a sub-query comparison in the new version.   Is
there an index on the CustID column in the StockPurchase table ?  If not,
then either add one or specify the query like this:

SELECT
 P.ProdCode,
 P.Name,
 CP.CustSuppProdCode

FROM CustomerProduct CP
LEFT JOIN Product P ON (CP.ProductID = P.ID)

WHERE CP.CustID IN
(
SELECT CustID FROM StockPurchase WHERE ID = 13586 ORDER BY CustID
)

--
Tim Young
Elevate Software
www.elevatesoft.com