Varchar(max) performance problem in SQL server 2008 R2

Question

Varchar(max) performance problem in SQL server 2008 R2

e90fleet

Default port

Points: 1450
More actions
February 18, 2012 at 9:55 am

#391592

Hi all,
I have this problem:
We have a table with a varchar (max) column. This column constantly called by the stored procedure in the select statement and it's not used as search criteria,
it's just returning the xml string. The number of records returned by this stored procedure is about 500 with each SP call and this table has around 2000 records total.
This is basically a static table which is used to operate the website by using xml string and the table not constantly updated or inserted.
The indexes are correctly defined on this table and the number of characters in this xml string varies from 2000 to 300000.
Here is the problem:
Each call to the stored procedure takes about 100ms of cpu time and return records in 2-3 seconds.
Once I remove xml string from the select statement in the stored procedure the response time from the proc is immediate.
The questions are what can be done to improve the performance of the retrieval of xml string from the varchar (max) column?

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

I don't believe that the VARCHAR(MAX) datatype is the problem. I believe it's just the shear amount of data you're trying to transfer through the pipe. You said that you're returning about 500 of 2000 rows each time and that the XML data varies from 2,000 to 300,000 characters. It wouldn't take very many 300,000 rows returned to clog the proverbial pipe.

I don't know if changing the column to an actual XML datatype might help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

e90fleet Default port Points: 1450 More actions · Answer 2

Jeff, thanks so much for your reply. One thing i don't understand and if you can explain is your comment on

300,000 rows returned to clog the proverbial pipe. Does 300000 characters equivalent to 300000 records?

if you could think of any possible solution to resolve this problem it will be very much appreciated.

i was thinking about creating a separate table to store xml string and then do a join or try a separate filegroup to store varchar (max) but not sure if that can help unless i can try.

Thanks

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

e90fleet (2/19/2012)
Jeff, thanks so much for your reply. One thing i don't understand and if you can explain is your comment on
300,000 rows returned to clog the proverbial pipe. Does 300000 characters equivalent to 300000 records?
if you could think of any possible solution to resolve this problem it will be very much appreciated.
i was thinking about creating a separate table to store xml string and then do a join or try a separate filegroup to store varchar (max) but not sure if that can help unless i can try.
Thanks

No. 300,000 characters is just that... 300,000 characters. For any of the blob datatypes (MAX, XML), you have 300,000 characters on a single row or across many rows.

So far as speeding things up go, I guess I'd need to know what the store proc is doing with the XML.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

e90fleet Default port Points: 1450 More actions · Answer 4

Jeff, this is how the column in the stored procedure used:

CREATE PROC [dbo].[get_ConfigurationsResult]

@DescriptorID AS INTEGER,

@SetFacetName AS varchar(100) = NULL

AS

SELECT CC.ConfigurationID,

CC.TypeID,

CC.XMLControlValueList, -- This is XML string column from dbo.Configuration table which is defined as varchar (max)

CC.ConfigurationName,

CC.Active

FROM dbo.Configuration CC

INNER JOIN dbo.ControlType RSDC

ON RSDC.TypeID = CC.TypeID

WHERE RSDC.ResultSetDescriptorID = @DescriptorID

AND (RSDC.SetFacetName IS NULL OR RSDC.SetFacetName = ISNULL(@SetFacetName, RSDC.SetFacetName))

AND CC.Active = 1

ScottPletcher SSC Guru Points: 101120 More actions · Answer 5

For XML data, you are very likely better off specifying XML as the data type rather than varchar(max), since SQL optimizes XML storage.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

suppose your varchar(max) data is off row, that may cost you a extra io to get to the first address of your varchar(max) lob storage. Keep in mind IO is your slowest component !

As you expect 500 rows returned with each call, worst case, as Jeff stated would be 500 * 300000 + the length of the others columns. That's at least 140MB ....

Even on an 100Mb network - which still is common - that may take a while.

How about doing a test run just returning

datalength( your varchar(max) column )

to get an idea.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me