Optimize sql with subquery
Optimize sql with subquery
(OP)
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.
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.
Talk To Other Members
RE: Optimize sql with subquery
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.