Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 2 of 2 total
Thread Slow query - advice?
Sun, Nov 8 2009 6:09 PMPermanent 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 PMPermanent Link

Tony Pomfrett
Sorry, I should have posted this in the SQL group - I'll do so now.
Image