Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM Client/Server » View Thread |
Messages 1 to 2 of 2 total |
Slow query - advice? |
Sun, Nov 8 2009 6:09 PM | Permanent Link |
Tony Pomfrett | Hi,
I'm having trouble with a slow query (see Plan below) and I'm hoping somebody can offer a suggestion to speed it up. At the moment it's taking a couple of minutes to execute. There are only 2 tables: Appointment has a primary index on ID ResourceEvents has a primary index on ResourceID;EventID Thanks in advance. ================================================================================ SQL statement (Executed with 4.26 Build 3) ================================================================================ SELECT * FROM APPOINTMENT WHERE ID IN (select A.ID from Appointment A inner join ResourceEvents R on (R.EventID = A.ID) where ResourceID in (52, 96, 128, 115, 363, 106)) -------------------------------------------------------------------------------- Sub-query -------------------------------------------------------------------------------- select A.ID from Appointment A inner join ResourceEvents R on (R.EventID = A.ID) where ResourceID in (52, 96, 128, 115, 363, 106) Tables Involved --------------- Appointment (A) table opened shared, has 151766 rows ResourceEvents (R) table opened shared, has 173970 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: ID ASC WHERE Clause Execution ---------------------- The expression: ResourceID in (52 , 96 , 128 , 115 , 363 , 106) is OPTIMIZED, covers 15474 rows or index keys, costs 216636 bytes, and will be applied to the ResourceEvents table (R) before any joins Join Ordering ------------- The driver table is the Appointment table (A) The Appointment table (A) is joined to the ResourceEvents table (R) with the INNER JOIN expression: A.ID = R.EventID 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 ResourceEvents table (R) The ResourceEvents table (R) is joined to the Appointment table (A) with the INNER JOIN expression: R.EventID = A.ID 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: R.EventID = A.ID is OPTIMIZED -------------------------------------------------------------------------------- Tables Involved --------------- APPOINTMENT (APPOINTMENT) table opened shared, has 151766 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Scan Expression Execution ------------------------- The expression: ID IN select A.ID from Appointment A inner join ResourceEvents R on (R.EventID = A.ID) where ResourceID in (52, 96, 128, 115, 363, 106) is UN-OPTIMIZED and will be applied to each candidate row in the result set as the result set is generated Sub-Query Execution ------------------- The expression: ID IN select A.ID from Appointment A inner join ResourceEvents R on (R.EventID = A.ID) where ResourceID in (52, 96, 128, 115, 363, 106) is OPTIMIZED and is estimated to cost 13 bytes per candidate row ================================================================================ >>>>> 14237 rows affected in 119.266 seconds ================================================================================ |
Sun, Nov 8 2009 6:11 PM | Permanent Link |
Tony Pomfrett | Sorry, I should have posted this in the SQL group - I'll do so now.
|
This web page was last updated on Thursday, March 28, 2024 at 06:05 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |