Firebird SQL optimization

Question

Can this query be optimized any further?

SELECT
    s.event_id as "id",
    s.event_start as "start",
    p.treatment_id as "treatment_id",
    s.status_id as "status_id"
FROM
    SCHEDULEREVENT s
    LEFT JOIN PATIENT_HAS_EVENT p ON p.event_id = s.event_id
WHERE
    p.patient_id = 50

p.event_id is a Primary Key. There is an index on p.patient_id

Execution is taking around a second which is far too long for my needs.

EDIT: The best solution was to do this query backwards with changing LEFT JOIN to JOIN.

SELECT
    s.event_id as "id",
    s.event_start as "start",
    p.treatment_id as "treatment_id",
    s.status_id as "status_id"
FROM
    PATIENT_HAS_EVENT p 
    JOIN SCHEDULEREVENT s ON s.event_id = p.event_id
WHERE
    p.patient_id = 50

Average execution time has been brought down from 912 ms to 10.69 ms

As it stands the `LEFT JOIN` in your code is being implicitly converted back to an `INNER JOIN` by including a condition on the outer-joined table in the `WHERE` clause. Is this what you intended? — , Dec 16 '15 at 15:11
@MarkBannister I'm not very good at databases and I don't understand what you're saying. Are you suggesting to select from a table used in `WHERE` clause and then `JOIN` the other table? — Kuba Szymanowski, Dec 16 '15 at 15:17
Do you know what the query is *supposed* to be doing? Are you sure that this is what it *is* doing? — , Dec 16 '15 at 15:21
@MarkBannister Basically I want select all events assigned to a specific patient — Kuba Szymanowski, Dec 16 '15 at 15:22
Try changing `LEFT JOIN PATIENT_HAS_EVENT` to simply `JOIN PATIENT_HAS_EVENT` - is there any change in performance? Do you still return all the required records? — , Dec 16 '15 at 15:25
See also this answer: http://stackoverflow.com/questions/8311096/whats-the-difference-between-where-clause-and-on-clause-when-table-left-join/8311172#8311172 - for the effect of a `WHERE` clause condition on a left-joined query. — , Dec 16 '15 at 15:27
Thanks for your answer. This little change brought down average execution time from 912 ms to 12.5 ms and it provides the same results. Although @Harriv answer is about 2 ms faster. — Kuba Szymanowski, Dec 16 '15 at 18:15

score 3 · Accepted Answer · answered Dec 16 '15 at 15:23

3

Do it "backwards":

SELECT
    s.event_id as "id",
    s.event_start as "start",
    p.treatment_id as "treatment_id",
    s.status_id as "status_id"
FROM
    PATIENT_HAS_EVENT p 
    JOIN SCHEDULEREVENT s ON p.event_id = s.event_id
WHERE
    p.patient_id = 50

answered Dec 16 '15 at 15:23

Harriv

6,029
6
44
76

Thank you very much. Your answer brought down an average execution time from 912 ms to 12 ms. Is it the general rule to avoid `WHERE` clauses that reference to joined tables? – Kuba Szymanowski Dec 16 '15 at 18:08
Actually, above comment was based on `LEFT JOIN`. Using your proposal with `JOIN` it now runs for 10.69 ms at average. – Kuba Szymanowski Dec 16 '15 at 18:14
I don't think it's required to avoid WHERE, but you need to think what the engine is actually doing. I usually try to first select smallest amount of data possible and only after that start adding data from other tables. Using inner join would cause the engine probably do the same while maintaining the query maybe more logical. – Harriv Dec 17 '15 at 07:17

score 1 · Answer 2 · answered Dec 16 '15 at 19:30

Or do it with "INNER JOIN".

"FROM SCHEDULEREVENT LEFT JOIN PATIENT_HAS_EVENT" gets all records from SCHEDULEREVENT, then join to PATIENT_HAS_EVENT, then apply where.

Harriv solution's first apply where on PATIENT_HAS_EVENT, then join to SCHEDULEREVENT.

Using "INNER JOIN" will make the engine to choose the same plan as Harriv solution's (I guess...).

Firebird SQL optimization

2 Answers2