Re: c# oledb - multiple inserts to access (mdb) VERY slow - help!
- From: "dan" <dan.t.matthews@xxxxxxxxx>
- Date: 30 Nov 2006 15:49:00 -0800
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/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.
On 29 Nov 2006 15:55:28 -0800, "dan" <dan.t.matthews@xxxxxxxxx> wrote:
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
.
- References:
- c# oledb - multiple inserts to access (mdb) VERY slow - help!
- From: dan
- Re: c# oledb - multiple inserts to access (mdb) VERY slow - help!
- From: Samuel R . Neff
- c# oledb - multiple inserts to access (mdb) VERY slow - help!
- Prev by Date: Opening Large Binary file efficiently
- Next by Date: Re: c# oledb - multiple inserts to access (mdb) VERY slow - help!
- Previous by thread: Re: c# oledb - multiple inserts to access (mdb) VERY slow - help!
- Next by thread: Re: c# oledb - multiple inserts to access (mdb) VERY slow - help!
- Index(es):
Relevant Pages
|
