Fast Array Management Functions UDF

LarsJ · March 25, 2018

With the functions in Accessing AutoIt Arrays it's possible to create some fast array management functions where all the hard work is done by compiled code or by accessing array memory directly.

This is the main UDF for a number of sub-UDFs. The sub-UDFs divided by topics (general functions, sorting functions, etc.) implements the actual array functions and will be published as new examples. The main UDF contains functions to access arrays, functions and utilities common to several sub-UDFs, and functions to display arrays. The display functions are copied from Data display functions based on virtual listviews.

Large arrays
Arrays of interest are large arrays with 100,000 - 1,000,000 elements. The numbers are very round numbers. Depending on the specific array and situation, it may be 10,000 or 200,000 instead of 100,000 elements. And it may be 500,000 or 10,000,000 instead of 1,000,000 elements.

Data types
Array data types are primarily simple data types such as integers, floats and strings. Array elements containing arrays (arrays of arrays), functions, objects, structures (DllStructs) or maps (in the beta version) are not supported.

The techniques to access AutoIt arrays from compiled code and to access array memory directly are both based on passing arrays as parameters to methods of COM objects. The arrays are passed by value. What happens if a special data type eg. a DllStruct contained in an array element is passed to compiled VB code? Is it still the same DllStruct when the array gets back to AutoIt? Such issues have not been taken into account in this first version.

As far as arrays of arrays are concerned and the embedded arrays also contains simple data types there are no problems. This allows for new versions of some of the functions with support for embedded arrays. Maybe also support for other data types.

Structure
The project is structured this way:

FAMproj\ - Top folder
- Display\ - Data display functions
- FAMudf\ - Fast Array Management Functions UDF
- FASudf\ - Fast Array Sorting and Management Functions UDF
- FGAudf\ - Fast General Array Management Functions UDF
- FXXudf\ - More fast array management functions UDFs

The name of the top folder, FAMproj, is only used in 7z-files. It's not used in any UDFs or scripts. You can rename FAMproj as you want. But the other folders cannot be renamed. These names are used in UDFs and scripts.

Main UDF
The main UDF, FAMudf, and FAMudf.7z below is organized this way:

FAMproj\ - Top folder
- Display\ - Data display functions
- FAMudf\ - Fast Array Management Functions UDF
  - AccArrays\ - Access AutoIt arrays from compiled code
  - Utilities\ - Contains utility functions common to all UDFs
  - Readme.txt - Folder list (this list) and usage instructions

Sub-UDFs
Sub-UDFs, FXXudf's, and corresponding FXXudf.7z files are organized this way:

FAMproj\ - Top folder
- FXXudf\ - Fast Array Management Functions sub-UDF
  - DLLFiles\ - C++ DLL-files and VB.NET assembly DLL-files
  - Examples\ - Example scripts to demonstrate the functions
  - HelpFiles\ - Detailed information about some of the functions
    - Functions.txt - Function list and headers (the main help file)
    - Use_of_UDF.txt - Explains how to use the UDF in your own project
  - Includes\ - Files that you include in your own AutoIt scripts
  - Resources\ - Resources used in examples and runtime measuring
  - Runtimes\ - Compare runtimes for AutoIt code and optimized code
  - Sources\ - Contains C++ source files and VB.NET source files
  - Utilities\ - Utility functions to generate VB.NET DLL-files
  - Readme.txt - Folder list (this list) and usage instructions

Not all sub-folders are used in all sub-UDFs.

Note that the top folder FAMproj\ is the same folder for all UDFs. If you unzip the 7z-files in <My folder>, FAMproj will be an immediate subfolder: <My folder>\FAMproj. The structure of FAMproj\ should look as described in the Structure-section above. The name FAMproj is only used in 7z-files. You can rename it as you want.

C++ code is delivered both as source files and as 32 and 64 bit dll-files. VB code is only delivered as source files which can be compiled and loaded on the fly. If you want to compile VB code into .NET assembly dll-files you have to do it yourself with the functions (usually only one) in Utilities\. All dll-files are stored in DLLFiles\.

So far, the following sub-UDFs are available:

Information
This thread will be used for general information for the entire project. Along the way more posts will be added below.

FAMudf.7z
FAMudf is delivered in FAMudf.7z and is the main UDF that's used by all the sub-UDFs. It contains functions to access arrays from compiled code and to access array memory directly. It also contains the display functions. FAMudf is a set of helper functions, but it does not contain any array management functions.

The sub-UDFs contains the array management functions. To use the array management functions you have to download one or more of the sub-UDFs.

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.

FAMudf.7z

Edited April 27, 2018 by LarsJ
SQLiteDisplay added to FAMudf.7z

Trong · March 27, 2018

WOW, Nice work thanks

LarsJ · April 2, 2018

Thank you, Mr. VIP. And thanks for the other feedback.

Improving performance
In relation to code optimization, it's interesting to know how much time is spent on the various steps that need to be performed to execute the code.

How long does it take to load compiled code? How long does it take to pass arrays back and forth between AutoIt code and compiled code? And how long does it take to execute the actual compiled code?

It's also interesting to know how much memory is used. How much memory is used to load the code? How much memory is used while executing the code?

FATfld.7z below contains a folder named FATfld to test such things (when this folder was created I think FATfld was an abbreviation for "Fast Array Test folder" or something like that). It's placed just below FAMproj. If you've downloaded and unzipped the previous 7z-files the FAMproj folder should look like this:

FAMproj\
- Display\
- FAMudf\
- FASudf\
- FATfld\
- FGAudf\

The code in FATfld\ is depending on the code in all other folders. But the other folders are not depending on FATfld\. You can install/delete FATfld\ at any time.

Most of the questions above are tested in Runtimes\. I don't want to review all tests. You can do it yourself if you are interested. I'll focus on a few of the tests.

Passing arrays
Includes\Method.au3 and Includes\VBcode.au3 contains code that's used to measure the time it takes to pass arrays back and forth between AutoIt code / method code and AutoIt code / VB code.

Includes\Method.au3:

#include-once
#include "..\..\FAMudf\AccArrays\AccArrays.au3"

Global $hTimerMethod, $fPassedToMethod, $fReturnedFromMethod

Func PassToFromMethod( $aArray )
  $hTimerMethod = TimerInit()
  AccArrays01( PassToFromMethodMtd, $aArray )
  $fReturnedFromMethod = TimerDiff( $hTimerMethod )
EndFunc

Func PassToFromMethodMtd( $pvArray )
  $fPassedToMethod = TimerDiff( $hTimerMethod )
  #forceref $pvArray
EndFunc

Func RunMethod( $aArray )
  AccArrays01( RunMethodMtd, $aArray )
EndFunc

Func RunMethodMtd( $pvArray )
  MsgBox( 0, "Open Task Manager", "Check memory usage" )
  #forceref $pvArray
EndFunc

Includes\VBcode.au3:

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

Func FAT_FromAutoItToVBcode( $aArray )
  If Not FAT_PassArrayInitVbSrc( "IsFAT_PassArrayInitVbSrc" ) Then Return SetError(4,0,0)

  Local $oPassArrayClass = FAT_PassArrayInit()
  If Not IsObj( $oPassArrayClass ) Then Return SetError(5,0,0)

  $oPassArrayClass.FromAutoIt( $aArray )
EndFunc

Func FAT_FromVBcodeToAutoIt( ByRef $aArray )
  If Not FAT_PassArrayInitVbSrc( "IsFAT_PassArrayInitVbSrc" ) Then Return SetError(4,0,0)

  Local $oPassArrayClass = FAT_PassArrayInit()
  If Not IsObj( $oPassArrayClass ) Then Return SetError(5,0,0)

  $aArray = $oPassArrayClass.ToAutoIt()
EndFunc

Func FAT_RunVBcode( $aArray )
  If Not FAT_PassArrayInitVbSrc( "IsFAT_PassArrayInitVbSrc" ) Then Return SetError(4,0,0)

  Local $oPassArrayClass = FAT_PassArrayInit()
  If Not IsObj( $oPassArrayClass ) Then Return SetError(5,0,0)

  $oPassArrayClass.RunVBcode( $aArray )
EndFunc

Sources\PassArray.vb:

Imports System.Windows.Forms

Class PassArrayClass
  'aObjects ~ aArray
  Dim aObjects As Object(,)

  Public Sub FromAutoIt( aObjectsIn As Object(,) )
    aObjects = aObjectsIn
  End Sub

  Public Function ToAutoIt() As Object(,)
    Return aObjects
  End Function

  Public Sub RunVBcode( aObjectsIn As Object(,) )
    MessageBox.Show( "Check memory usage", "Open Task Manager" )
  End Sub
End Class

Tests for a varying number of rows is performed with "Runtimes\Pass arrays\Pass arrays.au3". These are the results:

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

50,000 rows, 6 columns, 2 str cols           50,000 rows, 6 columns, 2 str cols           
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:           117.7597    From AutoIt to method:            85.9069    
From method to AutoIt:            59.0443    From method to AutoIt:            46.8230    
Total time:                      176.8040    Total time:                      132.7299    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:          164.0283    From AutoIt to VB code:          144.0661    
From VB code to AutoIt:           57.1975    From VB code to AutoIt:           44.9665    
Total time:                      221.2258    Total time:                      189.0325    

50,000 rows, 6 columns, 0 str cols           50,000 rows, 6 columns, 0 str cols           
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:            47.3782    From AutoIt to method:            40.2639    
From method to AutoIt:            22.7405    From method to AutoIt:            22.2371    
Total time:                       70.1186    Total time:                       62.5010    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:           77.6208    From AutoIt to VB code:           61.7502    
From VB code to AutoIt:           34.7429    From VB code to AutoIt:           29.7381    
Total time:                      112.3637    Total time:                       91.4883    

100,000 rows, 6 columns, 2 str cols          100,000 rows, 6 columns, 2 str cols          
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:           236.3509    From AutoIt to method:           173.5922    
From method to AutoIt:           119.5647    From method to AutoIt:            92.3186    
Total time:                      355.9155    Total time:                      265.9108    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:          332.0638    From AutoIt to VB code:          246.2865    
From VB code to AutoIt:          114.8042    From VB code to AutoIt:           92.1593    
Total time:                      446.8680    Total time:                      338.4458    

100,000 rows, 6 columns, 0 str cols          100,000 rows, 6 columns, 0 str cols          
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:            95.8640    From AutoIt to method:            71.1650    
From method to AutoIt:            46.2088    From method to AutoIt:            41.2588    
Total time:                      142.0727    Total time:                      112.4238    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:          174.9777    From AutoIt to VB code:          214.5529    
From VB code to AutoIt:           63.5418    From VB code to AutoIt:           53.0166    
Total time:                      238.5196    Total time:                      267.5695    

250,000 rows, 6 columns, 2 str cols          250,000 rows, 6 columns, 2 str cols          
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:           600.8753    From AutoIt to method:           438.1211    
From method to AutoIt:           300.9965    From method to AutoIt:           237.2756    
Total time:                      901.8717    Total time:                      675.3968    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:          860.9385    From AutoIt to VB code:          943.8392    
From VB code to AutoIt:          290.0850    From VB code to AutoIt:          234.5046    
Total time:                     1151.0235    Total time:                     1178.3438    

250,000 rows, 6 columns, 0 str cols          250,000 rows, 6 columns, 0 str cols          
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:           240.3400    From AutoIt to method:           178.3870    
From method to AutoIt:           114.4742    From method to AutoIt:           103.9808    
Total time:                      354.8143    Total time:                      282.3679    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:          478.6563    From AutoIt to VB code:          368.4721    
From VB code to AutoIt:          161.0129    From VB code to AutoIt:          134.6160    
Total time:                      639.6692    Total time:                      503.0881    

500,000 rows, 6 columns, 2 str cols          500,000 rows, 6 columns, 2 str cols          
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:          1213.4915    From AutoIt to method:           872.8612    
From method to AutoIt:           602.7456    From method to AutoIt:           483.1377    
Total time:                     1816.2372    Total time:                     1355.9989    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:         1829.4514    From AutoIt to VB code:         1324.2018    
From VB code to AutoIt:          583.9586    From VB code to AutoIt:          462.1512    
Total time:                     2413.4100    Total time:                     1786.3531    

500,000 rows, 6 columns, 0 str cols          500,000 rows, 6 columns, 0 str cols          
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:           480.8723    From AutoIt to method:           360.1075    
From method to AutoIt:           231.7580    From method to AutoIt:           206.3836    
Total time:                      712.6303    Total time:                      566.4911    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:          937.9331    From AutoIt to VB code:          769.3023    
From VB code to AutoIt:          323.9392    From VB code to AutoIt:          270.0216    
Total time:                     1261.8723    Total time:                     1039.3239    

750,000 rows, 6 columns, 2 str cols          750,000 rows, 6 columns, 2 str cols          
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:          1814.8179    From AutoIt to method:          1329.9400    
From method to AutoIt:           910.3338    From method to AutoIt:           736.7954    
Total time:                     2725.1517    Total time:                     2066.7354    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:         2819.8451    From AutoIt to VB code:         2192.5480    
From VB code to AutoIt:          878.5583    From VB code to AutoIt:          711.1307    
Total time:                     3698.4035    Total time:                     2903.6786    

750,000 rows, 6 columns, 0 str cols          750,000 rows, 6 columns, 0 str cols          
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:           725.8141    From AutoIt to method:           538.6317    
From method to AutoIt:           346.2186    From method to AutoIt:           312.2894    
Total time:                     1072.0327    Total time:                      850.9211    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:         1431.7707    From AutoIt to VB code:          979.0254    
From VB code to AutoIt:          481.3245    From VB code to AutoIt:          413.5206    
Total time:                     1913.0952    Total time:                     1392.5460    

1,000,000 rows, 6 columns, 2 str cols        1,000,000 rows, 6 columns, 2 str cols        
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:          2398.6548    From AutoIt to method:          1780.2107    
From method to AutoIt:          1214.6058    From method to AutoIt:           984.2918    
Total time:                     3613.2606    Total time:                     2764.5024    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:         3790.6409    From AutoIt to VB code:         3001.6664    
From VB code to AutoIt:         1167.6269    From VB code to AutoIt:          939.0294    
Total time:                     4958.2678    Total time:                     3940.6958    

1,000,000 rows, 6 columns, 0 str cols        1,000,000 rows, 6 columns, 0 str cols        
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:           958.5246    From AutoIt to method:           720.1056    
From method to AutoIt:           457.4911    From method to AutoIt:           416.1151    
Total time:                     1416.0156    Total time:                     1136.2207    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:         1916.9292    From AutoIt to VB code:         2074.5791    
From VB code to AutoIt:          644.4524    From VB code to AutoIt:          549.9856    
Total time:                     2561.3816    Total time:                     2624.5647    

2,000,000 rows, 6 columns, 2 str cols        2,000,000 rows, 6 columns, 2 str cols        
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:          4901.1622    From AutoIt to method:          3645.8892    
From method to AutoIt:          2470.5576    From method to AutoIt:          1972.5093    
Total time:                     7371.7199    Total time:                     5618.3984    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:         8066.7966    From AutoIt to VB code:         6371.5138    
From VB code to AutoIt:         2347.8576    From VB code to AutoIt:         1886.6691    
Total time:                    10414.6542    Total time:                     8258.1829    

2,000,000 rows, 6 columns, 0 str cols        2,000,000 rows, 6 columns, 0 str cols        
Pass array to/from method/VB code            Pass array to/from method/VB code            
-----------------------------------------    -----------------------------------------    
From AutoIt to method:          1940.5748    From AutoIt to method:          1443.4808    
From method to AutoIt:           924.8848    From method to AutoIt:           836.6967    
Total time:                     2865.4596    Total time:                     2280.1776    
-----------------------------------------    -----------------------------------------    
From AutoIt to VB code:         4407.3205    From AutoIt to VB code:         4503.7527    
From VB code to AutoIt:         1294.5233    From VB code to AutoIt:         1111.8894    
Total time:                     5701.8438    Total time:                     5615.6421

Most of the time is spent on internal AutoIt COM conversions discussed in details in Accessing AutoIt Variables. For the VB code there is also spent time on converting AutoIt variants to VB objects and vice versa (default marshaling).

From the results you can see that there is a slow and a fast direction for passing arrays. Passing arrays from AutoIt to method/VB code is the slow direction. Passing arrays from method/VB code to AutoIt is the fast direction. The fast direction is about twice as fast as the slow direction.

Because of variant/object conversions it takes much longer time to pass arrays to/from VB code than to/from method code (AutoIt method code).

It takes much longer time to pass arrays of strings than arrays of numbers.

For a large number of rows in can take really long time to pass arrays.

Runtime measurements
In Fast General Array Management Functions UDF the performance for inserting a single row in arrays with/without strings is testet for a varying number of rows. The measured time includes passing arrays. The results are not impressive. The compiled code is at best 2/4 times faster than pure AutoIt code for arrays with/without strings.

A new test is performed here that does not include the time it takes to pass arrays. Now the results looks much better (previous results to the left, new results to the right).

Runtimes\FAT_RowsInsert.au3:

Time for passing arrays to/from method included                       Time for passing arrays to/from method NOT included
===============================================                       ===================================================

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

100 rows, 6 columns                100 rows, 6 columns                100 rows, 6 columns                100 rows, 6 columns                
Insert one row at index 25         Insert one row at index 25         Insert one row at index 25         Insert one row at index 25         
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:            0.7159    _ArrayInsert:            0.6087    _ArrayInsert:            0.7186    _ArrayInsert:            0.6222    
FGA_RowsInsert:          2.6422    FGA_RowsInsert:          3.0201    FAT_RowsInsert:          0.8716    FAT_RowsInsert:          0.7009    

500 rows, 6 columns                500 rows, 6 columns                500 rows, 6 columns                500 rows, 6 columns                
Insert one row at index 125        Insert one row at index 125        Insert one row at index 125        Insert one row at index 125        
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:            6.7059    _ArrayInsert:           13.4817    _ArrayInsert:           14.8413    _ArrayInsert:            6.5049    
FGA_RowsInsert:          3.5246    FGA_RowsInsert:          6.3012    FAT_RowsInsert:          1.0256    FAT_RowsInsert:          0.3635    

1,000 rows, 6 columns              1,000 rows, 6 columns              1,000 rows, 6 columns              1,000 rows, 6 columns              
Insert one row at index 250        Insert one row at index 250        Insert one row at index 250        Insert one row at index 250        
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:           11.9255    _ArrayInsert:           10.2926    _ArrayInsert:           12.8272    _ArrayInsert:           10.8095    
FGA_RowsInsert:          3.6268    FGA_RowsInsert:          5.0115    FAT_RowsInsert:          0.6145    FAT_RowsInsert:          0.7765    

2,000 rows, 6 columns              2,000 rows, 6 columns              2,000 rows, 6 columns              2,000 rows, 6 columns              
Insert one row at index 500        Insert one row at index 500        Insert one row at index 500        Insert one row at index 500        
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:           14.6414    _ArrayInsert:           10.9816    _ArrayInsert:           12.9538    _ArrayInsert:           13.9317    
FGA_RowsInsert:          8.5630    FGA_RowsInsert:          5.3611    FAT_RowsInsert:          0.4499    FAT_RowsInsert:          0.3247    

5,000 rows, 6 columns              5,000 rows, 6 columns              5,000 rows, 6 columns              5,000 rows, 6 columns              
Insert one row at index 1,250      Insert one row at index 1,250      Insert one row at index 1,250      Insert one row at index 1,250      
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:           32.8972    _ArrayInsert:           28.7703    _ArrayInsert:           35.2970    _ArrayInsert:           27.7061    
FGA_RowsInsert:         18.5431    FGA_RowsInsert:         14.3076    FAT_RowsInsert:          1.0231    FAT_RowsInsert:          0.5571    

10,000 rows, 6 columns             10,000 rows, 6 columns             10,000 rows, 6 columns             10,000 rows, 6 columns             
Insert one row at index 2,500      Insert one row at index 2,500      Insert one row at index 2,500      Insert one row at index 2,500      
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:           65.9608    _ArrayInsert:           56.4863    _ArrayInsert:           66.7517    _ArrayInsert:           59.8562    
FGA_RowsInsert:         37.4105    FGA_RowsInsert:         29.5826    FAT_RowsInsert:          1.5897    FAT_RowsInsert:          1.0431    

20,000 rows, 6 columns             20,000 rows, 6 columns             20,000 rows, 6 columns             20,000 rows, 6 columns             
Insert one row at index 5,000      Insert one row at index 5,000      Insert one row at index 5,000      Insert one row at index 5,000      
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:          134.4746    _ArrayInsert:          115.3622    _ArrayInsert:          132.1033    _ArrayInsert:          110.1108    
FGA_RowsInsert:         74.8673    FGA_RowsInsert:         55.7314    FAT_RowsInsert:          2.9804    FAT_RowsInsert:          1.8265    

50,000 rows, 6 columns             50,000 rows, 6 columns             50,000 rows, 6 columns             50,000 rows, 6 columns             
Insert one row at index 12,500     Insert one row at index 12,500     Insert one row at index 12,500     Insert one row at index 12,500     
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:          328.4011    _ArrayInsert:          283.3676    _ArrayInsert:          328.3556    _ArrayInsert:          287.0136    
FGA_RowsInsert:        187.4815    FGA_RowsInsert:        138.7198    FAT_RowsInsert:          7.2069    FAT_RowsInsert:          4.3575    

100,000 rows, 6 columns            100,000 rows, 6 columns            100,000 rows, 6 columns            100,000 rows, 6 columns            
Insert one row at index 25,000     Insert one row at index 25,000     Insert one row at index 25,000     Insert one row at index 25,000     
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:          636.1870    _ArrayInsert:          560.0946    _ArrayInsert:          647.2785    _ArrayInsert:          539.8483    
FGA_RowsInsert:        374.8816    FGA_RowsInsert:        278.7284    FAT_RowsInsert:         14.2110    FAT_RowsInsert:          8.4874    

250,000 rows, 6 columns            250,000 rows, 6 columns            250,000 rows, 6 columns            250,000 rows, 6 columns            
Insert one row at index 62,500     Insert one row at index 62,500     Insert one row at index 62,500     Insert one row at index 62,500     
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:         1619.4215    _ArrayInsert:         1389.8712    _ArrayInsert:         1638.6130    _ArrayInsert:         1390.4849    
FGA_RowsInsert:        926.4933    FGA_RowsInsert:        693.0172    FAT_RowsInsert:         35.0150    FAT_RowsInsert:         20.7480    

500,000 rows, 6 columns            500,000 rows, 6 columns            500,000 rows, 6 columns            500,000 rows, 6 columns            
Insert one row at index 125,000    Insert one row at index 125,000    Insert one row at index 125,000    Insert one row at index 125,000    
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:         3218.2437    _ArrayInsert:         2823.3835    _ArrayInsert:         3306.2819    _ArrayInsert:         2746.6192    
FGA_RowsInsert:       1857.2046    FGA_RowsInsert:       1411.2169    FAT_RowsInsert:         70.0829    FAT_RowsInsert:         40.7386    

750,000 rows, 6 columns            750,000 rows, 6 columns            750,000 rows, 6 columns            750,000 rows, 6 columns            
Insert one row at index 187,500    Insert one row at index 187,500    Insert one row at index 187,500    Insert one row at index 187,500    
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:         4816.4340    _ArrayInsert:         4111.5426    _ArrayInsert:         4967.1980    _ArrayInsert:         4151.5911    
FGA_RowsInsert:       2803.2133    FGA_RowsInsert:       2070.2246    FAT_RowsInsert:        105.4979    FAT_RowsInsert:         61.0131    

1,000,000 rows, 6 columns          1,000,000 rows, 6 columns          1,000,000 rows, 6 columns          1,000,000 rows, 6 columns          
Insert one row at index 250,000    Insert one row at index 250,000    Insert one row at index 250,000    Insert one row at index 250,000    
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:         6483.6767    _ArrayInsert:         5592.3917    _ArrayInsert:         6513.4886    _ArrayInsert:         5427.5685    
FGA_RowsInsert:       3754.6153    FGA_RowsInsert:       2764.8844    FAT_RowsInsert:        139.1285    FAT_RowsInsert:         81.1930    

2,000,000 rows, 6 columns          2,000,000 rows, 6 columns          2,000,000 rows, 6 columns          2,000,000 rows, 6 columns          
Insert one row at index 500,000    Insert one row at index 500,000    Insert one row at index 500,000    Insert one row at index 500,000    
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:        12841.9769    _ArrayInsert:        11450.4454    _ArrayInsert:        13193.6124    _ArrayInsert:        10787.2675    
FGA_RowsInsert:       7481.8667    FGA_RowsInsert:       5691.6660    FAT_RowsInsert:        279.8582    FAT_RowsInsert:        164.2681

Runtimes\FAT_RowsInsert-Numbers.au3:

Time for passing arrays to/from method included                       Time for passing arrays to/from method NOT included
===============================================                       ===================================================

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

100 rows, 6 columns                100 rows, 6 columns                100 rows, 6 columns                100 rows, 6 columns                
Insert one row at index 25         Insert one row at index 25         Insert one row at index 25         Insert one row at index 25         
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:            1.2827    _ArrayInsert:            2.7468    _ArrayInsert:            0.8009    _ArrayInsert:            0.6125    
FGA_RowsInsert:          1.5758    FGA_RowsInsert:          2.3249    FAT_RowsInsert:          0.7200    FAT_RowsInsert:          1.0547    

500 rows, 6 columns                500 rows, 6 columns                500 rows, 6 columns                500 rows, 6 columns                
Insert one row at index 125        Insert one row at index 125        Insert one row at index 125        Insert one row at index 125        
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:           15.0842    _ArrayInsert:           14.2354    _ArrayInsert:           15.4898    _ArrayInsert:           15.3618    
FGA_RowsInsert:          3.9896    FGA_RowsInsert:          3.2785    FAT_RowsInsert:          0.7968    FAT_RowsInsert:          0.6940    

1,000 rows, 6 columns              1,000 rows, 6 columns              1,000 rows, 6 columns              1,000 rows, 6 columns              
Insert one row at index 250        Insert one row at index 250        Insert one row at index 250        Insert one row at index 250        
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:           14.3390    _ArrayInsert:           13.2319    _ArrayInsert:           12.7557    _ArrayInsert:           11.9728    
FGA_RowsInsert:          2.7953    FGA_RowsInsert:          2.1401    FAT_RowsInsert:          0.4056    FAT_RowsInsert:          0.3028    

2,000 rows, 6 columns              2,000 rows, 6 columns              2,000 rows, 6 columns              2,000 rows, 6 columns              
Insert one row at index 500        Insert one row at index 500        Insert one row at index 500        Insert one row at index 500        
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:           13.5841    _ArrayInsert:           14.7590    _ArrayInsert:           14.5545    _ArrayInsert:           16.2899    
FGA_RowsInsert:          3.1203    FGA_RowsInsert:          2.2363    FAT_RowsInsert:          0.2507    FAT_RowsInsert:          0.2028    

5,000 rows, 6 columns              5,000 rows, 6 columns              5,000 rows, 6 columns              5,000 rows, 6 columns              
Insert one row at index 1,250      Insert one row at index 1,250      Insert one row at index 1,250      Insert one row at index 1,250      
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:           30.6353    _ArrayInsert:           26.0359    _ArrayInsert:           30.8904    _ArrayInsert:           26.1819    
FGA_RowsInsert:          7.4227    FGA_RowsInsert:          5.7619    FAT_RowsInsert:          0.3818    FAT_RowsInsert:          0.2654    

10,000 rows, 6 columns             10,000 rows, 6 columns             10,000 rows, 6 columns             10,000 rows, 6 columns             
Insert one row at index 2,500      Insert one row at index 2,500      Insert one row at index 2,500      Insert one row at index 2,500      
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:           62.1590    _ArrayInsert:           56.1475    _ArrayInsert:           61.9371    _ArrayInsert:           53.3951    
FGA_RowsInsert:         15.6602    FGA_RowsInsert:         13.0668    FAT_RowsInsert:          0.6300    FAT_RowsInsert:          0.4133    

20,000 rows, 6 columns             20,000 rows, 6 columns             20,000 rows, 6 columns             20,000 rows, 6 columns             
Insert one row at index 5,000      Insert one row at index 5,000      Insert one row at index 5,000      Insert one row at index 5,000      
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:          123.7628    _ArrayInsert:          110.9456    _ArrayInsert:          124.0359    _ArrayInsert:          107.2285    
FGA_RowsInsert:         31.2173    FGA_RowsInsert:         23.0951    FAT_RowsInsert:          1.0780    FAT_RowsInsert:          0.6582    

50,000 rows, 6 columns             50,000 rows, 6 columns             50,000 rows, 6 columns             50,000 rows, 6 columns             
Insert one row at index 12,500     Insert one row at index 12,500     Insert one row at index 12,500     Insert one row at index 12,500     
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:          311.6687    _ArrayInsert:          268.6157    _ArrayInsert:          313.8828    _ArrayInsert:          269.0698    
FGA_RowsInsert:         74.9177    FGA_RowsInsert:         67.6460    FAT_RowsInsert:          2.3352    FAT_RowsInsert:          1.3846    

100,000 rows, 6 columns            100,000 rows, 6 columns            100,000 rows, 6 columns            100,000 rows, 6 columns            
Insert one row at index 25,000     Insert one row at index 25,000     Insert one row at index 25,000     Insert one row at index 25,000     
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:          608.1551    _ArrayInsert:          517.9057    _ArrayInsert:          613.7654    _ArrayInsert:          527.6046    
FGA_RowsInsert:        150.9420    FGA_RowsInsert:        120.8896    FAT_RowsInsert:          4.5030    FAT_RowsInsert:          2.5776    

250,000 rows, 6 columns            250,000 rows, 6 columns            250,000 rows, 6 columns            250,000 rows, 6 columns            
Insert one row at index 62,500     Insert one row at index 62,500     Insert one row at index 62,500     Insert one row at index 62,500     
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:         1515.0866    _ArrayInsert:         1318.7368    _ArrayInsert:         1521.3272    _ArrayInsert:         1293.1201    
FGA_RowsInsert:        380.4741    FGA_RowsInsert:        306.2980    FAT_RowsInsert:         10.9104    FAT_RowsInsert:          6.0059    

500,000 rows, 6 columns            500,000 rows, 6 columns            500,000 rows, 6 columns            500,000 rows, 6 columns            
Insert one row at index 125,000    Insert one row at index 125,000    Insert one row at index 125,000    Insert one row at index 125,000    
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:         3055.9588    _ArrayInsert:         2592.6736    _ArrayInsert:         3053.3713    _ArrayInsert:         2663.5527    
FGA_RowsInsert:        739.0425    FGA_RowsInsert:        603.7840    FAT_RowsInsert:         21.6550    FAT_RowsInsert:         11.9886    

750,000 rows, 6 columns            750,000 rows, 6 columns            750,000 rows, 6 columns            750,000 rows, 6 columns            
Insert one row at index 187,500    Insert one row at index 187,500    Insert one row at index 187,500    Insert one row at index 187,500    
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:         4579.8788    _ArrayInsert:         3906.9467    _ArrayInsert:         4594.4156    _ArrayInsert:         3866.0774    
FGA_RowsInsert:       1138.3392    FGA_RowsInsert:        912.8767    FAT_RowsInsert:         32.3634    FAT_RowsInsert:         17.6097    

1,000,000 rows, 6 columns          1,000,000 rows, 6 columns          1,000,000 rows, 6 columns          1,000,000 rows, 6 columns          
Insert one row at index 250,000    Insert one row at index 250,000    Insert one row at index 250,000    Insert one row at index 250,000    
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:         6158.5664    _ArrayInsert:         5282.8649    _ArrayInsert:         6060.0125    _ArrayInsert:         5241.2667    
FGA_RowsInsert:       1550.3611    FGA_RowsInsert:       1206.8620    FAT_RowsInsert:         42.9751    FAT_RowsInsert:         23.5342    

2,000,000 rows, 6 columns          2,000,000 rows, 6 columns          2,000,000 rows, 6 columns          2,000,000 rows, 6 columns          
Insert one row at index 500,000    Insert one row at index 500,000    Insert one row at index 500,000    Insert one row at index 500,000    
-------------------------------    -------------------------------    -------------------------------    -------------------------------    
_ArrayInsert:        12131.6853    _ArrayInsert:        10471.6424    _ArrayInsert:        12181.9698    _ArrayInsert:        10259.6884    
FGA_RowsInsert:       2976.3306    FGA_RowsInsert:       2425.4074    FAT_RowsInsert:         85.6680    FAT_RowsInsert:         47.1634

Especially 64 bit code is fast. Compiled code is 60 - 70 times faster than pure AutoIt code for arrays with strings. At best 200 times faster for arrays without strings.

Cause of poor results
The main reason that it's not possible to get better results when it comes to simple array operations (eg. moving elements around in an array without doing actual calculations) is that the time saved by using compiled code is spent on passing arrays. Particularly passing arrays between AutoIt code and VB code takes a long time.

To get better results it must be avoided to pass arrays back and forth between AutoIt code and compiled code.

What can be done?
Is it possible to avoid the time used to pass arrays, and thereby achieve much better performance? Yes. This is possible by replacing native AutoIt arrays with pure safearrays and do all array operations directly on the safearrays.

Inside the method code (the UI Automation object methods which are replaced with AutoIt methods) the arrays are safearrays anyway. The safearrays are already passed to compiled C++ code by reference as pointers. The step to avoid native AutoIt arrays is a small step.

Of course the arrays can at any time be converted back and forth between AutoIt arrays and safearrays. But these conversions can take a long time. To make the code really fast all array operations must be done on safearrays.

Replacing native AutoIt arrays with pure safearrays isn't possible in .NET and VB code. In new versions of these UDFs all .NET and VB code (all managed code) will be removed and replaced by AutoIt code and C++ code (unmanaged code). AutoIt code is used to handle safearrays. C++ code is used to optimize central loops crucial to performance.

New functions will be implemented to handle safearrays directly.

It's probably also necessary to implement some more safearray functions.

And it's necessary to implement a display function to display data in a virtual listview directly from a safearray source.

End goal
The end goal is to implement functions that can take both an $aArray (native AutoIt array) and a $pArray (pointer to safearray) as parameters. More or less in the same way as the functions in the GUICtrl UDFs can take both a native AutoIt controlID and a Windows control handle as parameters. In first place, it'll probably be 2 groups of functions.

FATfld.7z

Edited April 2, 2018 by LarsJ

LarsJ · April 27, 2018

Fast SQLite Management Functions UDF completes first version.

Next version will be about safearrays to improve performance of simple array manipulations as in the Fast General Array Management Functions UDF.

junkew · April 28, 2018

Why do you use the UIAutomation object to replace the methods? could it have been any com component. Not sure what you accomplish in AccArrays.au3.

Could you do it with

Anyway interesting thread and will read on more in detail on how to deal with this array udf.

LarsJ · April 29, 2018

junkew, Nice to see a comment.

I'm using the UIAutomation object because it was tests with RectToVariant and VariantToRect (especially RectToVariant) methods that gave me the idea for the entire Accessing AutoIt Variables project 1½ years ago. In addition, the object contains enough methods and is available on all PCs from Windows XP SP3 to Windows 10.

I could have used any other object containing enough methods and found on all PCs.

AccArrays.au3 is the UDF that's used to replace the methods in the UIAutomation object with AutoIt methods and to execute the AutoIt methods. It's absolutely central and important code. AccArrays.au3 contains the code that causes the idea of the Accessing AutoIt Variables project to work.

I haven't studied "AutoItObject Pure AutoIt" closer, so I don't know if I can use the code.

For the time being, the project here isn't super valuable. Most of the code is made to get started and to move on. The most useful function is probably _SQLite_Get_TableWEx() in the Fast SQLite Management Functions UDF.

Several of the functions in the Fast General Array Management Functions UDF are required to proceed. In order to avoid constantly converting back and forth between AutoIt arrays and safearrays it's for example necessary to be able to save a safearray in a file and read it again.

It's difficult to handle safarrays if there is no function for displaying safarrays. So SafeArrayDisplay is required. But before I started to code SafeArrayDisplay, I would make sure that ArrayDisplayEx worked correctly and flawlessly. To test ArrayDisplayEx on large arrays, I needed the Fast Array Sorting and Management Functions UDF.

So the project here is depending on a lot of code and that's why it's a quiet start. I've also spent some time finding a file and folder structure that can be used for a larger project.

I do not expect to have the next version with safarrays ready until the fall.

junkew · May 4, 2018

This one could even be more interesting as it seems to create objects fully dynamically (with objCreateinterface)

https://github.com/turbo/OOPEAu3/blob/master/OOPE/OOPE.au3

Edited May 4, 2018 by junkew

Earthshine · July 2, 2018

again, the zip says virus detected.... can you possibly fix the zip download?

LarsJ · July 4, 2018

It's the same problem with two small dll-files. Can't you download the zip-file on another PC without SmartScreen, delete the dll-files and create new dLL-files with Visual Studio? The dll-files are used in at least one of the examples.

Earthshine · July 4, 2018

thanks. will do

Fast Array Management Functions UDF

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members