在 where 子句中檢查 XML 列是否為空 (@xml = ‘’) 的最快方法是什麼？

以下問題的結論 如何在 sql server 中測試 XML = ‘’？

長話短說，有一個過程，其參數之一是 XML 變數@ListNoteTypeCode。

它是一個 XML，因為它可以包含多個值，並且我使用的是 sql server 2005。

有兩種方法可以檢查@ListNoteTypeCode參數是否為空或是否包含值。

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

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

哪個表現更好？

我很想知道哪個性能更好，所以我做了一個測試，並想在這里分享結果。

我創建了一個表以將一些數據保存在 XML 中，以便稍後進行測試

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

操作，MYXML 列應該能夠接受空值 - 需要更改它

ALTER TABLE RADHE_01
ALTER COLUMN MYXML XML NULL

我創建了一個過程以創建要添加到 MYXML 列的隨機值

我想向該表添加一百萬條記錄

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

我測試程序以檢查它是否正常工作

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

我在表中插入一百萬行。

我保留了我在實時過程中的 XML 格式。

這個 XML 有 2 個值，我忘記了這個細節，把第一個值留在那裡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

我玩數據，為了稍後測試結果，我會將一些行更改為 NULL 並將一些行更改為空''

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)

現在做測試

正如您在下面的腳本中看到的那樣，我已經執行了這兩個查詢。

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

統計TIME和統計IO的結果如下：

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.

這是來自sql server的執行計劃（圖片）

這是上面查詢和圖片的完整 XML 執行計劃。

這是另一張使用其他工具比較執行計劃的圖片。

結論：

對於這種情況，在這種情況下使用DATALENGTH已被證明更快。

引用自：https://dba.stackexchange.com/questions/147329