Fast SQLite Management Functions UDF

LarsJ · April 27, 2018

FSMudf is a sub-UDF of FAMudf, Fast Array Management Functions UDF. FAMudf is needed to use FSMudf. Note that FAMudf.7z is updated with new SQLite display functions that are required to run examples. FASudf, Fast Array Sorting and Management Functions UDF is needed to run examples (Resources\CreateDBs.au3).

Installing SQLite
Navigate to Precompiled Binaries for Windows and download the three zip-files.

Extract sqlite3.dll from sqlite-dll-win64-x64-3230100.zip and rename it to sqlite3_x64.dll
Extract sqlite3.dll from sqlite-dll-win32-x86-3230100.zip
Extract the 3 files in sqlite-tools-win32-x86-3230100.zip
Copy all 5 files to C:\Windows\System32
Copy sqlite3.dll to C:\Windows\SysWOW64

FSMudf
This is the list of functions as shown in Includes\SQLiteFuncs.au3:

; Functions\Initialization.au3
; ----------------------------
; FSM_SQLiteFuncsInitDll    Load compiled code from C++ DLL file

; Functions\SQLiteFuncs.au3
; ----------------------------
; _SQLite_Get_TableA        Passes out the results from a SQL query as a 1D/2D array, ANSI version
; _SQLite_Get_TableW        Passes out the results from a SQL query as a 1D/2D array, WCHAR (Unicode) version
; _SQLite_Get_TableWEx      Passes out the results from a SQL query as a 1D/2D array, WCHAR (Unicode) version
;                           Optimized C++ code


#include-once
#include "Functions\Initialization.au3"
#include "Functions\SQLiteFuncs.au3"

The main code is based on sqlite3_get_table. sqlite3_get_table extracts all elements from the data source at once and inserts the elements in a C-array as strings (more precisely as pointers to zero-terminated UTF-8 strings). The three _SQLite_Get_Table* functions extracts data from the C-array and stores data in native AutoIt arrays.

Note that sqlite3_get_table can use a lot of memory for large data sources because all data elements are stored in the C-array.

NULL-values are stored as "NULL" in result arrays. Since sqlite3_get_table treats all data as strings, BLOB-data should be excluded.

Both _SQLite_Get_TableA and _SQLite_Get_TableWEx are copied and updated from this post in Accessing AutoIt Variables.

The most interesting function is _SQLite_Get_TableWEx which is optimized with C++ code.

_SQLite_Get_TableWEx

; Passes out the results from a SQL query as a 1D/2D array, WCHAR (Unicode) version, optimized C++ code
Func _SQLite_Get_TableWEx( _
  $hDB, _           ; An open database
  $sSQL, _          ; SQL to be executed
  ByRef $aResult, _ ; Results of the query
  ByRef $iRows, _   ; Number of result rows
  ByRef $iCols, _   ; Number of result columns
  $bNames = True )  ; Include column names

  If Not FSM_SQLiteFuncsInitDll( "IsFSM_SQLiteFuncsInitDll" ) Then Return SetError(3,0,0)

  Local $pResult, $iRet = sqlite3_get_table( $hDB, $sSQL, $pResult, $iRows, $iCols ), $pResult0 = $pResult
  If @error Then Return SetError(@error, @extended, $iRet)

  $iRows += $bNames
  If Not $bNames Then $pResult += $iCols * ( @AutoItX64 ? 8 : 4 )

  ; Pass data to method
  Local $aData = [ $pResult, $iRows, $iCols ]
  FAM_PassDataToMethod( 0, $aData )

  ; Execute method
  $aResult = ""
  AccArrays01( _SQLite_Get_TableWEx_Mtd, $aResult )

  sqlite3_free_table( $pResult0 )
EndFunc

; Record the complete query results from one or more queries as a C-array
Func sqlite3_get_table( _
  $hDB, _           ; An open database
  $sSQL, _          ; SQL to be executed
  ByRef $pResult, _ ; Results of the query
  ByRef $iRows, _   ; Number of result rows
  ByRef $iCols )    ; Number of result columns
  If __SQLite_hChk($hDB, 2) Then Return SetError(@error, 0, $SQLITE_MISUSE)
  Local $tSQL8 = __SQLite_StringToUtf8Struct($sSQL)
  If @error Then Return SetError(3, @error, 0)
  Local $avRval = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_get_table", _
    "ptr", $hDB, _       ; An open database
    "struct*", $tSQL8, _ ; SQL to be executed
    "ptr*", 0, _         ; Results of the query
    "int*", 0, _         ; Number of result rows
    "int*", 0, _         ; Number of result columns
    "long*", 0)          ; Error msg written here
  If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error
  $pResult = $avRval[3]
  $iRows = $avRval[4]
  $iCols = $avRval[5]
EndFunc

Func _SQLite_Get_TableWEx_Mtd( $pvResult )
  ; Get data
  Local $aData
  FAM_PassDataToMethod( 1, $aData )
  Local $pResult = $aData[0], $iRows = $aData[1], $iCols = $aData[2]

  ; Create SAFEARRAYBOUND structure for one or two dimensions
  Local $tSafeArrayBound = DllStructCreate( $tagSAFEARRAYBOUND & ( $iCols > 1 ? $tagSAFEARRAYBOUND : "" ) )
  If $iCols > 1 Then DllStructSetData( $tSafeArrayBound, 1, $iCols ) ; Dimension 2: Elements
  DllStructSetData( $tSafeArrayBound, $iCols>1?3:1, $iRows )         ; Dimension 1: Elements

  ; Create safearray of BSTRs for one or two dimensions
  Local $pSafeArray =  SafeArrayCreate( $VT_BSTR, $iCols > 1 ? 2 : 1, $tSafeArrayBound )

  ; Pointer to data
  Local $pSafeArrayData
  SafeArrayAccessData( $pSafeArray, $pSafeArrayData )

  ; Fill safearray
  DllCall( FSM_SQLiteFuncsInitDll(), "int", "GetTableW", "int", $iRows * $iCols, "ptr", $pResult, "ptr", $pSafeArrayData )

  SafeArrayUnaccessData( $pSafeArray )

  ; --- Set $pvResult to match an array ---

  ; Set vt element to $VT_ARRAY + $VT_BSTR
  DllStructSetData( DllStructCreate( "word", $pvResult ), 1, $VT_ARRAY + $VT_BSTR )

  ; Set data element to safearray pointer
  DllStructSetData( DllStructCreate( "ptr", $pvResult + 8 ), 1, $pSafeArray )

  ; <<<< On function exit the safearray contained in a variant is converted to a native AutoIt array >>>>
EndFunc

SQLiteFuncs.cpp

#include <Windows.h>
#include <OleAuto.h>

int __stdcall GetTableW( int iElements, char **pResult, BSTR *pSafeArrayData )
{
  int iCharSize;

  for ( int i = 0; i < iElements; i++ ) {
    if ( pResult[i] == NULL ) { pSafeArrayData[i] = SysAllocString( L"NULL" ); continue; }
    iCharSize = MultiByteToWideChar( CP_UTF8, 0, pResult[i], -1, NULL, 0 );
    pSafeArrayData[i] = SysAllocStringLen( NULL, iCharSize );
    MultiByteToWideChar( CP_UTF8, 0, pResult[i], -1, pSafeArrayData[i], iCharSize );
  }

  return 0;
}

Create DBs
Run CreateDBs.au3 in Resources\ to create test databases. A table is created from an input array of random data generated with FASudf. The database tables contains from 10,000 - 2,000,000 rows and 7 columns. The column data types are integers, strings, integers, floats, dates (integers), times (integers) and row/col (strings). Inserting rows in the tables is implemented through bulk insertions to improve performance. This is a picture of a running CreateDBs.au3:

The green bar is a progress bar.

Examples
Examples\_SQLite_Get_TableWEx.au3:

#AutoIt3Wrapper_Au3Check_Parameters=-d -w- 1 -w 2 -w 3 -w 4 -w 5 -w 6

#include "..\..\FSMudf\Includes\SQLiteFuncs.au3"
FSM_SQLiteFuncsInitDll( "..\..\FSMudf\DLLFiles" )
#include "..\..\Display\Display.au3"

Opt( "MustDeclareVars", 1 )

Example( "10000.db" )

Func Example( $sDB )
  _SQLite_Startup()
  _SQLite_Open( "..\..\FSMudf\Resources\" & $sDB )

  Local $hQuery, $aNames, $sHeader
  Local $sSQL = "SELECT * FROM MyTable;"

  _SQLite_Query( -1, $sSQL, $hQuery )
  _SQLite_FetchNames( $hQuery, $aNames )
  _SQLite_QueryFinalize( $hQuery )
  For $i = 0 To UBound( $aNames ) - 1
    $sHeader &= $aNames[$i] & "|"
  Next

  Local $aResult, $iRows, $iCols
  _SQLite_Get_TableWEx( -1, $sSQL, $aResult, $iRows, $iCols, False )
  _ArrayDisplayEx( $aResult, "", $sHeader )

  _SQLite_Close()
  _SQLite_Shutdown()
EndFunc

Runtimes
A few weeks ago, SQLite display functions were published to display data from SQLite databases in virtual listviews. Code sections like this were used to create sorting indexes:

; Sort by dates and times ascending
_SQLite_Exec( -1, "CREATE TABLE IF NOT EXISTS DTIndexAsc AS SELECT Mytable.RowId AS RowIndex FROM MyTable ORDER BY Dates,Times;" )
_SQLite_GetTable( -1, "SELECT RowIndex FROM DTIndexAsc;", $aIndex, $iRows, $iColumns )
Local $aDTIndexAsc[$iRows]
For $i = 0 To $iRows - 1
  $aDTIndexAsc[$i] = $aIndex[$i+2] + 0
Next

; Sort by dates and times descending
_SQLite_Exec( -1, "CREATE TABLE IF NOT EXISTS DTIndexDesc AS SELECT Mytable.RowId AS RowIndex FROM MyTable ORDER BY Dates DESC,Times DESC;" )
_SQLite_GetTable( -1, "SELECT RowIndex FROM DTIndexDesc;", $aIndex, $iRows, $iColumns )
Local $aDTIndexDesc[$iRows]
For $i = 0 To $iRows - 1
  $aDTIndexDesc[$i] = $aIndex[$i+2] + 0
Next

The function _SQLite_GetTable (in SQLite.au3) is used to extract the index from the database as a 1D array. _SQLite_GetTable executes quite a lot of code and is not fast if there are more than 100,000 rows in the index. That's a problem for the SQLite display functions that are designed to be fast functions. The problem can be solved by using _SQLite_Get_TableWEx to extract the index.

The runtime measurements below compares _SQLite_GetTable and _SQLite_Get_TableWEx when they are used to extract the indexes in "DataDisplay\Examples\SQLiteDisplay\1) Single features\SortByCols.au3" in SQLite display functions. _SQLite_Get_TableWEx is 40 - 70 times faster than _SQLite_GetTable for 100,000 and more rows.

SortByMultCols, 32 bit, with sorting, res.au3:

10000.db 10000.db
10,000 rows, 7 columns 10,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 145.1446 Sort by dates and times ascending ... 141.5637
Load asc index with _SQLite_GetTable ... 530.7759 Load index with _SQLite_Get_TableWEx ... 37.1277
Convert asc index to array of ints ... 9.9348 Convert asc index to array of ints ... 18.2156
Sort by dates and times descending ... 138.9933 Sort by dates and times descending ... 159.1532
Load desc index with _SQLite_GetTable ... 548.0105 Load index with _SQLite_Get_TableWEx ... 32.6165
Convert desc index to array of ints ... 10.1298 Convert desc index to array of ints ... 18.9683
Sort by integers ascending ... 141.4529 Sort by integers ascending ... 119.9954
Load asc index with _SQLite_GetTable ... 555.4392 Load index with _SQLite_Get_TableWEx ... 29.1746
Convert asc index to array of ints ... 10.0639 Convert asc index to array of ints ... 21.4630
Sort by integers descending ... 105.6408 Sort by integers descending ... 146.2417
Load desc index with _SQLite_GetTable ... 511.1149 Load index with _SQLite_Get_TableWEx ... 26.9156
Convert desc index to array of ints ... 10.2198 Convert desc index to array of ints ... 19.5490
Sort by rowcol ... 4.9631 Sort by rowcol ... 5.6856
Display table ... Display table ...

50000.db 50000.db
50,000 rows, 7 columns 50,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 226.8609 Sort by dates and times ascending ... 262.4392
Load asc index with _SQLite_GetTable ... 2604.3736 Load index with _SQLite_Get_TableWEx ... 87.3304
Convert asc index to array of ints ... 51.5826 Convert asc index to array of ints ... 49.2577
Sort by dates and times descending ... 188.8095 Sort by dates and times descending ... 214.4735
Load desc index with _SQLite_GetTable ... 2582.5745 Load index with _SQLite_Get_TableWEx ... 68.6275
Convert desc index to array of ints ... 51.7762 Convert desc index to array of ints ... 48.4177
Sort by integers ascending ... 175.0653 Sort by integers ascending ... 199.8655
Load asc index with _SQLite_GetTable ... 2573.7460 Load index with _SQLite_Get_TableWEx ... 85.5111
Convert asc index to array of ints ... 52.2292 Convert asc index to array of ints ... 52.0790
Sort by integers descending ... 158.0193 Sort by integers descending ... 180.4677
Load desc index with _SQLite_GetTable ... 2569.1081 Load index with _SQLite_Get_TableWEx ... 100.6264
Convert desc index to array of ints ... 53.3551 Convert desc index to array of ints ... 46.7262
Sort by rowcol ... 25.3562 Sort by rowcol ... 25.1772
Display table ... Display table ...

100000.db 100000.db
100,000 rows, 7 columns 100,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 216.4729 Sort by dates and times ascending ... 299.9148
Load asc index with _SQLite_GetTable ... 5112.4636 Load index with _SQLite_Get_TableWEx ... 122.1789
Convert asc index to array of ints ... 105.2914 Convert asc index to array of ints ... 95.5248
Sort by dates and times descending ... 444.2007 Sort by dates and times descending ... 238.4213
Load desc index with _SQLite_GetTable ... 5135.0398 Load index with _SQLite_Get_TableWEx ... 107.8501
Convert desc index to array of ints ... 105.2233 Convert desc index to array of ints ... 95.5043
Sort by integers ascending ... 299.7295 Sort by integers ascending ... 256.2663
Load asc index with _SQLite_GetTable ... 5145.4701 Load index with _SQLite_Get_TableWEx ... 109.8088
Convert asc index to array of ints ... 104.0583 Convert asc index to array of ints ... 101.4750
Sort by integers descending ... 291.4356 Sort by integers descending ... 313.4189
Load desc index with _SQLite_GetTable ... 5145.6136 Load index with _SQLite_Get_TableWEx ... 106.5323
Convert desc index to array of ints ... 107.3509 Convert desc index to array of ints ... 96.1193
Sort by rowcol ... 51.4139 Sort by rowcol ... 54.0834
Display table ... Display table ...

500000.db 500000.db
500,000 rows, 7 columns 500,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 806.3354 Sort by dates and times ascending ... 892.4446
Load asc index with _SQLite_GetTable ... 25527.3910 Load index with _SQLite_Get_TableWEx ... 457.0484
Convert asc index to array of ints ... 524.8282 Convert asc index to array of ints ... 481.0475
Sort by dates and times descending ... 852.3726 Sort by dates and times descending ... 860.3113
Load desc index with _SQLite_GetTable ... 25605.2515 Load index with _SQLite_Get_TableWEx ... 417.6269
Convert desc index to array of ints ... 527.6528 Convert desc index to array of ints ... 477.9776
Sort by integers ascending ... 755.4579 Sort by integers ascending ... 757.7121
Load asc index with _SQLite_GetTable ... 25675.2018 Load index with _SQLite_Get_TableWEx ... 464.5835
Convert asc index to array of ints ... 531.9618 Convert asc index to array of ints ... 484.2971
Sort by integers descending ... 647.1808 Sort by integers descending ... 1090.7365
Load desc index with _SQLite_GetTable ... 25608.5690 Load index with _SQLite_Get_TableWEx ... 442.4290
Convert desc index to array of ints ... 530.2276 Convert desc index to array of ints ... 483.2574
Sort by rowcol ... 257.8365 Sort by rowcol ... 259.4505
Display table ... Display table ...

1000000.db 1000000.db
1,000,000 rows, 7 columns 1,000,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 1487.5694 Sort by dates and times ascending ... 1652.0749
Load asc index with _SQLite_GetTable ... 51230.0474 Load index with _SQLite_Get_TableWEx ... 834.6656
Convert asc index to array of ints ... 1049.9148 Convert asc index to array of ints ... 956.6362
Sort by dates and times descending ... 1757.4797 Sort by dates and times descending ... 1382.4794
Load desc index with _SQLite_GetTable ... 51054.0285 Load index with _SQLite_Get_TableWEx ... 844.8572
Convert desc index to array of ints ... 1048.6826 Convert desc index to array of ints ... 952.6937
Sort by integers ascending ... 1146.5287 Sort by integers ascending ... 1260.0946
Load asc index with _SQLite_GetTable ... 50953.7581 Load index with _SQLite_Get_TableWEx ... 873.6447
Convert asc index to array of ints ... 1052.2763 Convert asc index to array of ints ... 952.4081
Sort by integers descending ... 1202.5926 Sort by integers descending ... 1209.9363
Load desc index with _SQLite_GetTable ... 51029.9517 Load index with _SQLite_Get_TableWEx ... 854.2589
Convert desc index to array of ints ... 1050.6820 Convert desc index to array of ints ... 957.5352
Sort by rowcol ... 515.9625 Sort by rowcol ... 513.5077
Display table ... Display table ...

2000000.db 2000000.db
2,000,000 rows, 7 columns 2,000,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 3422.4791 Sort by dates and times ascending ... 3395.0194
Load asc index with _SQLite_GetTable ... 102323.3789 Load index with _SQLite_Get_TableWEx ... 1764.2740
Convert asc index to array of ints ... 2132.7891 Convert asc index to array of ints ... 1919.4284
Sort by dates and times descending ... 2811.0550 Sort by dates and times descending ... 3199.8887
Load desc index with _SQLite_GetTable ... 102314.9121 Load index with _SQLite_Get_TableWEx ... 1796.9594
Convert desc index to array of ints ... 2137.8481 Convert desc index to array of ints ... 1930.9825
Sort by integers ascending ... 2523.2688 Sort by integers ascending ... 2360.2149
Load asc index with _SQLite_GetTable ... 102350.4757 Load index with _SQLite_Get_TableWEx ... 1755.1357
Convert asc index to array of ints ... 2169.7877 Convert asc index to array of ints ... 1929.2912
Sort by integers descending ... 2666.8068 Sort by integers descending ... 2618.3271
Load desc index with _SQLite_GetTable ... 102249.5368 Load index with _SQLite_Get_TableWEx ... 1771.9980
Convert desc index to array of ints ... 2162.3654 Convert desc index to array of ints ... 1933.3957
Sort by rowcol ... 1020.9946 Sort by rowcol ... 1022.1221
Display table ... Display table ...

SortByMultCols, 64 bit, without sorting, res.au3:

10000.db 10000.db
10,000 rows, 7 columns 10,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 0.5646 Sort by dates and times ascending ... 0.4674
Load asc index with _SQLite_GetTable ... 417.8308 Load index with _SQLite_Get_TableWEx ... 11.1329
Convert asc index to array of ints ... 8.6621 Convert asc index to array of ints ... 13.4027
Sort by dates and times descending ... 0.1881 Sort by dates and times descending ... 0.2953
Load desc index with _SQLite_GetTable ... 407.9304 Load index with _SQLite_Get_TableWEx ... 6.4239
Convert desc index to array of ints ... 8.5864 Convert desc index to array of ints ... 11.4227
Sort by integers ascending ... 0.1837 Sort by integers ascending ... 0.2582
Load asc index with _SQLite_GetTable ... 405.9565 Load index with _SQLite_Get_TableWEx ... 8.5629
Convert asc index to array of ints ... 8.4277 Convert asc index to array of ints ... 8.1426
Sort by integers descending ... 0.1803 Sort by integers descending ... 0.1856
Load desc index with _SQLite_GetTable ... 413.6196 Load index with _SQLite_Get_TableWEx ... 5.9344
Convert desc index to array of ints ... 8.8632 Convert desc index to array of ints ... 8.1412
Sort by rowcol ... 4.2248 Sort by rowcol ... 4.2724
Display table ... Display table ...

50000.db 50000.db
50,000 rows, 7 columns 50,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 0.5884 Sort by dates and times ascending ... 1.2757
Load asc index with _SQLite_GetTable ... 2103.0735 Load index with _SQLite_Get_TableWEx ... 45.9596
Convert asc index to array of ints ... 44.7805 Convert asc index to array of ints ... 41.0902
Sort by dates and times descending ... 0.2017 Sort by dates and times descending ... 0.2081
Load desc index with _SQLite_GetTable ... 2076.3462 Load index with _SQLite_Get_TableWEx ... 32.7278
Convert desc index to array of ints ... 44.8958 Convert desc index to array of ints ... 42.2457
Sort by integers ascending ... 0.2009 Sort by integers ascending ... 0.2036
Load asc index with _SQLite_GetTable ... 2084.3007 Load index with _SQLite_Get_TableWEx ... 31.3150
Convert asc index to array of ints ... 44.5232 Convert asc index to array of ints ... 40.8115
Sort by integers descending ... 0.2025 Sort by integers descending ... 0.2092
Load desc index with _SQLite_GetTable ... 2077.9852 Load index with _SQLite_Get_TableWEx ... 32.0799
Convert desc index to array of ints ... 45.1648 Convert desc index to array of ints ... 40.9486
Sort by rowcol ... 22.0091 Sort by rowcol ... 22.4202
Display table ... Display table ...

100000.db 100000.db
100,000 rows, 7 columns 100,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 0.3660 Sort by dates and times ascending ... 0.3349
Load asc index with _SQLite_GetTable ... 4161.5110 Load index with _SQLite_Get_TableWEx ... 63.0222
Convert asc index to array of ints ... 90.3332 Convert asc index to array of ints ... 85.2626
Sort by dates and times descending ... 0.2014 Sort by dates and times descending ... 0.2045
Load desc index with _SQLite_GetTable ... 4155.9432 Load index with _SQLite_Get_TableWEx ... 64.5647
Convert desc index to array of ints ... 90.2999 Convert desc index to array of ints ... 80.4228
Sort by integers ascending ... 0.2056 Sort by integers ascending ... 0.2097
Load asc index with _SQLite_GetTable ... 4178.6196 Load index with _SQLite_Get_TableWEx ... 63.4502
Convert asc index to array of ints ... 104.8354 Convert asc index to array of ints ... 88.9006
Sort by integers descending ... 0.1986 Sort by integers descending ... 0.2995
Load desc index with _SQLite_GetTable ... 4184.0656 Load index with _SQLite_Get_TableWEx ... 61.9553
Convert desc index to array of ints ... 93.3349 Convert desc index to array of ints ... 84.9720
Sort by rowcol ... 43.3103 Sort by rowcol ... 44.7650
Display table ... Display table ...

500000.db 500000.db
500,000 rows, 7 columns 500,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 0.3255 Sort by dates and times ascending ... 0.3585
Load asc index with _SQLite_GetTable ... 21220.7373 Load index with _SQLite_Get_TableWEx ... 340.0055
Convert asc index to array of ints ... 454.1742 Convert asc index to array of ints ... 410.1735
Sort by dates and times descending ... 0.2219 Sort by dates and times descending ... 0.2211
Load desc index with _SQLite_GetTable ... 21250.0097 Load index with _SQLite_Get_TableWEx ... 317.6836
Convert desc index to array of ints ... 453.9035 Convert desc index to array of ints ... 410.4497
Sort by integers ascending ... 0.2244 Sort by integers ascending ... 0.2130
Load asc index with _SQLite_GetTable ... 21190.8114 Load index with _SQLite_Get_TableWEx ... 317.2279
Convert asc index to array of ints ... 458.1419 Convert asc index to array of ints ... 413.1949
Sort by integers descending ... 0.2366 Sort by integers descending ... 0.2205
Load desc index with _SQLite_GetTable ... 21240.0148 Load index with _SQLite_Get_TableWEx ... 315.8386
Convert desc index to array of ints ... 467.6600 Convert desc index to array of ints ... 413.8453
Sort by rowcol ... 223.0996 Sort by rowcol ... 227.9657
Display table ... Display table ...

1000000.db 1000000.db
1,000,000 rows, 7 columns 1,000,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 0.3399 Sort by dates and times ascending ... 0.3302
Load asc index with _SQLite_GetTable ... 42514.1138 Load index with _SQLite_Get_TableWEx ... 643.0386
Convert asc index to array of ints ... 906.9693 Convert asc index to array of ints ... 818.4471
Sort by dates and times descending ... 0.2491 Sort by dates and times descending ... 0.2269
Load desc index with _SQLite_GetTable ... 42535.1586 Load index with _SQLite_Get_TableWEx ... 635.4573
Convert desc index to array of ints ... 914.8249 Convert desc index to array of ints ... 814.0954
Sort by integers ascending ... 0.2651 Sort by integers ascending ... 0.2399
Load asc index with _SQLite_GetTable ... 42571.1798 Load index with _SQLite_Get_TableWEx ... 637.1402
Convert asc index to array of ints ... 924.1231 Convert asc index to array of ints ... 822.8766
Sort by integers descending ... 0.2726 Sort by integers descending ... 0.2455
Load desc index with _SQLite_GetTable ... 42548.2025 Load index with _SQLite_Get_TableWEx ... 628.0942
Convert desc index to array of ints ... 937.4548 Convert desc index to array of ints ... 823.7806
Sort by rowcol ... 438.8201 Sort by rowcol ... 453.7171
Display table ... Display table ...

2000000.db 2000000.db
2,000,000 rows, 7 columns 2,000,000 rows, 7 columns
------------------------------------------------------ ------------------------------------------------------
Sort by dates and times ascending ... 0.3402 Sort by dates and times ascending ... 0.3391
Load asc index with _SQLite_GetTable ... 85582.1108 Load index with _SQLite_Get_TableWEx ... 1343.2268
Convert asc index to array of ints ... 1868.8664 Convert asc index to array of ints ... 1645.0853
Sort by dates and times descending ... 0.3034 Sort by dates and times descending ... 0.2826
Load desc index with _SQLite_GetTable ... 85125.7264 Load index with _SQLite_Get_TableWEx ... 1327.8081
Convert desc index to array of ints ... 1883.5679 Convert desc index to array of ints ... 1647.1631
Sort by integers ascending ... 0.3383 Sort by integers ascending ... 0.2416
Load asc index with _SQLite_GetTable ... 85248.6628 Load index with _SQLite_Get_TableWEx ... 1300.6246
Convert asc index to array of ints ... 1914.6174 Convert asc index to array of ints ... 1639.7942
Sort by integers descending ... 0.4189 Sort by integers descending ... 0.3053
Load desc index with _SQLite_GetTable ... 85243.5332 Load index with _SQLite_Get_TableWEx ... 1293.3039
Convert desc index to array of ints ... 2010.6404 Convert desc index to array of ints ... 1678.0005
Sort by rowcol ... 882.9645 Sort by rowcol ... 901.0884
Display table ... Display table ...

Runtime measurements for the three _SQLite_Get_Table* functions compared to _SQLite_GetTable2d (in SQLite.au3):

Code executed as 32 bit code                              Code executed as 64 bit code                              
============================                              ============================                              

10000.db                                                  10000.db                                                  
10,000 rows, 7 columns                                    10,000 rows, 7 columns                                    
------------------------------------------------------    ------------------------------------------------------    
Extract data with _SQLite_GetTable2d ...     1459.3627    Extract data with _SQLite_GetTable2d ...     1181.4001    
Extract data with _SQLite_Get_TableA ...      686.4455    Extract data with _SQLite_Get_TableA ...      560.7858    
Extract data with _SQLite_Get_TableW ...     1894.6954    Extract data with _SQLite_Get_TableW ...     1438.9197    
Extract data with _SQLite_Get_TableWEx ...     57.4431    Extract data with _SQLite_Get_TableWEx ...     43.5755    

50000.db                                                  50000.db                                                  
50,000 rows, 7 columns                                    50,000 rows, 7 columns                                    
------------------------------------------------------    ------------------------------------------------------    
Extract data with _SQLite_GetTable2d ...     7326.2039    Extract data with _SQLite_GetTable2d ...     5855.6212    
Extract data with _SQLite_Get_TableA ...     3456.1112    Extract data with _SQLite_Get_TableA ...     2815.5756    
Extract data with _SQLite_Get_TableW ...     9460.7205    Extract data with _SQLite_Get_TableW ...     7192.0603    
Extract data with _SQLite_Get_TableWEx ...    308.1110    Extract data with _SQLite_Get_TableWEx ...    240.5781    

100000.db                                                 100000.db                                                 
100,000 rows, 7 columns                                   100,000 rows, 7 columns                                   
------------------------------------------------------    ------------------------------------------------------    
Extract data with _SQLite_GetTable2d ...    14713.1044    Extract data with _SQLite_GetTable2d ...    11671.0253    
Extract data with _SQLite_Get_TableA ...     6832.4168    Extract data with _SQLite_Get_TableA ...     5623.6683    
Extract data with _SQLite_Get_TableW ...    18881.6323    Extract data with _SQLite_Get_TableW ...    14307.3576    
Extract data with _SQLite_Get_TableWEx ...    570.2480    Extract data with _SQLite_Get_TableWEx ...    439.9531    

500000.db                                                 500000.db                                                 
500,000 rows, 7 columns                                   500,000 rows, 7 columns                                   
------------------------------------------------------    ------------------------------------------------------    
Extract data with _SQLite_GetTable2d ...    73256.2654    Extract data with _SQLite_GetTable2d ...    58339.9228    
Extract data with _SQLite_Get_TableA ...    34176.2231    Extract data with _SQLite_Get_TableA ...    28192.6978    
Extract data with _SQLite_Get_TableW ...    94791.4794    Extract data with _SQLite_Get_TableW ...    71838.5087    
Extract data with _SQLite_Get_TableWEx ...   2865.7178    Extract data with _SQLite_Get_TableWEx ...   2214.4506    

1000000.db                                                1000000.db                                                
1,000,000 rows, 7 columns                                 1,000,000 rows, 7 columns                                 
------------------------------------------------------    ------------------------------------------------------    
Extract data with _SQLite_GetTable2d ...   146762.3736    Extract data with _SQLite_GetTable2d ...   116974.8871    
Extract data with _SQLite_Get_TableA ...    68642.1209    Extract data with _SQLite_Get_TableA ...    56409.6025    
Extract data with _SQLite_Get_TableW ...   189849.3528    Extract data with _SQLite_Get_TableW ...   143717.5201    
Extract data with _SQLite_Get_TableWEx ...   5784.5193    Extract data with _SQLite_Get_TableWEx ...   4438.3908    

2000000.db                                                2000000.db                                                
2,000,000 rows, 7 columns                                 2,000,000 rows, 7 columns                                 
------------------------------------------------------    ------------------------------------------------------    
Extract data with _SQLite_GetTable2d ...   293500.3887    Extract data with _SQLite_GetTable2d ...   234128.4466    
Extract data with _SQLite_Get_TableA ...   137109.4020    Extract data with _SQLite_Get_TableA ...   112849.2381    
Extract data with _SQLite_Get_TableW ...   380164.0186    Extract data with _SQLite_Get_TableW ...   288153.6158    
Extract data with _SQLite_Get_TableWEx ...  11663.6742    Extract data with _SQLite_Get_TableWEx ...   9037.5468

_SQLite_Get_TableWEx is 25 times faster than _SQLite_GetTable2d. _SQLite_Get_TableA is twice as fast as _SQLite_GetTable2d and _SQLite_Get_TableW is slower than _SQLite_GetTable2d.

FSMudf.7z
FAMudf.7z must be downloaded and unzipped to use FSMudf. Note that FAMudf.7z is updated with new SQLite display functions that are required to run examples. FSMudf.7z must be unzipped in the same folder structure as FAMudf.7z. See Fast Array Management Functions UDF for details. FASudf.7z is needed to run examples (Resources\CreateDBs.au3).

Start running the examples in FAMproj\FSMudf\Examples\ (with F5 in SciTE) and look at the code in the examples.

You need AutoIt 3.3.10 or later. Tested on Windows 10 and Windows 7.

Comments are welcome. Let me know if there are any issues.

FSMudf.7z

Edited April 27, 2018 by LarsJ

Sign In

Fast SQLite Management Functions UDF

Recommended Posts

LarsJ

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta