Optimize sql with subquery

[tinac99]

Hi,

Is there any way to optimize this sql, hopefully by removing the subquery?

select distinct e.*, c.arraignment_dt, c.case_nbr, c.citation_nbr, c.court_cd,
(select count(*) from def d1
where d1.case_id = c.case_id and d1.warrant_request_flg = 'N') as num_warrant_N,
/* sort column */ CASE when arraignment_dt is null then 0
when (exists(select * from def d1
where d1.case_id = c.case_id and d1.warrant_request_flg = 'N')) then 1
when (c.arraignment_dt is not null) then 2
END as sort_order
from edc_queue e inner join casefile c on e.case_id = c.case_id
inner join case_dr cd on c.case_id = cd.case_id
inner join def d on c.case_id = d.case_id
where not exists(select * from def d1 where (d1.custody_flg = 'Y') and d1.case_id = d.case_id)


Thanks.

 

[kristof]

Hi,

A few quick pointers:

a) Don't use exists

It's horribly slow because you're working with a massive (relative or actual) collection of data, only to give you a yes or no. Instead, create that yes or no yourself like this:

when 0 < (select count(case_id) from def d1 where d1.case_id = c.case_id and d1.warrant_request_flg = 'N')
then 1

b) (repeated) subqueries typically call for a stored procedure rather than a single query

Rather than repeat the same query over and over, put the result aside in a variable, especially since you just want one value:

set @case_id_cnt = select count(d1.case_id)
from def d1
where d1.case_id = c.case_id and d1.warrant_request_flg = 'N'

(Sql server syntax)

That way you can use that variable directly:

when 0 < @case_id_cnt then 1

c) Don't use count(*)

As you may have noticed, I replaced the count with count(d1.case_id). It's better to use a field for a count, preferably an indexed one.