How can I put a lot of data from XML into my database quickly?

Question

I am using .NET to parse an XML file with about 20 million lines (1.56 GB), building LINQ objects out of the data, then inserting it into a SQL database. It is taking a really long time.

To improve performance I am considering asking for a pipe delimited file. I was also wondering if Perl might be any faster. Does anyone have suggestions for speeding up this process?

Answer 1

The pipe-delimited file might help you improve performance. If your task is CPU-bound, you can split the file into n chunks (where n is the number of processors you have), and run the jobs in parallel.

I doubt Perl will run faster than .NET, but you'll probably get your code working faster.

Answer 2

I don't think you'll be able to improve LINQ performance on that file to your satisfaction. My recommendation is to use XmlTextReader and read the file node by node and do your own processing of the data.

Answer 3

If you can get the data in a pipe separated format, you can use SQL tools to import it straight into DB. I guess, you could use BCP to do that.

Why go the route of file -> LINQ objects -> DB?
Also, try to get the file splitted instead of 1 big file with 20 million records.

Answer 4

First of all, I'm concerned to hear you talking about number of lines when processing XML. In most cases, an XML document, without the line breaks, is still the same document. Lines don't matter.

Second, you didn't say which RDBMS you're using, but I'll assume SQL Server 2005 or 2008. In this case, if this is a process that you will need to repeat often, then I suggest you do this in SQL Server Integration Services (SSIS). SSIS is optimized for this sort of thing. In particular, it can actually be reading from the XML file, performing your per-row modifications, and writing batches of prepared rows to the database, all at the same time.

The only potential issue would be with the size of the file (BTW, you said it's 20 million lines, but how many MB is that?). SSIS tries to load the entire document into memory at once. This can be a problem. To combat this, I found it relatively easy to create my own custom Source component. This looked under the root element of the document, and returned one child element (and its descendants) at a time. This was simple to do by using the XmlReader.ReadSubTree method, which returns a new XmlReader that only processes the current element and all of its children.

Answer 5

Before you go off fixing things, figure out which part is slow. There are three major parts here:

• Parsing the XML
• Making the LINQ objects
• Inserting it into a database

Which one is taking up all of the time? You might suspect that it is parsing the XML, but verify that before you waste a lot of time. It seems that every time I think I know the answer, I was wrong :) I can't suggest a .NET profiler, but Stackoverflow seems to have answers for that.

I'm not a .NET guy, but I would really be surprised if anything Perl could offer would be significantly faster. It's not a language problem since even the fast Perl stuff is an interface to common libraries.

Answer 6

Here's a radical thought and I honestly don't know if it'll improve your performance but it has a good chance to do so. I'll bet that you're instantiating your context object once and then using it to insert all the records, right? Doing so means that the context will keep track of all those objects until it is disposed and that might explain the degrading performance over time.

Now, you could clear the context cache but I have a nuttier idea. Those context objects are designed to have minimal overhead on instantiation (it says so in the documentation anyway, I haven't tested that assertion), so it might help if you instantiate the context on each iteration. i.e. re-create the context at the same time you create the object. Or, and this is a better idea, maintain an internal list of your data objects and kick the list to a separate method every n iterations to commit to the database. That method should both instantiate and dispose the context. Make sense?

Answer 7

Brian has the right idea--no need to start breaking stuff before establishing what is broken.

One other crazy trick--see if you can run it on the DB server which takes any network delays out of the picture. We had a few rather massive batch processes that had massive perf improvements from location changes alone.

Answer 8

This question on importing the Stack Overflow data dump might be of some use to you. You can probably write the data out to a CSV file, or files using XMLTextReader, and then use your database's bulk importanting mechanism to import the data very quickly.

Answer 9

First off, decide what is an acceptable amount of time for completion of the import process.

Then, as Brian and Wyatt suggested, do some analysis of where the bulk of the time is being spent. I'm guessing in the LINQ to SQL stuff (although guessing where performance issues are is itself a very risky business).

You can follow two routes, namely, use the ETL tools that are part of MS SQL server to do the import, or continue with some custom .NET code to do the import.

If you choose the latter, I would suggest:

1. Use ADO.NET only and remove LINQ to SQL stuff;
2. Run the import program on the database server--eliminating any network traffic issues;
3. It is unlikely that the XML parser will be any kind of bottleneck, but if it is, consider parsing the XML directly using simple pattern checks as opposed to a full-on compliant parser--of course, this will require the XML to be simple in format, and laid out in line-by-line fashion.

Perl will be slower than .NET since .NET is compiled and Perl isn't.

Answer 10

IF you find out that data access is the bottleneck, you might want to try inserting batches of xml objects using SqlBulkCopy. I have xml objects with 18 nodes each and this works for me.

        // read xml file into datatable
        DataSet ds = new DataSet();
        DataTable callList = new DataTable();

        string AppDataPath = ConfigurationManager.AppSettings["AppDataPath"];
        string dbSchema = AppDataPath + "/" + "CLBulkInsertSchema.xml";

        //Create a FileStream to the XML Schema file in Read mode
        FileStream finschema = new FileStream(dbSchema, FileMode.Open,
                               FileAccess.Read, FileShare.Read);

        //Read the Schema into the DataSet
        ds.ReadXml(finschema);

        //Close the FileStream
        finschema.Close();

        //Create a FileStream to the Xml Database file in Read mode
        FileStream findata = new FileStream(tempFilePathName, FileMode.Open,
                             FileAccess.Read, FileShare.ReadWrite);

        //Read the DataBase into the DataSet
        ds.ReadXml(findata);

        //Close the FileStream
        findata.Close();

        DataTable callList = ds.Tables["PhoneBook"];

        string conn = ConfigurationManager.ConnectionStrings["dbConnectionString"].ToString();

        using (SqlConnection connection =
               new SqlConnection(conn))
        {
            connection.Open();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName =
                    "dbo.CallBatchItems";

                // mappings required because we're skipping the BatchItemId column
                // and letting SQL Server handle auto incrementing of primary key.
                // mappings not required if order of columns is exactly the same
                // as destination table definition. 

                bulkCopy.ColumnMappings.Add("BatchId", "BatchId");
                bulkCopy.ColumnMappings.Add("FullName", "FullName");
                ...
                // Write from the source to the destination.
                bulkCopy.WriteToServer(callList);
            }
        }