SQLite and transactions

[JohnOne]

This is here because it's not explicitly an AutoIt question.

In my loop some SQL statements are generated, there can be anything between 0 and 20.

I make the SQLite query when broken out of loop.

jchd mentioned to me last week about transactions, and I had no idea what they were, but now I do.

My question is, how many statements do you think there has to be to warrant encapsulating them in one transaction

as opposed to x amount of single operations?

Speed is important to me, but not as much as safety, I want to do as much as is reasonable, to ensure the data is written

while not compromising speed too much.

From what I've read, if one single record is not written for some reason, then none are written at all when encapsulating 

statements within a single transaction, that is not at all desirable to me.

I have done my search but found virtually nothing about it, so I'm asking here rather than a flock of e-strangers elsewhere.

Thanks for reading.

Edited October 27, 2013 by JohnOne

[jchd]

There are two main reasons for using explicit transactions.

The first is only for convenience and aims to speed up considerably bulk inserts or updates. It is common to wrap a number of insert statements into a transaction. The number can be something like 1000 or 50000 (or much more) but that largely depends on the precise use case. Also building indices after insertion is most of the times a winning strategy.

The second use case for transactions is typically Read-Modify-Write operations. The archetype being a financial movement between two accounts. You will want to insure that the following steps are made atomic (all or nothing) without anything intervening in between:

check that both accounts exist

debit amount from source account (implies a check that the source account has enough provision)

credit amount to destination account (implies a check that the destination account will not reach a possible deposit limit after the amount is transfered)

Of course this simple scenario becomes more complex in the real life, where both accounts may not be held by the same bank.

If you don't wrap insert into transactions, the DB engine will have to make every individual SQL insert into its own transaction, down to writing the last bits to disk. With typical hard disk at 7200 rpm, a simple engine like SQLite can only write a maximum of 12 transactions per second. On the contrary, SQLite is perfectly capable of sustaining 100000 inserts per second (well, not with AutoIt).

From this point of view, there is no one-size-fits-all set of parameters: number of inserts by transaction, size of cache, size of DB page, etc.

Don't fall into prematrure optimization: try first with standard parameters and group your 0..20 inserts into one transaction. Time things and decide if they are fast enough for your needs.

Now there is something else: the WAL journalling mode. With WAL mode you simplify insertion and this is probably good enough for most common use cases. Experiment with this pragma and report how well/bad things work.

[JohnOne]

Thanks jchd, I am of course using WAL pragma after my last lesson   .

All my transactions will only be UPDATE, the whole db is built before hand and no INSERT will be required.

Doubtful there would be 12 transactions in a second either either, there will be 12 tables maximum in db, and max of 5 will be updated in any one transaction.

Just wondering at what point does it become practical to use transaction.

If I'm updating 1 row in 3 tables, or does it not matter until I'm updating 2000 rows across 12 tables?

[jchd]

There are more than a single saddle-point in all but the most simple schema. Hence it's very hard to advise in the blind.

If you're willing to share your schema or DB, possibly privately, I can have a look at your context.

[JohnOne]

My db is one of the most simple you will ever have encountered.

There are 5 tables each very similar to what looks like this. with only one row and half a dozen columns.

ID (int) | JOBID (int) | ENGINEER (TEXT) | CUSTOMERID (int) | JOBDATE (TEXT) | COMPLETE (int)

It's really that simple, and unlikely to grew much anytime soon.

I might have 

UPDATE MANCHESTER SET COMPLETE=1 WHERE ID=1;

Maybe there is only one update, I don't use BEGIN TRANSACTION, END TRANSACTION

If two tables need updating I the above statement x2 or x3 inside a transaction, but is it worth it, will three statements in a transaction be slower than 3 transactions.

Do I even need "WHERE ID=1" if there's only one column row.

Edited October 28, 2013 by JohnOne

[jchd]

I have no clue what your problem context is but from what I read I'd suspect that Manchester could well be a column by its own.

I read your last question to mean "one row"; if that's correct, then no in your schema. But I've my doubts about it due to the remark above.

[JohnOne]

Yes sorry, I did mean one row.

Thanks jchd, my project is working just fine with no problems, it's just that I know so little about what I'm using (database)

I feel as though I'm not in control, so just trying to discover fundamentals.

And of course, because I know so little, it could be working fine, but still be poorly optimized.

[jchd]

As long as your practical requirements in terms of performance are acceptable to you and/or your users, try to stick to reasonable defaults. You see, SQLite is one of the most widespread piece of software ever disseminated and the defaults it uses result from incredibly huge experience accumulated. They generally work pretty well for most purposes. Yet a large number of adjustable parameters and options can be tuned to fit unusual purposes.

If ever you estimate that the performance you see is below what you'd like in practice (in human perception and not just at looking at a few ms difference in TimerDiff), it's likely that a relatively simple optimization is available to cure the bottleneck, up to some limit of course.

[JohnOne]

That is advice I understand.

Thank you muchly jchd.