Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
Query way to slow |
Fri, Aug 2 2013 3:08 PM | Permanent Link |
Tutulla | After the first two inner joins I get 250 results in 0.406 secs, but the full query takes overall almost 900 seconds to complete. Any idea how to optimize the search? I tried to make a select call within the WHERE clause, but DBISAM does not allow this.
And these are 900 seconds for 7 days. At the end I need to run this for a full year. Any optimization would help: ================================================================================ SQL statement (Executed with 4.29 Build 3) ================================================================================ SELECT VD_AB.Menge AS Auftragsmenge, Artikel.Artikelnummer AS Artikelnummer, Artikel.Artikelbezeichnung_text AS Bezeichnung, V.Berechnungsdatum AS Datum FROM Artikel INNER JOIN Vorgangdetails AS VD_AB ON VD_AB.Auftrag LIKE 'AB-%' AND Artikel.Artikelnummer = VD_AB.Artikel INNER JOIN Vorgaenge AS V ON V.Berechnungsdatum >= '2013-07-01' AND V.Berechnungsdatum <= '2013-07-07' AND V.Nummer = VD_AB.Auftrag LEFT JOIN Vorgangdetails AS VD_L ON VD_AB.Artikel = VD_L.Artikel LEFT JOIN Vorgangdetails AS VD_RvL ON VD_L.Artikel = VD_RvL.Artikel WHERE (Artikel.Kategorie = 'KARSTADT->Aktive') AND V.Berechnet = TRUE AND VD_AB.RecId = VD_L.WeiterfuehrungVonPos AND (VD_L.Auftrag LIKE 'RE-%' OR (VD_L.Auftrag LIKE 'LF-%' AND VD_L.RecID = VD_RvL.WeiterfuehrungVonPos )) /**/ JOINOPTIMIZECOSTS Tables Involved --------------- Artikel (Artikel) table opened shared, has 12718 rows Vorgangdetails (VD_AB) table opened shared, has 203117 rows Vorgaenge (V) table opened shared, has 49027 rows Vorgangdetails (VD_L) table opened shared, has 203117 rows Vorgangdetails (VD_RvL) table opened shared, has 203117 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows WHERE Clause Execution ---------------------- The expression: Artikel.Kategorie = 'KARSTADT->Aktive' is OPTIMIZED, covers 914 rows or index keys, costs 135637 bytes, and will be applied to the Artikel table (Artikel) before any joins The expression: VD_AB.Auftrag LIKE 'AB-%' is OPTIMIZED, covers 48453 rows or index keys, costs 7122591 bytes, and will be applied to the Vorgangdetails table (VD_AB) before any joins The expression: V.Berechnungsdatum >= '2013-07-01' AND V.Berechnungsdatum <= '2013-07-07' is OPTIMIZED, covers 3480 rows or index keys, costs 627530 bytes, and will be applied to the Vorgaenge table (V) before any joins Join Ordering ------------- The driver table is the Artikel table (Artikel) The Artikel table (Artikel) is joined to the Vorgangdetails table (VD_AB) with the INNER JOIN expression: Artikel.Artikelnummer = VD_AB.Artikel The Vorgangdetails table (VD_AB) is joined to the Vorgaenge table (V) with the INNER JOIN expression: VD_AB.Auftrag = V.Nummer The Vorgangdetails table (VD_AB) is joined to the Vorgangdetails table (VD_L) with the LEFT OUTER JOIN expression: VD_AB.Artikel = VD_L.Artikel The Vorgangdetails table (VD_L) is joined to the Vorgangdetails table (VD_RvL) with the LEFT OUTER JOIN expression: VD_L.Artikel = VD_RvL.Artikel 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 Optimized Join Ordering ----------------------- The driver table is the Vorgaenge table (V) The Vorgaenge table (V) is joined to the Vorgangdetails table (VD_AB) with the INNER JOIN expression: V.Nummer = VD_AB.Auftrag The Vorgangdetails table (VD_AB) is joined to the Artikel table (Artikel) with the INNER JOIN expression: VD_AB.Artikel = Artikel.Artikelnummer The Vorgangdetails table (VD_AB) is joined to the Vorgangdetails table (VD_L) with the LEFT OUTER JOIN expression: VD_AB.Artikel = VD_L.Artikel The Vorgangdetails table (VD_L) is joined to the Vorgangdetails table (VD_RvL) with the LEFT OUTER JOIN expression: VD_L.Artikel = VD_RvL.Artikel The expression: V.Berechnet = TRUE is UN-OPTIMIZED and will be applied to each candidate row in the Vorgaenge table (V) as the result set is generated Scan Expression Execution ------------------------- The expression: VD_AB.RecId = VD_L.WeiterfuehrungVonPos AND VD_L.Auftrag LIKE 'RE-%' OR VD_L.Auftrag LIKE 'LF-%' AND VD_L.RecID = VD_RvL.WeiterfuehrungVonPos is UN-OPTIMIZED and will be applied to each candidate row in the result set as the result set is generated 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: V.Nummer = VD_AB.Auftrag is OPTIMIZED and is estimated to cost 6321 bytes per candidate row The expression: VD_AB.Artikel = Artikel.Artikelnummer is OPTIMIZED and is estimated to cost 72 bytes per candidate row The expression: VD_AB.Artikel = VD_L.Artikel is OPTIMIZED and is estimated to cost 32536 bytes per candidate row The expression: VD_L.Artikel = VD_RvL.Artikel is OPTIMIZED and is estimated to cost 32536 bytes per candidate row ================================================================================ >>>>> 5 rows affected in 882,235 seconds ================================================================================ |
Sat, Aug 3 2013 10:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tutulla
I may be wrong but if you look at the execution plan you'll see its saying that the first two joins are optimised, this isn't the case for the others. Try adding indices for each field used in the where clause. If that doesn't work it may be worth looking at a script, selecting the wanted rows from the linked tables into memory tables and then just joining those. Roy Lambert [Team Elevate] |
Sat, Aug 3 2013 10:52 AM | Permanent Link |
Tutulla | Roy Lambert wrote:
... Try adding indices for each field used in the where clause. If that doesn't work it may be worth looking at a script, selecting the wanted rows from the linked tables into memory tables and then just joining those. Setting indices: You mean alter the table? It is not my table, I am just trying to grab data from the table. Scripting: I need to grab the data from within FastReport. So you mean I should grab the first two joins and then run another query on each result? I am not sure if I understand you correctly. |
Sun, Aug 4 2013 5:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tutulla
>Setting indices: You mean alter the table? It is not my table, I am just trying to grab data from the table. That's exactly what I mean. When you read something like <<is UN-OPTIMIZED and will be applied to each candidate row in the result set as the result set is generated>> in the execution plan it means either there isn't an index available or it can't be used and, therefore, the query has to work through the table ie slow. You can also try adding NOJOINOPTIMISE to the end of the sql to see if that makes a difference. >Scripting: I need to grab the data from within FastReport. So you mean I should grab the first two joins and then run another query on each result? > >I am not sure if I understand you correctly. I have no idea what restrictions running the query from within FastReport will place on you. In DBISAM a script is a series of sql statements separated by a semi-colon. Sometimes it can speed up a query by doing part of the work in a different way eg SELECT * FROM Vorgaenge INTO Memory\V WHERE.Berechnungsdatum >= '2013-07-01' AND V.Berechnungsdatum <= '2013-07-07' will produce a memory table containing just those entries between those dates. Then the join would become INNER JOIN Memory\V AS V ON V.Nummer = VD_AB.Auftrag I have no idea if it will be faster or not though you'll have to try it and see. You'll also need a DROP Memory\V statement at the end of the script otherwise you end up with a memory leak. The syntax may not be totally correct since its a while since I used DBISAM Roy Lambert [Team Elevate] |
Sun, Aug 4 2013 2:33 PM | Permanent Link |
Tutulla | Memory did the job. Great - thanks.
But I cannot call DROP TABLE Memory\V. It always crashes. |
Mon, Aug 5 2013 3:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tutulla
>Memory did the job. Great - thanks. Good >But I cannot call DROP TABLE Memory\V. It always crashes. Probably because, thinking about it Memory\V is still in use in your main query. You'll have to drop memory tables when the report is complete. Roy Lambert [Team Elevate] |
This web page was last updated on Monday, May 6, 2024 at 12:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |