fastest upload method for large # of records

[wengang]

I have a VB6 app connecting to a SQL Server 2005 db

I'm uploading about 15,000 records on a regular basis.
The records are not very long, mustly numeric.
Some records go into one table and some go into another.
Using two Conn.Execute statements for each set of data was just too slow, taking hours.
THen I wrote a stored procedure that did it, but that wasn't much faster.
Next I conctatenated entire records one after another with a unique identifier and wrote a stored procedure that uses Pat Index to split the record strings up and send each one to the original stored procedure.
That worked, but it always timed out. Obviously it would have taken hours to finish.
So lastly, I broke records into blocks of 50 records (the number I can count on to execute and not time out) and now I use a loop of 300 Conn.Execute statements to send each batch of 50 records to the Pat Index stored procedure.
Currently, this takes over two hours to complete.

Have I missed something that would be faster? I've heard people say Pat Index is slow, but I don't know another way to send multiple records up at a time. Also I don't know how to keep SQL Server from timing out on this enormous job.
Anybody?

Thanks.

[techgnome]

Here's how I'd do it... more accurately, this is how I have done it... and with hundreds of thousands of records.... create a table in the database that mirrors what the data looks like raw. Read the data into a datatable, then use the SQLBulkCopy class to feed the data right into the table. Then I run an SProc that processes, scrubs and validates the data. Then I take the good, valid data and put it into it's final table(s).

-tg

[wengang]

you lost me in the middle

you're saying the datatable in vb6 right?
is SQL BulkCopy a class in VB6, in SQl Server, or in the datatable?
I've never used a datatable before.
Now, how does bulkcopy work? how can it prevent a timeout with SQL Server?

[techgnome]

Bleh... sorry, I missed the VB6 bit... I was thinking .NET.

hmmmm.... I'm not sure I have a solution then....

-tg

[dee-u]

Can't you perform a Bulk Insert or a DTS?

[wengang]

I'm not familiar with DTS or Bulk Insert. How do they work?

On .Net, yes in that case I know what you mean. I did use the datatable in .Net a few years ago.
I guess what I could do is have my VB6 app launch the .Net app when it's done, and then have the .Net app do the rest. I think I can get the data loaded into the .Net datatable, and I'm pretty sure I've connected a VB.Net app to a SQL Server db before.
So, I build this temptable, and then what is next?

[mikeblas]

Bulk Insert works by locking the table, then inserting all the provided rows as fast as possible into the table, then unlocking the table. The INSERT statement works on a single row. It does some housekeeping work, does the insert, and does some more housekeeping. In the case of many INSERT statements, the time spent doing the housekeeping work overwhelms the time spent doing the actual insertion work.

Bulk Insert works on multiple rows. It will do some housekeeping, then insert all the rows, then do some housekeeping. This minimizes the housekeeping work.

In VB5, I don't think there's a direct way to perform bulk inserts into SQL Server. There certainly is in VB.NET. If you're unable to migrate your code to a more modern platform, you'll have to consider using external objects or scripts to make the bulk data transfer happen.