Accessing AutoIt Variables

[LarsJ]

Subclassing
The technique used in posts 15, 16 (RectToVariant and VariantToRect methods of the UIAutomation object) and 20 (Items method of the Dictionary object) can be referred to as subclassing COM objects. In a COM object with lots of methods and properties (in AutoIt there is usually no distinction between methods and properties, it's common to describe the whole thing as methods) we want to execute some of our own code for a single method or property, either before or after we execute the original method/property code.

In post 15 code is added after the original RectToVariant is executed. The additional code prints out debug information.

In post 16 code is added before the original VariantToRect is executed. The additional code modifies the input array, and solves a real problem related to VariantToRect method. This problem is described in bottom of post 13.

Post 17 shows how the subclassing code for VariantToRect method can be added to the UIAutomation.au3 UDF by junkew. The only difference the user will see is that now the method is working.

Post 20 demonstrates that the subclassing technique also works for objects created with ObjCreate. The Dictionary object is used as an example. Note that this example does not work in AutoIt 3.3.14. You need AutoIt 3.3.10 or 3.3.12.

What has the subclassing technique to do with the code in first post? Not much except that the subclassing technique is exactly the same technique as is used in first post. The subclassing technique can be used to solve problems related to a specific method. The purpose of the code in first post is primarily to speed up array manipulations.

Code in posts 15, 16 and 20 is added to Examples\Subclassing\ folder in zip file in bottom of first post.

Edited March 5, 2017 by LarsJ

[LarsJ]

In many examples in zip file in bottom of first post _ArrayDisplayEx is used to display array data. Often the listview columns are too narrow to view the entire cell text. Because ArrayDisplayEx.au3 UDF is updated with a new parameter to set minimum column widths, it's easy to do something about it. Examples updated.

And because ArrayDisplayEx.au3 is now divided into a set of smaller files only a minor part of the entire UDF is included.

Minor issues in some of the examples are corrected.

Updated zip file in bottom of first post.

[Guest]

look interesting.
Thanks

Edited March 6, 2017 by Guest

[LarsJ]

Array variables
SafeArrayDisplay.au3
When the number of array elements exceeds one million the conversions between native AutoIt arrays and safearrays starts taking longer time.

In some situations you may use a safearray throughout the code without the need to convert to a native AutoIt array at all. This means that you can skip the conversions completely.

Or maybe you need more array elements than are supported by native AutoIt arrays. There is a limit around 16 million elements. There isn't such a limit for safearrays.

In these situations you create the safearray with SafeArrayCreate function and fills out and manipulates the safearray directly.

With recent updates in _ArrayDisplayEx based on virtual ListView it's easy to create a SafeArrayDisplay UDF to display the contents of a safearray in a virtual listview. In this very early version only safearrays of integers are supported.

Safearrays of integers
Examples\Demo examples\6) Safearrays of integers\ in the zip file in bottom of first post (goto top of second post and scroll up a little bit) contains five examples. Example 1 is about a native AutoIt array. Examples 2 - 5 is all about safearrays.

Example1.au3 is a copy of the example in the code box in top of first post. A few timers are added to measure the execution time for different parts of the code. On my PC the 1D safearray of 16 million integers is filled out in about 25 milli seconds. Conversion to a native AutoIt array takes about 765 milli seconds (pretty fast because a safearray of integers is a very simpel array). The listview (_ArrayDisplayEx) shows up in less than 800 milli seconds.

In Example2.au3 a 1D safearray of 100 million integers shows up in the listview (SafeArrayDisplay) in about 200 milli seconds. Assembler code is the same as in example 1. No conversions.

In Example3.au3 a 2D safearray of 10 million rows and 10 columns (100 million elements) shows up in about 200 milli seconds. Assembler code in Example3-x86/x64.asm.

Example4.au3: 50 million rows, 10 columns = 500 million elements. About 1 second.

Example5.au3 must be run as 64 bit code and you need at least 8 GB RAM: 100 million rows, 10 columns = 1,000 million (one billion) elements. About 2 seconds.

Edited March 12, 2017 by LarsJ

[LarsJ]

Real examples
sqlite3_get_table
sqlite3_get_table returns a 1D or 2D array of result data generated by executing a SQLite SELECT statement.

sqlite3_get_table is not coded in SQLite.au3, but _SQLite_GetTable and _SQLite_GetTable2d implements similar functionality.

Both _SQLite_GetTable and _SQLite_GetTable2d are based on a technique where the rows are extracted from a database and inserted in a 1D or 2D array one by one. If you extracts 1 GB of data from the database, the array will use 1 GB of memory when all rows are inserted.

Because rows are both extracted from the database and inserted in the array one by one, you need pretty much code in the loops that handles the rows.

_SQLite_GetTable and _SQLite_GetTable2d uses little memory, but are not very fast because of much code in the loops.

sqlite3_get_table extracts all rows from the database at once and inserts the rows in a C-array. Then you have to copy the rows from the C-array to an AutoIt array. If you extracts 1 GB of data from the database, the C-array will use 1 GB of memory. When all rows are inserted in an AutoIt array this will also use 1 GB of memory. When all rows are copied, the C-array can be deleted. But you still need 2 GB of memory to be able to copy the rows.

You don't need many code lines to copy data from a C-array to an AutoIt array. Code to copy data can be implemented in a tight and fast loop.

sqlite3_get_table uses much memory, but is fast because of a simple loop.

The fact that sqlite3_get_table uses much memory is the reason why it's not recommended to use the function. But you can use it if you know what you are doing.

In this example sqlite3_get_table is implemented as an AutoIt function. Then the loop to copy data from the C-array to the AutoIt array is optimized with a few lines of C++ code.

Installing SQLite
Navigate to "Precompiled Binaries for Windows" section here and download the three zip files.

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

CreateDBs.au3: Create databases
The example is stored in Examples\Real examples\sqlite3_get_table folder in zip file in bottom of first post (goto top of second post and scroll up a little bit). The folder contains 7 AutoIt scripts.

Run CreateDBs.au3 to create the databases. All databases, 5 in total, contains one table with 10 columns but a different number of rows. The name of a database indicates the number of rows when all rows are created.

You can select databases in the GUI. You can set a smaller number of rows in the Stop field (double click). The Created field is updated for every 10,000 rows. You can cancel the creation at any time (checked for every 10,000 rows). You can continue the creation at a later time.

It takes about 5 minutes to create all 5 databases and it uses about 140 MB of disk space.

Running examples
If you've created all tables with the maximum number of rows, the tables contains 10,000, 50,000, 100,000, 500,000 and 1,000,000 rows. For examples 1 - 4 it takes a long time to extract data from the last two tables. Because the examples displays the result array with _ArrayDisplayEx, you can exit an example by clicking the Exit Script button, when the array with 100,000 rows has been displayed. In this way you can skip the last two tables that takes long time.

If you're using AutoIt 3.3.10, please read this post.

Example1.au3: _SQLite_GetTable2d
Example1 extracts data from the tables with _SQLite_GetTable2d:

;#AutoIt3Wrapper_UseX64=y

#include <SQLite.au3>
#include "..\..\..\Includes\ArrayDisplayEx.au3"

Opt( "MustDeclareVars", 1 )

Example( "10000.db" )
Example( "50000.db" )
Example( "100000.db" )
Example( "500000.db" )
Example( "1000000.db" )

Func Example( $sDb )
  If Not FileExists( $sDb ) Then Return

  _SQLite_Startup()
  _SQLite_Open( $sDb )

  Local $hTimer = TimerInit()
  Local $aResult, $iRows, $iColumns
  Local $sSQL = "SELECT * FROM lvdata;"
  ConsoleWrite( "Db   = " & $sDb & @CRLF )
  _SQLite_GetTable2d( -1, $sSQL, $aResult, $iRows, $iColumns )
  ConsoleWrite( "Time = " & TimerDiff( $hTimer ) & @CRLF )
  ConsoleWrite( "Rows = " & $iRows & @CRLF & @CRLF )

  _ArrayDisplayEx( $aResult )

  _SQLite_Close()
  _SQLite_Shutdown()
EndFunc

Time measurements on my PC:

32 bit                64 bit
       ------                ------
Db   = 10000.db              10000.db
Time = 1867.58359001979      1478.42919526511
Rows = 10000                 10000

Db   = 50000.db              50000.db
Time = 9387.16462453407      7352.46471634831
Rows = 50000                 50000

Db   = 100000.db             100000.db
Time = 18718.1164395137      14667.2645397015
Rows = 100000                100000

Db   = 500000.db             500000.db
Time = 94108.9009941094      73874.0820654158
Rows = 500000                500000

Db   = 1000000.db            1000000.db
Time = 188802.773931406      147955.955762936
Rows = 1000000               1000000

Example2.au3: sqlite3_get_table
In Example2 _SQLite_GetTable2d is replaced by _SQLite_GetTableEx which implements sqlite3_get_table:

expandcollapse popup

Func _SQLite_GetTableEx( $hDB, $sSQL, ByRef $aResult, ByRef $iRows, ByRef $iColumns, $iMaxChars = 256 )
  ; Call sqlite3_get_table
  If __SQLite_hChk($hDB, 2) Then Return SetError(@error, 0, $SQLITE_MISUSE)
  If $iMaxChars = "" Or $iMaxChars < 1 Or $iMaxChars = Default Then $iMaxChars = 256
  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

  ; Copy data
  $iRows = $avRval[4]
  $iColumns = $avRval[5]
  Local $pResult = $avRval[3]
  Local $iPtrSize = @AutoItX64 ? 8 : 4
  Local $tagChars = "char[" & $iMaxChars & "]"
  If $iColumns = 1 Then
    Dim $aResult[$iRows+1] ; Add one row for column names
    For $i = 0 To $iRows
      $aResult[$i] = DllStructGetData( DllStructCreate( $tagChars, DllStructGetData( DllStructCreate( "ptr", $pResult ), 1 ) ), 1 )
      $pResult += $iPtrSize
    Next
  Else
    Dim $aResult[$iRows+1][$iColumns] ; Add one row for column names
    For $i = 0 To $iRows
      For $j = 0 To $iColumns - 1
        $aResult[$i][$j] = DllStructGetData( DllStructCreate( $tagChars, DllStructGetData( DllStructCreate( "ptr", $pResult ), 1 ) ), 1 )
        $pResult += $iPtrSize
      Next
    Next
  EndIf

  ; Cleanup
  DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_free_table", "ptr", $avRval[3])
  If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error
EndFunc

Note the loops to copy data from the C-array to the AutoIt array. There are two loops for 1D and 2D arrays. These loops are tight and fast loops:

32 bit                64 bit
       ------                ------
Db   = 10000.db              10000.db
Time = 483.665432687752      406.576381743318
Rows = 10000                 10000

Db   = 50000.db              50000.db
Time = 2424.12387220034      2031.13843558957
Rows = 50000                 50000

Db   = 100000.db             100000.db
Time = 4816.86633631272      3969.70711978683
Rows = 100000                100000

Db   = 500000.db             500000.db
Time = 24276.2591765753      20212.6546837174
Rows = 500000                500000

Db   = 1000000.db            1000000.db
Time = 48510.3322877481      40355.7039802608
Rows = 1000000               1000000

Example3.au3: General usage of the UDF
Example3 shows the general usage of the UDF (AccessingVariables.au3). AccessVariables05 is called with the method function GetTableData and 5 parameters. Especially the code in SQLite_GetTableEx is reorganized to fit into the UDF. Full code in Example3.au3:

expandcollapse popup

;#AutoIt3Wrapper_UseX64=y

Global $g_hDll_SQLite = 0   ; AutoIt v3.3.10
Global $__g_hDll_SQLite = 0 ; AutoIt v3.3.12/14

#include <SQLite.au3>
#include "..\..\..\Includes\AccessingVariables.au3"
#include "..\..\..\Includes\AccVarsUtilities.au3"
#include "..\..\..\Includes\ArrayDisplayEx.au3"

Opt( "MustDeclareVars", 1 )

Example( "10000.db" )
Example( "50000.db" )
Example( "100000.db" )
Example( "500000.db" )
Example( "1000000.db" )

Func Example( $sDb )
  If Not FileExists( $sDb ) Then Return

  _SQLite_Startup()
  If $g_hDll_SQLite Then $__g_hDll_SQLite = $g_hDll_SQLite

  _SQLite_Open( $sDb )

  Local $hTimer = TimerInit()
  Local $pResult, $iRows, $iColumns
  Local $sSQL = "SELECT * FROM lvdata;"
  ConsoleWrite( "Db   = " & $sDb & @CRLF )
  Local $hTimer2 = TimerInit()
  _SQLite_GetTableEx( -1, $sSQL, $pResult, $iRows, $iColumns )
  ConsoleWrite( "Time (SQLite) = " & TimerDiff( $hTimer2 ) & @CRLF )

  $iRows += 1 ; Add one row for column names

  Local $aResult
  Local $iMaxChars = 10
  AccessVariables05( GetTableData, $aResult, $pResult, $iRows, $iColumns, $iMaxChars )

  _SQLite_FreeTable( $pResult )
  ConsoleWrite( "Time (total)  = " & TimerDiff( $hTimer ) & @CRLF & @CRLF )

  _ArrayDisplayEx( $aResult )

  _SQLite_Close()
  _SQLite_Shutdown()
EndFunc

Func GetTableData( $vaResult, $vpResult, $viRows, $viColumns, $viMaxChars )
  ; Convert variants to AutoIt variables
  Local $pResult = Ptr( AccVars_VariantToVariable( $vpResult ) )
  Local $iRows = AccVars_VariantToVariable( $viRows )
  Local $iColumns = AccVars_VariantToVariable( $viColumns )
  Local $iMaxChars = AccVars_VariantToVariable( $viMaxChars )
  Local $iElements = $iRows * $iColumns

  ; Create SAFEARRAYBOUND structure for two dimensions
  Local $tSafeArrayBound = DllStructCreate( $tagSAFEARRAYBOUND & ( $iColumns > 1 ? $tagSAFEARRAYBOUND : "" ) ), $i = 0
  If $iColumns > 1 Then
    DllStructSetData( $tSafeArrayBound, 1, $iColumns ) ; Dimension 2: Elements
    DllStructSetData( $tSafeArrayBound, 2, 0 ) ;                      Lower bound
    $i = 2
  EndIf
  DllStructSetData( $tSafeArrayBound, $i+1, $iRows )   ; Dimension 1: Elements
  DllStructSetData( $tSafeArrayBound, $i+2, 0 ) ;                     Lower bound

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

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

  ; Character buffer
  Local $tagChars = "char[" & $iMaxChars & "]"

  ; Pointer size
  Local $iPtrSize = @AutoItX64 ? 8 : 4

  ; Fill safearray
  Local $hTimer = TimerInit()
  For $i = 0 To $iElements - 1
    DllStructSetData( DllStructCreate( "ptr", $pSafeArrayData ), 1, SysAllocString( DllStructGetData( DllStructCreate( $tagChars, DllStructGetData( DllStructCreate( "ptr", $pResult ), 1 ) ), 1 ) ) )
    $pSafeArrayData += $iPtrSize
    $pResult += $iPtrSize
  Next
  ConsoleWrite( "Time (loop)   = " & TimerDiff( $hTimer ) & @CRLF )

  SafeArrayUnaccessData( $pSafeArray )

  ; --- Set $vaResult to match an array of basic strings ---

  ; Set vt element to $VT_ARRAY + $VT_BSTR
  Local $tvt = DllStructCreate( "word", $vaResult )
  DllStructSetData( $tvt, 1, $VT_ARRAY + $VT_BSTR ) ; <<<< Not a proper AutoIt array >>>>
  ; This is an array of basic strings and not variants as a usual AutoIt array

  ; Set data element to safearray pointer
  Local $pData = $vaResult + 8
  Local $tData = DllStructCreate( "ptr", $pData )
  DllStructSetData( $tData, 1, $pSafeArray )

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

Func _SQLite_GetTableEx( $hDB, $sSQL, ByRef $pResult, ByRef $iRows, ByRef $iColumns )
  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]
  $iColumns = $avRval[5]
EndFunc

Func _SQLite_FreeTable( $pTable )
  DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_free_table", "ptr", $pTable)
  If @error Then Return SetError(1, @error, $SQLITE_MISUSE) ; DllCall error
EndFunc

The purpose of the UDF is to be able to use a safearray instead of a native AutoIt array. A safearray can be accessed by a function coded in assembler, C, C++, C# or FreeBasic which a native AutoIt array can't.

In Example3 data is copied from the C-array to a safearray with pure AutoIt code. In first place the loop to fill the safearray uses much more time than the loops in Example2.

When data is copied, the safearray is converted to a native AutoIt array, and the C-array is deleted.

Time measurements:

32 bit                          64 bit
       ------                          ------
Db   = 10000.db                        10000.db
Time (SQLite) = 23.1510956480495       15.8646911922054
Time (loop)   = 1259.64312687043       996.176992582274
Time (total)  = 1332.95543768019       1052.21221334371

Db   = 50000.db                        50000.db
Time (SQLite) = 114.037493676343       76.4651352473028
Time (loop)   = 6349.05395873402       5002.66300074637
Time (total)  = 6711.25392094445       5282.21047901322

Db   = 100000.db                       100000.db
Time (SQLite) = 173.200050312207       134.708718363014
Time (loop)   = 12620.353116337        10034.0305563544
Time (total)  = 13289.4445210948       10571.7439330294

Db   = 500000.db                       500000.db
Time (SQLite) = 860.637402471615       672.991986063297
Time (loop)   = 64303.8053870632       52010.5345339555
Time (total)  = 67677.4638436421       54740.0105611307

Db   = 1000000.db                      1000000.db
Time (SQLite) = 1712.64277058465       1333.27875456231
Time (loop)   = 143114.724853732       119586.97418064
Time (total)  = 149859.375441548       125065.107209883

Example4.au3: Simplified usage of the UDF
Because only one safearray and one AutoIt array is involved, we can use AccVars_SafeArrayToArray in AccVarsUtilities.au3 to perform the conversion. We don't need to use one of the AccessVariablesXY functions and we don't need to implement a method function or handle variants:

expandcollapse popup

;#AutoIt3Wrapper_UseX64=y

Global $g_hDll_SQLite = 0   ; AutoIt v3.3.10
Global $__g_hDll_SQLite = 0 ; AutoIt v3.3.12/14

#include <SQLite.au3>
#include "..\..\..\Includes\AccVarsUtilities.au3"
#include "..\..\..\Includes\ArrayDisplayEx.au3"

Opt( "MustDeclareVars", 1 )

Example( "10000.db" )
Example( "50000.db" )
Example( "100000.db" )
Example( "500000.db" )
Example( "1000000.db" )

Func Example( $sDb )
  If Not FileExists( $sDb ) Then Return

  _SQLite_Startup()
  If $g_hDll_SQLite Then $__g_hDll_SQLite = $g_hDll_SQLite

  _SQLite_Open( $sDb )

  Local $hTimer = TimerInit()
  Local $pResult, $iRows, $iColumns
  Local $sSQL = "SELECT * FROM lvdata;"
  ConsoleWrite( "Db   = " & $sDb & @CRLF )
  Local $hTimer2 = TimerInit()
  _SQLite_GetTableEx( -1, $sSQL, $pResult, $iRows, $iColumns )
  ConsoleWrite( "Time (SQLite) = " & TimerDiff( $hTimer2 ) & @CRLF )

  $iRows += 1 ; Add one row for column names

  ; Create SAFEARRAYBOUND structure for two dimensions
  Local $tSafeArrayBound = DllStructCreate( $tagSAFEARRAYBOUND & ( $iColumns > 1 ? $tagSAFEARRAYBOUND : "" ) ), $i = 0
  If $iColumns > 1 Then
    DllStructSetData( $tSafeArrayBound, 1, $iColumns ) ; Dimension 2: Elements
    DllStructSetData( $tSafeArrayBound, 2, 0 ) ;                      Lower bound
    $i = 2
  EndIf
  DllStructSetData( $tSafeArrayBound, $i+1, $iRows )   ; Dimension 1: Elements
  DllStructSetData( $tSafeArrayBound, $i+2, 0 ) ;                     Lower bound

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

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

  ; Character buffer
  Local $iMaxChars = 10
  Local $tagChars = "char[" & $iMaxChars & "]"

  ; Pointer size
  Local $iPtrSize = @AutoItX64 ? 8 : 4

  ; Fill safearray
  Local $pResult0 = $pResult
  Local $hTimer3 = TimerInit()
  For $i = 0 To $iRows * $iColumns - 1
    DllStructSetData( DllStructCreate( "ptr", $pSafeArrayData ), 1, SysAllocString( DllStructGetData( DllStructCreate( $tagChars, DllStructGetData( DllStructCreate( "ptr", $pResult ), 1 ) ), 1 ) ) )
    $pSafeArrayData += $iPtrSize
    $pResult += $iPtrSize
  Next
  ConsoleWrite( "Time (loop)   = " & TimerDiff( $hTimer3 ) & @CRLF )

  SafeArrayUnaccessData( $pSafeArray )

  _SQLite_FreeTable( $pResult0 )

  ; AutoIt array
  Local $aResult
  AccVars_SafeArrayToArray( $pSafeArray, $aResult )
  ConsoleWrite( "Time (total)  = " & TimerDiff( $hTimer ) & @CRLF & @CRLF )

  _ArrayDisplayEx( $aResult )

  _SQLite_Close()
  _SQLite_Shutdown()
EndFunc

32 bit                          64 bit
       ------                          ------
Db   = 10000.db                        10000.db
Time (SQLite) = 17.4150607486656       14.1043180396414
Time (loop)   = 1324.75255315058       1029.05446850528
Time (total)  = 1391.56528036588       1083.47476715366

Db   = 50000.db                        50000.db
Time (SQLite) = 95.3244555559804       82.5873772878063
Time (loop)   = 6532.85558900468       5074.77180809772
Time (total)  = 6874.98857170404       5363.29347697801

Db   = 100000.db                       100000.db
Time (SQLite) = 171.70619887396        137.223220524278
Time (loop)   = 13226.8659429023       10330.4403814197
Time (total)  = 13889.9586697408       10871.0854969522

Db   = 500000.db                       500000.db
Time (SQLite) = 854.537047349439       683.296291801936
Time (loop)   = 67230.8330161115       53188.3987142682
Time (total)  = 70543.9902123918       55966.6529250066

Db   = 1000000.db                      1000000.db
Time (SQLite) = 1713.11098440709       1361.08095887974
Time (loop)   = 151801.206107725       123302.690816526
Time (total)  = 158540.361416743       128827.087527172

Example5.au3: C++ code
In Example5 the loop to copy data from the C-array to the safearray is replaced by C++ code.

This is the loop in Example4:

; Fill safearray
Local $pResult0 = $pResult
Local $hTimer3 = TimerInit()
For $i = 0 To $iRows * $iColumns - 1
  DllStructSetData( DllStructCreate( "ptr", $pSafeArrayData ), 1, SysAllocString( DllStructGetData( DllStructCreate( $tagChars, DllStructGetData( DllStructCreate( "ptr", $pResult ), 1 ) ), 1 ) ) )
  $pSafeArrayData += $iPtrSize
  $pResult += $iPtrSize
Next
ConsoleWrite( "Time (loop)   = " & TimerDiff( $hTimer3 ) & @CRLF )

In Example5 the loop is replaced by a DllCall:

; Fill safearray
Local $hTimer3 = TimerInit()
Local $hGetTableDll = DllOpen( @AutoItX64 ? "GetTable_x64.dll" : "GetTable.dll" )
DllCall( $hGetTableDll, "int:cdecl", "GetTable", "int", $iRows * $iColumns, "ptr", $pResult, "ptr", $pSafeArrayData, "int", 256 )
ConsoleWrite( "Time (loop)   = " & TimerDiff( $hTimer3 ) & @CRLF )
DllClose( $hGetTableDll )

The Dll-files (8 and 9 KB) are included in the zip-file.

And this is the C++ code:

#define WIN32_LEAN_AND_MEAN

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

extern "C" __declspec(dllexport) int __cdecl GetTable( int iElements, char **pResult, BSTR *pSafeArrayData, int iMaxChars )
{
  int  iCharSize;
  BSTR sBuffer;

  for ( int i = 0; i < iElements; i++ ) {
    iCharSize = MultiByteToWideChar( CP_UTF8, 0, pResult[i], -1, NULL, 0 );
    if ( iCharSize > iMaxChars ) { iCharSize = iMaxChars; }
    sBuffer = SysAllocStringLen( NULL, iCharSize );
    MultiByteToWideChar( CP_UTF8, 0, pResult[i], -1, sBuffer, iCharSize );
    pSafeArrayData[i] = sBuffer;
  }

  return 0;
}

It's not much code but it makes a big difference. At this point we can really see some performance improvements:

32 bit                          64 bit
       ------                          ------
Db   = 10000.db                        10000.db
Time (SQLite) = 17.879859753638        13.0433770827516
Time (loop)   = 14.2736909526912       10.4447857519625
Time (total)  = 91.6964756674335       65.9479816314499

Db   = 50000.db                        50000.db
Time (SQLite) = 84.786660638378        79.0254340589442
Time (loop)   = 63.1917572018103       45.9053071102778
Time (total)  = 400.493344503398       331.284103048275

Db   = 100000.db                       100000.db
Time (SQLite) = 179.167608586765       134.67712087162
Time (loop)   = 133.477003851899       91.1557027167192
Time (total)  = 812.997001369663       628.453799373235

Db   = 500000.db                       500000.db
Time (SQLite) = 877.786144244536       677.600973834568
Time (loop)   = 593.045083620342       441.694591827236
Time (total)  = 3930.40734165769       3169.40903933015

Db   = 1000000.db                      1000000.db
Time (SQLite) = 1709.34257301543       1340.73765641414
Time (loop)   = 1177.70597022203       887.914832692828
Time (total)  = 7898.99402933472       6350.2593604695

Example6.au3: Safearray only
SafeArrayDisplay was introduced in the previous post. The old version was only able to handle integers ($VT_I4). The new version can also handle strings ($VT_BSTR). We can replace _ArrayDisplayEx with SafeArrayDisplay, and we don't need to convert the safearray to a native AutoIt array. This halves the total execution time:

32 bit                          64 bit
       ------                          ------
Db   = 10000.db                        10000.db
Time (SQLite) = 17.3684525471736       20.9012069874957
Time (loop)   = 14.0346094688457       19.0544895236322
Time (total)  = 36.3534062047047       44.0450149155818

Db   = 50000.db                        50000.db
Time (SQLite) = 104.392450455003       79.7554221353439
Time (loop)   = 62.9584934597501       47.4223525396382
Time (total)  = 191.106722871593       146.179073416577

Db   = 100000.db                       100000.db
Time (SQLite) = 203.835110791966       158.260029786838
Time (loop)   = 120.158807728621       93.6750619450983
Time (total)  = 370.362151655785       289.398301441027

Db   = 500000.db                       500000.db
Time (SQLite) = 895.89982181088        728.70761913627
Time (loop)   = 587.891118469169       441.994344046843
Time (total)  = 1716.73969317784       1362.86419063576

Db   = 1000000.db                      1000000.db
Time (SQLite) = 1780.12814545906       1427.71566645834
Time (loop)   = 1186.23090118482       878.533031475645
Time (total)  = 3438.9228534195        2694.91928298826

Note that the UDF (AccessingVariables.au3) is not included in Example6. The UDF is used for conversions between AutoIt arrays and safearrays. Since there are no conversions the UDF is not used.

Edited March 18, 2017 by LarsJ

[LarsJ]

What's next
This was pretty much what I had planned for this example. I'll check my notes to see if there's anything I've forgotten.

The next step is of course to utilize the UDF (AccessingVariables.au3) to create some fast array manipulation functions and to be able to deal with arrays with more elements.

It's natural to try to optimize some of the functions in Array.au3. But it'll not be sensibly simply to optimize the existing code. Eg. to sort an array with many elements it would not be appropriate to sort the entire array. It would be much better to use some kind of index based sorting procedure.

It's not my plan to create millions of lines of C++ or assembler kode. I'm particularly interested in functions where the AutoIt code contains a single central loop that is crucial for performance. Then, only the code in this loop has to be translated to compiled code. This is very well illustrated in the example above.

Currently I'm only at a stage where I've made a few notes. So it'll take some time.

It'll be a new project and I'll create a new example.

[RTFC]

@LarsJ: outstanding job; very impressive!

[ptrex]

Hi Larsj,

These examplesites show what is possiblexpected with au3.  And more specifically which qualities you have to contribute to the community 

Thanks a lot for the nice posts.

ptrex

[LarsJ]

RTFC and ptrex, Thank you very much.

And also thanks for all the other feedback.

Fortunately, I wrote no specific date. It should be possible to produce some code within "some time".

[Iczer]

I was interested in function SQLite_GetTableEx() from Example2.au3: sqlite3_get_table, but it seems it wont return unicode text as _SQLite_GetTable2d() do.

Is there a way to ?

 

[LarsJ]

I'll look into the problem.

[Earthshine]

the download link for the AccessVariables zip is maybe broken or my stuff is blocking it. Where may I obtain this gem?

 

update, the zip file downloads but a virus is reported in it so it deletes it. :-(

could you re-post it? I think I need it to do what I need to be able to do. Thanks!

Edited July 2, 2018 by Earthshine

[junkew]

Look also in this thread

 

[Earthshine]

thanks so much Gentlemen!!

[Danp2]

@Earthshine I think your AV software is acting up or reporting a false postive.

[Earthshine]

well, it's just win10, and on 4 different machines. 

[Earthshine]

thanks, i used a win7 vm and then scanned it in win10, no problems, but on all my vms and boxes, same issue. smartscreen thinks it's a trojan... lol

Edited July 2, 2018 by Earthshine

[LarsJ]

The zip-file contains two small dll-files used in this post above (C++ code near bottom of the post, 10 lines). I'm using Avira and it doesn't detect any viruses. You can delete the dll-files and recreate them with Visual Studio. C++ code in the zip-file.