A few different questions...

  • Thread starter Thread starter Michael Bray
  • Start date Start date
M

Michael Bray

I'm trying to import a large dataset (300,000 lines of delimited text) into
a SQL database with ADO.NET. I've run into a few issues -

Performance - the inserts ran very fast for the first 50,000 records or so,
but have slowed down significantly by the time they had inserted around
100,000 records. I am using a DataSet representation of the SQL database
and using the .AddtblXXXRow() to get a row, then setting each value, and
calling Update() on the TableAdapter for each row.

Logic - I was originally only calling Update() after every 500 AddtblXXXRow
()'s had been collected, and it seemed to work, but then it stopped working
after I deleted all the data from the tables, reseeded the identity column,
and tried to do it every 1000 inserts instead. I even went so far as to
completely rebuild the database but it still failed. The error I was
getting on the Update() was that the value in the primary key already
existed in the database.

Any ideas on these two? I think the performance issue might be due to
index maintenance - is there any way to suspend updating of indexes until
all the inserts are complete? I have no idea about the logic issue,
because it had been working and then it just seemed to stop working.

TIA!

-mdb
 
=?Utf-8?B?S2VycnkgTW9vcm1hbg==?=
Michael,

SQLBulkCopy is the tool you need to use for this application.
Click to expand...

Can it support logical operations? The data that I am importing doesn't
import directly. For example, if a particular column is Y then I want to
set a 'bit' field true, otherwise false. Another example, a column might
have 'N/A' in which case I would want the field to be set NULL.

-mdb
 
That depends on how you are transforming 'N/A' to Null. I would assume you
are using a trigger? By default triggers are turned off on BulkCopy,
however, there is an overloaded constructor that lets you pass in options.
You can use that constructor to turn triggers on.
 
Ah, wait. You never bulk copy directly into a production table--import to a
working table such as EvilData.
Once in the server, run a stored procedure to validate, massage and do your
code-based magic as you insert the rows into the production table(s).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top