Re: Bulk inserting into database using ADO.NET... deadly slow?

From: Chris Botha (chris_s_botha_at_AT_h.o.t.m.a.i.l.com)
Date: 05/01/04 

Date: Sat, 1 May 2004 10:09:45 -0400

There are not many options, I would have tried the following:
(1) In the app select less records at a shot, 50K for instance, where you
know it is still fast.
(2) Write the data to a file and call the "BULK INSERT" statement from your
program (look in your SQL Server documentation for "BULK INSERT").
Compare the speed of the two and decide which one to use.

"Massimo" <barone@mclink.it> wrote in message
news:OCrvPUyLEHA.3712@TK2MSFTNGP11.phx.gbl...
> I have this SQL Server 2000 database, which I want to populate with data
> generated from a program; the schema is quite simple (one table, seven
> columns, each one one byte wide), but there are plenty of data: about 450
> million rows, for an estimated total of ~3 GB.
> So, I wrote my data-generating .NET application, and then tried to put
data
> into a DataSet and then storing it into the DB using a SqlDataAdapter...
and
> it was painful. My first guess was to store as much data as possible into
> the DataSet, so to reduce the DB updates, but I found that when reaching
> about 100K rows the DataSet becomes slower and slower, each insertion
taking
> more time than the preceding ones. So i resorted to update the DB more
> often, and I tried doing it every 20000, every 10000 and every 5000 rows
> added, but it always seemed to take a lot of time (i.e. more than one
minute
> to insert 20K rows).
> Finally, I decided to avoid DataSets, and I simply wrote all of the
program
> output to a 6 GB CSV text file, and imported it into the DB using DTS;
and,
> wonderful... it started inserting 40-50K rows *per second*; after about
five
> hours the job was completed.
> So, here's the question: why is it so painfully slow to insert big chunks
of
> data into a DB using DataSets? It's so slow that even a 6 GB text file
> provides a *much* better throughput...
>
> Massimo
>

Relevant Pages

  • Bulk inserting into database using ADO.NET... deadly slow?
    ... the DataSet, so to reduce the DB updates, but I found that when reaching ... about 100K rows the DataSet becomes slower and slower, each insertion taking ... why is it so painfully slow to insert big chunks of ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Cannot update
    ... not install the updates you referred to. ... highly recommend way for the later one is to install SQL server, ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • RE: Ad hoc updates to system catalogs are not enabled
    ... Thank you for choosing Microsoft! ... system tables even after you disable allow updates. ... Prevent other users from accessing SQL Server while you are directly updating system ... This command starts an instance of SQL Server in single-user mode and enables allow ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure: Insert VS Update
    ... I have seen times where am insert followed by a couple of updates ran more ... > I have a stored procedure were I created a workfile. ... All the tables are indexed very efficiently and we are on SQL Server ...
    (microsoft.public.sqlserver.olap)
  • Re: index on a view
    ... SQL Server will have to maintain a copy of all data in the table and ... give SQL Server extra work to do on updates and ... updates to the view will be translated ... since it's a very simple one-on-one translation. ...
    (microsoft.public.sqlserver.mseq)