Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Bulk inserts into an OleDB database (e.g. Access) ?

Status
Not open for further replies.

MuadDubby

Programmer
Sep 23, 1999
236
CA
Hello

Is there an OleDB equivalent to System.Data.SqlClient.SqlBulkCopy ? I need to load an Access database with a fairly large table from an SQL Server source, and no matter how I do it, it's always slower than going directly into Access and doing an import. I have no idea how they do it, but it's fast.

Any ideas? I'm basically getting a reader for the source, and then looping through it. For every record I find, I issue an INSERT command.

Thx,
 
Nope, you have to do row by row inserts. I've thought about trying to speed it up by wrapping them all in a transaction, but I have not tried this yet so don't know if Access will support OleDb transactions.

If where you are getting the data from and putting it is fairly stable, I would look into using a DTS/SSIS package to populate your table. This will probably outperform Access' direct import even.

If all else fails, and you really need the speed, you could set up a linked table in access to your SQL Server database, and run a make table query off of that table. Something like
Code:
select * into myAccessTable from mySQLTable

Hope this helps,

Alex

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.
 
David -

I just ran some tests on a project I'm working on, and you can use the OleDbTransactions around your loop with Access. It does seem to improve speed a little bit.

Start it like this:

Code:
//start transaction using Connection.BeginTransaction()
OleDbTransaction oTran = oConn.BeginTransaction();
//assign Transaction attribute to command
oCom.Transaction = oTran;


//looping code for inserts

//Commit transaction
oTran.Commit();
//clean up
oTran.Dispose();

Hope this helps,

Alex


[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.
 
Thx Alex. I have a hard time undrstanding how adding an extra layer to the operation (i.e. transactions) would speed things up, but I guess you never know.

I think I'll look a bit more into the DTS option and see if its feasable.

Thxfor the help,
.DaviD.
 
It's more a database concept than a programming concept. When you issue an insert command, you are creating an implicit transaction. That means you need to begin and commit the transaction (think write to disk) for each insert statement issued.

If you wrap all your inserts in an explicit transaction, then all of the inserts are committed at once, potentially speeding up the process. I don't know a lot about transactions in Access, but I know it makes a big difference in speed in SQL Server, both because of the single commit and because you have to write to the transaction log only once, rather than once per insert.

I did not do any benchmarking when I tested this with Access the other night, but it did seem faster, with the smallish text file I was testing with.

Hope this helps,

Alex

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.
 
Ok, that makes sense now. I didn't think of the implicit transactions. Cool.

Thx.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top