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
================================================================================

"Tony Pomfrett" <tonyp@aline.com.au> wrote in message
news:E7325EDB-5233-449B-8C78-73DDEB5DAF72@news.elevatesoft.com...
> 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))
>


I'm not understanding something. Why the subquery?

SELECT * FROM APPOINTMENT A
inner join ResourceEvents R on (R.EventID = A.ID)
where R.ResourceID in (52, 96, 128, 115, 363, 106))

It will still be slow, it needs an index on r.eventid, but it should be a
lot better

Robert

Hi Robert,

Thanks for responding. I'll try to explain.

The tables are for use with a calendar application using the DevEx scheduler.
The scheduler stores "resources" (in my case people) in a Blob field of the Appointment table. I don't know how to query a blob field using SQL - I don't
think it's possible. So, in order that I can retrieve appointments that relate to specific people, I have to store that info separately. Hence the
ResourceEvents table, which has just 2 fields - ResourceID and EventID (where Event is an Appointment).

OK, now when I need to display appointments for person X, we select all records from ResourceEvents which refer to person X. Then we select all
appointments that are contained in that subquery. There probably is a better way to do it. This query was set up a couple of years ago when I knew even
less than I do now

I tried using an index on EventID in my development system (which has a much smaller database) but the resulting plan was exactly the same and there
was no difference in performance times.

Robert,

Perhaps it has more to do with the server load?

At present my query is running at around 11 seconds and yours is running at around 6 seconds. Although yours returns 1200 more records so the two
queries are not equivalent. So the query times have altered substantially as the day goes on and this is without a second index on the Resource Events
table.

I could shift the DBISAM server and database to another, more powerful and less loaded, server but external clients would still have to tunnel through the
present server to get to the new server - so I'm not sure what the end result of that would be. Internal clients could go direct to the new server so they
should notice a big improvement.

The present server is an SBS03 premium box running ISA server and Exchange for 70 users, so it cops a workout although it does surprisingly well.

"Tony Pomfrett" <tonyp@aline.com.au> wrote in message
news:CEC4271D-1CC2-45BA-B611-C2BE743A63E4@news.elevatesoft.com...
> Robert,
>
> Perhaps it has more to do with the server load?
>
> At present my query is running at around 11 seconds and yours is running
> at around 6 seconds. Although yours returns 1200 more records so the two
> queries are not equivalent.

I'd say the're not Anyway, my query will return all matches, so if
there is a match on more than one resoruce ID, you get more than one rows
returned for the same id. If that's not what you want, add a DISTINCT to the
query.

Robert

Robert,

It doesn't seem to be possible to use DISTINCT with that query because one of the fields is a BLOB. Is there a way to limit the DISTINCT qualifier to one
field only (ID)? Maybe that's why I used the subquery.

If I modify your query to only return the ID:

SELECT DISTINCT ID FROM Appointment etc...

it returns exactly the same number of records as my query so the extra records must be duplicates.

"Tony Pomfrett" <tonyp@aline.com.au> wrote in message
news:3CF6851F-904B-475B-B805-CDF096195AA1@news.elevatesoft.com...
> Robert,
>
> It doesn't seem to be possible to use DISTINCT with that query because one
> of the fields is a BLOB. Is there a way to limit the DISTINCT qualifier to
> one
> field only (ID)? Maybe that's why I used the subquery.

I'm assuming you need the blob. Otherwise, of course you can limit the
select to whatever fields you want.

>
> If I modify your query to only return the ID:
>
> SELECT DISTINCT ID FROM Appointment etc...
>
> it returns exactly the same number of records as my query so the extra
> records must be duplicates.
>

Fine. Then use a script. Select distinct into a memory table, create an
index on the memory table, then JOIN the memory table to your final select.

Robert