Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Optimize sql with subquery

Status
Not open for further replies.
tinac99

tinac99

Programmer
Jan 17, 2002
58
US
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.
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
417
carp
carp
mmiah1982
  • Locked
  • Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
369
mmiah1982
mmiah1982
fredong
  • Locked
  • Question
Update upper row data with next row data that was detached partially from import.
Replies
6
Views
549
carp
carp
mikrom
  • Locked
  • Question
from a table with symmetric relation select every pair only once
Replies
0
Views
292
mikrom
mikrom
jchewsmith
  • Locked
  • Question
Update Query with a MAX(Date) subquery 1
Replies
1
Views
452
JarlH
JarlH

Part and Inventory Search

Sponsor

Back
Top