优化一个select查询，该查询需要1分钟才能执行

Question

我有一个很短的选择查询，但是它花了1分钟来执行，如何优化它？

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

Answer 1

看起来您可以简化对单个left join的查询，而不是两个子查询(因为null值不是count()编辑的)：

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]

这样的索引可以支持count()：

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

如果applicaitonnumb为空，则若要计数两者均为非空，可以使用条件聚合，如下所示：

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]

而且索引还需要包括applicationnumb

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

Answer 2

对于此查询：

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;

首先，对于子查询，您需要tbl_applicant_case(CourtDistId, category)上的索引。这个索引还可以包括：FinalOrder、case_numb和applicationnumb。

其次，我不明白为什么在外部查询中需要GROUP BY。我建议移除它，除非你真的知道你需要删除重复的。