Issue with XML data type column of a table
We are facing an issue with a column in one of our SQL tables which is of XML data type. We are fetching this xml column in one of our queries which results in a single record. The size of this xml value is 6 MB. The query execution time is always 1 sec when we execute it manually on the server. But when we call it through a service the execution time is not constant and it varies from 1 second to 90 seconds or even more which we observed in the profiler. Even the same case when we try to update this xml value (entire value not a specific node). We tried changing the column type from XML to NVARCHAR(MAX) and VARCHAR(MAX) as well but still we are facing the same issue. We even do not have any indexes on this column. Estimated and Actual query execution plan everything seems to be fine. Can you please let us know if you have any pointers in resolving this.                                                                                                                             
May 18th, 2015 2:19am

So this has nothing to do with query plans, but the issue may in your network or your service. Getting 6MB of data takes some time, although 90 seconds is quite excessive.

You could consider implementing FILESTREAM and using the OpenSqlFilesteam API. For BLOBs of that size, FILESTREAM is generally faster.

Since that is a piece of work, in the meanwhile you could test with running the query from SSMS or SQLCMD on the machine where the service is running. If the execution times varies there as well, the service can be considered innocent.

Free Windows Admin Tool Kit Click here and download it now
May 18th, 2015 3:36am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics