SQLITE progress handler

[Skysnake]

Hi

Could not find the sqlite3_progress_handler in the AutoIt Forum.

SQLITE link is here https://www.sqlite.org/c3ref/progress_handler.html (sorry, LINK insert not working)

I wrote the code below based on examples from the SQLITE.AU3 include.  I do not understand the possible errors.  Also, I am not familiar with the the "C" syntax.

Comments and improvements are greatly appreciated.

Func _SQLite_Progress($hDB = -1, $iTimer = 1000,$sCallback = "_cb",$Param = 1)
    If __SQLite_hChk($hDB, 2) Then Return SetError(@error, 0, $SQLITE_MISUSE)
    If $iTimer = Default Then $iTimer = 1000
    If $sCallback = Default Then $sCallback = "_cb"
    If $Param = Default Then $Param = 1
    Local $avRval = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_progress_handler", _
            "ptr", $hDB, _      ; D An open database connection
            "int", $iTimer, _   ; N the number of virtual machine instructions to evaluate between successive callbacks
            "int", $sCallback, _ ; X user defined callback function
            "ptr", $Param)              ; P only parameter passed to callback X
    If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error
    If $avRval[0] <> $SQLITE_OK Then SetError(-1)
    Return $avRval[0]
EndFunc   ;==>_SQLite_SetTimeout

 

[jchd]

Skysnake,

The call doesn't return a value.

Also you don't explain why and  how this can be useful.

The name $Timer is misleading, since it doesn't refer to any notion of time. This integer represents the number of "JUMP" instructions in the VDBE (the bytecode generated by a given query being executed) between invokations of the user-supplied function. This number may greatly vary between different versions of sqlite3.dll for a given schema, data and query and it doesn't represent a measurable duration nor a percentage of the progress of the query to completion.

The user-supplied function cannot issue SQLite calls on the same DB connection, or an application crash may occur.

The only legitimate purpose of the user-supplied function should be to keep a GUI alive or something similar. But before using it, the question of why one needs it must be carefully examined. It's quite possible that a long-running query is in fact largely sub-optimal due to inadequate schema or badly written query. Most of the times the running time can see order(s) of magnitude improvements by relatively simple optimizations or schema change.

I'm not critisizing, just drawing attention.

[Skysnake]

Hey jchd, 

glad to see I got your attention.

1. I have an insert of 100000 lines - on Win7 Home Edition takes 3 seconds... On Vista 6 minutes...!!!  This is repetitive and beyond my control.

2. I have several selects which must TOTAL() 10k+ rows...

3. Most of these queries are pre-packaged and the end user will simply "click a button" 

4. Due to Windows standard response of "the application has stopped working" I need to be able to send back to the GUI a message saying "Hi, I am still busy, update progress by +1" etc.

Thank you very much for your response.  I do not know C - so to create this function was very difficult.  As to the name of the Timer - should be be called "Interval"?  It works, but does nothing... why not? Is the code wrong or do I not know how to implement it?  You seem to be a SQLITE expert, so if you can't find fault with the C implementation above, the only other option is the way this function is to be used in an actual AutoIt script.

From what I understand, the Callback can not be a sqlite3_prepare_v2() andsqlite3_step(); but that should mean I can use it with INSERT; SELECT and UPDATE queries, right?  So can the Callback be a call to an Insert, Select or Update?

Suggestions and examples would really be very welcome.

Thank you so much for helping.

[argumentum]

add GUIGetMsg() in your loop. That should help.

[argumentum]

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.dll.au3>

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#Region ### START Koda GUI section ### Form=
Local $Form = GUICreate("My app.", 325, 72, 280, 138)
Global $Label1 = GUICtrlCreateLabel("Label1", 15, 10, 296, 17, $SS_CENTER)
Local $Button1 = GUICtrlCreateButton("Button1", 15, 35, 75, 25)
Local $Button2 = GUICtrlCreateButton("Button2", 230, 35, 75, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

Local $hQuery, $aRow
Global $sec = @SEC
_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open()
; Without $sCallback it's a resultless statement
_SQLite_Exec(-1, "Create table tblTest (a,b int,c single not null);")
Local $makeALoopXTimes = 100
For $n = 1 To $makeALoopXTimes
    ;Sleep( 150 )
    If $sec <> @SEC Then
        $sec = @SEC
        GUICtrlSetData( $Label1 , $n &' of '&$makeALoopXTimes)
        GUIGetMsg()  ; <---- this is what i mean
    EndIf
    _SQLite_Exec(-1, "Insert into tblTest values ('string "&$n&"',"&$n&","&($n*2)&");")
Next
GUICtrlSetData( $Label1 , $makeALoopXTimes &' of '&$makeALoopXTimes)

;-------------------------------------------------

Local $d = _SQLite_Exec(-1, "Select rowid,* From tblTest", "_cb") ; _cb will be called for each row
Func _cb($aRow)
    For $s In $aRow
        If $sec <> @SEC Then
            $sec = @SEC
            GUIGetMsg() ; <---- this is what i mean
        EndIf
        ConsoleWrite($s & @TAB)
    Next
    ConsoleWrite(@CRLF)
    ; Return $SQLITE_ABORT ; Would Abort the process and trigger an @error in _SQLite_Exec()
EndFunc   ;==>_cb

_SQLite_Close()
_SQLite_Shutdown()

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

this is what I mean

[jchd]

Skysnake,

I'm not after you, but can you check the following:

o) you have placed your bulk inserts into a transaction (Begin immediate; ... Commit;)
o) you have tried using "chained insertion": e.g. insert into T values ('a', 1), ('b', 2), ('c', 3), ... , ('zzzzz', 999999); The only limit is sqlite3_exec SQL statement length limit, so experiment how many inserts you can group safely. This can cut insert time by a significant factor.
o) you don't have created countless useless indices on the table(s) being inserted into
o) you don't have too many triggers before/after insert
o) when a large bulk insert is routine job, dropping indices then recreating them after insertion can prove beneficial (in wall clock)
o) the data inserted in columns which you use total() on has consistent integer or real types over all rows
 

Can you post or PM the schema and/or the DB or a sample table and some sample data to insert? Masquerade private data as needed.

[jchd]

Forgot to answer that: no you can't issue any SQL statement but feel free to open another connection to keep on working. If you do use two or more concurrent connections to the same DB (in the same or different processes, that doesn't matter) switch to WAL mode. To do that, issue ONCE (the setting is persistent) the following statement when no other connection is active: _SQLite_Exec($hDB, "pragma journal_mode = WAL"). WAL mode allows one writer and multiple readers concurrently.

Also right after each connection (every time, setting is connection-wide), issue the following: _SQLite_Exec($hDB, "pragma busy_timeout = 30000") This makes the timeout reach 5 minutes, don't ask, it's OK.

Now when you have a read-modify-write operation, wrap it in an immediate transaction (begin immediate; ... end;).

[Skysnake]

Argumentum, thats a very clever use of GUIGetMsg - thanks, I will look at that, its easy to implement...

jchd, thank you very much,  advice is good but difficult  I need to check all the points you raised.  To simply say YES or NO is not good enough, I need to work on what you have given me. Thank you for your time.

[jchd]

Take your time and don't hesitate to experiment. Experimenting is free and easy: close all connections to the DB properly and copy the file to a work directory (there should be no hot journal file(s), but if there is one or more, copy it/them along. Then download SQLite Expert freeware (link in .sig) and from there on, you can try various options, queries, change the schema easily, all without having to write a single line of AutoIt code.

It's always better to decently optimize a DB (schema, data types and queries) rather than having to use acrobatic workarounds in routine use. That's not that argumentum trick is fundamently bad, it's just that it may be not needed at all. Since you say your imports are frequent jobs, it's well worth spending time once for all to carefully examine why it takes time and fix the issue at its root. I suspect something strange because there can't be good reasons for such a time difference between W7 and Vista, barring faulty hardware on one side or quantuum computer on the other!

For example, I just ran the following query on a table with 13K rows:
select count(*), total(mntfact), total(mntport) from ebayfiches where pays like 'fr'
It affected 12904 rows without indices on amount summed up and took 49 ms on my old hardware (no SSD, DB uncached).

Another one on a table of Unicode 5.1 characters (19316 rows):
select count(*), total(codepoint) from unicodedata where regexp('(?i)letter', charactername)
summed up (nonsensical total, but it's just for illustration) 5773 rows using a slow PCRE regexp extension over the all the official Unicode character names and took 43ms (DB also not cached, first run).

A last one on a join between two indexed tables of all english and french words (resp. 109582 and 336561) which illustrate how a small difference in the query can lead to runtime disaster:
select count(*), total(length(en.mot)) from en join fr on en.mot = fr.mot where regexp('(?i)^[^aeuy]+$', en.mot) -- finds 795 matching words EN = FR in 105ms but
select count(*), total(length(en.mot)) from en join fr on en.mot like fr.mot where regexp('(?i)^[^aeuy]+$', en.mot) -- ran like mad for > 5 minutes before I stopped it

I use a custom LIKE which uses lower(unaccent()) both custom functions: the indices on mot can't be used, while they are used with the first query.

[Skysnake]

thank you for sharing your wisdom