SQL Server Performance

Huge Inserts On Large, Flat Table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by aeblank, Nov 21, 2008.

  1. aeblank New Member

    Hi,
    I was wondering what's the fastest way to make huge, non-computationally intensive inserts to a flat table. Some things I'm already doing: dropping indexes and constraints, using a stored procedure for insertion.
    Right now, the queries are executing one at a time, although I have heard about "batch inserting". As I understand, you just surround the batched insert statements or stored procedures with the "GO" command.
    However, I will be calling an "InsertAZillionRowsAtOnce" stored procedure from code, after programmatically calculating what to insert for many rows at one time. So, I can realistically only pass in a few arguments and can only execute a stored procedure once.
    Specific Questions :
    1) What data-type should I be passing to the stored procedure so that by executing one stored procedure I can actually batch/bulk insert many, many rows--an array/a table/?
    2) Should I be inserting directly into the table, or first into the TempDB or a table variable, and then merging that TempDB/table variable into the actual table?
    3) Does anyone know of the syntax to do this from C#, or even just where to find the syntax? (preferably using ADO.NET, but I'll take what I can get)
    Thanks
    SCHEMA: Table contains 3 integer fields, 1 bigint field (primary key), and 1 bit field.
  2. MohammedU New Member

    If I am not mistaken...
    BCP/BULK INSERT or SSIS/DTS (fast load) is the best way to make the insert faster...
    If you are not using these then it is advisable to use SELECT INTO instead of INSERT INTO...
    I don't know much about c# but check IRowsetFastload interface...
  3. aeblank New Member

    Thanks for the tip. That's exactly what I ended up doing (SqlBulkCopy), resulted in a roughly 20x speed increase.

Share This Page