Jump to content

SQLite performance

HurleyShanabarger
 Share

Recommended Posts

HurleyShanabarger Polymath

HurleyShanabarger

Posted
Posted (edited)

Hi,

I am going to work with databases taht will be around 1-10 MB (5000 rows, 20 columns) and during runtime I am going to run some queries depening on the user input.

However I find the performance of the function "_SQLite_GetTable2d" s rather bad (~800ms), compared to directly executing it using _SQLite_SQLiteExe and perform the query via cmd, dump the output and read it with _FileReadToArray it takes until ~400ms . Am I doing a mistake or is just like that?

Find attached a database with random data. 

#NoTrayIcon

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
    #AutoIt3Wrapper_UseX64=n
    #AutoIt3Wrapper_Res_Fileversion=1.0.0.0
    #AutoIt3Wrapper_Res_Fileversion_AutoIncrement=y
    #Tidy_Parameters=/reel /ri /sf
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

#Region Includes
    #include <Array.au3>
    #include <SQLite.au3>
#EndRegion Includes

#Region Main
    Global $g_sFile_Trg = @DesktopDir & "\xxx.csv"
    Global $g_sFile_Src = @DesktopDir & "\random_data.db"
    Global $g_sTable = "RandomData"
    Global $g_boShowArray = False

    For $iLoop = 1 To 5
        _Query_Internal()
        _Query_External()
    Next

    Func _Query_External()
        $iTimer = TimerInit()

        Local $sOutput
        Local $sCmd
        $sCmd &= ".open '" & $g_sFile_Src & "'" & @CRLF
        $sCmd &= ".output '" & $g_sFile_Trg & "'" & @CRLF
        $sCmd &= 'Select * FROM ' & $g_sTable & ' Where "Column_1" <> "";' & @CRLF
        $sCmd &= ".exit" & @CRLF
        _SQLite_SQLiteExe($g_sFile_Trg, $sCmd, $sOutput, @ScriptDir & "\FileInstall\sqlite3.exe", True)
        Local $aQuery
        _FileReadToArray($g_sFile_Trg, $aQuery, 0, "|")
        ConsoleWrite("ex:" & @TAB & TimerDiff($iTimer) & @CRLF)

        If $g_boShowArray Then _ArrayDisplay($aQuery)
    EndFunc   ;==>_Query_External

    Func _Query_Internal()
        _SQLite_Startup()

        Local $hDB = _SQLite_Open($g_sFile_Src)
        Local $aQuery, $iRows, $iColumns
        $iTimer = TimerInit()
        _SQLite_GetTable2d($hDB, 'Select * FROM ' & $g_sTable & ' Where "Column_1" <> "";', $aQuery, $iRows, $iColumns)
        ConsoleWrite("in:" & @TAB & TimerDiff($iTimer) & @CRLF)
        _SQLite_Close($hDB)
        _SQLite_Shutdown()

        If $g_boShowArray Then _ArrayDisplay($aQuery)
    EndFunc   ;==>_Query_Internal
#EndRegion Main

 

random_data.db

Edited by HurleyShanabarger
Link to comment
Share on other sites
SlackerAl Polymath

SlackerAl

Posted
Posted

I thought I would try to replicate your findings here as I probably have a different setup to you - I run my AutoIt stuff from portable apps on a USB 3 drive. I made some minor changes to your example to accommodate my set up: 

#NoTrayIcon

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
    #AutoIt3Wrapper_UseX64=n
    #AutoIt3Wrapper_Res_Fileversion=1.0.0.0
    #AutoIt3Wrapper_Res_Fileversion_AutoIncrement=y
    #Tidy_Parameters=/reel /ri /sf
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

#Region Includes
    #include <Array.au3>
    #include <SQLite.au3>
#EndRegion Includes

#Region Main
    Global $g_sFile_Trg = @ScriptDir & "\xxx.csv"
    Global $g_sFile_Src = @ScriptDir & "\random_data.db"
    Global $g_sTable = "RandomData"
    Global $g_boShowArray = False

    For $iLoop = 1 To 5
        _Query_Internal()
        _Query_External()
    Next

    Func _Query_External()
        $iTimer = TimerInit()

        Local $sOutput
        Local $sCmd
        $sCmd &= ".open '" & $g_sFile_Src & "'" & @CRLF
        $sCmd &= ".output '" & $g_sFile_Trg & "'" & @CRLF
        $sCmd &= 'Select * FROM ' & $g_sTable & ' Where "Column_1" <> "";' & @CRLF
        $sCmd &= ".exit" & @CRLF
        _SQLite_SQLiteExe($g_sFile_Trg, $sCmd, $sOutput, @ScriptDir & "\sqlite3.exe", True)
        Local $aQuery
        _FileReadToArray($g_sFile_Trg, $aQuery, 0, "|")
        ;ConsoleWrite("ex:" & @TAB & TimerDiff($iTimer) & @CRLF)
        MsgBox(0,"external", TimerDiff($iTimer))
        If $g_boShowArray Then _ArrayDisplay($aQuery)
    EndFunc   ;==>_Query_External

    Func _Query_Internal()
        _SQLite_Startup()

        Local $hDB = _SQLite_Open($g_sFile_Src)
        Local $aQuery, $iRows, $iColumns
        $iTimer = TimerInit()
        _SQLite_GetTable2d($hDB, 'Select * FROM ' & $g_sTable & ' Where "Column_1" <> "";', $aQuery, $iRows, $iColumns)
        ;ConsoleWrite("in:" & @TAB & TimerDiff($iTimer) & @CRLF)
        MsgBox(0,"internal", TimerDiff($iTimer))
        _SQLite_Close($hDB)
        _SQLite_Shutdown()

        If $g_boShowArray Then _ArrayDisplay($aQuery)
    EndFunc   ;==>_Query_Internal
#EndRegion Main

I first ran this using the USB drive as the temporary file space and then I ran it from my desktop. For reference my laptop is running 64 Bit Win 7 on an i7-6920HQ @ 2.9GHz. I found the results to vary very little depending on drive, with fairly consistent numbers from all runs:

Internal, typically: 0.060

External, typically: 1200

I.E. very fast using the internal query and slow using the external. I then moved the timer trap to just before the _FileReadToArray and recorded almost exactly the same times, so it is not slowing down there.

Sorry I can't help more.

Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Link to comment
Share on other sites
SlackerAl Polymath

SlackerAl

Posted
Posted (edited)

Ah, you are right, I had not copied the sqlite.dll to the run area, with that the internal comes in consistently at 800 ms. So still faster than the external, but no longer crazy fast!

Edit: Yes, using your database

Edited by SlackerAl

Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Link to comment
Share on other sites
jchd Universalist

jchd

Posted
Posted

This is the difference between C code (sqlite3.exe) and AutoIt DllCall speed.

 

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)

Link to comment
Share on other sites
SlackerAl Polymath

SlackerAl

Posted
Posted

Except Hurley appears to be getting the opposite to me:

Hurley:

Internal ( _SQLite_GetTable2d ) - 800 ms

External - 400ms

Me:

Internal: 800 ms

External: 1200 ms

Of course it is entirely possible we are using different versions of dll / exe.

Problem solving step 1: Write a simple, self-contained, running, replicator of your problem.

Link to comment
Share on other sites
Skysnake Universalist

Skysnake

Posted
Posted

Only modification is from @Desktop to @ScriptDir 

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "C:\AutoIt\SQLite_benchmark\SQLite_speedtest.au3" /UserParams    
+>15:59:16 Starting AutoIt3Wrapper v.17.224.935.0 SciTE v.3.7.3.0   Keyboard:00000409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0409)  CodePage:0  utf8.auto.check:4
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\user\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\user\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.14.2)  from:C:\Program Files (x86)\AutoIt3  input:C:\AutoIt\SQLite_benchmark\SQLite_speedtest.au3
+>15:59:16 AU3Check ended.rc:0
>Running:(3.3.14.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\AutoIt\SQLite_benchmark\SQLite_speedtest.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
in: 1125.03149318241
ex: 43.1144564053243
in: 1135.41853239287
ex: 39.2690602037083
in: 1096.93212536356
ex: 39.1919205295821
in: 1104.40904553724
ex: 39.0906126313733
in: 1098.93775689084
ex: 39.0571744035332
+>15:59:22 AutoIt3.exe ended.rc:0
+>15:59:22 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 6.331

 

Skysnake

Why is the snake in the sky?

Link to comment
Share on other sites
HurleyShanabarger Polymath

HurleyShanabarger

Posted
  • Author
Posted

I just updated the sqlite DLL to the latest one and using the script as X64 version, but there is not a big change.

When database increases the time difference is getting bigger: 

ex: Row: 36298  Col: 20 Time:  1776.65ms
ex: Row: 36298  Col: 20 Time:  1804.74ms
ex: Row: 36298  Col: 20 Time:  1810.61ms
ex: Row: 36298  Col: 20 Time:  1818.60ms
ex: Row: 36298  Col: 20 Time:  1820.34ms
in: Row: 36299  Col: 20 Time: 10696.33ms
in: Row: 36299  Col: 20 Time: 10788.32ms
in: Row: 36299  Col: 20 Time: 10802.92ms
in: Row: 36299  Col: 20 Time: 10853.34ms
in: Row: 36299  Col: 20 Time: 10860.38ms

 

48 minutes ago, jchd said:

This is the difference between C code (sqlite3.exe) and AutoIt DllCall speed.

 

So judging by that the faster approach will actually be my way.

8 minutes ago, Skysnake said:

Only modification is from @Desktop to @ScriptDir 

>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "C:\AutoIt\SQLite_benchmark\SQLite_speedtest.au3" /UserParams    
+>15:59:16 Starting AutoIt3Wrapper v.17.224.935.0 SciTE v.3.7.3.0   Keyboard:00000409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0409)  CodePage:0  utf8.auto.check:4
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\user\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\user\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.14.2)  from:C:\Program Files (x86)\AutoIt3  input:C:\AutoIt\SQLite_benchmark\SQLite_speedtest.au3
+>15:59:16 AU3Check ended.rc:0
>Running:(3.3.14.2):C:\Program Files (x86)\AutoIt3\autoit3.exe "C:\AutoIt\SQLite_benchmark\SQLite_speedtest.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
in: 1125.03149318241
ex: 43.1144564053243
in: 1135.41853239287
ex: 39.2690602037083
in: 1096.93212536356
ex: 39.1919205295821
in: 1104.40904553724
ex: 39.0906126313733
in: 1098.93775689084
ex: 39.0571744035332
+>15:59:22 AutoIt3.exe ended.rc:0
+>15:59:22 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 6.331

 

If you do it like this you might not have the sqlite3.exe in the same directory.

Link to comment
Share on other sites
Skysnake Universalist

Skysnake

Posted
Posted (edited)

Hmm, speaking of this, @jchd, is there a way to show the location of the sqlite.dll being used? 

Func _Query_Internal()
        _SQLite_Startup()

ConsoleWrite( _SQLite_LibVersion() &@CRLf) ; ---> 3.18.0 


        Local $hDB = _SQLite_Open($g_sFile_Src)
        Local $aQuery, $iRows, $iColumns
        $iTimer = TimerInit()
        _SQLite_GetTable2d($hDB, 'Select * FROM ' & $g_sTable & ' Where "Column_1" <> "";', $aQuery, $iRows, $iColumns)
        ConsoleWrite("in:" & @TAB & TimerDiff($iTimer) & @CRLF)
        _SQLite_Close($hDB)
        _SQLite_Shutdown()

        If $g_boShowArray Then _ArrayDisplay($aQuery)
    EndFunc   ;==>_Query_Internal

Why the repetitive connection open/close? Why not single open and close at the end? Is this intentional?

Skysnake

Edited by Skysnake

Skysnake

Why is the snake in the sky?

Link to comment
Share on other sites
HurleyShanabarger Polymath

HurleyShanabarger

Posted
  • Author
Posted
4 minutes ago, Skysnake said:

Hmm, speaking of this, @jchd, is there a way to show the location of the sqlite.dll being used? 

Func _Query_Internal()
        _SQLite_Startup()

ConsoleWrite( _SQLite_LibVersion() &@CRLf) ; ---> 3.18.0 


        Local $hDB = _SQLite_Open($g_sFile_Src)
        Local $aQuery, $iRows, $iColumns
        $iTimer = TimerInit()
        _SQLite_GetTable2d($hDB, 'Select * FROM ' & $g_sTable & ' Where "Column_1" <> "";', $aQuery, $iRows, $iColumns)
        ConsoleWrite("in:" & @TAB & TimerDiff($iTimer) & @CRLF)
        _SQLite_Close($hDB)
        _SQLite_Shutdown()

        If $g_boShowArray Then _ArrayDisplay($aQuery)
    EndFunc   ;==>_Query_Internal

Why the repetitive connection open/close? Why not single open and close at the end? Is this intentional?

Skysnake

_SQLite_Startup() returns the path to the used DLL. In my script it will be called only once, this is just for speed testing. 

It seems that I have to "deal" with it...

 

Link to comment
Share on other sites
Skysnake Universalist

Skysnake

Posted
Posted

I moved the Sqlite startup / open and close/shutdown to outside the Query_internal function for a single startup and shutdown. Negligible improvement.

  

in: 1101.50554793171
ex: 37.2230379457672
in: 1085.84354551158
ex: 39.2448919796259
in: 1080.75530666331
ex: 37.493854484116
in: 1073.18634859518
ex: 36.7767534791483
in: 1084.96455051242
ex: 37.9454360957393

 

Skysnake

Why is the snake in the sky?

Link to comment
Share on other sites
HurleyShanabarger Polymath

HurleyShanabarger

Posted
  • Author
Posted

Can you change the consolewrite line and retry? 

ConsoleWrite("in:" & @TAB & "Row: " & UBound($aQuery, 1) & @TAB & "Col: " & UBound($aQuery, 2) & @TAB & "Time: " & StringFormat("%.2f", TimerDiff($iTimer)) & @CRLF)
ConsoleWrite("ex:" & @TAB & "Row: " & UBound($aQuery, 1) & @TAB & "Col: " & UBound($aQuery, 2) & @TAB & "Time: " & StringFormat("%.2f", TimerDiff($iTimer)) & @CRLF)

 

Link to comment
Share on other sites
HurleyShanabarger Polymath

HurleyShanabarger

Posted
  • Author
Posted

Sure: 

#NoTrayIcon
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
    #AutoIt3Wrapper_UseX64=Y
    #AutoIt3Wrapper_Change2CUI=y
    #AutoIt3Wrapper_Res_Fileversion=1.0.0.1
    #AutoIt3Wrapper_Res_Fileversion_AutoIncrement=y
    #Tidy_Parameters=/reel /ri /sf
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

#Region Includes
    #include <Array.au3>
    #include <SQLite.au3>
#EndRegion Includes

#Region Main
    Global $g_sFile_Trg = @DesktopDir & "\xxx.csv"
    Global $g_sFile_Src = @DesktopDir & "\random_data.db"
    Global $g_sTable = "RandomData"
    Global $g_boShowArray = False

    ConsoleWrite(_SQLite_Startup() & @CRLF)
    _SQLite_Open($g_sFile_Src)

    For $iLoop = 1 To 5
        _Query_Internal()
        _Query_External()
    Next

    _SQLite_Close()
    _SQLite_Shutdown()

    Func _Query_External()
        $iTimer = TimerInit()

        Local $sOutput
        Local $sCmd
        $sCmd &= ".open '" & $g_sFile_Src & "'" & @CRLF
        $sCmd &= ".output '" & $g_sFile_Trg & "'" & @CRLF
        $sCmd &= 'Select * FROM ' & $g_sTable & ' Where "Column_1" <> "";' & @CRLF
        $sCmd &= ".exit" & @CRLF
        _SQLite_SQLiteExe($g_sFile_Trg, $sCmd, $sOutput, @ScriptDir & "\FileInstall\sqlite3.exe", True)
        Local $aQuery
        _FileReadToArray($g_sFile_Trg, $aQuery, 0, "|")
        ConsoleWrite("ex:" & @TAB & "Row: " & UBound($aQuery, 1) & @TAB & "Col: " & UBound($aQuery, 2) & @TAB & "Time: " & StringFormat("%.2f", TimerDiff($iTimer)) & @CRLF)

        If $g_boShowArray Then _ArrayDisplay($aQuery)
    EndFunc   ;==>_Query_External

    Func _Query_Internal()
        Local $aQuery, $iRows, $iColumns
        $iTimer = TimerInit()
        _SQLite_GetTable2d(-1, 'Select * FROM ' & $g_sTable & ' Where "Column_1" <> "";', $aQuery, $iRows, $iColumns)
        ConsoleWrite("in:" & @TAB & "Row: " & UBound($aQuery, 1) & @TAB & "Col: " & UBound($aQuery, 2) & @TAB & "Time: " & StringFormat("%.2f", TimerDiff($iTimer)) & @CRLF)

        If $g_boShowArray Then _ArrayDisplay($aQuery)
    EndFunc   ;==>_Query_Internal
#EndRegion Main

 

Link to comment
Share on other sites
Skysnake Universalist

Skysnake

Posted
Posted (edited) 
>"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "C:\AutoIt\SQLite_benchmark\SQLite_speedtest.au3" /UserParams    
+>16:47:37 Starting AutoIt3Wrapper v.17.224.935.0 SciTE v.3.7.3.0   Keyboard:00000409  OS:WIN_10/  CPU:X64 OS:X64  Environment(Language:0409)  CodePage:0  utf8.auto.check:4
+>         SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE   UserDir => C:\Users\user\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper   SCITE_USERHOME => C:\Users\user\AppData\Local\AutoIt v3\SciTE 
>Running AU3Check (3.3.14.2)  from:C:\Program Files (x86)\AutoIt3  input:C:\AutoIt\SQLite_benchmark\SQLite_speedtest.au3
+>16:47:37 AU3Check ended.rc:0
>Running:(3.3.14.2):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "C:\AutoIt\SQLite_benchmark\SQLite_speedtest.au3"    
--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop

in: Row: 0  Col: 0  Time: 0.04
ex: Row: 0  Col: 0  Time: 36.71
in: Row: 0  Col: 0  Time: 0.03
ex: Row: 0  Col: 0  Time: 37.22
in: Row: 0  Col: 0  Time: 0.04
ex: Row: 0  Col: 0  Time: 36.94
in: Row: 0  Col: 0  Time: 0.03
ex: Row: 0  Col: 0  Time: 38.60
in: Row: 0  Col: 0  Time: 0.03
ex: Row: 0  Col: 0  Time: 34.74
+>16:47:37 AutoIt3.exe ended.rc:0
+>16:47:37 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 0.8162

Only modification is from @Desktop to @ScriptDir

Skysnake

Edited by Skysnake

Skysnake

Why is the snake in the sky?

Link to comment
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
 Share
Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...