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

So the sub query determines the number of policies per ACCGRPID. Do you have indexes on ACCGRP.ACCGRPID and POLICY.ACCGRPID already? If so I can't see much scope to optimise this really (other than precalculation) as it is a necessary input to the second step.

You aren't using the COUNT value after 5 so it might be scanning some unnecessary rows but I can't think of a way to avoid that and it probably wouldn't be worth trying unless this was a large proportion of records.

Maybe doing COUNT(POLICY.ACCGRPID) instead of COUNT(POLICYID) might help if it doesn't change the semantics as POLICY.ACCGRPID is already being used elsewhere in the query and it might avoid an unnecessary lookup or allow a narrower index to be used. You'd have to look at the query plan to see if this makes any difference. Possibly if it has a not null constraint SQL Server will make this optimisation anyway.

Why have you been asked to optimise it? Is it causing a performance problem? If so can you post the execution plan?

Answer 2

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.