July 5, 2017 at 3:59 pm
Hi,
Is there any way the performance can be improved if a XML input variable is passed whch cntains all col values and there after each column is extracted to each variable and worked on Vs creating a table variable and get the xml values to the table variable?
I know that XML value is useful if we do not know the correct data type or varying data types but if we know that there are 50 columns and most of them with certain data types, replacing option 1 to a table variable, can have any added advantage?
Thanks.
July 5, 2017 at 6:47 pm
An interesting idea. My guess would be no, but that's based on my experience with XML, which isn't expansive. What have you tried thus far?
July 5, 2017 at 10:30 pm
If you don'y know your data type, how are you going to extract it from the XML?
I had a similar question about the performance difference between extracting data from XML vs a TVP, so I wrote up a few tests.
My tests seemed to indicate that the TVPs performed better than shredding XML.
However, note that if you want to alter a TVP, you need to drop all procs that reference it, make the change, and then rebuild the procs.
First, create some sample dataSET NOCOUNT ON;
DECLARE @SAMPLE_SIZE BIGINT = 1000000;
DECLARE
@MinInt INT = 100
, @MaxInt INT = 50000
, @StartDate DATE = DATEADD(YY, DATEDIFF(YY, 0, GETDATE())-10, 0)
, @EndDate DATE = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)
, @MinAmount DECIMAL(18,2) = 100.00
, @MaxAmount DECIMAL(18,2) = 1000.00;
WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(@SAMPLE_SIZE) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
SELECT
RowNum = IDENTITY(INT,1,1) -- NM.N
, IntVal = ABS(CHECKSUM(NEWID())%(@MaxInt-@MinInt))+@MinInt
, DecVal = CONVERT(DECIMAL(18,2),RAND(CHECKSUM(NEWID()))*(@MaxAmount-@MinAmount)+@MinAmount)
, DateVal = RAND(CHECKSUM(NEWID()))*DATEDIFF(DD,@StartDate,@EndDate)+CONVERT(DATETIME,@StartDate)
, GUIDVal = NEWID()
, HexVal = RIGHT(NEWID(),12)
, ShortStringVal = LOWER(RIGHT(REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', ''), ABS(CHECKSUM(NEWID())%5)+3))
, LongStringVal = LOWER(RIGHT(REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', ''), ABS(CHECKSUM(NEWID())%30)+15))
, ShortUnicodeVal = RIGHT(REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', ''), ABS(CHECKSUM(NEWID())%5)+3)
, LongUnicodeVal = RIGHT(REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', ''), ABS(CHECKSUM(NEWID())%30)+15)
INTO dbo.SampleData
FROM NUMS AS NM
OPTION (RECOMPILE);
GO
ALTER TABLE dbo.SampleData
ADD PRIMARY KEY CLUSTERED (RowNum);
GO
Next, create a TVP, and some procs to use TVP and XML params in different ways.CREATE TYPE dbo.TVP AS TABLE (
RowNum INT NOT NULL
, IntVal INT NOT NULL
, DecVal DECIMAL(18,2) NOT NULL
, DateVal DATETIME NOT NULL
, GUIDVal UNIQUEIDENTIFIER NOT NULL
, PRIMARY KEY CLUSTERED (RowNum)
)
GO
CREATE PROCEDURE dbo.pr_ShredParam_TVP
@TVP dbo.TVP READONLY
AS
BEGIN
SELECT
RowNum
, IntVal
, DecVal
, DateVal
, GUIDVal
INTO #TVP
FROM @TVP;
END;
GO
CREATE PROCEDURE dbo.pr_ShredParam_XAT
@XAT XML
AS
BEGIN
SELECT src='@XAT',
RowNum = T.r.value('@RowNum', 'INT')
, IntVal = T.r.value('@IntVal', 'INT')
, DecVal = T.r.value('@DecVal', 'DECIMAL(18,2)')
, DateVal = T.r.value('@DateVal', 'DATETIME')
, GUIDVal = T.r.value('@GUIDVal', 'UNIQUEIDENTIFIER')
INTO #XAT
FROM @XAT.nodes('TVP/row') T(r);
END;
GO
CREATE PROCEDURE dbo.pr_ShredParam_XLM
@XLM XML
AS
BEGIN
SELECT src='@XLM',
RowNum = T.r.value('(./RowNum/text())[1]', 'INT')
, IntVal = T.r.value('(./IntVal/text())[1]', 'INT')
, DecVal = T.r.value('(./DecVal/text())[1]', 'DECIMAL(18,2)')
, DateVal = T.r.value('(./DateVal/text())[1]', 'DATETIME')
, GUIDVal = T.r.value('(./GUIDVal/text())[1]', 'UNIQUEIDENTIFIER')
INTO #XLM
FROM @XLM.nodes('TVP/row') T(r);
END;
GO
CREATE PROCEDURE dbo.pr_JoinParam_TTB
@TVP dbo.TVP READONLY
AS
BEGIN
DECLARE @Rcrds INT;
SELECT
RowNum
, IntVal
, DecVal
, DateVal
, GUIDVal
INTO #TVP
FROM @TVP;
SELECT @Rcrds = COUNT(*)
FROM dbo.SampleData As src
INNER JOIN #TVP as p
ON src.RowNum = p.RowNum;
END;
GO
CREATE PROCEDURE dbo.pr_JoinParam_TTV
@TVP dbo.TVP READONLY
AS
BEGIN
DECLARE @Rcrds INT;
DECLARE @T TABLE (
RowNum INT NOT NULL
, IntVal INT NOT NULL
, DecVal DECIMAL(18,2) NOT NULL
, DateVal DATETIME NOT NULL
, GUIDVal UNIQUEIDENTIFIER NOT NULL
, PRIMARY KEY CLUSTERED (RowNum)
);
INSERT INTO @T ( RowNum, IntVal, DecVal, DateVal, GUIDVal )
SELECT RowNum, IntVal, DecVal, DateVal, GUIDVal
FROM @TVP;
SELECT @Rcrds = COUNT(*)
FROM dbo.SampleData As src
INNER JOIN @T as p
ON src.RowNum = p.RowNum;
END;
GO
CREATE PROCEDURE dbo.pr_JoinParam_TVP
@TVP dbo.TVP READONLY
AS
BEGIN
DECLARE @Rcrds INT;
SELECT @Rcrds = COUNT(*)
FROM dbo.SampleData As src
INNER JOIN @TVP as p
ON src.RowNum = p.RowNum;
END;
GO
CREATE PROCEDURE dbo.pr_JoinParam_XAT
@XAT XML
AS
BEGIN
DECLARE @Rcrds INT;
SELECT @Rcrds = COUNT(*)
FROM dbo.SampleData As src
INNER JOIN (
SELECT
RowNum = T.r.value('@RowNum', 'INT')
, IntVal = T.r.value('@IntVal', 'INT')
, DecVal = T.r.value('@DecVal', 'DECIMAL(18,2)')
, DateVal = T.r.value('@DateVal', 'DATETIME')
, GUIDVal = T.r.value('@GUIDVal', 'UNIQUEIDENTIFIER')
FROM @XAT.nodes('TVP/row') T(r)
) as p
ON src.RowNum = p.RowNum;
END;
GO
CREATE PROCEDURE dbo.pr_JoinParam_XLM
@XLM XML
AS
BEGIN
DECLARE @Rcrds INT;
SELECT @Rcrds = COUNT(*)
FROM dbo.SampleData As src
INNER JOIN (
SELECT
RowNum = T.r.value('(./RowNum/text())[1]', 'INT')
, IntVal = T.r.value('(./IntVal/text())[1]', 'INT')
, DecVal = T.r.value('(./DecVal/text())[1]', 'DECIMAL(18,2)')
, DateVal = T.r.value('(./DateVal/text())[1]', 'DATETIME')
, GUIDVal = T.r.value('(./GUIDVal/text())[1]', 'UNIQUEIDENTIFIER')
FROM @XLM.nodes('TVP/row') T(r)
) as p
ON src.RowNum = p.RowNum;
END;
GO
Consume the procsCREATE TABLE #Stats (
ID INT IDENTITY(1,1) NOT NULL
, RunTime DATETIME2(7) NOT NULL DEFAULT(SYSDATETIME())
, NumRcrds INT NOT NULL
, object_id INT NOT NULL
, ProcName NVARCHAR(128) NOT NULL
, execution_count_before BIGINT NOT NULL
, total_worker_time_before BIGINT NOT NULL -- CPU time, in microseconds
, total_elapsed_time_before BIGINT NOT NULL -- Clock time, in microseconds
, total_logical_reads_before BIGINT NOT NULL
, total_logical_writes_before BIGINT NOT NULL
, total_physical_reads_before BIGINT NOT NULL
, execution_count_after BIGINT NOT NULL DEFAULT(0)
, total_worker_time_after BIGINT NOT NULL DEFAULT(0)
, total_elapsed_time_after BIGINT NOT NULL DEFAULT(0)
, total_logical_reads_after BIGINT NOT NULL DEFAULT(0)
, total_logical_writes_after BIGINT NOT NULL DEFAULT(0)
, total_physical_reads_after BIGINT NOT NULL DEFAULT(0)
, execution_count AS execution_count_after - execution_count_before
, total_worker_time AS total_worker_time_after - total_worker_time_before
, total_elapsed_time AS total_elapsed_time_after - total_elapsed_time_before
, total_logical_reads AS total_logical_reads_after - total_logical_reads_before
, total_logical_writes AS total_logical_writes_after - total_logical_writes_before
, total_physical_reads AS total_physical_reads_after - total_physical_reads_before
, PRIMARY KEY CLUSTERED (object_id, NumRcrds, ID)
);
GO
DECLARE
@minVal INT = 1
, @maxVal INT = 5000
, @NumRcrds INT
, @NumRuns INT;
DECLARE
@TVP dbo.TVP
, @XAT XML
, @XLM XML;
SET @NumRuns = ABS(CHECKSUM(NEWID())%10)+10;
WHILE (@NumRuns > 0)
BEGIN
SET @NumRcrds = ABS(CHECKSUM(NEWID())%(@maxVal-@minVal))+@minVal;
RAISERROR('Run %i ... %i Rcrds', 0, 1, @NumRuns, @NumRcrds) WITH NOWAIT;
DELETE FROM @TVP;
INSERT INTO @TVP ( RowNum, IntVal, DecVal, DateVal, GUIDVal )
SELECT TOP(@NumRcrds) RowNum, IntVal, DecVal, DateVal, GUIDVal
FROM dbo.SampleData
ORDER BY NEWID();
SET @XAT = (
SELECT
RowNum [@RowNum]
, IntVal [@IntVal]
, DecVal [@DecVal]
, DateVal [@DateVal]
, GUIDVal [@GUIDVal]
FROM @TVP
FOR XML PATH('row'), ROOT('TVP'), TYPE
);
SET @XLM = (
SELECT
RowNum [RowNum]
, IntVal [IntVal]
, DecVal [DecVal]
, DateVal [DateVal]
, GUIDVal [GUIDVal]
FROM @TVP
FOR XML PATH('row'), ROOT('TVP'), TYPE
);
insert into #Stats (
NumRcrds
, object_id
, ProcName
, execution_count_before
, total_worker_time_before
, total_elapsed_time_before
, total_logical_reads_before
, total_logical_writes_before
, total_physical_reads_before
)
select
@NumRcrds
, object_id
, ProcName = OBJECT_NAME(object_id)
, execution_count = SUM(execution_count)
, total_worker_time = SUM(total_worker_time)
, total_elapsed_time = SUM(total_elapsed_time)
, total_logical_reads = SUM(total_logical_reads)
, total_logical_writes = SUM(total_logical_writes)
, total_physical_reads = SUM(total_physical_reads)
from sys.dm_exec_procedure_stats
where database_id = DB_ID()
and [type] = 'P'
and ( OBJECT_NAME(object_id) like 'pr_ShredParam%'
or OBJECT_NAME(object_id) like 'pr_JoinParam%')
group by object_id, OBJECT_NAME(object_id)
order by object_id, OBJECT_NAME(object_id);
EXEC dbo.pr_ShredParam_TVP @TVP;
EXEC dbo.pr_ShredParam_XAT @XAT;
EXEC dbo.pr_ShredParam_XLM @XLM;
EXEC dbo.pr_JoinParam_TVP @TVP;
EXEC dbo.pr_JoinParam_TTB @TVP;
EXEC dbo.pr_JoinParam_TTV @TVP;
EXEC dbo.pr_JoinParam_XAT @XAT;
EXEC dbo.pr_JoinParam_XLM @XLM;
update s
set
execution_count_after = v.execution_count
, total_worker_time_after = v.total_worker_time
, total_elapsed_time_after = v.total_elapsed_time
, total_logical_reads_after = v.total_logical_reads
, total_logical_writes_after = v.total_logical_writes
, total_physical_reads_after = v.total_physical_reads
from #Stats as s
inner join (
select
object_id
, execution_count = SUM(execution_count)
, total_worker_time = SUM(total_worker_time)
, total_elapsed_time = SUM(total_elapsed_time)
, total_logical_reads = SUM(total_logical_reads)
, total_logical_writes = SUM(total_logical_writes)
, total_physical_reads = SUM(total_physical_reads)
from sys.dm_exec_procedure_stats
where database_id = DB_ID()
and [type] = 'P'
and ( OBJECT_NAME(object_id) like 'pr_ShredParam%'
or OBJECT_NAME(object_id) like 'pr_JoinParam%')
group by object_id, OBJECT_NAME(object_id)
) as v
on s.object_id = v.object_id
and s.NumRcrds = @NumRcrds
and s.execution_count_after = 0;
SET @NumRuns -= 1;
END;
Validate the resultswith cteStats AS (
select
ProcName
, NumRcrds = case
when NumRcrds <= 100 then ' 1 ... 100'
when NumRcrds <= 250 then ' 101 ... 250'
when NumRcrds <= 500 then ' 251 ... 500'
when NumRcrds <= 1000 then ' 501 ... 1000'
when NumRcrds <= 1500 then '1001 ... 1500'
when NumRcrds <= 2000 then '1501 ... 2000'
when NumRcrds <= 5000 then '2001 ... 5000'
else '5001 ...'
end
, execution_count
, total_worker_time
, total_elapsed_time
, total_logical_reads
, total_logical_writes
, total_physical_reads
from #Stats
)
select
ProcName
, NumRcrds
, execution_count = SUM(execution_count)
, avg_worker_time = AVG(total_worker_time)
, avg_elapsed_time = AVG(total_elapsed_time)
, avg_logical_reads = AVG(total_logical_reads)
--, avg_logical_writes = AVG(total_logical_writes)
--, avg_physical_reads = AVG(total_physical_reads)
from cteStats
where ProcName like 'pr_JoinParam%'
group by ProcName, NumRcrds
order by ProcName, NumRcrds;
July 6, 2017 at 1:38 am
SQL-DBA-01 - Wednesday, July 5, 2017 3:59 PMHi,Is there any way the performance can be improved if a XML input variable is passed whch cntains all col values and there after each column is extracted to each variable and worked on Vs creating a table variable and get the xml values to the table variable?
I know that XML value is useful if we do not know the correct data type or varying data types but if we know that there are 50 columns and most of them with certain data types, replacing option 1 to a table variable, can have any added advantage?
There are several options here but we need more information, samples and what you have tried so far.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply