System.Data.SQLite

Hello,

I'm trying to optimize a bulk copy operation from an oracle database to an in memory sqlite database. I created the sqlite database with these settings:

"Data Source=:memory:;Journal Mode=Off"

 I arranged the command text and parameters outside the loop and begin transaction then within loop I inserted the values:

         While dr_source.Read()
            cmd_ids.Parameters("ID").Value = dr_source("ID")
            cmd_ids.Parameters("ROLE_ID").Value = dr_source("ROLE_ID")
            cmd_ids.Parameters("PARTY_ID").Value = dr_source("PARTY_ID")
            cmd_ids.Parameters("PARTY_ROLE_ID").Value = dr_source("PARTY_ROLE_ID")
            cmd_ids.Parameters("IND_ID").Value = dr_source("IND_ID")
            cmd_ids.Parameters("ORG_ID").Value = dr_source("ORG_ID")
            cmd_ids.Parameters("CUST_ID").Value = dr_source("CUST_ID")
            cmd_ids.Parameters("CNTC_ID").Value = dr_source("CNTC_ID")
            cmd_ids.ExecuteNonQuery()
        End While

       txn_ids.Commit()

 All parameters have decimal values. Source table has 10 million rows. If I create index for the target sqlite table, it takes 300 seconds to process the loop. If I drop index, it takes 280 seconds. If I comment out the executenonquery line, it takes 50 seconds to read the source table. So isn't it a slow performance to insert 10 million rows to a in memory database in ~250 second? If I change the target database to file based, it takes 320 seconds to process.Only 20 seconds faster to use memory instead of file? Is it normal? How can I improve this performance? Is replacing the memory allocation library of sqlite will dramatically increase this speed? Any feedback will be appreciated.

 Kind regards.

What is the size of the database?

Is it possible the database doesn't fit in memory, so memory swapping is wasting this time?

The size of the final db is 622mb. My systems have enough memory for this.

I did some benchmarks again with a different pc (my home pc) and here are the latest results for the same task:

Without ExecuteNonQuery line, the loop ends in 57 seconds. 

With in memory database and inserts, the loop ends in 240 seconds.

With a file db on disk (but different then the oracle source disk) and inserts, the loop ends in 249 seconds.

With a fast ram disk drive software (I used gillisoft ramdisk 3.3 and according to some benchmarks, it is one of the fastest ram disks available), the loop ends in 252 seconds.

With a file db on the same disk with oracle db files, the loop ends in 253 seconds.

I also changed the malloc, free and realloc functions of sqlite3 source with some free functions  dlmalloc, dlfree and dlrealloc and nothing change for the speed part. 

My hardware info: 

Intel Core 2 Duo E8500, 3166 MHz,  3328 MB  (DDR2-800 DDR2 SDRAM), ST3500410AS  (500 GB, 7200 RPM, SATA-II), ST31000528AS  (1000 GB, 7200 RPM, SATA-II)

 How can ram and disk insert speeds be same?

i'm not a specialist in this, but i'm intrested in this problem.

Can you make a small routine i can run and play with on my machine?  maybe if other readers of your post can execute the same routine with the same data,someone can find the answer.

 Ok, I wrote a sample project for this. Source db is also a sqlite db this time. Has 200,000 rows. With in memory db I got 6.45 seconds, with file db in the same folder of src db, I got 6.65 seconds. With file db on another drive, I got 6.64 seconds. You can multiple the source rows for a better benchmarking. 

Source db is at the debug directory named src.db3

I can't attach the archive to this post (forum gives an error). You can find the project here:

http://rapidshare.com/files/406394201/test.rar

I made a small change and took the reading of the source records out of the loop.

Writing to a disk target file (same harddisk) : 5 seconds

Writing to memory target:  3.1 seconds

Your times are far better then mines (altough ram is 10x faster than hd)

 Are you writing the same record everytime?  Can you share what changes you did?

i placed the last version on my site    http://www.a-d-e.net/test/dbfTest.zip

Initial time with the in memory database (without changes) was 7.5 seconds.  I then placed the reading of all the source records in a List

This changed the speed of the writing to the target database to +/- 3 Seconds.

I tried many other things, but speed was always the same

I did 1 change to the database: i replaced the null values with -1, because i didn't know how to handle nullable values in VB. (i always use c#) (I tried, but decided to replace the values itself)   I don't think this affected the speed.

Also tried with changing the database DECIMAL types with TEXT, but this also didn't show a difference.

If you want me to test something else, mail me directly.  I don't visit the forum often, and the warning with changes to this post doesn't seem to arrive.

luc.wuyts at a-d-e.net

Your method, as you said can't handle the null values, the program gives an error when it tries to set the null value of the reader to the decimal structure element.  By this method, the data was modified and can not be accepted as a bulk copy system. In basic.net there is a dbnull.value variable that you can check against the reader value by Is or IsNot controls. 

Thanks for your efforts.  I also tried a lot of things but can't speed up the process. I'll write to your mail about another db system, written in c#, and claims that it is much more faster then sqlite. My c# is not good. May be you can help me to write this sample program with that db system. 

Regards,

I want to add final notes to this post.

Past last days, I tried other databases and net 4.0 to see if this performance can be increased. Here are my results:

Sqlite.net is faster then other embedded relational databases for my scenario 

Sqlite.net is  faster then Db4o (7.2seconds vs 17seconds)

Sqlite.net has a similiar speed with STSdb (if we add null value handling to classes) but STSdb is an oodb and I prefer a relational database for my task. 

And finaly, 3.5 framework is faster then 4.0 framework for Sqlite (7.2s vs 8.2s)