Friday, 18 July 2008

An interesting query for potential optimisation...?

Just wanted to capture a query I was working on yesterday which was slightly 'interesting' in that it consists of a couple of layers of left outer joins and the joining with a derived aggregation table - and time permitting I'd like to revisit it because I'm sure it can be improved / optimised (but it is functionally correct).

t1.alias as alias,
t1.asset_label as asset_label,
t1.terminal_id as terminal_id,
t1.location_address as terminal_address,
t1.last_updated as last_connection, as questionnaire_name,
t1.questionnaire_id as questionnaire_id,
t1.state as state,
t1.value as publishing_state,
response_stats.rcount as rcount,
response_stats.last_response as last_response
(select as tid,
t.last_updated,, as questionnaire_id,
qnr.status as state,
from terminal t
left outer join
(delivery_stats d,
terminal_activity ta,
questionnaire qnr,
activity_type at)
d.terminal_id =
and d.terminal_activity_id =
and ta.questionnaire_id =
and ta.activity_type =
and qnr.status = "L"
where in(1, 20, 21, 22, 23)) as t1
left outer join
tcq.terminal_id tid,
tcq.questionnaire_id qid,
count(*) rcount,
max(uploaded_date) as last_response
tcquestionnaire tcq
group by
tcq.terminal_id, tcq.questionnaire_id) as response_stats
t1.tid = response_stats.tid
and t1.questionnaire_id = response_stats.qid;

Whilst technically correct - I'm not entirely satisfied of the need for the the top level left outer join which creates a full derived table rather than having a correlated sub-query / nested join that contains only the rows actually relevant to the result set. Something to think about perhaps, on a rainy (very rainy!?!) day :)


No comments: