如何改进包含嵌套子查询的SQL Server查询 [英] How to improve SQL Server query containing nested sub query

问题描述

我的老板给了我这个SQL查询，并告诉我改善/优化它

I was given this SQL query by my boss and told to improve/optimize it

DECLARE @pol_0 int, @pol_1 int, @pol_2 int, @pol_3 int, @pol_4 int, @pol_5plus int,
    @peril_0 int, @peril_1 int, @peril_2 int, @peril_3 int, @peril_4 int, @peril_5plus int,
    @loc_1 int, @loc_2_10 int, @loc_11_100 int, @loc_101_1000 int, @loc_1001_5000 int, @loc_5001plus int,
    @locfass int, @polfass int, @pollim int, @polattpt int, @polded int, @maxded int, @polres int, @sublimit int,
    @sitelim int, @siteded int, @SS int, @WX int, @QS int, @CAT int, @CORP int, @SL int,
    @ty_port int, @ty_acct int, @ty_pol int, @ty_loc int,
    @2mod_eq_0 int, @2mod_eq_1_10 int, @2mod_eq_11_20 int, @2mod_eq_21_27 int,
    @2mod_hu_0 int, @2mod_hu_1_10 int, @2mod_hu_11_20 int, @2mod_hu_21_27 int

SELECT @pol_0 = COUNT(CASE CNT WHEN 0 THEN 99 ELSE NULL END),
   @pol_1 = COUNT(CASE CNT WHEN 1 THEN 99 ELSE NULL END),
   @pol_2 = COUNT(CASE CNT WHEN 2 THEN 99 ELSE NULL END),
   @pol_3 = COUNT(CASE CNT WHEN 3 THEN 99 ELSE NULL END),
   @pol_4 = COUNT(CASE CNT WHEN 4 THEN 99 ELSE NULL END),
   @pol_5plus = COUNT(CASE WHEN CNT >= 5 THEN 99 ELSE NULL END) 
FROM   ( SELECT  ACCGRP.ACCGRPID,
                COUNT(POLICYID) AS CNT
       FROM     ACCGRP
                LEFT OUTER JOIN POLICY
                ON       ACCGRP.ACCGRPID = POLICY.ACCGRPID
       GROUP BY ACCGRP.ACCGRPID
       )

我的第一个想法是放弃DECLARE，然后将COUNT转换为类似的

My first idea was to discard the DECLARE and then convert the COUNT's into something like

SELECT 
(select COUNT(CASE CNT WHEN 0 THEN 99 ELSE NULL END),
(select COUNT(CASE CNT WHEN 1 THEN 99 ELSE NULL END),
(select COUNT(CASE CNT WHEN 2 THEN 99 ELSE NULL END),
(select COUNT(CASE CNT WHEN 3 THEN 99 ELSE NULL END),
(select COUNT(CASE CNT WHEN 4 THEN 99 ELSE NULL END),
(select COUNT(CASE CNT WHEN >= 5 THEN 99 ELSE NULL END) FROM

，但是FROM子句具有嵌套的子查询

but the FROM clause has a nested sub-query

FROM (SELECT ACCGRP.ACCGRPID, COUNT(POLICYID) AS CNT FROM ACCGRP LEFT OUTER JOIN POLICY ON ACCGRP.ACCGRPID = POLICY.ACCGRPID
GROUP BY ACCGRP.ACCGRPID)

有人建议我删除嵌套的子查询，但是我不确定到底有什么比嵌套的子查询更好的选择.任何建议将不胜感激！

I was given the suggestion by someone to remove the nested sub query but I am not exactly sure what would be a better alternative to the nested subquery. Any suggestions would be greatly appreciated!

推荐答案

此查询是否真的很慢?

如果是这样，那么您应该获得一个执行计划并根据执行结果进行优化.

If so then you should get an execution plan and optimise based on the results of that.

如果没有，那么没有什么可以优化的！ :-)

If not, then there is nothing to optimise! :-)

常见的误解是嵌套子查询很慢，但是事实并非如此.在特定情况下，嵌套子查询可能会导致性能问题，但是在通常情况下，嵌套子查询通常由SQL Server进行优化，直至达到与联接类似的执行计划.

There is a common misconception that nested subqueries are slow, however this simply isn't the case. In specific circumstances a nested subquery might cause a performance problem however in the general case often nested subqueries are optimised by SQL server down to similar execution plans as joins.

这篇关于如何改进包含嵌套子查询的SQL Server查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！