FOR XML AUTO - Performance under SQL 2000 SP3

I'm currently having a performance problem with a straight SQL select statement with the 'for xml auto' clause returning a result set involving approximately 5,000 records. This is taking about 16 seconds on a SQL 2000 installation SP3 (8.00.818), while it only takes a few milliseconds when removing the 'for xml auto' (when executed via Query Analyzer). Time under the 'for xml auto' is directly proportional to the number of records processed.

Testing the same query/table on another server with SP4 installed shows little difference in times.

I'll be trying to set up another test installation with SP3 on a different server to see if this behavior is something specific to SP3, but has anyone else encountered this problem?

Not anything specific to SP3...just tested it on a 8.00.818 box that I created and the query with/without the 'for xml auto' clause processed the results instantaneously. Will check to see what might be different about server configuration settings and collations.|||Seems to be associated with amount of data being output in the XML stream. The original output consists of 261 KB data and takes 16 seconds duration to execute. Renaming attributes in table to single characters to reduce tag name overhead reduces data size by 69% and duration to 70%. Execution was via Query Analyzer local to the database server. Execution on same table/data on another server was 115 ms. Query plans generated are the same. Output of data as a normal SQL result set (without 'for xml clause') on the problem installation takes time down to milliseconds also.|||

It is hard to give you any response to this without an exact repro (data and query at least). Did you compare the query plans?

The XML will be bigger than TDS rowset format but should not have such a big overhead. Are you sure you are not including the time it takes the QA to show the data?

Also, if the overhead does not show in SP4, could you just upgrade to SP4?

Best regards

Michael

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall
"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David
|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index[vbcol=seagreen]
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> > For Sql 2000, for isolation levels not serialized, can a key lock
> > (especially created by an inserted row), involving a nonprimary key
index
> > end up locking more than one row?
> >
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>