sqlite performance

[Tate83]

Hi all,

I am a LiveCode beginner and haven't used SQLite before as well..

I have a few database UPDATE's I am running on my machine using SQLite.
I noticed that it takes quite a long time (> 1600 Milliseconds) to make about 10 update orders.
My code looks like this, repeated for about 13 times. two of the update commands are in IF brackets testing if radio button has a or b and then executes a different update.

Code: Select all

   put "Update settings set SettingValue=" & quote & "kg" &quote & "where SettingNo='MeasUnit'" into tSQLUpdateWeight
    ### run query
   revExecuteSQL tDatabaseID, tSQLUpdateWeight

   put "Update settings set SettingValue=" & quote & field "InputDBEngine" &quote & "where SettingNo='DBEngine'" into tSQLUpdateDBEngine
        ### run query
   revExecuteSQL tDatabaseID, tSQLUpdateDBEngine 

however, according to this: http://www.sqlite.org/speed.html those few update's shouldn't be that long..

can I do this differently? my session should remain open, but can I go into some transaction mode or so? Or does the if-clause based on the radio buttons make it slow? The variables are not declared before I assign a value.

Thanks,
Pascal

[BvG]

Every time you call revExecuteQuery, you are carrying a large overhead. it's best to create longer sql strings and execute them all at once.

Another technique, which I used in BvG Docu, is to use the begin and commit transaction commands of sqlite, to group several executions together. When it recreates the whole dictionary from xml as sqlite database from scratch, it did take more then 5 minutes without using this approach, but less then 10 seconds with. The tricky part is to find out how many commands are best grouped together into one commit, to maximize the speed up effect.

http://sqlite.org/lang_transaction.html

[Tate83]

Thanks, I will look into that.
Actually, BvG Docu was the very reason why I thought it should be much quicker generating those updates

[Tate83]

Turns out the performance is much better on Mac, well below 100ms for the same operation.
Running 6.0.2 RC1 instead of 6.0.1. Probably I should try again on my PC to check if it really was a Windows issue.

However, will look into optimizing the code for the large trxn's to come anyway..

[phaworth]

Code: Select all

   put "Update settings set SettingValue=" & quote & "kg" &quote & "where SettingNo='MeasUnit'" into tSQLUpdateWeight
    ### run query
   revExecuteSQL tDatabaseID, tSQLUpdateWeight

   put "Update settings set SettingValue=" & quote & field "InputDBEngine" &quote & "where SettingNo='DBEngine'" into tSQLUpdateDBEngine
        ### run query
   revExecuteSQL tDatabaseID, tSQLUpdateDBEngine 

Hi Pascal,
Sounds like you got this resolved but just one unrelated thing to watch out for. The official delimiter for strings in SQLite is a single quote, not double quotes as in your code above. Double quotes are used as delimiters for table names, column names, etc. Having said that, SQLite is pretty lax about accepting variations on that where the context is clear but it can get you into trouble sometimes when the context is ambiguous. For example:

SELECT * from Table1 WHERE Column1="Column2"

Assuming Column2 is actually a column name in Table 1, SQLite is probably going to get confused as to whether you mean the contents of Column 1 or the character string "Column1".

Hope that helps and good luck with SQLite and Livecode!

Pete

[Tate83]

Hi Pete,

Thanks for that, I'll keep that in mind.

Best,
Pascal