Performance Issue with XML datatype

Question

Post reply

Performance Issue with XML datatype

priya.ramalingam

Grasshopper

Points: 11
More actions
March 20, 2009 at 6:57 am

#198106

I have a stored procedure with input parameter as @elem_dtls XML.
I will be passing a 4 MB XML data() as Input parameter to this stored procedure.
My worry is it takes almost 4 hours to insert the data to the temp table .I have attached the snippet of the code. Any help would be greatly appreciated.
CREATE Procedure [dbo].[usp_INS_TT_DATA]
(
@elem_dtls xml = null
)
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #tbl_timebooking
(
Cluster VARCHAR(25) NOT NULL,
StoreId Varchar(25) NOT NULL,
StoreName Varchar(50) NOT NULL,
CourseCode VARCHAR(50) NOT NULL,
CourseName VARCHAR(20) NOT NULL,
TechnicalCourseName VARCHAR(20) NOT NULL,
CourseDate DATETIME NOT NULL,
CourseTime DATETIME NOT NULL,
Duration INT NOT NULL,
MaxDelegates INT NULL,
WeekStarting DATETIME NULL,
RoomNo Varchar(10) NOT NULL,
HostId Varchar(25) NOT NULL,
ScheduledDelegates INT NULL,
Attendees INT NULL
)
BEGIN TRAN
INSERT INTO
#tbl_timebooking
SELECT
elem_dtls.header.value('../../CLN[1]','VARCHAR(25)'),
elem_dtls.header.value('../ID[1]','VARCHAR(25)'),
elem_dtls.header.value('../NM[1]','VARCHAR(50)'),
'',
'',
elem_dtls.header.value('TCN[1]','VARCHAR(20)'),
elem_dtls.header.value('DT[1]','DATETIME'),
elem_dtls.header.value('DT[1]','DATETIME'),
'',
'',
'',
'',
'',
0,
0
FROM
@elem_dtls.nodes('/iPOSTSB/CL/STR/C') AS elem_dtls(header)
END
COMMIT TRAN
SET NOCOUNT OFF
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Sorry, my original stated post was incorrect, so I had to edit (= delete) it....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 2

Large XML strings can take a while to parse. What I've sometimes found faster is to insert them into a table (temp or real) that can have an XML index added to the XML column. Generally, adding the index and then querying it, takes less time than parsing the unindexed XML. Doesn't always work, but it's worked more often than not for me.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

4 hours for this does not sound right at all. Can you provide a zipped copy of the XML so that we can test it?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]