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?

user437069
  • 61
  • 4

2 Answers2

2

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

Magnus
  • 45,362
  • 8
  • 80
  • 118
  • I must write data to Access database.The SqlBulkCopy class can be used to write data only to SQL Server tables. – user437069 Feb 26 '11 at 16:31
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.

Michael Dillon
  • 31,973
  • 6
  • 70
  • 106
  • 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. – user437069 Feb 26 '11 at 11:58
  • You can always do the transactions inside SQL statements as shown here http://stackoverflow.com/questions/2078432/do-we-have-transactions-in-ms-access – Michael Dillon Feb 26 '11 at 21:03
  • 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. – user437069 Feb 27 '11 at 01:14
  • 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: http://stackoverflow.com/questions/4562973/whats-the-difference-between-jet-oledbtransaction-commit-mode-and-jet-oledbuse/4581122#4581122 – David-W-Fenton Feb 28 '11 at 05:00