June 20, 2008 at 3:30 pm
While creating an Expense Request form using ASP.NET 2.0, my initial idea was to have the request info in one table, and the expense line items in a child table. The line items have these fields: Date, Description, Mileage, Type, Amount.
I thought I would be able to pass datatables from the ASP app into a stored procedure for inserting into parent/child tables with a transaction, but then I learned 2k5 SPs can't accept table parameters.
I found a suggestion to pass XML data to the stored procedure instead.... so I started thinking why not just store the line items as XML in the first place and eliminate the child table altogether?
I have not used XML in any form yet, but I am wondering if this is a good idea? What would the XML file look like?
June 23, 2008 at 5:48 am
First, putting the line items in the table as an XML object is a bad idea. There are some uses for XML data (I think), but reducing your relational data to a big character field is not one of them. It will make querying the data complicated, reduce your data integrity to hoping your application handled it correctly, and actually end up taking significantly more space.
Now, you should really be breaking up your inserts, updates, and deletes into the correct pieces. If you want to have a transaction for your insert, then start a transaction, insert the parent record, insert the child record(s), and commit the transaction. It is good to have the distinct pieces.
It can be very tempting to try to bulk things together into big strings and XML data, but it is going to hurt your performance more than it will help it. XML is big - the tags take a lot of space - so sending your data in XML is going to take more bandwidth and more time. If you don't store it as a big character field or XML field, you are also going to have to take the time to parse it back apart when it gets to the server - taking more time. You should avoid this approach if possible.
June 23, 2008 at 8:02 am
Just to take a slightly contrarian point of view.
We did a lot of testing and found, depending on the number of columns and the type of data in a table, that fewer than ~100 rows (this can vary, it's just a reasonable average) usually work best when inserted through normal transaction processing as defined above. After that, the necessary overhead of working with XML was offset by the single, neat transaction that we could achieve by using it. So, I think you can use XML to change the transactions and receive a benefit for it, but you need to test & verify that your situation is the same as ours.
As to denormalizing your database into XML... I have done it for systems under development. We were unsure of the entire structure, but had a lot of it in hand, so we chose to store some of the data in XML while we developed the structure. Once the development & design was done though we had a normalized structure for the data.
The problem is, to do this in a production system means you have to entail XML overhead if you want to filter queries based on that data, search that data, sort that data.... you get the picture. Initiating XML is not cheap. It will cause performance bottlenecks, depending on the application. You may also need to consider reporting, secondary systems...
I wouldn't recommend this approach.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply