Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
SQL takes Hours |
Fri, Nov 14 2008 9:28 AM | Permanent Link |
Malcolm | The following SQL has yet to complete and I have left it running for 3+ hours (so do not have a plan as yet). Can anyone please give me some
pointers as to how to optimise. There are indexes on ProductCode LocationNo Field1 & JJWLocNo on all relevant tables. I have verified ProductDay. Product has 14500 Records ProductDetail has 750,000 Records ProductDay has 3.5million Records CstPrc has 13500 Records JJWLocation has 51 Records Any pointers will be much appreciated. Select D.JJWName, D1.Field1, D2.Description, Sum(D3.SalesQty) as Quantity, D4.RetailPrice1, Sum(D3.SalesValue) as TotVal, D1.Field2 From Product D2 JOIN ProductDetail D4 ON D2.ProductCode = D4.ProductCode JOIN ProductDay D3 ON D4.ProductCode = D3.ProductCode AND D3.LocationNo = D4.LocationNo JOIN JJWLocation D ON D3.LocationNo = D.JJWLocNo JOIN CstPrc D1 ON D4.FreeText1 = D1.Field1 Where D1.Field1 LIKE 'I01%' GROUP BY D.JJWName, D1.Field1, D2.Description, D4.RetailPrice1, D1.Field2 ORDER BY D.JJWName Regards Malcolm |
Fri, Nov 14 2008 9:58 AM | Permanent Link |
"Eduardo [HPro]" | Malcolm
Try adding NOJOINOPTIMIZE at the end of SQL statement and see if it helps. Eduardo |
Fri, Nov 14 2008 10:58 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Malcolm
You don't mention indices on these JOIN CstPrc D1 ON D4.FreeText1 = D1.Field1 Where D1.Field1 LIKE 'I01%' GROUP BY D.JJWName, D1.Field1, D2.Description, D4.RetailPrice1, D1.Field2 ORDER BY D.JJWName also if you do have an index on D4.FreeText1 and D1.Field1 and they are case insensitive make sure to wrap them with UPPER. I'd also suggest trying without the LIKE because I think that would be generating a row scan use an IN test for a few values just to see. Roy Lambert [Team Elevate] |
Fri, Nov 14 2008 2:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Malcolm,
<< The following SQL has yet to complete and I have left it running for 3+ hours (so do not have a plan as yet). Can anyone please give me some pointers as to how to optimise. There are indexes on ProductCode LocationNo Field1 & JJWLocNo on all relevant tables. I have verified ProductDay. >> Do a reverse-engineer in DBSYS on the relevant tables, and then post the SQL code here. That will allow us to see what the situation is. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Nov 17 2008 5:56 AM | Permanent Link |
Malcolm | Hi All and may thanks for your responses.
I had missed an index on the FreeText1 field. I went back to basics and built up the SQL a bit at a time and now have it running in 8 to 10 minutes, which is a vast improvement. With the record counts I gave are these timeings what you would expect? I notice in the plan (below) that the The expression: D3.ProductCode = D1.ProductCode AND D3.LocationNo = D1.LocationNo is PARTIALLY-OPTIMIZED and is estimated to cost 32650 bytes per candidate row What does this imply? can it be improved? Many Thanks Malcolm ================================================================================ SQL statement (Executed with 4.26 Build 3) ================================================================================ select D2.JJWLOcNo, D4.Field1, D.Description, Sum(D3.SalesQTY)as Quantity, D1.RetailPrice1, Sum(D3.SalesValue)As TotVal, D4.Field2 FROM JJWLocation D2 INNER JOIN ProductDay D3 ON D2.JJWLocNo = D3.LocationNo INNER JOIN Product D ON D3.ProductCode = D.ProductCode INNER JOIN ProductDetail D1 ON D3.ProductCode = D1.ProductCode AND D3.LocationNo = D1.LocationNo INNER Join CstPrc D4 ON D1.FreeText1 = D4.Field1 WHERE D.ProductCode LIKE 'I01%' GROUP BY D2.JJWLocNo, D4.Field1, D.Description ORDER BY JJWLocNo, D4.Field1 JOINOPTIMIZECOSTS Tables Involved --------------- JJWLocation (D2) table opened shared, has 51 rows ProductDay (D3) table opened shared, has 3272361 rows Product (D) table opened shared, has 14351 rows ProductDetail (D1) table opened shared, has 731901 rows CstPrc (D4) table opened shared, has 13472 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: JJWLOcNo Field1 Description Result set will be ordered by the following column(s) using a case-sensitive temporary index: JJWLOcNo ASC Field1 ASC WHERE Clause Execution ---------------------- The expression: D.ProductCode LIKE 'I01%' is OPTIMIZED, covers 75 rows or index keys, costs 2100 bytes, and will be applied to the Product table (D) before any joins Join Ordering ------------- The driver table is the JJWLocation table (D2) The JJWLocation table (D2) is joined to the ProductDay table (D3) with the INNER JOIN expression: D2.JJWLocNo = D3.LocationNo The ProductDay table (D3) is joined to the Product table (D) with the INNER JOIN expression: D3.ProductCode = D.ProductCode The ProductDay table (D3) is joined to the ProductDetail table (D1) with the INNER JOIN expression: D3.ProductCode = D1.ProductCode AND D3.LocationNo = D1.LocationNo The ProductDetail table (D1) is joined to the CstPrc table (D4) with the INNER JOIN expression: D1.FreeText1 = D4.Field1 Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further Join Execution -------------- Costs ARE being taken into account when executing this join Remove the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to stop considering costs when optimizing this join The expression: D2.JJWLocNo = D3.LocationNo is OPTIMIZED and is estimated to cost 1330137 bytes per candidate row The expression: D3.ProductCode = D.ProductCode is OPTIMIZED and is estimated to cost 28 bytes per candidate row The expression: D3.ProductCode = D1.ProductCode AND D3.LocationNo = D1.LocationNo is PARTIALLY-OPTIMIZED and is estimated to cost 32650 bytes per candidate row The expression: D1.FreeText1 = D4.Field1 is OPTIMIZED and is estimated to cost 0 bytes per candidate row ================================================================================ >>>>> 1598 rows affected in 634.516 seconds ================================================================================ |
Mon, Nov 17 2008 8:18 AM | Permanent Link |
"Eduardo [HPro]" | Malcolm
Have you tested the SQL statement with NOJOINOPTIMIZE ? Eduardo |
Mon, Nov 17 2008 4:23 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Malcolm,
<< I had missed an index on the FreeText1 field. I went back to basics and built up the SQL a bit at a time and now have it running in 8 to 10 minutes, which is a vast improvement. With the record counts I gave are these timeings what you would expect? >> It's really hard to judge just by the record counts. The organization of the records in the table and all sorts of other issues can affect the timings in various ways. << I notice in the plan (below) that the The expression: D3.ProductCode = D1.ProductCode AND D3.LocationNo = D1.LocationNo > is PARTIALLY-OPTIMIZED and is estimated to cost 32650 bytes per candidate > row What does this imply? can it be improved? >> That could simply be from the JOINOPTIMIZECOSTS clause. I would try the query with and without that clause to see what the results are. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Nov 18 2008 10:03 AM | Permanent Link |
Malcolm | Hi Tim and Many thanks again
I was just wondering if the timings were in the right ball park or were way out, As I said 8 to 10 Mins is a great improvement and I can live with it but always looking to improve Re the Partialy optmised. I tried with and without the JOINOPTMISEDCOSTS and come down in fovour of using it by about 1.5 - 2 minutes. Appreciate all your imputs Regards Malcolm |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |