SQLiteDatabase is so slow

Has anybody else noted a drastic slowdown in performance after switching from REALSQLDatabase to SQLiteDatabase?

I’ve just changed over some of my utilities and discovered a major performance discrepancy.

In my tests on a database of nearly 10 million records I can write these to a REALSQLDatabase in 40 minutes (on a MacBook Air using OS 10.10.2), whereas using SQLiteDatabase the same operation takes over two hours - that’s more than 3 times as long. I’m using the INSERT INTO tablename VALUES … instruction for the writes.

Obviously I am disappointed by this and wondered if others have had the same experience or can suggest any ways I can speed this up. I don’t see why there should be this difference in performance as the resulting databases are identical.

You are executing 10,000,000 INSERT INTO statements? I’m surprised the ANY database could execute that much in 2 hours even.

I’m sure an XOJO engineer will correct me here, but I believe the only MAJOR difference is the version of the underlying SQLite Database Engine (note I said “major”, as I know the newer version has features and functions not in the older).

Are you wrapping these into a transaction set? Or issuing a commit after each one (every 100? every 1000?),
Is the destination table indexed? (if so, drop the indexes and recreate them after the fact).

I had a system under Oracle (on a much larger machine than I’m betting you have), and it took 90 minutes to do 125,000 inserts, but only 20 seconds to do a database to database table create (and that was across a network between Arizona and Pennsylvania)

Thanks Dave

To answer your questions. I have tried both a single db.Commit once after all 10M insertions, and also once after each insertion. The latter is slightly slower. No indexing is involved.

I have to admit that in all other respects SQLDatabase is performing well and searches etc. are just as good as they were with REALSQLDatabase

Most likely your “issue” is due to the fact the new SQLiteDatabase has the eqivalent of “AUTOCOMMIT=TRUE”, so it is doing a commit after EACH insert. This is one reason I asked if you were wrapping things into a Transaction Set…

John, try calling BEGIN TRANSACTION before the inserts and then commit afterwards.

Thanks Greg for the suggestion. Just to be clear. My INSERT INTO SQL is in a loop that is executed 10M times. Do you mean that the BEGIN TRANSACTION should be before the loop and the db.Commit should be afterwards?

So Dave, are you suggesting that I turn AUTOCOMMIT off?

Thanks to you both.

Yes, Begin before the loop and Commit afterward. The AutoCommit feature should be automatically disabled inside the transaction.

Ah! Looks like we’ve cracked it. In 10 minutes I’m up to 3M records already so this will be faster than REALSQLDatabase. Obviously the point I missed was that SQLite is autocommit.

Thanks to you both.

Superb! 9.75M records loaded in 17 minutes. That’s twice as fast as I was getting with REALSQLDatabase!

also please try with bigger cache size:

http://www.monkeybreadsoftware.net/faq-howtosetcachesizeforsqliteorrealsqldatabase.shtml

How big should you set the cache if your database is too big to fit into memory?

Mine is over 4 GB …

When i use BEGIN TRANSACTION, and then after either commit or rollback, does it need to be in the same method??

@Markus Winter:
I normally use a 20 MB cache here. Cache size should be big enough that all your queries fit in.
And most data (Especially blobs) is not often accessed.

@Richard Duke They must not be in same method. e.g. you can put them in helper methods.

call i called DoTransaction(“Start”) , do the work i need to do and then called DoTransaction(“End”)

I guess I need to explain better. This is exactly what I was talking about in all the answers I tried to provide…

I got the impression that John hadn’t used transactions before and to be quite frank, I have always been surprised that we have commands for Commit and Rollback, but not for StartTransaction, so I thought I’d better point it out. :slight_smile:

i got it working… the speed is amazing…

<https://xojo.com/issue/25257> Add Database.OpenTransaction method

@Christian Why would you put the “Begin Transaction” and “End Transaction” in different methods?

Henry