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
No comments:
Post a Comment