|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
FOR XML performance questionI am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source. The older current component requests this data using an ADO recordset and then manually converts this to XML. I have run several performance tests comparing the 2 and on narrow and medium width tables I have found that the performance gain is massive (approx 80% gain). However, when I run the 2 on very wide tables, ones which contain text/ntext columns, FOR XML only performs about 10% better pulling back 1 row but pulling back 20 rows it becomes over twice as slow as the older component. Can anyone suggest why? Or even better, any methods/tips that could improve performance in this instance? Thanks in advance. I'm cross posting this to microsoft.public.sqlserver.xml, a more appropriate
forum for this question. -- Show quoteHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com "Lee" <L**@discussions.microsoft.com> wrote in message news:F314E284-6E34-403C-94C1-B307682B1418@microsoft.com... >I am currently rewriting a data access component to make use of the FOR XML > SQL statement to return XML data as an ADO stream from a specified > source. > The older current component requests this data using an ADO recordset and > then manually converts this to XML. > > I have run several performance tests comparing the 2 and on narrow and > medium width tables I have found that the performance gain is massive > (approx > 80% gain). However, when I run the 2 on very wide tables, ones which > contain > text/ntext columns, FOR XML only performs about 10% better pulling back 1 > row > but pulling back 20 rows it becomes over twice as slow as the older > component. > > Can anyone suggest why? Or even better, any methods/tips that could > improve > performance in this instance? > > Thanks in advance. I'm cross posting this to microsoft.public.sqlserver.xml, a more appropriate
forum for this question. -- Show quoteHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com "Lee" <L**@discussions.microsoft.com> wrote in message news:F314E284-6E34-403C-94C1-B307682B1418@microsoft.com... >I am currently rewriting a data access component to make use of the FOR XML > SQL statement to return XML data as an ADO stream from a specified > source. > The older current component requests this data using an ADO recordset and > then manually converts this to XML. > > I have run several performance tests comparing the 2 and on narrow and > medium width tables I have found that the performance gain is massive > (approx > 80% gain). However, when I run the 2 on very wide tables, ones which > contain > text/ntext columns, FOR XML only performs about 10% better pulling back 1 > row > but pulling back 20 rows it becomes over twice as slow as the older > component. > > Can anyone suggest why? Or even better, any methods/tips that could > improve > performance in this instance? > > Thanks in advance. I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source. The older current component requests this data using an ADO recordset and then manually converts this to XML. I have run several performance tests comparing the 2 and on narrow and medium width tables I have found that the performance gain is massive (approx 80% gain). However, when I run the 2 on very wide tables, ones which contain text/ntext columns, FOR XML only performs about 10% better pulling back 1 row but pulling back 20 rows it becomes over twice as slow as the older component. Can anyone suggest why? Or even better, any methods/tips that could improve performance in this instance? Thanks in advance. ----------------------------------------------------------------------- In addition to the above I have done some further investigation. On a query which returns the top row from a table the FOR XML method performed 83.5% faster than the recordset version. However, when I run a where query which I know returns a single row the FOR XML method performance plunges and is actually 6% slower than the recordset version. Is SQLXML just one of those things which seems like a great idea but has no real practical use in an enterprise environment? I find it very frustrating that its performance is superb in some situations but is so awful in others. Is it a work in progress? That said, are there any resources which discuss various ways to pull data from SQL server 2000 as(and convert to) XML format? Surely there is a better way than using a ADO recordset as described above? Thanks. I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source. The older current component requests this data using an ADO recordset and then manually converts this to XML. I have run several performance tests comparing the 2 and on narrow and medium width tables I have found that the performance gain is massive (approx 80% gain). However, when I run the 2 on very wide tables, ones which contain text/ntext columns, FOR XML only performs about 10% better pulling back 1 row but pulling back 20 rows it becomes over twice as slow as the older component. Can anyone suggest why? Or even better, any methods/tips that could improve performance in this instance? Thanks in advance. ----------------------------------------------------------------------- In addition to the above I have done some further investigation. On a query which returns the top row from a table the FOR XML method performed 83.5% faster than the recordset version. However, when I run a where query which I know returns a single row the FOR XML method performance plunges and is actually 6% slower than the recordset version. Is SQLXML just one of those things which seems like a great idea but has no real practical use in an enterprise environment? I find it very frustrating that its performance is superb in some situations but is so awful in others. Is it a work in progress? That said, are there any resources which discuss various ways to pull data from SQL server 2000 as(and convert to) XML format? Surely there is a better way than using a ADO recordset as described above? Thanks. Hi Lee
This is hard to answer without having more specifics. How does your FOR XML query look like? How does it compare to the previous query, what indices do yo have on it? Etc. Best regards Michael Show quote "Lee" <L**@discussions.microsoft.com> wrote in message news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@microsoft.com... >I am currently rewriting a data access component to make use of the FOR XML > SQL statement to return XML data as an ADO stream from a specified > source. > The older current component requests this data using an ADO recordset and > then manually converts this to XML. > > I have run several performance tests comparing the 2 and on narrow and > medium width tables I have found that the performance gain is massive > (approx > 80% gain). However, when I run the 2 on very wide tables, ones which > contain > text/ntext columns, FOR XML only performs about 10% better pulling back 1 > row > but pulling back 20 rows it becomes over twice as slow as the older > component. > > Can anyone suggest why? Or even better, any methods/tips that could > improve > performance in this instance? > > Thanks in advance. > > ----------------------------------------------------------------------- > > In addition to the above I have done some further investigation. On a > query > which returns the top row from a table the FOR XML method performed 83.5% > faster than the recordset version. However, when I run a where query > which I > know returns a single row the FOR XML method performance plunges and is > actually 6% slower than the recordset version. > > Is SQLXML just one of those things which seems like a great idea but has > no > real practical use in an enterprise environment? I find it very > frustrating > that its performance is superb in some situations but is so awful in > others. > Is it a work in progress? > > That said, are there any resources which discuss various ways to pull data > from SQL server 2000 as(and convert to) XML format? Surely there is a > better > way than using a ADO recordset as described above? > > Thanks. Hi Lee
This is hard to answer without having more specifics. How does your FOR XML query look like? How does it compare to the previous query, what indices do yo have on it? Etc. Best regards Michael Show quote "Lee" <L**@discussions.microsoft.com> wrote in message news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@microsoft.com... >I am currently rewriting a data access component to make use of the FOR XML > SQL statement to return XML data as an ADO stream from a specified > source. > The older current component requests this data using an ADO recordset and > then manually converts this to XML. > > I have run several performance tests comparing the 2 and on narrow and > medium width tables I have found that the performance gain is massive > (approx > 80% gain). However, when I run the 2 on very wide tables, ones which > contain > text/ntext columns, FOR XML only performs about 10% better pulling back 1 > row > but pulling back 20 rows it becomes over twice as slow as the older > component. > > Can anyone suggest why? Or even better, any methods/tips that could > improve > performance in this instance? > > Thanks in advance. > > ----------------------------------------------------------------------- > > In addition to the above I have done some further investigation. On a > query > which returns the top row from a table the FOR XML method performed 83.5% > faster than the recordset version. However, when I run a where query > which I > know returns a single row the FOR XML method performance plunges and is > actually 6% slower than the recordset version. > > Is SQLXML just one of those things which seems like a great idea but has > no > real practical use in an enterprise environment? I find it very > frustrating > that its performance is superb in some situations but is so awful in > others. > Is it a work in progress? > > That said, are there any resources which discuss various ways to pull data > from SQL server 2000 as(and convert to) XML format? Surely there is a > better > way than using a ADO recordset as described above? > > Thanks. The query is a simple :-
SELECT stuff FROM table WHERE condition (optional) FOR XML RAW There is a single index on the primary key of the table and that was the field I did my where clause on as described in my above posts. Show quote "Michael Rys [MSFT]" wrote: > Hi Lee > > This is hard to answer without having more specifics. > > How does your FOR XML query look like? How does it compare to the previous > query, what indices do yo have on it? > > Etc. > > Best regards > Michael > > "Lee" <L**@discussions.microsoft.com> wrote in message > news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@microsoft.com... > >I am currently rewriting a data access component to make use of the FOR XML > > SQL statement to return XML data as an ADO stream from a specified > > source. > > The older current component requests this data using an ADO recordset and > > then manually converts this to XML. > > > > I have run several performance tests comparing the 2 and on narrow and > > medium width tables I have found that the performance gain is massive > > (approx > > 80% gain). However, when I run the 2 on very wide tables, ones which > > contain > > text/ntext columns, FOR XML only performs about 10% better pulling back 1 > > row > > but pulling back 20 rows it becomes over twice as slow as the older > > component. > > > > Can anyone suggest why? Or even better, any methods/tips that could > > improve > > performance in this instance? > > > > Thanks in advance. > > > > ----------------------------------------------------------------------- > > > > In addition to the above I have done some further investigation. On a > > query > > which returns the top row from a table the FOR XML method performed 83.5% > > faster than the recordset version. However, when I run a where query > > which I > > know returns a single row the FOR XML method performance plunges and is > > actually 6% slower than the recordset version. > > > > Is SQLXML just one of those things which seems like a great idea but has > > no > > real practical use in an enterprise environment? I find it very > > frustrating > > that its performance is superb in some situations but is so awful in > > others. > > Is it a work in progress? > > > > That said, are there any resources which discuss various ways to pull data > > from SQL server 2000 as(and convert to) XML format? Surely there is a > > better > > way than using a ADO recordset as described above? > > > > Thanks. > > > The query is a simple :-
SELECT stuff FROM table WHERE condition (optional) FOR XML RAW There is a single index on the primary key of the table and that was the field I did my where clause on as described in my above posts. Show quote "Michael Rys [MSFT]" wrote: > Hi Lee > > This is hard to answer without having more specifics. > > How does your FOR XML query look like? How does it compare to the previous > query, what indices do yo have on it? > > Etc. > > Best regards > Michael > > "Lee" <L**@discussions.microsoft.com> wrote in message > news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@microsoft.com... > >I am currently rewriting a data access component to make use of the FOR XML > > SQL statement to return XML data as an ADO stream from a specified > > source. > > The older current component requests this data using an ADO recordset and > > then manually converts this to XML. > > > > I have run several performance tests comparing the 2 and on narrow and > > medium width tables I have found that the performance gain is massive > > (approx > > 80% gain). However, when I run the 2 on very wide tables, ones which > > contain > > text/ntext columns, FOR XML only performs about 10% better pulling back 1 > > row > > but pulling back 20 rows it becomes over twice as slow as the older > > component. > > > > Can anyone suggest why? Or even better, any methods/tips that could > > improve > > performance in this instance? > > > > Thanks in advance. > > > > ----------------------------------------------------------------------- > > > > In addition to the above I have done some further investigation. On a > > query > > which returns the top row from a table the FOR XML method performed 83.5% > > faster than the recordset version. However, when I run a where query > > which I > > know returns a single row the FOR XML method performance plunges and is > > actually 6% slower than the recordset version. > > > > Is SQLXML just one of those things which seems like a great idea but has > > no > > real practical use in an enterprise environment? I find it very > > frustrating > > that its performance is superb in some situations but is so awful in > > others. > > Is it a work in progress? > > > > That said, are there any resources which discuss various ways to pull data > > from SQL server 2000 as(and convert to) XML format? Surely there is a > > better > > way than using a ADO recordset as described above? > > > > Thanks. > > >  |
|||||||||||||||||||||||
Other interesting topics