Transaction and loop

Databases
1

I wrote Records inside a loop. I’ve made a test and get exact same result (except time: not tested).

Test #1:
BEGIN TRANSACTION
Loop
Write Records
End Loop
COMMIT TRANSACTION

Test #2:
Loop
BEGIN TRANSACTION
Write Record
COMMIT TRANSACTION
End Loop

The idea behind test #2 is… if a crash arise during the loop, what was done is stored in the .sqlite file.

What’s correct ?

2

In #2 you are wastimg time with explicit BEGIN/COMMIT. If you do not do that, then SQLite does it for you, so there is no need.

#1 will be faster and I use that here and there in my app. Nothing stops you making your loop in #1 write blocks of 100 records or whatever so you get a speed up without too much loss. Then you choose the number to write in one transaction, to suit.

3

Apparently, SQLite (or is it Xojo ?) write the data in blocks, reverse order…

Say 50 first rows from the ListBox (excepted the first one) from 50 to 1, then do the same a second time, then… continue (from 50 to 0), and so on until it reach the end of the ListBox.

That is what I can see when I check what’s inside the .sqlite file (with a text editor).

The write is very fast (less than a minute for 452 record / 49,152Bytes)… I would say some seconds.

4

It may look to you like reverse order but that doesn’t mean that it is.

In any case, you should never rely on the order of rows in a database. If the order when reading is impoprtant to you, always use ORDER BY. Otherwise the order is not guaranteed.

5

You are right.

But consider the Rows in the ListBox:
there is 452 entries noted from 0 to 451…

You get an eye on the file contents and see what I wrote in my previous entry above…
I do not have read the file (as db, imported in a ListBox), but I am sure it will be presented in the correct order (it was all the previous times).

6

The SQLite devs reserve the right to present the results in any order if you do not use an ORDER BY. So in a future Xojo release, using a later SQLite library, the order may change. That is explicit in the SQLite doc.

1 Like
7

They may do like Apple in current OS ? No problem, I will sort it there too…
It will be easier with SQLite !