XML data storage options

Question

I'm currently doing a little research on different options to log user transactions. Currently, we have a design where transactions are sent in form of XML messages. Using SQL as the database, each XML message/transaction is stored in a table with four fields (primary key which uses a guid, type of transaction, timestamp of when transaction occurred, and a field that contains the entire XML. Basically if an error occurred during a transaction, the original XML message could be retrieved based on the type of transaction and when the error occurred to evaluate the data sent over in the XML message.

However, using this design, we find that querying the database takes a really long time. I'm not sure if it's because of the number of transaction that are logged each day (can be over hundreds of thousands of transactions). Thus, I have been researching to see if there are other types of database applications that would be more efficient for my situation. There are so many choices, but I wasn't sure where to start. There were quite a few discussions on mongodb but I'm not sure if that fits into what I'm trying to do. Can any of you provide me with some suggestions on the types of database options that I can research?

score 0 · Answer 1 · edited Jun 11 '13 at 02:21

0

I would incorporate this db scheme:

id, parentid, text
1, 0, 'test'
2, 0, 'test2'
3, 1, 'test child'
4, 3, 'test child child'

then recursively rebuild the tree as xml

edited Jun 11 '13 at 02:21

John Saunders

160,644
26
247
397

answered Jun 11 '13 at 02:19

Andrew

7,619
13
63
117

score 0 · Answer 2 · edited May 23 '17 at 11:57

Your MySQL instance is likely using dynamic row format since the BLOB/TEXT field storing the xml is a variable length.

Create a separate associative table to hold the XML data with a foreign key link to the PK in your transaction meta data table. That way you can run queries to find records on your meta data without the XML data stored in the BLOB/TEXT affecting those queries. Only access the XML data when you need it via separate query that utilizes a join.

See:

http://dev.mysql.com/doc/refman/5.5/en/optimize-blob.html

XML data storage options

2 Answers2