Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Fairly simple SQL running slow, although segments run fast ... why? |
Thu, Oct 5 2006 9:12 AM | Permanent Link |
adam | 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 |
Thu, Oct 5 2006 9:38 AM | Permanent Link |
"Robert" | "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 |
Thu, Oct 5 2006 4:03 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |