Query + XML Data + Time Consuming

Question

Query + XML Data + Time Consuming

karthik M

One Orange Chip

Points: 29139
More actions
February 26, 2010 at 7:04 am

#145524

All,
I have a Table which contains just 1200 records . One of the data type is xml data type.
When I am querying the Table it takes lot of time . The moment I empty the xml column, the result set returns immediately
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[XMLData](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PortfolioInstrumentId] [int] NOT NULL,
[InstrumentXML] [xml] NOT NULL,
[CreatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UpdatedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateCreated] [datetime] NOT NULL,
[DateUpdated] [datetime] NOT NULL
) ON [PRIMARY]
CREATE PROCEDURE [dbo].[P_XMLData]
AS
BEGIN
/* Suppress the Output */
SET NOCOUNT ON
DECLARE @CURRENTID int
DECLARE @CURRENTPORTFOLIOINSTRUMENTID int
SELECT @CURRENTPORTFOLIOINSTRUMENTID = 1
-- 5 - WHILE loop
WHILE @CURRENTPORTFOLIOINSTRUMENTID <= 1200
BEGIN
INSERT INTO XMLData VALUES (@CURRENTPORTFOLIOINSTRUMENTID,
'<instrument>
<field> portfolioFieldId="1" value="0.7" </field>
<field> portfolioFieldId="2" value="-0.3" </field>
<field> portfolioFieldId="3" value="0.1" </field>
<field> portfolioFieldId="4" value="0" </field>
<field> portfolioFieldId="5" value="2" </field>
<field> portfolioFieldId="6" value="0.5" </field>
<field> portfolioFieldId="7" value="23/02/2010" </field>
<field> portfolioFieldId="8" value="156" </field>
<field> portfolioFieldId="9" value="9.8" </field>
<field> portfolioFieldId="10" value="6.38" </field>
<field> portfolioFieldId="11" value="0.5" </field>
<field> portfolioFieldId="14" value="([AS]*[AS])" </field>
<field> portfolioFieldId="16" value="3" </field>
<field> portfolioFieldId="17" value="0.08" </field>
<field> portfolioFieldId="18" value="AAA" </field>
<field> portfolioFieldId="19" value="29/03/2021" </field>
<field> portfolioFieldId="20" value="EUR" </field>
<field> portfolioFieldId="21" value="A¦B" </field>
<field> portfolioFieldId="22" value="I" </field>
<field> portfolioFieldId="24" value="E5" </field>
<field> portfolioFieldId="25" value="F8" </field>
</instrument>','snapuser','snapuser',getDate(),getDate())
-- 9 - Decrement @MAXOID
SET @CURRENTPORTFOLIOINSTRUMENTID = @CURRENTPORTFOLIOINSTRUMENTID + 1
END
IF @@ERROR <> 0
BEGIN
RETURN 0
END
SET NOCOUNT OFF
RETURN 1
END
Select * from XMLData where Id<=1200
The above query took 42 seconds to complete its execution.
Inputs are welcome to optimize the execution time.
karthik

Viewing 8 posts - 1 through 7 (of 7 total)

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I just tried to reproduce your problem, but couldn't. I used your code and then I ran the select statement but got the results very fast. I tried playing a bit with the connection’s set option, but it didn’t work and I still got the results very fast.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

karthik M One Orange Chip Points: 29139 More actions · Answer 2

karthik M

One Orange Chip

Points: 29139

February 26, 2010 at 9:48 am

#1125687

How much time it took ?

karthik

LutzM SSC Guru Points: 107049 More actions · Answer 3

423ms on my home PC...

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]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

About 450ms on this lappy.

Cheers

ChrisM

Junior Software Engineer

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

On my laptop it took 273 MS.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

LutzM SSC Guru Points: 107049 More actions · Answer 6

Running 100 or up to 200 times faster than Karthik's measurement indicates to me that there's something seriously wrong with his system.

I strongly recommend using profiler to figure out what's going on.

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]

Paul White SSC Guru Points: 150467 More actions · Answer 7

ChrisM@home (2/26/2010)
ChrisM
Junior Software Engineer

LMAO! :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi