DataSet.WriteXML Vs. SQL Server 2000 XML performance issue???

[manoj241176]

Hi,
I have concern regarding two approaches to decide,

1>DataSet.WriteXML
2>SQL Server 2000 to return XML from stored procedure.

With Second approach, I have to open Cursor's and nested stored proc
calls and have to create temporary tables. while in first approach I
dont have to create all this.

So please suggest me, out of these two approaches which will be faster
and will affect performance??

Thanks,
Manoj

 

[Shawn Wildermuth (C# MVP)]

Hello (e-mail address removed),

There are a lot of different things that affect the performance. Its difficult
to determine exactly from this set of minimal description. Have you tested
the performance locally?

Thanks,
Shawn Wildermuth
Speaker, Author and C# MVP
http://adoguy.com

 

[manoj241176]

Hi Shawn,
I have two options with me 1> send XML data from SQL Server to .NET
Application.
2> Send Resultset from SQL Server to .NET application

So what i want to know is,

1> if i write stored proc which will return me xml structre of the
desired data. This XML will be combination of data returnd from multipe
stored or proc.
OR
2> I execute different stored proc, take them in different DataTable,
now using DataRelation i set relation between them and after this use
DataSet.WriteXML method to create the desired xml.

Out of these two, which will be faster and performance effective.

I have gone thorugh this link..it says DataSet is faster....please
clear me if i am wrong out here..

http://www.topxml.com/sqlxml/sqlxml_performance.asp

Please let me know, if you want any futher clarification.

Regards,
Manoj

 

[Shawn Wildermuth (C# MVP)]

Hello (e-mail address removed),

The DataSet definitely *can* be faster...but the actual performance depends
on many more details than just the raw processing. If the client (where
the DataSet is created and used to create XML) is already heavily over-utilized
(e.g. CPU usage is at a premium), then doing the work in SQL Server will
be faster...

But the inverse is also true, if you have a heavily consumed DB Server, then
doing the work in teh DataSet makes even more sense. If both are under utlized
machines, they I would expect the DataSet to be faster. But understand that
performance is a complicated issue. If you are just counting how many CPU/File
IO cycles it takes, then DataSet probably wins but I can give you a "Its
always faster" stamp of approval.

This problem of perceived performance is especially problematic in web applications
as the machines that most companies use for web servers are usually under-powered
commidity hardware, so pushing additional CPU/FIle IO processing to the web
server may not be a good decision.

Lastly, understand that there are several ways of creating the XML in SQL
Server (both in 2000 and 2005). SQLXML tends to be problematic in my experience
as it hides some of its implementation to be done on the client even though
it tries to act as if it is being performed on the server, but this is not
true of the FOR XML syntax stuff.

So if I was forced (by gunpoint) to give an opinion based on what you have
explained, I'd suggest starting out with teh DataSet approach as it is more
straightforward (IMHO). But monitoring your app for CPU/IO usage will be
important to determine if it really is faster.

HTH

Thanks,
Shawn Wildermuth
Speaker, Author and C# MVP
http://adoguy.com

 

[manoj241176]

Hi Shawn,
Thanks a lot. Your explaination helped a lot in understanding the
issue and getting the solution. Thanks again.

Shawn, if you dont mind can i get your e-mail id or any blog you share.

Thanks & Regards,
Manoj

 

[Shawn Wildermuth (C# MVP)]

Hello (e-mail address removed),

My blog is at:

http://adoguy.com

(RSS: http://adoguy.com/rss)


Thanks,
Shawn Wildermuth
Speaker, Author and C# MVP
http://adoguy.com