Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
Very SLOW query. |
Tue, Jul 10 2007 2:17 PM | Permanent Link |
Abdulaziz Jasser | 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 |
Tue, Jul 10 2007 6:28 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Jul 10 2007 7:24 PM | Permanent Link |
Abdulaziz Jasser | 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 ================================================================================ |
Wed, Jul 11 2007 7:56 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Sun, Jul 15 2007 4:20 AM | Permanent Link |
Abdulaziz Jasser | 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... |
Sun, Jul 15 2007 4:41 PM | Permanent Link |
Abdulaziz Jasser | 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. |
Mon, Jul 16 2007 4:50 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jul 18 2007 5:11 PM | Permanent Link |
Abdulaziz Jasser | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |