what is the quickest way to check whether a XML column is empty ( @xml = '') in the where clause?

Question

following the conclusion of the question how to test if XML = '' in sql server?

to cut a long story short there is a procedure which has one of its parameters a XML variable @ListNoteTypeCode.

It is a XML because it can contain more than one value and I am using sql server 2005.

there are 2 ways to check whether or not the @ListNoteTypeCode parameters is empty or contain value(s).

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

2) @ListNoteTypeCode is NULL OR DATALENGTH(@ListNoteTypeCode) = 5

which one performs better?

Answer 1

I was intrigued as which one would perform better so I did a test and would like to share the results here.

I create a table to keep put some data in XML and for testing later on

USE MY_TEST_DATABASE 
GO

--drop table radhe_01
--GO

CREATE TABLE RADHE_01 ( i int not null identity(1,1) primary key clustered,
                        MYXML XML NOT NULL
                       )
GO

Ops, the MYXML column should be able to accept nulls - need to change it

ALTER TABLE RADHE_01
ALTER COLUMN MYXML XML NULL

I create a procedure in order to create random values to be added to MYXML column

I want to add one million records to that table

CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ''

WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))

IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END

I test the procedure to check it is working nicely

declare @randomString varchar(50)
exec SpGenerateRandomString 30, @randomString output
select @randomString 

I insert one million rows into the table.

I kept my XML format as I have in the live procedure.

This XML has 2 values, I forgot about this detail and left the first value there GarmentComposition.

set nocount on
declare @randomString varchar(50)
declare @i int


select @i =1
while @i < 1000000 begin

      exec SpGenerateRandomString 30, @randomString output
      insert into radhe_01 (myxml) values (N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>' + @randomString  + '</NoteTypeCode></BulletPoint>')
      select @i = @i +1
end

I play with the data, in order to test the results later on, I will change some rows to NULL and some rows to empty ''

SELECT COUNT(*) FROM
RADHE_01
WHERE I%117=0
--8547

SELECT COUNT(*) FROM
RADHE_01
WHERE I%1217=0
--821

SET NOCOUNT OFF
UPDATE RADHE_01
SET MYXML = ''
WHERE I%117=0
-- 8547 WILL BE EMPTY
UPDATE RADHE_01
SET MYXML = NULL
WHERE I%1217=0
-- 821 WILL BE NULL
SET NOCOUNT ON

--(8547 row(s) affected)

--(821 row(s) affected)

Now doing the test

I have run both queries as you can see on the script below.

set statistics io off
set statistics time off

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

set statistics io on
set statistics time on

--find the number of records that are null or empty - using XML.exists
select t.*
from RADHE_01 t
where t.MYXML.exist('*') = 0 OR t.MYXML IS NULL


set statistics io off
set statistics time off

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

set statistics io on
set statistics time on


--find the number of records that are null or empty - using datalength
select t.*
from RADHE_01 t
where DATALENGTH(t.MYXML) = 5 OR t.MYXML IS NULL

set statistics io oFF
set statistics time oFF

The result of the statistics TIME and statistics IO are as follows:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Table 'RADHE_01'. Scan count 1, logical reads 23902, physical reads 3, read-ahead reads 23445, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2187 ms,  elapsed time = 2646 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Table 'RADHE_01'. Scan count 5, logical reads 24132, physical reads 1, read-ahead reads 23477, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 391 ms,  elapsed time = 1853 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

This is the execution plan (picture) from sql server

This is the full XML execution plan of the query and picture above.

This is another picture using another tool to compare execution plans.

Conclusion:

Using DATALENGTH on this occasion, for this situation, has been proven quicker.