February 13, 2014 at 11:41 am
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
February 13, 2014 at 11:43 am
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.
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
February 13, 2014 at 12:52 pm
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
February 14, 2014 at 4:40 am
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
February 14, 2014 at 6:56 am
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
February 14, 2014 at 7:00 am
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
February 14, 2014 at 7:03 am
Great point Grant, thx
February 14, 2014 at 9:03 am
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