11

I am working with sql server 2005 and I had a situation where many values can be passed in a parameter.

Based on this: Passing multiple values for one SQL parameter this procedure uses XML as a parameter.

here is the code of the stored 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

more info about this procedure here

this is an example of how it can be called:

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

Question:

what is the best way to find out whether or not the parameter @ListNoteTypeCode XML is empty?

what if they call this procedure like this:

declare @p5 int  set @p5=86400 
exec DenormV2.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'',
@CacheDuration=@p5 output  select @p5

as it was suggested here I could avoid the select altogether by testing the parameter @ListNoteTypeCode.

My main goal in this scenario is to retrieve the data in the best possible performance, since this procedure is not cached in the web-servers and is called over a million times a day.

Community
  • 1
Marcello Miorelli
  • 16,170
  • 52
  • 163
  • 300

3 Answers3

10

You may check for NULL and nodes absence (exist method of xml type):

@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('*') = 0

Your XPath can be more specific, if necessary:

@ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('/BulletPoint/NoteTypeCode/text()') = 0
i-one
  • 2,354
  • 2
  • 13
  • 23
  • @marcellomiorelli The check itself may help execute procedure faster in case, when it happen that provided xml instance is empty. In such case SqlServer will not spend resources to subsequent select statement compilation (or possible recompilation) and execution, instead "empty" select statement can be executed (which is much simpler) as suggested in answer to one of the previous questions. – i-one Aug 18 '16 at 12:11
  • 1
    @marcellomiorelli Regarding the '*' vs '/BulletPoint/NoteTypeCode/text()'. It is matter of semantics primarily. If given xml instance can not contain nodes other than specified, then checking just '*' may be sufficient. However, if provided xml instance can contain nodes other than specified, and you use only part of this xml instance then you should be more specific in your XPath to test whether given xml is empty for the purpose of this stored proc. – i-one Aug 18 '16 at 12:12
5

Another way to test for an empty XML parameter, variable, or column is to check the DATALENGTH. Any empty XML item should be 5 bytes. For example:

DECLARE @Test TABLE (XmlParam XML NULL);
INSERT INTO @Test ([XmlParam]) VALUES (NULL), (N''), (N'g');

SELECT t.[XmlParam],
       DATALENGTH(t.[XmlParam]) AS [DATALENGTH],
       CASE (ISNULL(DATALENGTH(t.[XmlParam]), 5))
          WHEN 5 THEN 'EMPTY'
          ELSE 'Not Empty'
       END AS [IsEmpty]
FROM   @Test t;

Returns:

XmlParam     DATALENGTH    IsEmpty
--------     ----------    ---------
NULL         NULL          EMPTY
             5             EMPTY
g            9             Not Empty

Please note that I used ISNULL(DATALENGTH(t.[XmlParam]), 5) as that should be fine for checking a parameter or variable. If checking a column, it might be better to use XmlColumn IS NULL OR DATALENGTH(XmlColumn) = 5.

Please also note that while it is possible for the internal representation of XML data to change between versions, I have tested on SQL Server 2008 R2, 2012, and 2014 and the size of an empty XML item is consistently 5.

Solomon Rutzky
  • 68,731
  • 8
  • 152
  • 292
  • 1
    @marcellomiorelli My gut feeling is that checking DATALENGTH would be faster than invoking the XML parser, BUT I have not tested it. However, it should be easy to test by generating a result set of 1 million rows (CROSS JOIN sys.all_columns) and include my version and then remove that and run again with i-one's version. Please post the results. – Solomon Rutzky Aug 18 '16 at 16:12
  • 1
    I have posted the results of the comparison here Please have a look when you get a chance. – Marcello Miorelli Aug 19 '16 at 15:52
0

My XML uses Schema.
DATALENGTH will definitely not work.
Even with no Data, you will still have Schema taking up random amounts of space.

This will test for Missing Elements/Nodes (works with or without Schema):

SELECT ISNULL(@ListNoteTypeCode.exist('*:BulletPoint/*:NoteTypeCode'), 0)[HasRows]

With that out of the way, the purpose of your question was to improve performance.
You are using a Correlated-SubQuery!  :O
Yikes! Move that into an Table-Variable with a PK on NoteTypeCode.
Then Inner-Join to your Table-Variable and forget about all this Test-For-Missing-Xml business.

MikeTeeVee
  • 263
  • 3
  • 6