Jump to content
LarsJ

Fast SQLite Management Functions UDF

Recommended Posts

LarsJ

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.

  1. Extract sqlite3.dll from sqlite-dll-win64-x64-3230100.zip and rename it to sqlite3_x64.dll
  2. Extract sqlite3.dll from sqlite-dll-win32-x86-3230100.zip
  3. Extract the 3 files in sqlite-tools-win32-x86-3230100.zip
  4. Copy all 5 files to C:\Windows\System32
  5. 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:


daR0q1v.png

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 by LarsJ
  • Like 4

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

×