title: Stored Procedure Optimisation
body: <p>I have written the following SQL Stored Procedure and because of all the select commands (I think) it's really slow running now the database has been populated with lots of data. Is there a way to optimise it so that it runs much quicker? Currently in an Azure S0 DB it takes around 1:40 min to process. Here's the stored procedure:</p>
  
  <pre><code>USE [cmb2SQL]
  GO
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE PROCEDURE [dbo].[spStockReport] @StockReportId as INT
  AS
      select 
          ProductId, 
          QtyCounted, 
          LastStockCount, 
          Purchases, 
          UnitRetailPrice, 
          CostPrice, 
          GrossProfit, 
          Consumed,
          (Consumed * UnitRetailPrice) as ValueOfSales,
          (QtyCounted * CostPrice) as StockOnHand, 
          StockCountId
      from (
          select 
              ProductId, 
              QtyCounted, 
              LastStockCount, 
              Purchases, 
              UnitRetailPrice, 
              CostPrice, 
              GrossProfit, 
              (LastStockCount + Purchases) - QtyCounted as Consumed,
              StockCountId
          from (
              select
                  distinct
                  sci.StockCountItem_Product as ProductId,  
                  (Select ISNULL(SUM(Qty), 0) as tmpQty from 
                      (Select Qty from stockcountitems
                      join stockcounts on stockcountitems.stockcountitem_stockcount = stockcounts.id
                      where stockcountitem_Product = p.Id and stockcountitem_stockcount = sc.id and stockcounts.stockcount_pub = sc.StockCount_Pub
                      ) as data
                  ) as QtyCounted,
                  (Select ISNULL(SUM(Qty), 0) as LastStockCount from 
                      (Select Qty from StockCountItems as sci
                          join StockCounts on sci.StockCountItem_StockCount = StockCounts.Id
                          join Products on sci.StockCountItem_Product = Products.id
                      where sci.StockCountItem_Product = p.id and sci.stockcountitem_stockcount =
                          (select top 1 stockcounts.id from stockcounts
                          join stockcountitems on stockcounts.id = stockcountitem_stockcount
                          where stockcountitems.stockcountitem_product = p.id and stockcounts.id &lt; sc.id  and StockCounts.StockCount_Pub = sc.StockCount_Pub
                          order by stockcounts.id desc)
                      ) as data
                  ) as LastStockCount,
                  (Select ISNULL(SUM(Qty * CaseSize), 0) as Purchased from
                      (select Qty, Products.CaseSize from StockPurchaseItems
                          join Products on stockpurchaseitems.stockpurchaseitem_product = products.id
                          join StockPurchases on stockpurchaseitem_stockpurchase = stockpurchases.id
                          join Periods on stockpurchases.stockpurchase_period = periods.id
                      where Products.id = p.Id and StockPurchases.StockPurchase_Period = sc.StockCount_Period and StockPurchases.StockPurchase_Pub = sc.StockCount_Pub) as data
                  ) as Purchases,
                  sci.RetailPrice as UnitRetailPrice,
                  sci.CostPrice,
                  (select top 1 GrossProfit from Pub_Products where Pub_Products.Pub_Product_Product = p.id and Pub_Products.Pub_Product_Pub = sc.StockCount_Pub) as GrossProfit,
                  sc.Id as StockCountId
              from StockCountItems as sci 
                  join StockCounts as sc on sci.StockCountItem_StockCount = sc.Id
                  join Pubs on sc.StockCount_Pub = pubs.Id
                  join Periods as pd on sc.StockCount_Period = pd.Id
                  join Products as p on sci.StockCountItem_Product = p.Id
                  join Pub_Products as pp on p.Id = pp.Pub_Product_Product
              Where StockCountItem_StockCount = @StockReportId  and pp.Pub_Product_Pub = sc.StockCount_Pub
              Group By sci.CostPrice, sci.StockCountItem_Product, sci.Qty, sc.Id, p.Id, sc.StockCount_Period, pd.Id, sci.RetailPrice, pp.CountPrice, sc.StockCount_Pub
          ) as data
      ) as final
  
  GO
  </code></pre>
  
  <p>As requested here is the execution plan in XML (had to upload it to tinyupload as it exceeds the message character length):</p>
  
  <p><a href="http://s000.tinyupload.com/index.php?file_id=97403313684323569774" rel="nofollow noreferrer">execusionplan.xml</a></p>
  
  <p>Schema:</p>
  
  <p><a href="https://i.stack.imgur.com/qMt7r.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qMt7r.jpg" alt="Table Schema"></a></p>
  
  <p>Row Counts:</p>
  
  <pre><code>Table   row_count
  StockPurchaseItems  57511
  Products    3116
  StockCountItems 60949
  StockPurchases  6494
  StockCounts 240
  Periods 30
  Pub_Products    5694
  Pubs    7
  </code></pre>