Hi,

The below query takes less than a second with DBISAM 3 and take 224 seconds with EDB!!!  I am running the same query against convert DB from
DBISAM3.  Why is so slow?

NOTE:  tbItems is a memory table while the rest are normal tables.  Both databases have the same data, same fields, and same indexes.


SELECT TB_Invoices.BranchSysNo,
      TB_Invoices.InvoiceSysNo,
      TB_Invoices.InvoiceNo,
      TB_Invoices.InvoiceDate,
      TB_Invoices.Address,
      TB_Sales.ItemSysNo,
      TB_Sales.UnitSysNo,
      TB_Sales.SalesPrice,
      TB_Sales.SalesQuantity,
      TB_Sales.ReturnQuantity,
      TB_Sales.ItemDiscount,

      tbItems.ItemNo,
      tbItems.ItemName_A,
      tbItems.ItemName_E,

      TB_Units.UnitName

FROM   TB_Invoices,TB_Sales,MEMORY tbItems,TB_Units

WHERE  TB_Sales.InvoiceSysNo     = TB_Invoices.InvoiceSysNo

AND    tbItems.ItemSysNo         = TB_Sales.ItemSysNo

AND    TB_Units.UnitSysNo        = TB_Sales.UnitSysNo


AND TB_Invoices.InvoiceType = 0
AND TB_Invoices.YearSysNo = 3
AND TB_Invoices.BranchSysNo = 1
AND TB_Sales.BranchSysNo = 1
AND TB_Units.BranchSysNo = 1
AND TB_Invoices.BranchSysNo = 1
AND TB_Invoices.InvoiceDate <= 20070710
ORDER BY tbItems.ItemNo
NOJOINOPTIMIZE

Abdulaziz,

<< The below query takes less than a second with DBISAM 3 and take 224
seconds with EDB!!!  I am running the same query against convert DB from
DBISAM3.  Why is so slow? >>

Generate a query execution plan and post it here.  That should give me
something to at least go by to start with.

--
Tim Young
Elevate Software
www.elevatesoft.com

I've made some changes. tbItems is no longer a memory table.  It is a normal table now.  But thing are getting worse.  Here is the execution plan.


================================================================================
SQL Query (Executed by ElevateDB 1.04 Build 3)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.

================================================================================

SELECT ALL
"TB_Invoices"."BranchSysNo" AS "BranchSysNo",
"TB_Invoices"."InvoiceSysNo" AS "InvoiceSysNo",
"TB_Invoices"."InvoiceNo" AS "InvoiceNo",
"TB_Invoices"."InvoiceDate" AS "InvoiceDate",
"TB_Invoices"."Address" AS "Address",
"TB_Sales"."ItemSysNo" AS "ItemSysNo",
"TB_Sales"."UnitSysNo" AS "UnitSysNo",
"TB_Sales"."SalesPrice" AS "SalesPrice",
"TB_Sales"."SalesQuantity" AS "SalesQuantity",
"TB_Sales"."ReturnQuantity" AS "ReturnQuantity",
"TB_Sales"."ItemDiscount" AS "ItemDiscount",
"TB_Items"."ItemNo" AS "ItemNo",
"TB_Items"."ItemName_A" AS "ItemName_A",
"TB_Items"."ItemName_E" AS "ItemName_E",
"TB_Units"."UnitName" AS "UnitName"
FROM "TB_Invoices", "TB_Sales", "TB_Items", "TB_Units"
WHERE "TB_Invoices"."InvoiceType" = 0 AND "TB_Invoices"."YearSysNo" = 3 AND
"TB_Invoices"."BranchSysNo" = 1 AND "TB_Invoices"."BranchSysNo" = 1 AND
"TB_Invoices"."InvoiceDate" <= 20070710 AND "TB_Sales"."BranchSysNo" = 1 AND
"TB_Units"."BranchSysNo" = 1 AND "TB_Sales"."InvoiceSysNo" = "TB_Invoices"."InvoiceSysNo" AND
"TB_Items"."ItemSysNo" = "TB_Sales"."ItemSysNo" AND "TB_Units"."UnitSysNo" =
"TB_Sales"."UnitSysNo"
ORDER BY "TB_Items"."ItemNo"
NOJOINOPTIMIZE

Source Tables
-------------

TB_Invoices: 439 rows
TB_Sales: 406 rows
TB_Items: 2856 rows
TB_Units: 150 rows

Result Set
----------

The result set was static
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the TB_Invoices table:

"TB_Invoices"."InvoiceType" = 0 [Index scan: 189 keys, 4096 bytes estimated
cost]

The following filter condition was applied to the TB_Invoices table:

"TB_Invoices"."YearSysNo" = 3 [Index scan: 439 keys, 4096 bytes estimated cost]

The following filter condition was applied to the TB_Invoices table:

"TB_Invoices"."BranchSysNo" = 1 [Index scan: 430 keys, 4096 bytes estimated
cost]

The following filter condition was applied to the TB_Invoices table:

"TB_Invoices"."BranchSysNo" = 1 [Index scan: 430 keys, 4096 bytes estimated
cost]

The following filter condition was applied to the TB_Invoices table:

"TB_Invoices"."InvoiceDate" <= 20070710 [Index scan: 440 keys, 12288 bytes
estimated cost]

The following filter condition was applied to the TB_Sales table:

"TB_Sales"."BranchSysNo" = 1 [Index scan: 0 keys, 4096 bytes estimated cost]

The following filter condition was applied to the TB_Units table:

"TB_Units"."BranchSysNo" = 1 [Index scan: 25 keys, 4096 bytes estimated cost]

The following filter condition was applied to the result set rows as they were
generated:

"TB_Sales"."InvoiceSysNo" = "TB_Invoices"."InvoiceSysNo" AND
"TB_Items"."ItemSysNo" = "TB_Sales"."ItemSysNo" AND "TB_Units"."UnitSysNo" = "TB_Sales"."UnitSysNo"

Joins
-----

The driver table was the TB_Invoices table

The NOJOINOPTIMIZE clause was used and the optimizer left the joins in their
declared order

================================================================================
192 row(s) returned in 2178.063 secs
================================================================================

Abdulaziz,

<< The following filter condition was applied to the result set rows as they
were generated:

"TB_Sales"."InvoiceSysNo" = "TB_Invoices"."InvoiceSysNo" AND
"TB_Items"."ItemSysNo" = "TB_Sales"."ItemSysNo" AND "TB_Units"."UnitSysNo"
= "TB_Sales"."UnitSysNo" >>

See here:

http://www.elevatesoft.com/edb1d7_statements.htm

Under the SELECT statement:

"ElevateDB does not optimize join expressions in the WHERE clause, otherwise
known as SQL-89 style joins. You must use the JOIN clause in order to have
ElevateDB optimize the joins."

Re-phrase the query using the INNER JOIN syntax and you'll be all set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,
<<Re-phrase the query using the INNER JOIN syntax and you'll be all set.>>

I've made the changes and now it works faster than DBISAM3.  Thanks...

Woow, I've changed other quires to use the JOIN clause and I can see what you said.  In fact I can read the future of the EDB.  EDB is becoming a
BIG DB ENGINE (with a small footprint) following the world wide standards.  Although I don't like changing my code which was working perfectly for
a couple of years (changing a lot of code and quires) but I like the new performance.

Abdulaziz,

<< Woow, I've changed other quires to use the JOIN clause and I can see what
you said.  In fact I can read the future of the EDB.  EDB is becoming a BIG
DB ENGINE (with a small footprint) following the world wide standards.
Although I don't like changing my code which was working perfectly for a
couple of years (changing a lot of code and quires) but I like the new
performance. >>

I'm glad that you're seeing an improvement.   Also, it can get a bit faster
still and will when I make some further improvements.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<I'm glad that you're seeing an improvement.   Also, it can get a bit faster
still and will when I make some further improvements. >>


Keep the good work