Sign in to follow this  
d000hg

[.net] How fast should writing rows to an Access DB be?

Recommended Posts

I have a very crude DB saver in an app I'm working on. The app only has to insert a bunch of rows into a single table in an Access DB, however it does set 65 columns in each row (it's a very bad DB but I don't have the option to change that). From a C# app, I guess I'm getting an throughput of 500-1000 INSERT statements to run per second... so the 100K rows I want to insert is taking a few minutes. I know Access is not designed for speed so please no comments about getting a "proper DB"... again this is not possible. I;ve never done a proper DB app against Access but this slow seems a little over the top. So far, my DB writing is extremely crude while I test - I get one connection and then for each INSERT statement create a new DbCommand and build its QueryString dynamically, baking values into the string rather than binding parameters and reusing the same command as a prepared statement. Obviously I'd like to improve this, but my question is if this will make a massive difference? Is it likely my app is the bottleneck here and it would be 10X quicker if I did the DB commands properly, or would I expect a much smaller performance gain because it's Access/Jet which is holding things up?

Share this post


Link to post
Share on other sites
I only have one connection - I open the connection, write all the rows, and close the connection. Very simple indeed.

My question is simply if this kind of slow performance is just due to Access, or if it's so slow I should blame my code.

Please people, unless you've actually done serious work against an Access database, don't bother posting about how Access is slow - I KNOW it's slow compared to other DBs but I am interested more in raw performance than relative... is only being able to write 500 rows (an insert statement with 65 values being set) per second reasonable or an order of magnitude less than I should be expecting?

Share this post


Link to post
Share on other sites
If you are using OleDB & Jet than in my opinion the performance you are seeing is of the expected magnitude for a series of insert statements. It is very likely that the driver and the database are the bottleneck. That does not mean you cannot make it faster -- I have heard (and this is not direct experience) that Access can perform better under table binding than it does with SQL queries. Might be worth a try.

Also there are some new 'drivers' for access and excel here but I have no idea if and how they work -- still might be worth a look.

Oh, yeah, one more thing: I don't know about access, but most other databases I have used, prepared statements have given a significant performance boost.

Share this post


Link to post
Share on other sites
Does you table have a primary key? In the past I have solved performance issues with Access in tables that didn't have a primary key by adding an autonumbered primary key field. IIRC that was with select queries, rather than insert queries so I don't know if it will be relevant for you.

Share this post


Link to post
Share on other sites
Quote:
Original post by d000hg
My question is simply if this kind of slow performance is just due to Access, or if it's so slow I should blame my code.

I'd say it's a combination of Access itself and the database design. 65 columns in one table?!?

I wouldn't expect any kind of decent performance at all, especially as the record count increases.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this