SQL Server XML Statistics and Execution Plans

QL Scripts


–Test Table
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N’[TableA]‘) )
DROP TABLE TableA
GO
CREATE TABLE TableA (
GroupID int NOT NULL,
ID int NOT NULL,
rMoney money NOT NULL,
rDate datetime NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ( [ID] ) WITH FILLFACTOR = 100 )
GO

SET NOCOUNT ON
DECLARE @BatchTotal int, @BatchSize int, @TotalRows int, @BatchStart int, @BatchEnd int, @BatchRow int, @I int, @RowsPerPage int, @Card int, @DistinctValues int
SELECT @BatchStart=1, @BatchEnd=1, @BatchTotal=1, @BatchSize=100200, @RowsPerPage=100, @Card=300
SELECT @TotalRows=@BatchTotal*@BatchSize SELECT @I=(@BatchStart-1)*@BatchSize+1, @DistinctValues=@TotalRows/@Card
WHILE @BatchStart <= @BatchEnd BEGIN BEGIN TRANSACTION
SELECT @BatchRow = @BatchStart*@BatchSize WHILE @I <= @BatchRow BEGIN
INSERT TableA (GroupID, ID, rMoney, rDate)
VALUES ( (@I-1)/@Card+1, @I, 10000*rand(), DATEADD(hour,@I,’1900-01-01′) )
IF @@ERROR > 0 BEGIN GOTO B END SET @I = @I+1 END COMMIT TRANSACTION CHECKPOINT
SET @BatchStart = @BatchStart+1
END B: IF @@TRANCOUNT > 0 COMMIT TRANSACTION
GO

CREATE INDEX IX_GroupID ON TableA(GroupID)
GO
Demo 1.
DECLARE @XML nvarchar(4000) DECLARE @idoc INT
SELECT @XML = N’<ROOT><Group1>
<Test GroupID=”1″ ID=”1″ rMoney=”10.10″ rDate=”2005-01-01″/>
<Test GroupID=”1″ ID=”2″ rMoney=”10.20″ rDate=”2005-01-02″/>
<Test GroupID=”1″ ID=”3″ rMoney=”11.20″ rDate=”2005-02-02″/>
</Group1></ROOT>’

exec sp_xml_preparedocument @idoc OUTPUT, @XML

SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime)

SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime)
WHERE GroupID = 1

Demo 2
DECLARE @XML nvarchar(4000) DECLARE @idoc INT
SELECT @XML = N’<ROOT><Group1>
<Test GroupID=”1″ ID=”1″ rMoney=”10.10″ rDate=”2005-01-01″/>
<Test GroupID=”1″ ID=”2″ rMoney=”10.20″ rDate=”2005-01-02″/>
<Test GroupID=”1″ ID=”3″ rMoney=”11.20″ rDate=”2005-02-02″/>
</Group1></ROOT>’

exec sp_xml_preparedocument @idoc OUTPUT, @XML

SELECT * FROM ( SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime) ) s
INNER JOIN ( SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime) ) t ON t.ID = s.ID
Demo 3
DECLARE @XML nvarchar(4000) DECLARE @idoc INT
SELECT @XML = N’<ROOT><Group1>
<Test GroupID=”1″ ID=”1″ rMoney=”10.10″ rDate=”2005-01-01″/>
<Test GroupID=”1″ ID=”2″ rMoney=”10.20″ rDate=”2005-01-02″/>
<Test GroupID=”1″ ID=”3″ rMoney=”11.20″ rDate=”2005-02-02″/>
</Group1></ROOT>’

exec sp_xml_preparedocument @idoc OUTPUT, @XML

SELECT * FROM TableA t INNER JOIN ( SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime) ) x
ON t.ID = x.ID
WHERE t.GroupID = 1

SELECT * FROM TableA t INNER JOIN ( SELECT TOP 100 * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime) ) x
ON t.ID = x.ID
WHERE t.GroupID = 1
Demo 4
DECLARE @XML nvarchar(4000) DECLARE @idoc INT
SELECT @XML = N’<ROOT><Group1>
<Test GroupID=”1″ ID=”1″ rMoney=”10.10″ rDate=”2005-01-01″/>
<Test GroupID=”1″ ID=”2″ rMoney=”10.20″ rDate=”2005-01-02″/>
<Test GroupID=”1″ ID=”3″ rMoney=”11.20″ rDate=”2005-02-02″/>
</Group1></ROOT>’

exec sp_xml_preparedocument @idoc OUTPUT, @XML

INSERT TableA SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime)

INSERT TableA SELECT TOP 500 * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime)


 
Copyright 2005 by the Author.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |