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