Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Optimize sql with subquery

Optimize sql with subquery

Optimize sql with subquery


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)

Replies continue below

Recommended for you

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close