json与xml 是比较好的传参方式,那哪个比较优秀呢?
由下可知:xml 稍慢一点,可能是因为多了一些标记导致的
USE tempdb
GO
DECLARE @json VARCHAR(MAX)
SET @json=(
SELECT TOP 10000 NEWID() AS id
FROM MASTER.dbo.spt_values a CROSS JOIN MASTER.dbo.spt_values b
FOR json PATH)
DECLARE @d DATETIME
SET @d=GETDATE()
SELECT id
FROM OPENJSON(@json)
WITH (
id VARCHAR(50) 'strict $.id'
)
SELECT DATEDIFF(ms,@d,GETDATE()) AS ms
/*
ms
30~50
*/
USE tempdb
GO
DECLARE @x XML
SET @x=(
SELECT TOP 10000 ltrim(NEWID()) i
FROM MASTER.dbo.spt_values a CROSS JOIN MASTER.dbo.spt_values b
FOR XML PATH('r'))
DECLARE @d DATETIME
SET @d=GETDATE()
SELECT
T.C.value('i[1]','varchar(50)') AS id
FROM @x.nodes('//r') AS T(C)
SELECT DATEDIFF(ms,@d,GETDATE()) AS ms
/*
ms
47~66
*/