Save high volume xml transactions to db

15 pts.
Tags:
SQL Server
SQL Server 2008
XML
I'm not a DB developer, I know basic select statements but that's about it. We have a high volume xml web service that takes roughly 200K transactions per day. Each transaction request has a corresponding response so that's about 400k transactional xmls per day to save.

Each request xml is anywhere from 20k to 80k bytes of xml and the xml responses are anywhere from 20k to 400k bytes of xml. So say an average of 100k bytes per xml packet would make it about 40 gig per day.

Our Business team wants to temporarily store all requests and responses to a DB with a purge of old records at about 1 month. They want to be able to retrieve a specific xml packet from the database based on a specific selection of data fields within the xml packet.

I don't want this saving of data to harm the performance of my web service so I think I can get around this by spawning threads to save the data to a DB.

I really don't care about the peformance of the business tool that will query the data since it won't affect any business critical applications (unless somehow it affects the saving of the data if they are querying the database while the web service is writing to it).

Questions:

1) Is the volume and size of the data too much to use a database? Should we consider just dumping the xmls to a folder on a server.

2) If a database is OK to use then: I see that SQL Server has an XML data type and some functions that can query an xml record using xpath. For querying the DB should I use the built in XML type and functions like:

 


Create Table

{

    Clob XML

}



Then use the built in xml functions like

SELECT Clob from table where Clob.query(/root/subnode[vendorId='1234'])

(Sorry, I don't know the syntax)

or would it be better to extract the specific data items from the xml as it's being saved and save the data items to separate columns like:



Create Table

{

    name varchar,

    timestame datetime,

    vendorID varchar,

    ... (more searchable data items)

    Clob XML

}



Then use a query like

Select clob from table where vendorID = '1234'

As I said before, I don't know much about databases but the first option above seems like it would be extremely slow to execute an xpath against 400K x 30 days worth of xml transactions.

Thanks to anyone willing to read through my diatribe and even more thanks to anyone will to provide advice!

 



Software/Hardware used:
SQL Server 2008. Hardware unknown, I don't have access but I'm confident it's high performance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could do it both ways. How you will be querying the data is what should determine your table structure. SQL Server does have XML indexes which will index the data within the XML documents within the table.

Storing XML documents shouldn’t be that difficult to do. What you’ll probably want to is use MSMQ or some other queuing technology to queue the XML data, so that another application can then insert the data into the database later so that your front end application isn’t slowed down by the writing of the data to the database. This also allows the application to function without the database that you are writing the XML documents to being online.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • GreenJello
    Thank you Mrdenny, your suggestions are very helpful!
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following