Database or flat file for 600K records?

Question

Hi,

I'm writing a C# application which needs to insert about 600K records into a database at a certain point in time.

They are very simple records: just 3 longs.

I'm using params to set up the command, and then loop through the data in memory to make the inserts, assigning the values to the command parameter's at each loop and running command.ExecuteNonQuery()

It takes about 50 seconds to finish on SqlServer, and it's even slower on MySql while inserting the same data on a flat file only takes a few miliseconds.

Am I doing something wrong or the database simply too slow?

Answer 1

Are you doing a bulk insert? I'd use it if you arnt already.

INSERT INTO dbo.NewTable(fields) 
SELECT fields 
FROM dbo.oldTable 
WHERE ...

In the above example you would want to ensure the tables used in the select statement have the appropriate indexes... correctly assigning the clustered index to the most relevant field.

If the select statement is slow, check the execution plan to possibly find the bottleneck.

Answer 2

You will see greater speed writing to a flat file for a few reasons:

• ExecuteNonQuery does not group multiple insert statements into batches, so you are incurring a full inter-process communication turnaround per record. Send your insert statements in groups.
• The data you have are already in the shape of a flat file, so you can fire it all off in one write, or a few writes with buffering.
• Database operations tend to use trees which take n log n time, while a simple array-shaped construct will take linear time. On the other hand, if you're merging into a sorted flat file, that will take a while.

Answer 3

So that's about 8 milliseconds for a single row versus about that for the entire file. Fair?

A database certainly has a lot more potentially going on:

1. Parsing, validating, executing SQL
2. Calculating the values of any indexes
3. Managing rollback logs if this is a single transaction
4. Writing to its own file

I'll assume that you're running locally, so there's no network latency to include.

So I would guess that a database is slower. I wouldn't have thought 600K times slower, though.

Answer 4

you are probably running the command over and over against the database server, what if you construct a command text that includes multiple inserts and then run this ? ie

string commandText = "insert into x ( y, z) values ( 1, 2 );\r\n"
commandText += "insert into x ( y, z) values ( 2, 3 );"

command.Text = commandText;
command.ExecuteNonQuery();

Answer 5

I can't help you much with MySQL. However, SQL Server 2005 and greater have some pretty intriguing XML support that might help you out. I recommend looking into Updategrams, a feature that allows you to submit a batch of data to be inserted, updated, or deleted. This might help you improve the performance with SQL Server, as you only need to issue a single statement rather than 600,000 statements. I am not sure it would be quite as fast as writing to a raw file, but it should be significantly faster than issuing individual statements.

You can start learning about updategrams here: http://msdn.microsoft.com/en-us/library/aa258671(SQL.80).aspx

Answer 6

If you do not require many concurrent users try using MS-Jet, i.e. "Microsoft Access" as your DBMS. The MSJet performance can be about 10x faster than SqlServer. BTW, inserting 600k records in just 50 seconds (12k/sec) is very fast for SqlServer.

Answer 7

Use SqlBulkCopy:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server

Answer 8

As Alex said: use SqlBulkCopy, nothing beats it when it comes to performance.

It is a bit tricky to use, for sample code have a look here:

http://github.com/sambo99/So-Slow/blob/1552b1293525bfe36f6c9b522e370de626ac6f05/Importer.cs

Answer 9

If all you need is to insert the data and never read it back then you can write a noop function and pretend you inserted them in /dev/nul. The real question is how do you plan to consume the said data? Do you need to interrogate, filter, sort, reference the individual records? Ie. why did you even consider a database to start with, if a flat file appears to be just as good?

With SQL Server you can certainly achieve better performance with a database and insert at a rate of about 50-100k per second at least. Your current chocking point is probably the lgo flush on each insert. You must batch commits and make sure your log is on a fast array of spindles. Start a transaction, insert roughly enough records to fill a log page (64kb) then commit. Also is worth using a battery of 5-10 SqlCommands and connections and use async commands (BeginExecuteNonReader with callback) to launch multiple inserts in parallel, this way you can leverage all dead times you now loose in network round-trip and execution context preparation.

Answer 10

My guess is that you're doing transactional inserts: inserts that look like this:

INSERT INTO dbo.MyTable (Field1, Field2, Field3)
VALUES (50, 100, 150)

That'll work, but like you've found, that doesn't scale. In order to push a lot of data into SQL Server very quickly, there are tools and techniques to pull it off.

Probably the simplest way to do it is with BCP. Here's a couple of links about it:

• http://sqlfool.com/2008/12/bcp-basics/
• http://sqlserverpedia.com/wiki/BCP_Overview

Next, you'll want to set up SQL Server in order to insert as many records as possible. Is your database in full recovery mode or simple recovery mode? To find out, go into SQL Server Management Studio, right-click on the database name, and click Properties. Full recovery mode will log every transaction, but simple recovery mode will run somewhat faster. Are the data files and log files located on separate arrays? How many drives are in each array, and what RAID type is it (1, 5, 10)? If both the data and log files are on the C drive, for example, you'll have poor performance.

Next, you'll want to set up your table, too. Do you have constraints and indexes on the table? Do you have other records in it already, and do you have other people querying it at the same time? If so, consider building an empty table for data loads with no indexes or constraints. Dump all the data in there as fast as possible, and then apply the constraints or indexes, or move the data into its final destination.

Answer 11

my SQL Server 2005 solution

StringBuilder sb = new StringBuilder();
bool bFirst = true;

foreach(Record r in myData)
{
    if (bFirst)
        sb.AppendLine("INSERT INTO tbl (f1, f2, f3)");
    else
        sb.AppendLine("UNION ALL");
    bFirst = false;

    sb.AppendLine("SELECT " + r.data1.ToString() + "," + 
        r.data2.ToString() + "," + r.data3.ToString());
}

SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
cmd.ExecuteNonQuery();

wonder how that would perform ;)

Answer 12

Ayende has some interesting code to batch up exactly these ExecuteNonQuery situations. Opening Up Query Batching was the intro post where he talks about SqlCommandSet, then releases the code in There Be Dragons: Rhino.Commons.SqlCommandSet.

If you can optimise for SQL2008, you could also try the shiny new table value parameters. This sqlteam article is a good intro to them.