How to improve SQL Server query containing nested sub query

Question

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
       )

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

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!

Answer 1

Is this query actually slow?

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! :-)

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.