CODEWITHSUNDEEP
sqlsql-server
ankit soni
ankit soni

Reputation: 35

optimize a select query which took 1 minute to execute

I have a very short select query, but it took 1 minute to execute, how to optimize it?

SELECT dsss.[distID],
       dsss.[distname],
       (select count(applicationnumb) from tbl_applicant_case with(nolock) where tbl_applicant_case.[CourtDistId]=dsss.[distID] and tbl_applicant_case.case_numb is not null and [category]=N'अ-27') as ttlcaseentrered,
       (select count(applicationnumb) from tbl_applicant_case  with(nolock) where tbl_applicant_case.[CourtDistId]=dsss.[distID] and tbl_applicant_case.FinalOrder is not null and [category]=N'अ-27') as ttlcasedispopsed
FROM [tbl_district] as dsss
group by dsss.[distID], dsss.[distname]
order by ttlcaseentrered desc

Upvotes: 1

Views: 56

Answers (2)

SqlZim
SqlZim

Reputation: 38023

It looks like you can simplify your query to this single left join instead of two subqueries (because a null value is not count()ed):

select 
    dsss.[distID]
  , dsss.[distname]
  , ttlcaseentrered  = count(ac.case_numb)
  , ttlcasedispopsed = count(ac.FinalOrder)
from [tbl_district] as dsss
  left join tbl_applicant_case as ac with(nolock) 
    on ac.[CourtDistId]=dsss.[distID] 
   and ac.[category]=N'अ-27'
group by dsss.[distID], dsss.[distname]

And the count() could be supported with an index like this:

create nonclustered index ix_tbl_applicant_case_CourtDistId_Category_cover 
  on tbl_applicant_case(CourtDistId, Category)
    include (CaseNumb, FinalOrder);

If applicaitonnumb is nullable, to count when both are not null you can use conditional aggregation like so:

select 
    dsss.[distID]
  , dsss.[distname]
  , ttlcaseentrered  = sum(case when ac.applicationnumb is not null and ac.case_numb is not null then 1 else 0 end)
  , ttlcasedispopsed = sum(case when ac.applicationnumb is not null and ac.FinalOrder is not null then 1 else 0 end)
from [tbl_district] as dsss
  left join tbl_applicant_case as ac with(nolock) 
    on ac.[CourtDistId]=dsss.[distID] 
   and ac.[category]=N'अ-27'
group by dsss.[distID], dsss.[distname]

And the index would also need to include applicationnumb:

create nonclustered index ix_tbl_applicant_case_CourtDistId_Category_cover 
  on tbl_applicant_case(CourtDistId, Category)
    include (CaseNumb, FinalOrder, applicationnumb);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

For this query:

select dsss.[distID],
       dsss.[distname],
       (select count(applicationnumb)
        from tbl_applicant_case ac
        where ac.[CourtDistId] = dsss.[distID] and
              ac.case_numb is not null and
              ac.[category] = N'अ-27'
      ) as ttlcaseentrered,
      (select count(applicationnumb) 
       from tbl_applicant_case ac
       where ac.[CourtDistId] = dsss.[distID] and
             ac.FinalOrder is not null and
             ac.[category]=N'अ-27'
      ) as ttlcasedispopsed
from [tbl_district] dsss
group by dsss.[distID], dsss.[distname]
order by ttlcaseentrered desc;

First, for the subqueries, you want an index on tbl_applicant_case(CourtDistId, category). This index could also include: FinalOrder, case_numb, and applicationnumb.

Second, I don't see why GROUP BY is needed in the outer query. I would suggest removing it, unless you really know that you need to remove duplicates.

Upvotes: 1

Related Questions