Access Database Inserts Very Slow

[jeffward]

I have a VB.NET application that is getting data from various sources and saving this to an access database for subsequent reporting.

I am inserting approx 18000 records via a dataset and when I call the update command this is taking over 5 minutes to run

Is this normal or am I missing something?

After opening my database connection I initialise my adapter etc. as follows: -

DA_MyData = New OleDbDataAdapter
DA_MyData.SelectCommand = New OleDbCommand("SELECT * FROM [MyTable]", con)
CB_MyData = New OleDbCommandBuilder(DA_MyData)

DA_MyData.Fill(DS_MyData, "MyTable")
DT_MyData = DS_MyData.Tables("MyTable")

Then the processing that loops through the various other data sources populates this dataset as follows:-

...start of loop...
DR_MyData = DT_MyData.NewRow
DR_MyData("Field1") = strField1
DR_MyData("Field2") = strField2
DR_MyData("Field3") = strField3
DR_MyData("Field4") = strField4
DT_MyData.Rows.Add(DR_MyData)
...end of loop...

Finally before closing the database and tidying up i run the update on the dataset as follows:-

DA_MyData.Update(DS_MyData, "MyTable")

It is this last bit that takes a long time to run!

 

[ThatRickGuy]

Access Database Inserts Very Slow

Yes, yes it is. You may want to look into a more robust database engine if you are going to be moving large amounts of data like that. There is a free desktop version of SQL Server (I can't remember it's acronym at the moment), I know Oracle used to allow free use of their engine as long as it was non-prof work (ie: personal and education), or you can check out MySQL although I'm not sure how well it handles large amounts of data, it can't be worse then Access.

Access attempts to be all things to all people, which means it has to make sacrifices. One of them is performance.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[gmmastros]

Rick,

I think you are referring to MSDE

http://www.microsoft.com/sql/msde/downloads/download.asp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ThatRickGuy]

That's the one. Sorry, my brain is kinda adrift, had to <shudder> buy upgrade licenses for Crystal Reports this morning. I still feel dirty.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[AccordingToDale]

MSDE is definitely the way to go if you are talking large chunks of data (if you want free), but it's a bit of work initially setting up.

18,000 records shouldn't be that bad for Access. (Don't get me wrong, I am no fan, but it can handle much more than that.)

Try a Compact and Repair of the db, that may speed it up a bit.

Also, is the db local or are you processing over a network, b/c that can certainly slow things down.

Dale

 

[jeffward]

Thanks but I am loading the data into an existing access database and I dont have time to rewrite all the forms and reports at the moment as this is only a spall part of what it does.

I cant understand why its so slow but as a test I have exported the data to a comma seperated text file and then reloaded it using import and this takes less than 3 seconds!

Does this mean its going to be faster for me to write the data to a temporary text file and then simply import this into access?!

 

[ThatRickGuy]

That would be my suggestion. You can use the office libraries to run the text import through code too.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[jeffward]

Thanks,

I have amended my applicaton to write to a temporary file and then at the end it automatically imports this file into access, which only takes about 2 seconds!

So much for progress!!

 

[chiph]

Part of the reason for the slowness is Access doing transactions for you. They're safer, but they do slow things down.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first