My subquery is slowing down the process. Any optimization method?

Question

I have written codes using subquery but it is too slow. I am looking for a solution that can optimize the process of this query. It is intended to return 6 columns - PO Number, PO Line Number, Amount for each PO Line, Count of PO Lines per each PO Number, Sum of Line Amounts for each PO Number, Categorization on each PO Number by Line Amount. Below is a sample table and actual query I wrote in. Your help is much appreciated!

|PO NUMBER |PO LINE|LINE AMOUNT|TOTAL PO LINES|TOTAL PO AMOUNT|TOTAL PO GROUP|
|1721574   |   1   |   10.76   |      1       |     10.76     |   $0-100     |
|1722154   |   1   |   30.00   |      1       |     30.00     |   $0-100     |
|1723538   |   1   |   15.97   |      4       |     65.63     |   $0-100     |
|1723538   |   2   |   11.23   |      4       |     65.63     |   $0-100     |
|1723538   |   3   |   15.97   |      4       |     65.63     |   $0-100     |
|1723538   |   4   |   22.46   |      4       |     65.63     |   $0-100     |
|1723877   |   1   |   15.70   |      1       |     15.70     |   $0-100     |

Query

select ph.ponumber, 
       pl.poline, 
       pl.polinebasemerchamount, 
       (select count(pl2.poline) 
               from dbo.polineflat as pl2 
               inner join dbo.poheader as ph2 
               on ph2.pokey = pl2.pokey 
               where ph2.pokey = ph.pokey 
               group by ph2.ponumber), 
       (select sum(pl2.polinebasemerchamount) 
               from dbo.polineflat as pl2 
               inner join dbo.poheader as ph2 
               on ph2.pokey = pl2.pokey 
               where ph2.pokey = ph.pokey 
               group by ph2.ponumber), 
       (select case 
                   when sum(pl2.PoLineBaseMerchAmount) between 0 and 100 then '$0-100'
                   when sum(pl2.polinebasemerchamount) between 101 and 500 then '$101-500'
                   when sum(pl2.polinebasemerchamount) between 501 and 1000 then '$501-1000'
                   else '1000+' end
               from dbo.polineflat as pl2 
               inner join dbo.poheader as ph2 
               on ph2.pokey = pl2.pokey 
               where ph2.pokey = ph.pokey 
               group by ph2.ponumber) 
from dbo.poheader as ph
inner join dbo.polineflat as pl on ph.pokey = pl.pokey

Answer 1

Three sub-queries can be combined into one single query using Cross Apply. This one advantage of using Cross Apply over correlated sub-queries it can return more than one column in select list

SELECT ph.ponumber, 
       pl.poline, 
       pl.polinebasemerchamount, 
       oa.poline_count, 
       oa.polinebasemerchamount_sum, 
       CASE 
         WHEN oa.polinebasemerchamount_sum BETWEEN 0 AND 100 THEN '$0-100' 
         WHEN oa.polinebasemerchamount_sum BETWEEN 101 AND 500 THEN '$101-500' 
         WHEN oa.polinebasemerchamount_sum BETWEEN 501 AND 1000 THEN '$501-1000' 
         ELSE '1000+' 
       END AS Range
FROM   dbo.poheader AS ph 
       CROSS Apply (SELECT Count(pl2.poline)              AS poline_count, 
                           Sum(pl2.polinebasemerchamount) AS polinebasemerchamount_sum                               
                    FROM   dbo.polineflat AS pl2 
                    WHERE  pl2.pokey = ph.pokey) oa 

Note : I have removed the Group By present in the sub-query since it should be useless else it would have thrown error in original query

Update : To improve the query further, create the following Non clustered indexes

--polineflat table
CREATE NONCLUSTERED INDEX IX_polineflat_pokey
    ON dbo.polineflat (pokey) Include (poline,polinebasemerchamount);  

--poheader table
CREATE NONCLUSTERED INDEX IX_poheader_pokey
    ON dbo.poheader (pokey) Include (ponumber,poline,polinebasemerchamount); 

Answer 2

In these cases, I actually use #temp tables. I find them much better at performance and I have better control on the result that I want. It is also easier to understand(at least for me and other people that reads them). My goal in answering this post is to help you utilize this type of coding. Rather than just straight up giving you the answer you can Copy and Paste and hit F5.

NOTE: Feel free to name the temp tables that make sense to you. You can also play with LEFT JOIN/RIGHT JOIN to get all the values from dbo.poheader and see the values matches your #temp table. You may need to switch the ID's /temp tables to fit the results you need. I hope this helps you get the idea.

 --Insert Subquery 1 into a #temp1

 select count(pl2.poline) as Count1, ph2.ponumber 
 Into #temp1
 from dbo.polineflat as pl2 
           inner join dbo.poheader as ph2 
           on ph2.pokey = pl2.pokey 
           where ph2.pokey = ph.pokey 
           group by ph2.ponumber)

  --Insert Subquery 2 into #temp2

  select sum(pl2.polinebasemerchamount) as Sum1, ph2.ponumber
  into #temp2
           from dbo.polineflat as pl2 
           inner join dbo.poheader as ph2 
           on ph2.pokey = pl2.pokey 
           where ph2.pokey = ph.pokey 
           group by ph2.ponumber

  --Insert Subquery 3 into #temp3. Pull pl2.polinebasemerchant from #temp2

   select case 
    when sum(#temp2.PoLineBaseMerchAmount) between 0 and 100 then '$0-100'
    when sum(#temp2.polinebasemerchamount) between 101 and 500 then'$101-500'
    when sum(#temp2..polinebasemerchamount) between 501 and 1000 then '$501-1000'
    else '1000+' end 'DollarAmounts',ph2.ponumber
    into #temp3
    from dbo.polineflat as pl2 
    inner join dbo.poheader as ph2 
     on ph2.pokey = pl2.pokey 
     where ph2.pokey = ph.pokey 
     group by ph2.ponumber


 -- Select here with the temp table groups
  select ph.ponumber, 
         pl.poline, 
         pl.polinebasemerchamount,
         #temp1.Count1,
         #temp2.Sum1,
         #temp3.DollarAmounts

   from dbo.poheader as ph
   join dbo.polineflat as pl on ph.pokey = pl.pokey

   --join your temp tables here
   Join #temp1 on #temp1.ponumber = ph.ponumber
   join #temp2 on #temp2.ponumber = ph.ponumber
   join #temp3 on #temp3.ponumber = ph.ponumber


   TRUNCATE TABLE #temp1
   TRUNCATE TABLE #temp2
   TRUNCATE TABLE #temp3

Answer 3

Looks like you're doing group by, by using selects

select ph.ponumber, 
   pl.poline, 
   pl.polinebasemerchamount, 
   count(pl.poline) as total_count,
   sum(pl.polinebasemerchamount) as total_sum,
case  when sum(pl.PoLineBaseMerchAmount) between 0 and 100 then '$0-100'
  when sum(pl.polinebasemerchamount) between 101 and 500 then '$101-500'
  when sum(pl.polinebasemerchamount) between 501 and 1000 then '$501-1000'
       else '1000+' end as total_group

from dbo.poheader as ph
inner join dbo.polineflat as pl
on ph.pokey = pl.pokey
group by     ph.ponumber, pl.poline, pl.polinebasemerchamount 

Answer 4

I would compute the sum once and then do your case in a parent query:

SELECT ponumber, poline, polinebasemerchamount, polineCount, polineFlatSum,
    CASE 
        WHEN polineFlatSum between 0 and 100 then '$0-100'
        WHEN polineFlatSum between 101 and 500 then '$101-500'
        WHEN polineFlatSum  between 501 and 1000 then '$501-1000'
        ELSE '1000+' 
    END AS polineFlatSumString
from
(
    select ph.ponumber, 
           pl.poline, 
           pl.polinebasemerchamount, 
           (select count(pl2.poline) 
                   from dbo.polineflat as pl2 
                   inner join dbo.poheader as ph2 
                   on ph2.pokey = pl2.pokey 
                   where ph2.pokey = ph.pokey 
                   group by ph2.ponumber) AS polineCount, 
           (select sum(pl2.PoLineBaseMerchAmount) 
                   from dbo.polineflat as pl2 
                   inner join dbo.poheader as ph2 
                   on ph2.pokey = pl2.pokey 
                   where ph2.pokey = ph.pokey 
                   group by ph2.ponumber) AS polineFlatSum,        
    from dbo.poheader as ph
        inner join dbo.polineflat as pl on ph.pokey = pl.pokey
) T

Answer 5

Try this...

select ph2.ponumber, 
       pl2.poline, 
       pl2.polinebasemerchamount, 
       count(pl2.poline) ,
       sum(pl2.polinebasemerchamount) ,
case  when sum(pl2.PoLineBaseMerchAmount) between 0 and 100 then '$0-100'
      when sum(pl2.polinebasemerchamount) between 101 and 500 then '$101-500'
      when sum(pl2.polinebasemerchamount) between 501 and 1000 then '$501-1000'
           else '1000+' end

from dbo.poheader as ph2
inner join dbo.polineflat as pl2
on ph.pokey = pl.pokey
inner join dbo.polineflat as pl2 
on ph.pokey = pl2.pokey
group by  ph2.ponumber, 
       pl2.poline, 
       pl2.polinebasemerchamount