-
Jun 17th, 2010, 06:40 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Inserting Large Amounts of Data
Hi all,
[.NET 3.5, Visual Studio C# 2008, OleDb connection, Access 2003]
As part of a database synchronisation program, I need to delete all the data in one table and replace it with the data in another. Currently I've been doing this by calling a DeleteAll() method, followed by looping over all the rows and calling a TableAdapter.Insert() method for each one.
This works, but it's slow: 5000 rows takes about half an hour, according to my users. For comparison, I can manually copy the same data in access in less than 5 seconds. That 5000 rows could easily end up as >50,000 rows by the end of the project, so that sort of performance is not an option.
I've been trying to use DataSetA.Table.Load(DataTableReader); and DataSetB.Table.CopyToDataTable(DataSetA.Table); but neither method has resulted in any change to the database. Either I don't understand how to use them properly, or they're not usable on designer-generated DataSets...
Is there any way of doing this? Any help would be appreciated.
Thanks,
Qu.
"Why do all my attempts at science end with me getting punched by batman?" xkcd.
| Pong| |
Sorry for not posting more often.
-
Jun 17th, 2010, 08:18 PM
#2
Re: Inserting Large Amounts of Data
I don't think truncate is available in Access... so instead
1) Rename old table
2) Copy structure to new table (use previous name)
3) Drop old table
4) Fill-up new table with data
-
Jun 17th, 2010, 08:19 PM
#3
Re: Inserting Large Amounts of Data
Populate a DataTable and call Update on your TableAdapter.
-
Jun 17th, 2010, 08:50 PM
#4
Thread Starter
Hyperactive Member
Re: Inserting Large Amounts of Data
Jmcilhinney wins. Thanks man, I owe you many alcoholic beverages. I replaced my TableAdapter.Insert() with a Table.AddTableRow() and followed the process up with an Update, and it's running many times faster.
Quick follow-up question to assuage my curiosity: would there be any disadvantage to re-working all my foreach(Row) { Insert } loops to use AddTableRow? And if not, what use is TableAdapter.Insert(), aside from saving you an Update call?
"Why do all my attempts at science end with me getting punched by batman?" xkcd.
| Pong| |
Sorry for not posting more often.
-
Jun 17th, 2010, 09:27 PM
#5
Re: Inserting Large Amounts of Data
Originally Posted by Quasar6
Quick follow-up question to assuage my curiosity: would there be any disadvantage to re-working all my foreach(Row) { Insert } loops to use AddTableRow? And if not, what use is TableAdapter.Insert(), aside from saving you an Update call?
Using methods like TableAdapter.Insert is akin to using Command.ExecuteNonQuery. Such methods are designed for executing a single SQL statement. If you only want to insert one record then that would be an easier way than messing about with a DataTable. If you have multiple records to insert though, it's easier to populate the DataTable first and then save them as a batch.
-
Jun 17th, 2010, 09:32 PM
#6
Thread Starter
Hyperactive Member
Re: Inserting Large Amounts of Data
Hah, that's brilliant! Not knowing that, I've been using Insert for batch saving. Re-working it will vastly improve performance on those functions. Thanks Jmc, I really do owe you.
"Why do all my attempts at science end with me getting punched by batman?" xkcd.
| Pong| |
Sorry for not posting more often.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|