August 8, 2001 at 1:58 am
Has anyone got any figures of the effect on the server of processing XML in the database. I am currently of the thinking that to gain the most out of the server XML should not be used in the database.
Any thoughts?
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
August 8, 2001 at 4:28 am
I havent seen any - Leon and I have talked about this but never came up with a really good test methodology. It's certainly going to take some cpu time to build an xml document, probably some memory too. How much would probably depend on the # and size of the documents. Obvious? Not using XML at all might be comparable to saying "no queries" which would also help performance! I think you want the equivalent of "no 20 min queries"!
So far we're only lightly using the xml features of SQL2K. Along with maybe a better answer to Simon's question I'd be interested in hearing to what extent everyone is using XML from SQL?
Andy
August 8, 2001 at 4:53 am
I am really torn because I see the returning of an XML document with hierachical data as really neat and will reduce the master, detail calls to the database to 1, with out the previous overhead of duplicating data. So there will be a win and a loose.
This is even nicer with .net and datasets.
Unfortunately I just haven't the time to do any testing.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
August 8, 2001 at 7:40 am
Depends on the hierarchy you want too. If you try using explicit, it gets to be ugly in a hurry. Raw and auto are quick and easy. If you need xml for your app, use it! Just keep your select small (like you should anyway!).
Andy
August 9, 2001 at 7:04 am
Hi guys
Not exactly what you were talking about, but we have an app where the developers pull out a lot of data from the DB, then wrapper it with XML and send it onto the ASP/XSL for processing. The wrappering of the returned data with a series of XML tags is very CPU intensive, they have tried a variety of methods but im not that impressed with the results. As you can imagine, even a small result set with say 10 columns for a single row means a lot of extra work.
I am planning on moving this to the DB via the FOR XML AUTO, ELEMENTS properties with an aim to reduce the workload on the COM objects and simplify them. This is what i havent tested to date. Perhaps a simple test is a series of batched queries over a period of time to measure total query time, network throughput issues and sort segment usage.
Cheers
Chris.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 9, 2001 at 7:19 am
<rant>
The options in ADO (2.6) are return the data from the database, translate to xml using save method then using XSL transform it into the form you want i.e getting rid of ADO stuff. Other option is to return the XML from the database and do similar with the returned document. For the later there are the options of FOR XML method. The easiest XML out is using the EXPLICIT but is a mind bender to create the query.
in .net populate the datatables make your dataset and export the xml. or return the xml to auto populate the dataset as with above the FOR XML options come into play.
One other point is that the methods on the MSXML object model are clearly not microsoft standard (well it wasn't written by them). Also they are 101 ways of doing something in a DOM especially when it comes to Xpath. Given we are handling strings, the difference between the best way and the worst way can be huge in processing time.
This later is similar to database access, ask someone that doesn't know about indexes and SQL and they will write the worst query possible, and kill the server.
I suppose my point is that this is all new technology with lots of ways of doing things. There must be best practices. We have spent years developing the best practices for using ADO, we are going to have to do the same for XML.
</rant>
p.s does rant mean the same thing in the US as in UK
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
August 9, 2001 at 1:03 pm
Rant does mean the same and nice rant.
I think you will find there will be a movement of the stuff you write in ADO to stuff MS has written. Don't forget the XML is now being processed on the database. In general, something I feel is a bad idea as this is the single point of contention. I would think you could retrieve the XML from a separate box and optimize the conversion process there.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply