The more records the more slow adding rows

Question

Hi everybody, It's terrible how slow my DB Connection gets. I uses C# and an Access DB. I'm importing Data from files into the DB. e.g. I have 4 files the DB is empty it's very fast appr. 1 Minute. Next time, same files, only 2 files take 1 Minute it slows down double. Then at 2 files I abort, because that's boring. Next Time 1 file takes 1Minute and so on, until you wait and wait. What's the problem here ? I tried to put away all the indices (only at the Primary Key there was one), but nothing, please help. Thanks ! Greetings, Nick

tomwing · Answer

It will be deploved publicly and thanks for the blob tip, i will do this this way. Well ADO.NET or not, i'm using OleDbConnection connecting to Access DB so not really ADO.NET. This cleanup thing is possible todo from C#, because it must be transparent and automatically to the user of course. I'm adding 1000 rows, the next 1000 rows then get more slowy and slowy, also on the next program start. Thanks for help Nick P.S.: I just have a field for blobs but still not adding them

spgilmore · Answer

How many rows are you adding? I presume that you are using ADO.NET, right? The best thing you can do is move to embedded firebird or something like that. MSDE would also work, but it isn't embedded and the data files aren't portable. However, it has a bulk-insert features that is incredibly fast. If you can insert 100 rows / sec normally, you can usually import 5,000-10,000 rows / sec with a bulk insert. If this is not an application that you will deploy to many users, you can use any number of free database servers. If you simply must use MSAccess, try a compact and repair operation between the long insert runs. It will clean things up and may speed up the inserts. Are you using blobs? Consider storing blob data as individual files and storing a path/filename in the database instead of the data itself.