Wednesday, March 10, 2010

SqlBulkCopy

SQL Server 2008 has table-valued parameters in ADO.NET, but if you're stuck with a poorly performing inserts and a previous version of SQL Server, you can still get some awesome speed with the System.Data.SqlClient.SqlBulkCopy class. Just create a new one (in a using block), set up the destination table name and optionally a set of column mappings, and fire away. I ran a test with 65536 rows (width: 3 x INT + 1 x FLOAT) and it completed in 750ms compared with 36'447ms to send the equivalent table row-by-bleeding-row using multiple stored procedure invocations. Also, it's much easier to use than writing your own wrapper around bcp.exe (the only option if you're stuck with Sybase)!

No comments: