Jump to content
Skysnake

SQLITE progress handler

Recommended Posts

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

 


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
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.

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
argumentum
#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

  • Like 1

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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;).

  • Like 2

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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.


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
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.

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Skysnake

thank you for sharing your wisdom


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • Skysnake
      By Skysnake
      ; works Local $sqlCLI = 'sqlite3.exe C:/Users/AppData/Local/Temp/~knakvog.tmp ".read sql2csv.txt" ' Local $sqlCLI = 'sqlite3.exe C:/Users/AppData/Local/Temp/~knakvog.tmp < xpt2csv.txt ' Local $openCSV = Run(@ComSpec & " /k " & $sqlCLI,@ScriptDir,@SW_HIDE) Much less impressive than I hoped for.
      Change the @SW_HIDE to MAX to see the results of the labour. 
      It appears as if SQLITE3.EXE can accept one argument after the dbfile name, so the ".read..." works.  But it does not allow multiple consecutive parameters.
      -- sql text file content sql2.csv.txt .headers on .mode csv .once dataout.csv SELECT * FROM memCSV; .system dataout.csv .quit The above line reads the 'sql2csv.txt' file using either of the "< or .read" options, and executes correctly.
      Trying to stipulate all in the AutoIt command line results in failure
      Line below does NOT work. 
      ; not working Local $sqlCLI = 'sqlite3.exe "C:/Users/AppData/Local/Temp/~knakvog.tmp" ".headers on" ".mode csv" ".once dataout.csv" SELECT * FROM memCSV; ".system dataout.csv" ' Failure appears to be a native SQLITE3 "too many parameters" issue and not related to AutoIt.  
    • jvds
      By jvds
      when i try to run my code i get the folowing error
      @@ Debug(257) : __SQLite_Download_SQLite3File : $URL = http://www.autoitscript.com/autoit3/files/beta/autoit/archive/sqlite/sqlite3.dll
          $sTempfile = C:\DOCUME~1\user\CONFIG~1\Temp\~hdyarac.dll
      >Error: 13
      #include <SQLite.au3> #include <SQLite.dll.au3> Local $aResult, $iRows, $iColumns, $iRval Local $hFile, $vData, $sFileName, $sData, $hQuery, $aRow, $sMsg _SQLite_Startup() ConsoleWrite("_SQLite_Startup" & @LF) ;~ ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) Local $sqldb = _SQLite_Open('DB') only on a WinXP computer, it works on win7, any idea what it may be?
×