Not sure where I should post this, but we are looking to change some processes here at work.
Currently the process uses SQL XML 3.0 and it locks some of our database up for a period of time every evening.
A little bit about this process: we aggregate apartment data to the industries listing services and this process creates the output that goes to them. Right now it's just one big query to pull back all of the apartments and all their aggregate data all at once. Of course this is going to lock things up because of the amount of data it's getting. Not sure if it would be better to get one apartment at a time rather than going for them all at once. The XML file we end up varies dependng on the listing service from 100K to 2 MB.
I'm not sure if SQL XML makes this situation worse though. I've been told SQL XML is quite a resource hog, so we are planning to go away from it.
So now we have to decide what to use. Some of the options are:
1. Getting the data via a DataReader for 1 apartment complex at a time and using the XMLWriter to write out the XML, creating the XML file one apartment node at a time
2. Using forXML queries to pull apartment data out of the database (the db is SQL 2000) and pushing that against XSLT to produce the individual apartment nodes
3. Continuing to use SQLXML but use it for 1 property at a time rather than for the whole data set.
Some of you probably have a lot better ideas of how to handle ths situation. What we are trying to accomplish is to not lock up the database as much and hopefully use less resources all together while this process is running.
Thanks in advance for your suggestions.
Shane