c# oledb - multiple inserts to access (mdb) VERY slow - help!

[dan]

within a loop i am building a sql insert statement to run against my
(programatically created) mdb. it works but it seems unreasonably SLOW!
Sorry, dont have the code here but the jist is very standard (I
think!);

e.g:

# get connection

loop
{
build sql (35 fields to insert)
call ExecuteNonQuery on command obj associated to connection.
}

# close conn.

the insert is called about 500,000 times and that is proving very
time-consuming (e.g. about 15 minutes!!!!)

no exception is thrown and all records get inserted. i cant see any
prob save the dire performance. am i expecting too much or is there a
better perfoming solution (and i wont laugh if someone replie with
'SqlServer' or 'Oracle' as a solution... ;-)

cheers. dan

 

[Marc Gravell]

so <2ms per insert, and that is too slow? Considering this is access I
think you got away lightly! It isn't clear from the pseudo#, but you
might try ensuring that the connection and command both exist *outside*
of the loop, i.e. (more pseudo#):


open connection
create and "prepare" parameterised command (? syntax)
loop {
set parameters for this iteration
execute
}
close connection

You could use command batching, but I doubt Access supports this... in
SqlServer you could use the bulk-copy class to shove this over the
wire, but not access...

Marc

 

[Samuel R. Neff]

First if you're inserting that much data then I would certainly test
it out with SqlServer instead of Access to do a performance comparison
and then evaluate if you can move to SqlServer and if that alone fixes
the problem.

If it doesn't fix the problem or you can't move to SqlServer, then
look at a few different options.

1. Indices. How many indices do you have on the target table? If
there are a lot perhaps you can drop the indices, add the records, and
then recreate the indices. This can produce huge performance benefits
for a lot of inserts (with implications for concurrency though).

2. Values or Select. Are you inserting straight values or are you
inserting based on some select? If your combining known data with a
select (say to lookup id's based on names) can you pre-select the data
and make it an insert-values statement instead?

3. Non-parameterized SQL. If you're not using parameters in your sql
then change start (previous poster mentioned this too).

4. Background. Can you insert the records in the background so user
isn't waiting 15 minutes? That depends on the type of app and
situation.

5. Transaction. Are you inserting the records in a transaction? If
so don't, it's too much data for a single transaction (which can have
huge performance implications)

6. Multiple threads. Is it feasible to break up the data and have
multilple threads running and inserting the data on multiple
connections? I'm not sure how well this would work with acces but
it's worth a try.

7. Use a profiler. Run a .NET performance profiler to see exactly
what's taking the time. Sure it's not your code (and if you haven't
run a profiler then you really can't be absolutely sure). I like ANTS
Profiler but there are a lot out there (and some free ones).
http://www.red-gate.com/products/ants_profiler/index.htm

HTH,

Sam

 

[dan]

Thanks EVERYONE for your suggestions - Sam, very kind to go to such
lengths in your answer. To be honest, I think I have accounted for
pretty much all points raised by everyone. Ultimately, i think i am
simply being impatient, it is 0.5m records after all...

I have tried SServer today but havent had a chance to perform metrics
on it yet as it is a shared server that was being hammered all day!

One query (no pun intended) regards direct sql or parameterised quert -
in my app direct sql is QUICKER than using parameters, strange as I
thought it would be quicker given that it presumably reduces
type-checking of inbound data?

No doubt i will post tomm when i have more perf. tests, and running it
thru' ANTS was already on my list so I'll give that a go to...

p.s. regards the Dataset approach by the last poster, this is a
back-end process so it's not a UI slow-response issue but purely a
matter of making it as quick as poss to complete hence the dataset isnt
going to help...

thanks again everyone ;-)

========================

 

[dan]

First if you're inserting that much data then I would certainly test
it out with SqlServer instead of Access to do a performance comparison
and then evaluate if you can move to SqlServer and if that alone fixes
the problem.

If it doesn't fix the problem or you can't move to SqlServer, then
look at a few different options.

1. Indices. How many indices do you have on the target table? If
there are a lot perhaps you can drop the indices, add the records, and
then recreate the indices. This can produce huge performance benefits
for a lot of inserts (with implications for concurrency though).

2. Values or Select. Are you inserting straight values or are you
inserting based on some select? If your combining known data with a
select (say to lookup id's based on names) can you pre-select the data
and make it an insert-values statement instead?

3. Non-parameterized SQL. If you're not using parameters in your sql
then change start (previous poster mentioned this too).

4. Background. Can you insert the records in the background so user
isn't waiting 15 minutes? That depends on the type of app and
situation.

5. Transaction. Are you inserting the records in a transaction? If
so don't, it's too much data for a single transaction (which can have
huge performance implications)

6. Multiple threads. Is it feasible to break up the data and have
multilple threads running and inserting the data on multiple
connections? I'm not sure how well this would work with acces but
it's worth a try.

7. Use a profiler. Run a .NET performance profiler to see exactly
what's taking the time. Sure it's not your code (and if you haven't
run a profiler then you really can't be absolutely sure). I like ANTS
Profiler but there are a lot out there (and some free ones).
http://www.red-gate.com/products/ants_profiler/index.htm

HTH,

Sam


------------------------------------------------------------
We're hiring! B-Line Medical is seeking Mid/Sr. .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

 

[dan]

First if you're inserting that much data then I would certainly test
it out with SqlServer instead of Access to do a performance comparison
and then evaluate if you can move to SqlServer and if that alone fixes
the problem.

If it doesn't fix the problem or you can't move to SqlServer, then
look at a few different options.

1. Indices. How many indices do you have on the target table? If
there are a lot perhaps you can drop the indices, add the records, and
then recreate the indices. This can produce huge performance benefits
for a lot of inserts (with implications for concurrency though).

2. Values or Select. Are you inserting straight values or are you
inserting based on some select? If your combining known data with a
select (say to lookup id's based on names) can you pre-select the data
and make it an insert-values statement instead?

3. Non-parameterized SQL. If you're not using parameters in your sql
then change start (previous poster mentioned this too).

4. Background. Can you insert the records in the background so user
isn't waiting 15 minutes? That depends on the type of app and
situation.

5. Transaction. Are you inserting the records in a transaction? If
so don't, it's too much data for a single transaction (which can have
huge performance implications)

6. Multiple threads. Is it feasible to break up the data and have
multilple threads running and inserting the data on multiple
connections? I'm not sure how well this would work with acces but
it's worth a try.

7. Use a profiler. Run a .NET performance profiler to see exactly
what's taking the time. Sure it's not your code (and if you haven't
run a profiler then you really can't be absolutely sure). I like ANTS
Profiler but there are a lot out there (and some free ones).
http://www.red-gate.com/products/ants_profiler/index.htm

HTH,

Sam


------------------------------------------------------------
We're hiring! B-Line Medical is seeking Mid/Sr. .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

 

[dan]

THANKS everyone. I have covered most post comments, ultimately I think
I am just expecting a bit too much from Access! Have added S-Server
cvode today and will test it tomm.

Regards the Dataset approach, the code is running as a back-end process
so the advantages in response (i.e. for a UI) will not be relevant, i
just want the fastest possible insert speeds...

p.s. When i tried parameterising the Insert statements it was actually
slower - bizarre, i kind of expecting a speed increase since it would
reduce data type checking by the Jet engine??

oh, i'm hoping to run ANTS as a matter of course too so will post back
with any info.

Thanks again all!

 

[dan]

First if you're inserting that much data then I would certainly test
it out with SqlServer instead of Access to do a performance comparison
and then evaluate if you can move to SqlServer and if that alone fixes
the problem.

If it doesn't fix the problem or you can't move to SqlServer, then
look at a few different options.

1. Indices. How many indices do you have on the target table? If
there are a lot perhaps you can drop the indices, add the records, and
then recreate the indices. This can produce huge performance benefits
for a lot of inserts (with implications for concurrency though).

2. Values or Select. Are you inserting straight values or are you
inserting based on some select? If your combining known data with a
select (say to lookup id's based on names) can you pre-select the data
and make it an insert-values statement instead?

3. Non-parameterized SQL. If you're not using parameters in your sql
then change start (previous poster mentioned this too).

4. Background. Can you insert the records in the background so user
isn't waiting 15 minutes? That depends on the type of app and
situation.

5. Transaction. Are you inserting the records in a transaction? If
so don't, it's too much data for a single transaction (which can have
huge performance implications)

6. Multiple threads. Is it feasible to break up the data and have
multilple threads running and inserting the data on multiple
connections? I'm not sure how well this would work with acces but
it's worth a try.

7. Use a profiler. Run a .NET performance profiler to see exactly
what's taking the time. Sure it's not your code (and if you haven't
run a profiler then you really can't be absolutely sure). I like ANTS
Profiler but there are a lot out there (and some free ones).
http://www.red-gate.com/products/ants_profiler/index.htm

HTH,

Sam


------------------------------------------------------------
We're hiring! B-Line Medical is seeking Mid/Sr. .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

 

[dan]

THANKS everyone. I have covered most post comments, ultimately I think
I am just expecting a bit too much from Access! Have added S-Server
cvode today and will test it tomm.

Regards the Dataset approach, the code is running as a back-end process
so the advantages in response (i.e. for a UI) will not be relevant, i
just want the fastest possible insert speeds...

p.s. When i tried parameterising the Insert statements it was actually
slower - bizarre, i kind of expecting a speed increase since it would
reduce data type checking by the Jet engine??

oh, i'm hoping to run ANTS as a matter of course too so will post back
with any info.

Thanks again all!

 

[Marc Gravell]

Have added S-Server
Be sure to look at SqlBulkCopy, then; I believe that (via the
SqlBulkCopyOptions) you can (if needed) enable triggers, identity
inserts, constraint checks etc - however, a perfectly valid form of use
(if you have the disk space) is to bulk-copy into a staging table with
everything *TURNED OFF* (to minimise the network IO and db-impact), and
then issue a single (or a few batches) of SQL commands to dopy the data
from the staging table to the transactional table (with no network
hops). Of course this breaks identity insert, but the database should
be providing these anyway.

Marc