I have been monitoring the execution of my stored procedures in my webservers in order to tune them for best performance.
I will call the procedure that has most executions per day as myprocedure and its code is here:
create procedure [dbo].[myprocedure] (
@Tier1 varchar(10),
@LanguageID int,
@SeasonItemID VARCHAR(5) = NULL,
@ListNoteTypeCode varchar(max),
@CacheDuration int output )
with execute as 'myuser'
as
declare @NoteTypeCodeTable table (
NoteTypeCode varchar(50) )
declare @ListNoteTypeCodeXml xml
set @ListNoteTypeCodeXml = convert(xml, @ListNoteTypeCode)
insert into @NoteTypeCodeTable ( NoteTypeCode )
select BulletPoint.NoteTypeCode.value('.', 'varchar(50)') as NoteTypeCode
from @ListNoteTypeCodeXml.nodes('/BulletPoint/NoteTypeCode') as BulletPoint ( NoteTypeCode )
select pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,
pbp.NoteGroup, pbp.SortOrder
from dbo.ProductBulletPoint pbp
join @NoteTypeCodeTable ntc on pbp.NoteTypeCode = ntc.NoteTypeCode
where Tier1 = @Tier1 and
LanguageId = @LanguageID and
(
SeasonItemId = @SeasonItemID
or
@SeasonItemID is null
)
select @CacheDuration = Duration
from dbo.CacheDuration
where [Key] = 'Product'
GO
This procedure receives a parameter @ListNoteTypeCode that is used inside the procedure to convert into a table variable all the possible values, most of the time one single value.
the table variable, later is used in a join.
how can I improve the performance of this stored procedure? the bit with the xml part is high cpu intensive, is there another way to pass a table to a procedure, making best usage of CPU?
I still have to run a good trace and identify how often this procedure results in more than one value in @ListNoteTypeCode, but regardless of this I still need to find a solution.
I have set up a server side trace in order to identify the possible ways this procedure is generally called. The trace can be seen here.
So far I have only seen variations of the following(with no value to @ListNoteTypeID):
declare @p5 int
set @p5=NULL
exec dbo.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'AR740',
@LanguageID=1,
@SeasonItemID=N'16HSM',
@ListNoteTypeCode=N'',
@CacheDuration=@p5 output
select @p5
that produces the following query plan in XML:
query plan procedure dbo.udpProductBulletPointSelectByTier1NoteTypeCode
If I find anything different I will update this question.
I am using sql server 2005 enterprise edition.
The new procedure
Based on the answer by srutzky and many of the comments and other answers, I came out with the following new procedure:
CREATE PROCEDURE [DENORMV2].[udpProductBulletPointSelectByTier1NoteTypeCode] (
@Tier1 VARCHAR(10),
@LanguageID INT,
@SeasonItemID VARCHAR(5) = NULL,
@ListNoteTypeCode XML,
@CacheDuration INT OUTPUT )
WITH EXECUTE AS 'webUserWithRW'
AS
SELECT pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,
pbp.NoteGroup, pbp.SortOrder
FROM dbo.ProductBulletPoint pbp
WHERE Tier1 = @Tier1
AND LanguageId = @LanguageID
AND ( SeasonItemId = @SeasonItemID
OR
@SeasonItemID is null
)
AND pbp.NoteTypeCode IN (
SELECT NoteTypeCode=BulletPoint.NoteTypeCode.value('./text()[1]', 'varchar(50)')
FROM @ListNoteTypeCode.nodes('/BulletPoint/NoteTypeCode') AS BulletPoint ( NoteTypeCode )
)
SELECT @CacheDuration = Duration
FROM dbo.CacheDuration
WHERE [Key] = 'Product'
GO
when I run the following, in order to compare both procedures:
SET STATISTICS IO ON
SET STATISTICS TIME ON
use US16HSMMProduct_ORIGINAL
go
declare @p5 int set @p5=86400
exec dbo.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>FootwearAccessoryComposition</NoteTypeCode></BulletPoint>',
@CacheDuration=@p5 output select @p5
use US16HSMMProduct_AFTER_CHANGES
go
declare @p5 int set @p5=86400
exec DenormV2.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>FootwearAccessoryComposition</NoteTypeCode></BulletPoint>',
@CacheDuration=@p5 output select @p5
I get this from SQL Sentry plan explorer (partial view):
and this is from sql server (partial view)
option(recompile)
to theSELECT
underneath. Attribute-based XML is more efficient at higher volumes but I have to pump 100,000 records into my test rig to get it to run in >0.4s. – wBob Jul 29 '16 at 13:53@ListNoteTypeCode
? And what is the maximum number of them that can be sent to this proc? For the moment, you might see some gain by changing the XQuery in the.value()
function to be./text()[1]
instead of just.
. – Solomon Rutzky Jul 29 '16 at 19:46