Huge Inserts On Large, Flat Table [xx(]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Huge Inserts On Large, Flat Table [xx(]

Author

Topic

aeblank
Starting Member

12 Posts

Posted - 2008-11-22 : 01:25:20

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 01:33:20

you can insert the records in a batch. where will your values to insert be coming from? is it from some flat file? in such cases its easier to just export data to table using BULK INSERT or export import wizard.
Anyways some background info on source and also what values and computations you perform will help. may be some sample data to illustrate them

aeblank
Starting Member

12 Posts

Posted - 2008-11-22 : 01:46:39

Hey,
The data is being generated in a c# program (so no intermediate file). The database is literally being used to store memory structures (each row represents a structure held in memory).

So, the program in C# "generates" several million memory structures, works with them, does some analysis, modifies them, and then needs to "save" these memory structures. Ideally, it then calls some function like "Save All Memory Structures To Database". This, effectively, means inserting several million rows into the database.

Does that clarify it all?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 02:02:12

quote:
Originally posted by aeblank

Hey,
The data is being generated in a c# program (so no intermediate file). The database is literally being used to store memory structures (each row represents a structure held in memory).

So, the program in C# "generates" several million memory structures, works with them, does some analysis, modifies them, and then needs to "save" these memory structures. Ideally, it then calls some function like "Save All Memory Structures To Database". This, effectively, means inserting several million rows into the database.

Does that clarify it all?

so what will be format of data? as arrays?

aeblank
Starting Member

12 Posts

Posted - 2008-11-22 : 02:10:17

Lists of classes. But it would be very easy to convert them to arrays before dumping them to the DB.
So sure, just assume arrays of data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 02:14:16

quote:
Originally posted by aeblank

Lists of classes. But it would be very easy to convert them to arrays before dumping them to the DB.
So sure, just assume arrays of data.

then just use dynamic sql to insert them to table. extract individual values from array and pass it in a select statement like

SQL="INSERT INTO Destination table
SELECT " & Array(0).value & "," & Array(1).value...

this is just a pseudocode

aeblank
Starting Member

12 Posts

Posted - 2008-11-22 : 02:23:21

Cool, I'll give it a shot. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 02:42:12

Cheers