Response time

I have a table with 37000 records with a int column and a xml column. The int
column have primary index, the xml column have primary index, path index and
value index. When I execute "Select * from MyTable", the response time is
very slow (almost 16 seconds), only when I include the xml column, but if I
execute "Select IntColumn from MyTable" is very fast (less than a second).

What happens????

I tried convert the xml column like this:

select IntColumn, convert(varchar(1500), xmlColumn) from MyTable

In this case, the time was 6 seconds. Seems to me that the conversion of the
xmlColumn to a restricted size speeds up the result.

Someone knows why the retrieval of xml data (or varchar(max)) is so slow?

Thanks for your help.

Hi,

XML and MAX data types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) etc are
called Large Value types that can store data up to 2 GB. The size of a data
row in SQL Server is 8060 bytes. When you store a large value type to a row,
if the size of the row goes beyond 8060 bytes, the Large value type is not
stored in the row. Instead, it will be stored in a different location and a
pointer to the location is stored in the row.

When you read a large value type which is stored 'off row', SQL Server need
to do some additional work. When it reads each row, it will take the
pointers stored in the row and locate the specific location and read the
large value type. So there is an additional lookup needed to fetch large
values.

This will be contributing to the delay when you try to read columns that
stores large value types. In addition the processing needed to read those
values (IO/CPU etc) and the time needed to transfer the values to the client
etc needs to be considered.

I would recommend reading the following:
http://www.sqlserverandxml.com/2008/07/points-to-remember-while-using-large.html
http://www.sqlserverandxml.com/2008/07/when-should-i-use-large-value-types.html
http://www.sqlserverandxml.com/2008/01/varcharnvarchar-n-vs-max.html


--
Jacob Sebastian
SQL Server MVP
http://www.sqlserverandxml.com

"byteman" <byteman DeleteThis @discussions.microsoft.com> wrote in message
news:7628738D-4D0F-4713-8436-72512E3C45A7@microsoft.com...
> I have a table with 37000 records with a int column and a xml column. The
> int
> column have primary index, the xml column have primary index, path index
> and
> value index. When I execute "Select * from MyTable", the response time is
> very slow (almost 16 seconds), only when I include the xml column, but if
> I
> execute "Select IntColumn from MyTable" is very fast (less than a second).
>
> What happens????
>
> I tried convert the xml column like this:
>
> select IntColumn, convert(varchar(1500), xmlColumn) from MyTable
>
> In this case, the time was 6 seconds. Seems to me that the conversion of
> the
> xmlColumn to a restricted size speeds up the result.
>
> Someone knows why the retrieval of xml data (or varchar(max)) is so slow?
>
> Thanks for your help.