Slow to create tables using SQL Server

[anthon...@gmail.com]

Hi,

I've written a test in both DAO and ADO.NET to create and a populate a
table with 50 fields and 100,000 rows. In my DAO test I create an
Access MDB file and it takes about 14 seconds. In my ADO.NET test I
create the table in an SQL Server database and it takes about 320
seconds - which is incredibly slow. To read the data back from either
however, is very fast.

In my ADO.NET code I'm using an SQL statement like this to add each
row: INSERT INTO tablename (fieldname1, fieldname2, ...) VALUES
(value1, value2, ...).

Using the SqlTransation class to Begin and Commit the transaction for
adding a block of the rows speeds it up somewhat (137 seconds), but
this is still unacceptibly slow.

Is there a faster way ? In DAO I added rows to a recordset object
without having to perform an SQL transaction add each row. Is there a
comparable way to populate a database table in SQL Server ?

Thanks,
Anthony.

[RobinS]

You might want to post this to the
microsoft.public.dotnet.framework.adonet
newsgroup. That's for .Net; I think this one is not.

If you're going to import 100,000 rows into SqlServer, I'd
use the Bulk Insert capabilities. If you search the
above newsgroup for "bulk" and "insert", you'll probably
find a post telling how to do that.

Robin S.
-------------------------------
<anthon...@gmail.com> wrote in message
news:1166406639....@t46g2000cwa.googlegroups.com...

[anthon...@gmail.com]

Thanks. A work colleague said he has used the SQLBulkCopy function to
perform a similar task with excellent performance, so I'll give this a
try.

Anthony.

[RobinS]

I've used it with VB6; it worked pretty well. There's been
several posting in the other group, so if you get stuck,
search that group for help, or post there.

Have a merry Christmas!
Robin S.
------------------------------------
<anthon...@gmail.com> wrote in message
news:1166656235.2...@73g2000cwn.googlegroups.com...