Results 1 to 6 of 6

Thread: [RESOLVED] Inserting Large Amounts of Data

  1. #1

    Thread Starter
    Hyperactive Member Quasar6's Avatar
    Join Date
    Mar 2008
    Location
    Sol 3
    Posts
    325

    Resolved [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.

  2. #2
    PowerPoster
    Join Date
    Nov 2002
    Location
    Manila
    Posts
    7,629

    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

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Inserting Large Amounts of Data

    Populate a DataTable and call Update on your TableAdapter.

  4. #4

    Thread Starter
    Hyperactive Member Quasar6's Avatar
    Join Date
    Mar 2008
    Location
    Sol 3
    Posts
    325

    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.

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Inserting Large Amounts of Data

    Quote Originally Posted by Quasar6 View Post
    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.

  6. #6

    Thread Starter
    Hyperactive Member Quasar6's Avatar
    Join Date
    Mar 2008
    Location
    Sol 3
    Posts
    325

    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
  •  



Click Here to Expand Forum to Full Width