none
SQl - Help Optimize Query RRS feed

  • Question

  • I have a sp I've been asked to try to optimize. I'm not sure where to make adjustments on how to make it better. I'm hoping you guys might have suggestions or ideas where to look. 

    Order: 32 seconds - Returns 8 rows
    Production: 1 second - Returns 1 row - Note Needs addational params to fully execute
    ShipMethod: 25 seconds - 8 rows
    Packaging: 9 seconds - 1 row

    DECLARE
    	@StatusType varchar(32) = 'Packaging', 
    	@Filter varchar(32) = NULL,
    	@Station varchar(32) = '',
    	@AttrValue varchar(128) = '',
    	@AttrValue2 varchar(128) = '',
    	@AttrValue3 varchar(128) = ''
    --AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	DECLARE @Date datetime = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
    	DECLARE @sStatusType varchar(32) = @StatusType
    	DECLARE @sFilter varchar(32) = @Filter
    	DECLARE @sStation varchar(32) = @Station
    	DECLARE @sAttrValue varchar(128) = @AttrValue
    	DECLARE @sAttrValue2 varchar(128) = @AttrValue2
    	DECLARE @sAttrValue3 varchar(128) = @AttrValue3
    
    --Order: 
    SELECT @sFilter AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total
    		FROM SFLY_Status
    		WHERE StatusType = @sStatusType
    		UNION
    		SELECT @sFilter AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
    		UNION
    		SELECT s.Facility AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, COUNT(i.SubOrderStatus) AS Total
    		FROM (
    			SELECT DISTINCT o.Facility, o.DueDate, s.StatusSort, s.StatusDescription
    			FROM SFLY_SubOrdersFacility_vw o WITH (NOLOCK)
    			CROSS JOIN SFLY_Status s
    			WHERE s.StatusType = @sStatusType AND o.Facility = @sFilter AND ((o.DueDate < @Date AND o.SubOrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s
    		LEFT OUTER JOIN SFLY_SubOrdersFacility_vw i WITH (NOLOCK) ON i.Facility = @sFilter AND i.SubOrderStatus = s.StatusDescription AND i.DueDate = s.DueDate AND ((i.DueDate < @Date AND i.SubOrderStatus NOT IN ('Shipped','Cancelled')) OR i.DueDate >= @Date)
    		GROUP BY s.Facility, s.DueDate, s.StatusSort, s.StatusDescription
    		UNION
    		SELECT Facility AS Filter, DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total
    		FROM SFLY_SubOrdersFacility_vw WITH (NOLOCK)
    		WHERE Facility = @sFilter AND ((DueDate < @Date AND SubOrderStatus NOT IN ('Shipped','Cancelled')) OR DueDate >= @Date)
    		GROUP BY Facility, DueDate
    		ORDER BY Filter, DueDate, StatusSort

    Most of the queries are the same, so if I can optimize one, I can optimize the others. Can anyone see anything that stands out? I'm not a DBA, I'm just a .Net developer. 

    Friday, December 13, 2019 3:07 PM

All replies

  • Reviewing the sp structure. Please tell me if I'm an idiot or not. I noticed we make a call to the "SFLY_SubOrdersFacility_vw" view. I wonder if it would be faster to build a temp table witht he bulk of the data needed and then pull addational data from the temp table version. This way I'm not making a cal twice to the same view. I don't know just and idea. Open to suggestions 
    Friday, December 13, 2019 4:17 PM
  • Try remove all local variables and use parameters directly;

    Use UNION ALL instead of UNION  if you don't have duplicate data;

    Remove ORDER By clause.

    Check your execution plan and see where you can improve.

    Friday, December 13, 2019 5:00 PM
    Moderator
  • Tried your suggestions and had little impact on it. I also ran the execution plan. I'm not sure what some of it means. Taking just a chunk of it. I hope you can explain this to me a little:

    <StmtSimple StatementCompId="10" StatementEstRows="6329.01" StatementId="10" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="1504.07" StatementText="SELECT @sFilter AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total&#xD;&#xA;		FROM SFLY_Status&#xD;&#xA;		WHERE StatusType = @sStatusType&#xD;&#xA;		UNION ALL&#xD;&#xA;		SELECT @sFilter AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total&#xD;&#xA;		UNION ALL&#xD;&#xA;		SELECT ss.Facility AS Filter, ss.DueDate, ss.StatusSort, ss.StatusDescription, COUNT(i.SubOrderStatus) AS Total&#xD;&#xA;		FROM (&#xD;&#xA;			SELECT DISTINCT o.Facility, o.DueDate, s.StatusSort, s.StatusDescription&#xD;&#xA;			FROM SFLY_SubOrdersFacility_vw o&#xD;&#xA;			CROSS JOIN SFLY_Status s&#xD;&#xA;			WHERE s.StatusType = @sStatusType AND o.Facility = @sFilter AND ((o.DueDate &lt; @Date AND o.SubOrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate &gt;= @Date)) ss&#xD;&#xA;		LEFT OUTER JOIN SFLY_SubOrdersFacility_vw i WITH (NOLOCK) ON i.Facility = @sFilter AND i.SubOrderStatus = ss.StatusDescription AND i.DueDate = ss.DueDate AND ((i.DueDate &lt; @Date AND i.SubOrderStatus NOT IN ('Shipped','Cancelled')) OR i.DueDate &gt;= @Date)&#xD;&#xA;		GROUP BY ss.Facility, ss.DueDate, ss.StatusSort, ss.StatusDescription&#xD;&#xA;		UNION ALL&#xD;&#xA;		SELECT Facility AS Filter, DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, COUNT(*) AS Total&#xD;&#xA;		FROM SFLY_SubOrdersFacility_vw WITH (NOLOCK)&#xD;&#xA;		WHERE Facility = @sFilter AND ((DueDate &lt; @Date AND SubOrderStatus NOT IN ('Shipped','Cancelled')) OR DueDate &gt;= @Date)&#xD;&#xA;		GROUP BY Facility, DueDate&#xD;&#xA;		ORDER BY Filter, DueDate, StatusSort" StatementType="SELECT" QueryHash="0xF298020A24E1A0B1" QueryPlanHash="0xAF3E8F2DCF1FF8C4" RetrievedFromCache="true" SecurityPolicyApplied="false">
              <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
              <QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="552" CompileTime="82" CompileCPU="82" CompileMemory="6344">
                <MissingIndexes>
                  <MissingIndexGroup Impact="11.1386">
                    <MissingIndex Database="[Shutterfly]" Schema="[dbo]" Table="[SFLY_SubOrderItems]">
                      <ColumnGroup Usage="INEQUALITY">
                        <Column Name="[ItemNum]" ColumnId="21" />
                      </ColumnGroup>
                      <ColumnGroup Usage="INCLUDE">
                        <Column Name="[SubOrderId]" ColumnId="2" />
                        <Column Name="[sku]" ColumnId="4" />
                        <Column Name="[formFactor]" ColumnId="12" />
                        <Column Name="[orientation]" ColumnId="18" />
                      </ColumnGroup>
                    </MissingIndex>
                  </MissingIndexGroup>
                  <MissingIndexGroup Impact="11.1386">
                    <MissingIndex Database="[Shutterfly]" Schema="[dbo]" Table="[SFLY_SubOrderItems]">
                      <ColumnGroup Usage="INEQUALITY">
                        <Column Name="[ItemNum]" ColumnId="21" />
                      </ColumnGroup>
                      <ColumnGroup Usage="INCLUDE">
                        <Column Name="[SubOrderId]" ColumnId="2" />
                        <Column Name="[sku]" ColumnId="4" />
                        <Column Name="[formFactor]" ColumnId="12" />
                        <Column Name="[orientation]" ColumnId="18" />
                      </ColumnGroup>
                    </MissingIndex>
                  </MissingIndexGroup>
                  <MissingIndexGroup Impact="11.1386">
                    <MissingIndex Database="[Shutterfly]" Schema="[dbo]" Table="[SFLY_SubOrderItems]">
                      <ColumnGroup Usage="INEQUALITY">
                        <Column Name="[ItemNum]" ColumnId="21" />
                      </ColumnGroup>
                      <ColumnGroup Usage="INCLUDE">
                        <Column Name="[SubOrderId]" ColumnId="2" />
                        <Column Name="[sku]" ColumnId="4" />
                        <Column Name="[formFactor]" ColumnId="12" />
                        <Column Name="[orientation]" ColumnId="18" />
                      </ColumnGroup>
                    </MissingIndex>
                  </MissingIndexGroup>
                </MissingIndexes>

    Friday, December 13, 2019 5:40 PM
  • Is it possible to break those unions out into their own queries? If so anyone have an example. for tearing these apart for example? I'm trying but get errors:

    SELECT @sFilter AS Filter, NULL AS DueDate, 999.0000 AS StatusSort, 'Total' AS StatusDescription, 0 AS Total
    		UNION ALL
    		SELECT ss.Facility AS Filter, ss.DueDate, ss.StatusSort, ss.StatusDescription, COUNT(i.SubOrderStatus) AS Total
    		FROM (
    			SELECT DISTINCT o.Facility, o.DueDate, s.StatusSort, s.StatusDescription
    			FROM SFLY_SubOrdersFacility_vw o
    			CROSS JOIN SFLY_Status s
    			WHERE s.StatusType = @sStatusType AND o.Facility = @sFilter AND ((o.DueDate < @Date AND o.SubOrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) ss
    		LEFT OUTER JOIN SFLY_SubOrdersFacility_vw i WITH (NOLOCK) ON i.Facility = @sFilter AND i.SubOrderStatus = ss.StatusDescription AND i.DueDate = ss.DueDate AND ((i.DueDate < @Date AND i.SubOrderStatus NOT IN ('Shipped','Cancelled')) OR i.DueDate >= @Date)

    I think my best course of action right now might be to pull these all out into their own queries. Any suggestions great appreciated and welcome 


    • Edited by old_School Friday, December 13, 2019 6:10 PM
    Friday, December 13, 2019 6:09 PM
  • Hi,

    Sure you can break all those unions into single queries but I doubt you will get any performance boost from it. Yes, by breaking into single queries you can debug it and find out where the bottle neck is and try to optimize it. However, your output returns within seconds and what is there to optimize?

    SELECT @sFilter AS Filter, NULL AS DueDate, StatusSort, StatusDescription, 0 AS Total
    into #tmp1
    FROM SFLY_Status
    WHERE StatusType = @sStatusType

    SELECT s.Facility AS Filter, s.DueDate, s.StatusSort, s.StatusDescription, COUNT(i.SubOrderStatus) AS Total
    into #tmp2
    FROM (
    SELECT DISTINCT o.Facility, o.DueDate, s.StatusSort, s.StatusDescription
    FROM SFLY_SubOrdersFacility_vw o WITH (NOLOCK)
    CROSS JOIN SFLY_Status s
    WHERE s.StatusType = @sStatusType AND o.Facility = @sFilter AND ((o.DueDate < @Date AND o.SubOrderStatus NOT IN ('Shipped','Cancelled')) OR o.DueDate >= @Date)) s

    ...etc

    at the end:

    select * from #tmp1
    union
    select * from #tmp2 etc
      
    Saturday, December 14, 2019 3:45 AM