|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
A few different questions...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 Michael,
SQLBulkCopy is the tool you need to use for this application. Kerry Moorman Show quote "Michael Bray" wrote: > 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==?=
<KerryMoor***@discussions.microsoft.com> wrote in news:D73C84A9-7869-417C-9F68-3E61D432FDC6@microsoft.com: Can it support logical operations? The data that I am importing doesn't > Michael, > > SQLBulkCopy is the tool you need to use for this application. > 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. Show quote "Michael Bray" <mbray@ctiusa_dot_com> wrote in message news:Xns99E0DEFF4C6A4mabarayactiusacom@207.46.248.16... > =?Utf-8?B?S2VycnkgTW9vcm1hbg==?= > <KerryMoor***@discussions.microsoft.com> wrote in > news:D73C84A9-7869-417C-9F68-3E61D432FDC6@microsoft.com: > >> Michael, >> >> SQLBulkCopy is the tool you need to use for this application. >> > > 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 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 -- Show quote____________________________________ 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) ----------------------------------------------------------------------------------------------------------------------- "Michael Bray" <mbray@ctiusa_dot_com> wrote in message news:Xns99E0DEFF4C6A4mabarayactiusacom@207.46.248.16... > =?Utf-8?B?S2VycnkgTW9vcm1hbg==?= > <KerryMoor***@discussions.microsoft.com> wrote in > news:D73C84A9-7869-417C-9F68-3E61D432FDC6@microsoft.com: > >> Michael, >> >> SQLBulkCopy is the tool you need to use for this application. >> > > 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  |
|||||||||||||||||||||||
Other interesting topics