473,326 Members | 2,732 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,326 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 8607
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.