What is the fastest way to load an XML file into MySQL using C#?
There are 2 parts to this:
- reading the xml file
- writing to the database
For reading the xml file, this link http://csharptutorial.blogspot.com/2006/10/reading-xml-fast.html , shows that 1 MB can be read in 2.4 sec using stream reader, that would be 2400 seconds or 40 mins (if my maths is working this late) for 1 GB file.
From what I have read the fastest way to get data into MySQL is to use LOAD DATA.
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
Therefore, if you can read the xml data, write it to files that can be used by LOAD DATA, then run LOAD DATA. The total time may be less than the hours that you are experiancing.
Ok, I'm going to be an idiot here and answer your question with a question.
Why put it in a database?
What if ... just a what-if... you wrote the xml to files on local drive and, if needed, write some indexing information in the database. This should perform significantly faster than trying to load a database and would much more portable. All you would need on top of it is a way to search and a way to index relational references. There should be plenty of help with searching, and the relational aspect should be easy enough to build? You might even consider re-writing the information so that each file contains a single post with all the answers and comments right there.
Anyway, just my two-cents (and that is not worth a dime).
I have a few thoughts to help speed this up...
The size of the query may need to be tweaked, there's often a point where the big statement costs more in parsing time and so becomes slower. The 500 may be optimal, but perhaps it is not and you could tweak that a little (it could be more, it could be less).
Go multithreaded. Assuming your system isn't already flatlined on the processing, you could make some gains by having breaking up the data in to chunks and having threads process them. Again, it's an experimentation thing to find the optimal number of threads, but a lot of people are using multicore machines and have CPU cycles to spare.
On the database front, make sure that the table is as bare as it can be. Turn off any indexes and load the data before indexing it.