Hi,

I have recently converted to DBISam. I have managed to optimize all my queries but I am having a speed issue with a query with a join and a sub-query. It takes 57.second to execute. Please can you see what could be wrong?

Query :

SELECT s.Suppl_Id, s.Pastel_Export_Suppl_Code, s.Current_Disc, s."A30day_Disc", s."A60day_Disc",  i.Outwork_YN, s.NO_VAT_YN, i.Inv_date, i.Inv_no, i.Amount, i.AmtLessDisc, i.AmtPayable, i.Disc_Rate, i.AP_Order_No, i.Export_Date, i.code, i.job_no
FROM Supplier_Table s
INNER JOIN Invoice_table i
ON (s.suppl_Id = I.suppl_id)
WHERE Cancel_Date IS NULL
AND i.Export_Date IS NULL
OR Inv_No IN (SELECT Inv_No FROM AP_Inv_Exported WHERE (Accepted_Into_Accpac_YN = FALSE OR Accepted_Into_Accpac_YN IS NULL))

Zane


Can you run the query in DBSys and post the plan. It would also be helpful if you ran the sub-query in DBSys and posted the plan for that.

General guidance is that you don't have an index for all of the WHERE conditions / JOINS but its difficult to say without the necessary information.

It may also be worthwhile to run the subquery to generate an in-memory table and then query against that.

Roy Lambert [Team Elevate]

================================================================================
SQL statement (Executed with 4.31 Build 3)
================================================================================

SELECT s.Suppl_Id, s.Pastel_Export_Suppl_Code, s.Current_Disc, s."A30day_Disc",
s."A60day_Disc",  i.Outwork_YN, s.NO_VAT_YN, i.Inv_date, i.Inv_no, i.Amount,
i.AmtLessDisc, i.AmtPayable, i.Disc_Rate, i.AP_Order_No, i.Export_Date, i.code,
i.job_no

FROM Invoice_table i

INNER JOIN Supplier_Table s

ON (s.suppl_Id = I.suppl_id)

WHERE Cancel_Date IS NULL

AND i.Export_Date IS NULL

OR Inv_No IN (SELECT Inv_No FROM AP_Inv_Exported WHERE (Accepted_Into_Accpac_YN
= FALSE OR Accepted_Into_Accpac_YN IS NULL))

--------------------------------------------------------------------------------
Sub-query
--------------------------------------------------------------------------------

SELECT Inv_No FROM AP_Inv_Exported WHERE (Accepted_Into_Accpac_YN = FALSE OR
Accepted_Into_Accpac_YN IS NULL)

Tables Involved
---------------

AP_Inv_Exported (AP_Inv_Exported) table opened shared, has 26471 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Result set will be ordered by the following column(s) using a case-sensitive
temporary index:

Inv_No ASC

WHERE Clause Execution
----------------------

The expression:

Accepted_Into_Accpac_YN = FALSE OR Accepted_Into_Accpac_YN IS NULL

is UN-OPTIMIZED and will be applied to each candidate row in the
AP_Inv_Exported table (AP_Inv_Exported) as the result set is generated

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

Tables Involved
---------------

Invoice_table (i) table opened shared, has 26471 rows
Supplier_Table (s) table opened shared, has 668 rows

Result Set Generation
---------------------

Result set will be canned

Result set will consist of one or more rows

Join Ordering
-------------

The driver table is the Invoice_table table (i)



The Invoice_table table (i) is joined to the Supplier_Table table (s) with the
INNER JOIN expression:



I.suppl_id = s.suppl_Id

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 Supplier_Table table (s)



The Supplier_Table table (s) is joined to the Invoice_table table (i) with the
INNER JOIN expression:



s.suppl_Id = I.suppl_id

Scan Expression Execution
-------------------------

The expression:

Cancel_Date IS NULL  AND i.Export_Date IS NULL  OR Inv_No IN SELECT Inv_No FROM
AP_Inv_Exported WHERE (Accepted_Into_Accpac_YN = FALSE OR
Accepted_Into_Accpac_YN IS NULL)

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 NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the
optimizer to consider costs when optimizing this join

The expression:

s.suppl_Id = I.suppl_id

is UN-OPTIMIZED

Sub-Query Execution
-------------------

The expression:

Inv_No IN SELECT Inv_No FROM AP_Inv_Exported WHERE (Accepted_Into_Accpac_YN =
FALSE OR Accepted_Into_Accpac_YN IS NULL)

is OPTIMIZED and is estimated to cost 0 bytes per candidate row

================================================================================
>>>>> 0 rows affected in 57.252 seconds
================================================================================

How would I do the following suggestion?

"It may also be worthwhile to run the subquery to generate an in-memory table and then query against that."

I am writing in Delphi.

Zane


Try creating indices on Inv_No, Accepted_Into_Accpac_YN, suppl_id (both in Invoice_table and Supplier_Table), Cancel_Date, Export_Date

Roy Lambert [Team Elevate]

Zane


You create a script along the lines of

DROP TABLE IF EXISTS Memory\Temp1;

SELECT Inv_No FROM AP_Inv_Exported WHERE (Accepted_Into_Accpac_YN = FALSE OR Accepted_Into_Accpac_YN IS NULL INTO Memory\Temp1;

SELECT s.Suppl_Id, s.Pastel_Export_Suppl_Code, s.Current_Disc, s."A30day_Disc",
s."A60day_Disc",  i.Outwork_YN, s.NO_VAT_YN, i.Inv_date, i.Inv_no, i.Amount,
i.AmtLessDisc, i.AmtPayable, i.Disc_Rate, i.AP_Order_No, i.Export_Date, i.code,
i.job_no

FROM Invoice_table i

INNER JOIN Supplier_Table s

ON (s.suppl_Id = I.suppl_id)

WHERE Cancel_Date IS NULL

AND i.Export_Date IS NULL

OR Inv_No IN (SELECT Inv_No FROM Memory\Temp1);

and remember to DROP the temporary memory table AFTER you've used the result set

Roy Lambert [Team Elevate]

Hi Roy,

Thanks for your help..
I tried your suggestions. The only thing that worked eventually is to add an index to supplier table on suppl_id.