Need some advice on increasing performance of a stored procedure

  • I have a stored procedure that is timing out. I have added indexes but it still isn't working as it should. Any advice is appreciated:

    USE [AXInquiry]

    GO

    /****** Object: StoredProcedure [ShipmentRequest].[uspCatalogSearch] Script Date: 02/13/2014 13:04:05 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /* TEST

    ShipmentRequest.uspCatalogSearch @Customer='TWTC001'

    , @ParentCustomer='TWTC000'

    , @DataAreaId='mdsi'

    , @ItemId='4900M-ACPWRKIT'

    , @Category=''

    , @MultiItemIds='<MultiItems></MultiItems>'

    , @MultiAlternateItemIds='<MultiAlternateItems></MultiAlternateItems>'

    , @Description=''

    , @ManufacturerId=''

    , @AlternateItemId= ''

    */

    ALTER PROCEDURE [ShipmentRequest].[uspCatalogSearch]

    -----------

    -- required

    -----------

    @Customer AS NVARCHAR(20)

    ,@ParentCustomer AS NVARCHAR(20)

    ,@DataAreaId AS NVARCHAR(4)

    -----------

    -- Optional

    -----------

    ,@ItemId AS NVARCHAR(30) = ''

    ,@Description AS NVARCHAR(60) = ''

    ,@ManufacturerId AS NVARCHAR(10) = ''

    ,@AlternateItemId AS NVARCHAR(30) = ''

    ,@Category AS NVARCHAR(20) = ''

    ,@MultiItemIds AS VARCHAR(max) = '<MultiItems></MultiItems>'

    ,@MultiAlternateItemIds AS VARCHAR(max) = '<MultiAlternateItems></MultiAlternateItems>'

    AS

    BEGIN

    SET NOCOUNT ON

    SET @ItemId = ISNULL(@ItemId, '')

    SET @Description = ISNULL(@Description, '')

    SET @ManufacturerId = ISNULL(@ManufacturerId, '')

    SET @AlternateItemId = ISNULL(@AlternateItemId, '')

    SET @Category = ISNULL(@Category, '')

    SET @MultiItemIds = CASE WHEN @MultiItemIds IS NULL OR @MultiItemIds = '' THEN '<MultiItems></MultiItems>' ELSE @MultiItemIds END

    SET @MultiAlternateItemIds = CASE WHEN @MultiAlternateItemIds IS NULL OR @MultiAlternateItemIds = '' THEN '<MultiAlternateItems></MultiAlternateItems>' ELSE @MultiAlternateItemIds END

    -- Handles for XML Documents

    DECLARE @MultiItems INT

    DECLARE @MultiAlternateItems INT

    -- Prepare the xml documents if any

    EXEC sp_xml_preparedocument @MultiItems OUTPUT, @MultiItemIds;

    EXEC sp_xml_preparedocument @MultiAlternateItems OUTPUT, @MultiAlternateItemIds;

    -- Get primary part list

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    INTO #FilteredPartsList

    FROM PactProductCatalog AS P

    WHERE P.Category LIKE ISNULL(@Category, '') + '%'

    AND P.ManufacturerId LIKE ISNULL(@ManufacturerId, '') + '%'

    AND P.ItemId LIKE '%' + ISNULL(@ItemId, '') + '%'

    AND P.ItemDescription LIKE '%' + ISNULL(@Description, '') + '%'

    AND P.AlternateItemId LIKE ISNULL(@ALTERNATEITEMID, '') + '%'

    AND P.CUSTACCOUNT = @ParentCustomer and P.DATAAREAID = @DataAreaId

    ORDER BY p.ItemId, p.Manufacturer, p.ItemDescription

    IF (@MultiItemIds != '<MultiItems></MultiItems>')

    BEGIN

    -- multi item search

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    INTO #FilteredPartsListMultiItems

    FROM #FilteredPartsList AS P

    INNER JOIN OPENXML(@MultiItems, '/MultiItems/Item', 1) WITH (ItemId NVARCHAR(30)) AS M ON P.ItemId = M.ItemId

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,'' as Project --to support backwards compat of mitch's crap

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    ,CAST(CASE WHEN ISNULL(IA.QTYAVAILABLE,0)>0 THEN ISNULL(IA.QTYAVAILABLE,0) ELSE 0 END AS INT) AS QtyAvailable

    ,CAST(ISNULL(IA.QtyOnPO, 0) AS INT) AS QtyOnPO

    FROM #FilteredPartsListMultiItems P

    LEFT JOIN InventoryAvailable_AggregatedQuantities IA ON P.ITEMID = IA.ITEMID AND IA.DATAAREAID = @DataAreaId AND IA.ACCOUNTNUM = @customer

    DROP TABLE #FilteredPartsListMultiItems

    END

    ELSE

    BEGIN

    IF (@MultiAlternateItemIds != '<MultiAlternateItems></MultiAlternateItems>')

    BEGIN

    -- multi alternate item search

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    INTO #FilteredPartsListMultiAlternateItems

    FROM #FilteredPartsList AS P

    INNER JOIN OPENXML(@MultiAlternateItems, '/MultiAlternateItems/AlternateItem', 1) WITH (AlternateItemId NVARCHAR(30)) AS M ON P.AlternateItemId = M.AlternateItemId

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,'' as Project --to support backwards compat of mitch's crap

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    ,CAST(CASE WHEN ISNULL(IA.QTYAVAILABLE,0)>0 THEN ISNULL(IA.QTYAVAILABLE,0) ELSE 0 END AS INT) AS QtyAvailable

    ,CAST(ISNULL(IA.QtyOnPO, 0) AS INT) AS QtyOnPO

    FROM #FilteredPartsListMultiAlternateItems P

    LEFT JOIN InventoryAvailable_AggregatedQuantities IA ON P.ITEMID = IA.ITEMID AND IA.DATAAREAID = @DataAreaId AND IA.ACCOUNTNUM = @customer

    DROP TABLE #FilteredPartsListMultiAlternateItems

    END

    ELSE

    BEGIN

    -- normal part search

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,'' as Project --to support backwards compat of mitch's crap

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    ,CAST(CASE WHEN ISNULL(IA.QTYAVAILABLE,0)>0 THEN ISNULL(IA.QTYAVAILABLE,0) ELSE 0 END AS INT) AS QtyAvailable

    ,CAST(ISNULL(IA.QtyOnPO, 0) AS INT) AS QtyOnPO

    FROM #FilteredPartsList P

    LEFT JOIN InventoryAvailable_AggregatedQuantities IA ON P.ITEMID = IA.ITEMID AND IA.DATAAREAID = @DataAreaId AND IA.ACCOUNTNUM = @customer

    END

    END

    DROP TABLE #FilteredPartsList

    -- Release Documents

    EXEC sp_xml_removedocument @MultiItems

    EXEC sp_xml_removedocument @MultiAlternateItems

    SET NOCOUNT OFF

    END

  • If you take a look at the second link in my sig (index/tuning help) you'll see some pieces we need to help you. Even though you can't get an actual execution plan due to the timeout, even the estimated can help. We'll definately need to see table/index definitions as well, though, with an actual.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • dndaughtery (2/13/2014)


    I have a stored procedure that is timing out. I have added indexes but it still isn't working as it should. Any advice is appreciated:

    USE [AXInquiry]

    GO

    /****** Object: StoredProcedure [ShipmentRequest].[uspCatalogSearch] Script Date: 02/13/2014 13:04:05 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /* TEST

    ShipmentRequest.uspCatalogSearch @Customer='TWTC001'

    , @ParentCustomer='TWTC000'

    , @DataAreaId='mdsi'

    , @ItemId='4900M-ACPWRKIT'

    , @Category=''

    , @MultiItemIds='<MultiItems></MultiItems>'

    , @MultiAlternateItemIds='<MultiAlternateItems></MultiAlternateItems>'

    , @Description=''

    , @ManufacturerId=''

    , @AlternateItemId= ''

    */

    ALTER PROCEDURE [ShipmentRequest].[uspCatalogSearch]

    -----------

    -- required

    -----------

    @Customer AS NVARCHAR(20)

    ,@ParentCustomer AS NVARCHAR(20)

    ,@DataAreaId AS NVARCHAR(4)

    -----------

    -- Optional

    -----------

    ,@ItemId AS NVARCHAR(30) = ''

    ,@Description AS NVARCHAR(60) = ''

    ,@ManufacturerId AS NVARCHAR(10) = ''

    ,@AlternateItemId AS NVARCHAR(30) = ''

    ,@Category AS NVARCHAR(20) = ''

    ,@MultiItemIds AS VARCHAR(max) = '<MultiItems></MultiItems>'

    ,@MultiAlternateItemIds AS VARCHAR(max) = '<MultiAlternateItems></MultiAlternateItems>'

    AS

    BEGIN

    SET NOCOUNT ON

    SET @ItemId = ISNULL(@ItemId, '')

    SET @Description = ISNULL(@Description, '')

    SET @ManufacturerId = ISNULL(@ManufacturerId, '')

    SET @AlternateItemId = ISNULL(@AlternateItemId, '')

    SET @Category = ISNULL(@Category, '')

    SET @MultiItemIds = CASE WHEN @MultiItemIds IS NULL OR @MultiItemIds = '' THEN '<MultiItems></MultiItems>' ELSE @MultiItemIds END

    SET @MultiAlternateItemIds = CASE WHEN @MultiAlternateItemIds IS NULL OR @MultiAlternateItemIds = '' THEN '<MultiAlternateItems></MultiAlternateItems>' ELSE @MultiAlternateItemIds END

    -- Handles for XML Documents

    DECLARE @MultiItems INT

    DECLARE @MultiAlternateItems INT

    -- Prepare the xml documents if any

    EXEC sp_xml_preparedocument @MultiItems OUTPUT, @MultiItemIds;

    EXEC sp_xml_preparedocument @MultiAlternateItems OUTPUT, @MultiAlternateItemIds;

    -- Get primary part list

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    INTO #FilteredPartsList

    FROM PactProductCatalog AS P

    WHERE P.Category LIKE ISNULL(@Category, '') + '%'

    AND P.ManufacturerId LIKE ISNULL(@ManufacturerId, '') + '%'

    AND P.ItemId LIKE '%' + ISNULL(@ItemId, '') + '%'

    AND P.ItemDescription LIKE '%' + ISNULL(@Description, '') + '%'

    AND P.AlternateItemId LIKE ISNULL(@ALTERNATEITEMID, '') + '%'

    AND P.CUSTACCOUNT = @ParentCustomer and P.DATAAREAID = @DataAreaId

    ORDER BY p.ItemId, p.Manufacturer, p.ItemDescription

    IF (@MultiItemIds != '<MultiItems></MultiItems>')

    BEGIN

    -- multi item search

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    INTO #FilteredPartsListMultiItems

    FROM #FilteredPartsList AS P

    INNER JOIN OPENXML(@MultiItems, '/MultiItems/Item', 1) WITH (ItemId NVARCHAR(30)) AS M ON P.ItemId = M.ItemId

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,'' as Project --to support backwards compat of mitch's crap

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    ,CAST(CASE WHEN ISNULL(IA.QTYAVAILABLE,0)>0 THEN ISNULL(IA.QTYAVAILABLE,0) ELSE 0 END AS INT) AS QtyAvailable

    ,CAST(ISNULL(IA.QtyOnPO, 0) AS INT) AS QtyOnPO

    FROM #FilteredPartsListMultiItems P

    LEFT JOIN InventoryAvailable_AggregatedQuantities IA ON P.ITEMID = IA.ITEMID AND IA.DATAAREAID = @DataAreaId AND IA.ACCOUNTNUM = @customer

    DROP TABLE #FilteredPartsListMultiItems

    END

    ELSE

    BEGIN

    IF (@MultiAlternateItemIds != '<MultiAlternateItems></MultiAlternateItems>')

    BEGIN

    -- multi alternate item search

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    INTO #FilteredPartsListMultiAlternateItems

    FROM #FilteredPartsList AS P

    INNER JOIN OPENXML(@MultiAlternateItems, '/MultiAlternateItems/AlternateItem', 1) WITH (AlternateItemId NVARCHAR(30)) AS M ON P.AlternateItemId = M.AlternateItemId

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,'' as Project --to support backwards compat of mitch's crap

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    ,CAST(CASE WHEN ISNULL(IA.QTYAVAILABLE,0)>0 THEN ISNULL(IA.QTYAVAILABLE,0) ELSE 0 END AS INT) AS QtyAvailable

    ,CAST(ISNULL(IA.QtyOnPO, 0) AS INT) AS QtyOnPO

    FROM #FilteredPartsListMultiAlternateItems P

    LEFT JOIN InventoryAvailable_AggregatedQuantities IA ON P.ITEMID = IA.ITEMID AND IA.DATAAREAID = @DataAreaId AND IA.ACCOUNTNUM = @customer

    DROP TABLE #FilteredPartsListMultiAlternateItems

    END

    ELSE

    BEGIN

    -- normal part search

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,'' as Project --to support backwards compat of mitch's crap

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    ,CAST(CASE WHEN ISNULL(IA.QTYAVAILABLE,0)>0 THEN ISNULL(IA.QTYAVAILABLE,0) ELSE 0 END AS INT) AS QtyAvailable

    ,CAST(ISNULL(IA.QtyOnPO, 0) AS INT) AS QtyOnPO

    FROM #FilteredPartsList P

    LEFT JOIN InventoryAvailable_AggregatedQuantities IA ON P.ITEMID = IA.ITEMID AND IA.DATAAREAID = @DataAreaId AND IA.ACCOUNTNUM = @customer

    END

    END

    DROP TABLE #FilteredPartsList

    -- Release Documents

    EXEC sp_xml_removedocument @MultiItems

    EXEC sp_xml_removedocument @MultiAlternateItems

    SET NOCOUNT OFF

    END

    1) You ISNULL all of those variables at the top, but then you ISNULL them AGAIN in the WHERE clause. That is BAD BAD! NEVER wrap a function around a column in a WHERE clause if you can at all help it. And here it is completely redundant. Remove them and see how perf goes.

    2) Perf may still suck because you have a complex WHERE clause with lots of LIKEs. It can be tough for optimizer to get accurate estimates with that initial grab. Hopefully this will be a magic bullet.

    3) You have an ORDER BY for an INSERT into a temp table. That is completely useless and work for nothing. Remove it. There is NO ordering in set based processing unless you specify it on the outermost action, and in this case that action comes when you JOIN to that temp table.

    4) I note numerous situations of "ID" fields that are declared as some form of char. ALWAYS use EXACTLY the same datatype. This is an incredibly common mistake I see at clients and it can be 4-5 orders of magnitude perf hit, and concurrency killer as well, due to the CONVERT_IMPLICIT you will get under the covers. An int is not a varchar, a varchar is not an Nvarchar, etc. etc.

    5) I almost NEVER JOIN to OPENXML like you have. Put the outputs into a temp table (NOT table variable) first, using the correct datatype(s) you are JOINing to, then use the temp table for the JOIN.

    6) You also should be checking for blocking during execution. sp_whoisactive is a great, free tool.

    7) You should also do a file IO stall and wait stats differential analysis while the code is running a long time. Perhaps your tempdb is suffering from horrible IO performance and that is killing things overall. Lots of possibilities here.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Everything Kevin said, then check out the execution plan to understand where the indexes are getting used and where they aren't. Just tossing them vaguely towards a query never works out well.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for the info guys. I have another idea I'd like to run by you. In the stored procedure the temp tables are being created with a select .... INTO query. Would it speed things up if I created the temp table and created a clustered index on it like this:

    Create Table #FilteredPartsListMultiAlternateItems(

    ItemId varchar(60) null

    ,ItemDescription varchar(60) null

    ,Manufacturer varchar(50) null

    ,AlternateItemId varchar(50) null

    ,StandardCost numeric(28,12) null

    ,CustomerInventClass varchar(20) null

    ,Category varchar(10) null

    ,IsKit bit null

    ,HasSubstitution bit null

    ,IsItemConfigurable bit null

    )

    CREATE CLUSTERED INDEX IDX_FilteredPartsListMultiAlternateItems ON #FilteredPartsListMultiAlternateItems(ItemId,Manufacturer)

    -- multi alternate item search

    INSERT INTO #FilteredPartsList( ItemId

    ,ItemDescription

    ,Manufacturer

    ,AlternateItemId

    ,StandardCost

    ,CustomerInventClass

    ,Category

    ,IsKit

    ,HasSubstitution

    ,IsItemConfigurable )

    SELECT

    P.ItemId

    ,P.ItemDescription

    ,P.Manufacturer

    ,P.AlternateItemId

    ,P.StandardCost

    ,P.CustomerInventClass

    ,P.Category

    ,P.IsKit

    ,P.HasSubstitution

    ,P.IsItemConfigurable

    --INTO #FilteredPartsListMultiAlternateItems

    FROM #FilteredPartsList AS P

    INNER JOIN OPENXML(@MultiAlternateItems, '/MultiAlternateItems/AlternateItem', 1) WITH (AlternateItemId NVARCHAR(30)) AS M ON P.AlternateItemId = M.AlternateItemId

  • Yeah, it could, although you might be better off creating the index on the table after you load it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Great point Grant, thx

  • I have REMOVED indexed from temp tables at clients probably 10 times as often as I have ADDED them to enhance performance. The two main times I have found indexes to be helpful to the overall performance of a process is a) you ITERATIVELY hit the temp object with what is now a seek with the index in place and b) you get a MERGE join because the clustered index on temp object allows for it. The first of those is often associated with cursor-based processing that can usually be replaced. And for the second the optimizer is getting increasingly able to put a SORT into the query plan on the temp object output and still get the MERGE join automatically.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply