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
================================================================================

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]

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.

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]

Memory did the job. Great - thanks.

But I cannot call DROP TABLE Memory\V. It always crashes.

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]