I have the following 2 part SQL statement:



SELECT

 IF(Current = true then "" else "zzz ") + P.Name + " (" + P.ProdCode + ")" as Name,
 P.ID

FROM CustomerProduct C
LEFT JOIN Product P ON P.ID = C.ProductID

WHERE C.CustID IN

(

SELECT CustIDReceiving

FROM SalesInvoice SI

WHERE SI.ID = 17085

)


ORDER BY Name



--------------


If I run the internal section:


SELECT CustIDReceiving

FROM SalesInvoice SI

WHERE SI.ID = 17085

it takes 0.204 secords to run.




If I run the external section:


SELECT

 IF(Current = true then "" else "zzz ") + P.Name + " (" + P.ProdCode + ")" as Name,
 P.ID

FROM CustomerProduct C
LEFT JOIN Product P ON P.ID = C.ProductID

WHERE C.CustID = 2821

It takes just 0.047 seconds!

Both of which are fantastic.

------


When I put the 2 together into a single script they take about 12 seconds ... which is
not so fantastic.




I thought that nested SQL basically ran as a series, with 1 section running straight into
the next, so the total time to run ought to be roughly equal to the sum of the times for
each part.

Any ideas why this is not the case here?

(I am using 3.30)

PS:

Rewriting it as:


SELECT

 IF(Current = true then "" else "zzz ") + P.Name + " (" + P.ProdCode + ")" as Name,
 P.ID

FROM CustomerProduct C
LEFT JOIN Product P ON P.ID = C.ProductID
LEFT JOIN SalesInvoice SI ON SI.CustIDReceiving = C.CustID

WHERE  SI.ID = 17127

ORDER BY Name


Actually takes even longer ... 17 seconds.

And re-writing it as:

SELECT ID, CustIDReceiving as CustID

INTO "C:\DBdata\DB\CustID.dat"

FROM SalesInvoice SI

WHERE SI.ID = %s
;

SELECT DISTINCT

 IF(Current = true then "" else "zzz ") + P.Name + " (" + P.ProdCode + ")" as Name,
 P.ID

FROM CustomerProduct C
LEFT JOIN Product P ON P.ID = C.ProductID
LEFT JOIN "C:\DBdata\DB\CustID.dat" CID ON CID.CustID = C.CustID

ORDER BY Name


Runs in 1.2 seconds!!!

Any suggestions out there of ways of getting even closer to the 0.47 seconds that seems
the potential minimum???


Adam

adam


I seem to remember that the sub SELECT runs once for each row in the outside table so ......

Roy Lambert

Adam,

<< I thought that nested SQL basically ran as a series, with 1 section
running straight into the next, so the total time to run ought to be roughly
equal to the sum of the times for each part. >>

Nope.  The sub-select must be run for every row in the CustomerProduct
table, just like a join.  Also, you could try this to speed things up:

SELECT
 IF(Current = true then "" else "zzz ") + P.Name + " (" + P.ProdCode + ")"
as Name,
 P.ID
FROM CustomerProduct C
LEFT JOIN Product P ON P.ID = C.ProductID
WHERE C.CustID IN
(
SELECT CustIDReceiving
FROM SalesInvoice SI
WHERE SI.ID = 17085
ORDER BY 1
)
ORDER BY Name

--
Tim Young
Elevate Software
www.elevatesoft.com