Hello, I want to replace a large number of individual insert queries with a single statement to get it done faster. I was told the way to do it is by using a BEGIN... END statement in SQLite. But so far i do not understand how it works, code like this doesn't seem to effect the database:



I have tried going through their documentation but it is too general. Just a few diagrams, very unclear.

Right now the application I am developing takes 5 minutes to write ~2000 records using individual inserts, which is unacceptable. What am I doing wrong?

I don't use SQLLite much, but I expect the advice you were given was to put the bunch of INSERT statements you want into a transaction using BEGIN TRANSACTION And END TRANSACTION.

With a single insert statement in a block, you're not going to notice any difference at all because nothing has changed. What I figure is happening to you is that each insert you make is in its own transaction. Each transaction must be locked, isolated, committed, journaled, and logged atomically, distinct from everything else that is happening to the database at the same time.

If you have 2000 batches of single insert statements, you're doing all the log management steps 2000 times. If you have one batch of 2000 insert statements, you're doing a larger block of log management, but you only do it once. That would cause a performance change, hopefully for the better.

If you're going to do this regularly, you'll want to figure out how to get bulk insert or batch loading working, if SQLite supports it. Such a technique usually locks the table, batches the insert, doesn't log it, and then releases the lock. If that's appropriate for your application, it is generally much (much!) faster than individual inserts in individual transactions, or even a single transaction of many inserts.

You are right on the money. That is exactly the advice I got. The only reason I used only one insert there is because I wanted to test it. I have a code that is generating the rest of the inserts. Didn't really want to change the code before I tested it. It did work, I used a simple SQLite database browser to try it out. When I say worked I mean it did not return a syntax error. But the record was not added. Or it was rolled back, but I am not sure. How do I prevent that from happening or make it actually add records?

You might check the SQLite docs to learn more about transactions and how that database handles them.

I did just that, I do not understand how it works. It is too generic and there is no example there :S.

Usually if you start a transaction you need to do a COMMIT before the end, otherwise it is implicitly rolled back.