473,372 Members | 1,099 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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

Nov 29 '06 #1
9 8614
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

Nov 30 '06 #2

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/ant...iler/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.
On 29 Nov 2006 15:55:28 -0800, "dan" <da************@gmail.comwrote:
>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
Nov 30 '06 #3
dan

Samuel R. Neff wrote:
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/ant...iler/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.
On 29 Nov 2006 15:55:28 -0800, "dan" <da************@gmail.comwrote:
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
Nov 30 '06 #4
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 ;-)

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

Samuel R. Neff wrote:
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/ant...iler/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.
On 29 Nov 2006 15:55:28 -0800, "dan" <da************@gmail.comwrote:
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
Nov 30 '06 #5
dan

Samuel R. Neff wrote:
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/ant...iler/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.
On 29 Nov 2006 15:55:28 -0800, "dan" <da************@gmail.comwrote:
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
Nov 30 '06 #6
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!

Nov 30 '06 #7
dan

Samuel R. Neff wrote:
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/ant...iler/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.
On 29 Nov 2006 15:55:28 -0800, "dan" <da************@gmail.comwrote:
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
Dec 1 '06 #8
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!

Dec 1 '06 #9
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

Dec 1 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

39
by: Scotter | last post by:
Okay I think my title line was worded misleadingly. So here goes again. I've got quite 20 identical MDB files running on an IIS5 server. From time to time I need to go into various tables and add...
2
by: Joe | last post by:
Hey, I'm going to give some background on my situation in case anyone can point out a way around my problem altogether... for the problem itself, please skip to the bottom of the post. thanks....
2
by: Joe | last post by:
Hi All, I am new to using the Access DB and I need some help if someone is able to give it to me. What I want to do is get the names of the columns of certain tables. Not the data in the table...
12
by: Riley DeWiley | last post by:
I am developing in c++, OLEDB, and targeting the Jet backend. I have realized late in the project that the Jet backend is too slow, and I need to retarget. I do not want to rewrite my source code....
7
by: ddsvi78 | last post by:
I am a complete idiot when it comes to access. Now that said, I work for a computer security company and one of our customers came to us with an access problem. They had been running fine for a...
0
by: roiavidan | last post by:
Hi, I'm having a bit of a problem with a small application I wrote in C#, which uses an Access database (mdb file) for storing financial data. After looking for a similiar topic and failing to...
6
by: daver | last post by:
Hello all, I am running IIS 5.1 on Windows XP professional. I am writing a web application in C# with Visual Studio.NET. I would like to populate various data structures in my web application...
0
by: Randy | last post by:
Hi,all I met a strange problem, I connected to access database using oledb connection, connection string like "Provider=Microsoft.Jet.OLEDB.4.0; Data...
13
by: Terry Olsen | last post by:
I'm using OleDb to connect with an Access Database. I have anywhere from 10 to over 100 records that I need to either INSERT if the PK doesn't exist or UPDATE if the PK does exist, all in a single...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.