0

I'm looking for the fastest method to read from multiple binary files and write everything inside single MS Access database within multiple tables. SQL Server or any other database are not an option. I'm using C#.

Let's say I have only one file containing 1 GB of data.

  1. File starts with header (containing column names code) followed by more than 700 000 blocks of measurement data. Header is less than 100 byte long. One data block is 1500 bytes long ( 372 x single float + 12 bytes for date and time)

  2. first 84 single values must be inserted in first database table

  3. remaining 288 single values are divided equally in 9 tables, 32 values each
  4. every table row has time and date as first column

For now, I'm using binary reader. I'm reading block by block and executing INSERT statement (ADO.NET) 10 times per block. That would be more than 7 millions of INSERT.

Execute time for 1.5MB is about 1 minute - 1GB would be more than 10 hours.

Is there any way to do this much faster? What do you think is possible to expect?

Improve this question

2 Answers 2

Reset to default
2

You should use the SqlBulkCopy Class See. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I must write data to Access database.The SqlBulkCopy class can be used to write data only to SQL Server tables.
0

You are probably committing on every insert. It is better to begin a transaction, process some fixed number of data blocks, say 100 or 500, and then commit the transaction and begin another. Keep a text file log of which records have been committed so that if you need to restart for some reason, you know where to start.

You could try changing the Jet engine properties for sycing but it would be better to simply begin an SQL transaction on the connection, and then commit after a large chunk of records. You will also need to turn of Auto Commit as in this VB example.

In addition, if you specified sorted keys then undo that. Use ADOX to make them index keys after you load all the data.

Improve this answer

4 Comments

Thanks for replay, but I didn't found StartTrans either. I have read all my data and wrote it inside DataTable. Now I have large DataTables ready to be inserted inside mdb file. I've found few threads suggesting use od Microsoft DAO 3.6 Object Libary or some other COM.
You can always do the transactions inside SQL statements as shown here stackoverflow.com/questions/2078432/…
I've just tried Inserting with ADODB. I got 2x better execution time, but that is still too slow. I tried using BeginTrans/CommitTrans but it looks like there's no effect. After BeginTrans I'm Executing Insert statement for each row in DataTable. Execute method takes to long (90% of processing time) regardles of BeginTrans I declared. Commit is executed in 100 ms, so there is no point of starting new transaction.
The advice in the cited URL to change registry settings is very bad advice. These settings, ImplicitCommitSync and UserCommitSync be set to OPPOSITE settings because they apply to different things. See this post for a full explanation of what those settings apply to: stackoverflow.com/questions/4562973/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.