Jump to content
Sign in to follow this  
gcue

arrayunique time stats

Recommended Posts

gcue

hello all.

i am using arrayunique with a huge array (60k lines - 31 dimensions).. heres some time stats:

==

6k line array: 1 min - success!

27k line array: 14 min and still not done.... had to exit script.

60k line array: 37 min and still not done.... had to exit script.

==

any way to improve this performance?

ConsoleWrite("start " & @hour & ":" & @min & ":" & @SEC & @LF)
$new = _ArrayUnique($avOut, 1, 1)
ConsoleWrite("done unique dimension extract" & @hour & ":" & @min & ":" & @SEC & @LF)
_ArrayDisplay($new)

Share this post


Link to post
Share on other sites
weaponx

I think you are confusing "dimensions" with "columns" or even "elements".

i.e. Dim $array[10000][31]

Not Dim $array[10000][1][1][1][1][1][1][1][1][1][1][1][1][1][1] etc....

It would be helpful if you posted a few rows from your array so I could see the format.

Edited by weaponx

Share this post


Link to post
Share on other sites
gcue

so wait

elements: number of lines or records in the array

dimensions are not the columns in the array?

so in the screenshot i sent you.. $array[9][5] = CISCO 3845

where 9 is the element and 5 is the column

isnt 5 the dimension?

Share this post


Link to post
Share on other sites
weaponx

so wait

elements: number of lines or records in the array

dimensions are not the columns in the array?

so in the screenshot i sent you.. $array[9][5] = CISCO 3845

where 9 is the element and 5 is the column

isnt 5 the dimension?

Example:

$Array[1] <--- 1 Dimensional

$Array[1][1] <--- 2 Dimensional

$Array[1][1][1] <--- 3 Dimensional

$Array[1][1][1][1] <--- 4 Dimensional

$Array[9][5] <--- 9 Rows X 5 Columns...9 Elements in the first dimension, 5 elements in the second dimension

So you just want an array of all the "Active" computers?

Share this post


Link to post
Share on other sites
gcue

nah i want to do a variety of different queries..

ie: get all the "laptops" (col 18) pcs from a given "location" and/or "office" bldg

but the names are a bit strange so i want to prepopulate the drop down with the unique "locations" (col 9)and "office" (col 8) bldgs

Share this post


Link to post
Share on other sites
weaponx

nah i want to do a variety of different queries..

ie: get all the "laptops" (col 18) pcs from a given "location" and/or "office" bldg

but the names are a bit strange so i want to prepopulate the drop down with the unique "locations" (col 9)and "office" (col 8) bldgs

I'm thinking you need to use SQLite instead of one big array. It will be so much nicer and a lot faster.

Share this post


Link to post
Share on other sites
gcue

sqllite? you mean out of autoit right?

hmm im trying to integrate this functionality to an existing autoit script.

Share this post


Link to post
Share on other sites
weaponx

sqllite? you mean out of autoit right?

hmm im trying to integrate this functionality to an existing autoit script.

SQLite functions are shown in the help file under User Defined Functions. All you would need to do is insert your data into a table instead of an array.

Share this post


Link to post
Share on other sites
gcue

i tried the help sample for sqlite_gettable and got an error:

C:\Program Files\AutoIt3\Examples\Helpfile\_SQLite_GetTable.au3(49,1) : ERROR: syntax error

)

^

C:\Program Files\AutoIt3\Examples\Helpfile\_SQLite_GetTable.au3 - 1 error(s), 0 warning(s)

do you have a working sample? of making a sql table with an array and then processing..

sounds cool by the way if im understanding this right. (inject array elements into a sql table, then run sql queries?)

Share this post


Link to post
Share on other sites
weaponx

do you have a working sample? of making a sql table with an array and then processing..

sounds cool by the way if im understanding this right. (inject array elements into a sql table, then run sql queries?)

Can you post in order what each column is?

Share this post


Link to post
Share on other sites
weaponx

This may seem confusing at first. This will take your current array and dump it to a SQL table. Ideally you would just add the data directly to the table from its original source.

#include <SQLite.au3>
#include <SQLite.dll.au3>
#Include <Array.au3>

Dim $hQuery, $aRow, $sMsg
Const $iColumns = 27

Dim $aColumns[$iColumns]
$aColumns[0] = 'AssetTag'
$aColumns[1] = 'Status'
$aColumns[2] = 'AssetType'
$aColumns[3] = 'CatType'
$aColumns[4] = 'Catalog'
$aColumns[5] = 'Model'
$aColumns[6] = 'SerialNumber'
$aColumns[7] = 'Region'
$aColumns[8] = 'Site'
$aColumns[9] = 'Floor'
$aColumns[10] = 'Location'
$aColumns[11] = 'ArchDept'
$aColumns[12] = 'LocCategory'
$aColumns[13] = 'Owner'
$aColumns[14] = 'Company'
$aColumns[15] = 'DeptCode'
$aColumns[16] = 'Department'
$aColumns[17] = 'CostCenter'
$aColumns[18] = 'BusinessFunction'
$aColumns[19] = 'PlaceWithinRoom'
$aColumns[20] = 'AssetRole'
$aColumns[21] = 'CustomInfo1'
$aColumns[22] = 'ReceivedDate'
$aColumns[23] = 'PurchaseOrder'
$aColumns[24] = 'LastScanned'
$aColumns[25] = 'RessourceID'
$aColumns[26] = 'Comment'

;Implode column names into comma separated string
$sColumns = _ArrayToString($aColumns,",")

;2 Dimensional array containing information on all systems
Dim $aData[3][$iColumns]
;Fake system 1
$aData[0][0] = "123456" ;Asset tag
$aData[0][1] = "Active" ;Status
$aData[0][13] = "Bob" ;Owner

;Fake system 2
$aData[1][0] = "56767567" ;Asset tag
$aData[1][1] = "Inactive" ;Status
$aData[1][13] = "Steve" ;Owner

;Fake system 3
$aData[2][0] = "14534778" ;Asset tag
$aData[2][1] = "Active" ;Status
$aData[2][13] = "Frank" ;Owner

_SQLite_Startup ()
;ConsoleWrite("_SQLite_LibVersion=" &_SQLite_LibVersion() & @CR)
_SQLite_Open () ; open :memory: Database
_SQLite_Exec (-1, "CREATE TABLE aTest (" & $sColumns & ");") ; CREATE a Table

;Populate table with array data 
For $Y = 0 to Ubound($aData)-1
    ;Copy one row into one dimensional array
    Dim $aTemp[$iColumns]
    For $X = 0 to $iColumns-1
        $aTemp[$X] = "'" & $aData[$Y][$X] & "'"
    Next
    
    ;Implode array into comma separated string
    $stemp = _ArrayToString($aTemp,",")
    _SQLite_Exec (-1, "INSERT INTO aTest(" & $sColumns & ") VALUES (" & $stemp & ");") ; INSERT Data
Next

_SQlite_Query (-1, "SELECT AssetTag,Owner FROM aTest WHERE status='Active'", $hQuery) ; the query
While _SQLite_FetchData ($hQuery, $aRow) = $SQLITE_OK
    $sMsg &= $aRow[0] & " " & $aRow[1] & @CRLF
WEnd
_SQLite_Exec (-1, "DROP TABLE aTest;") ; Remove the table
ConsoleWrite($sMsg)

_SQLite_Close()
_SQLite_Shutdown()

Share this post


Link to post
Share on other sites
gcue

wow very interesting.. looks awesome!!! many thanks weapon.. didnt know autoit had this ability!

so im seeing where u created the dummy values, i only want to use 2 columns for the query so i dont need to

inject all the columns... is this what it would look like?

Dim $aData[2][$iColumns]  ;existing array

for $x=1 to ubound($array) -1      ;from existing array
    $aData[$x][0] = $array[$x][8]  ;desired column
    $aData[$x][1] = $array[$x][9]  ;desired column
next

at what point does it show only the unique values in a column?

Edited by gcue

Share this post


Link to post
Share on other sites
weaponx

Well you are still filling the table with a few thousand entries right?

Share this post


Link to post
Share on other sites
weaponx

yep =)

so looks ok?

I don't think so. The first dimension is the number of rows (systems) and should have already been established in your existing script.

Edited by weaponx

Share this post


Link to post
Share on other sites
gcue

ahh ok then like this?

Dim $aData[2][$iColumns]

For $x = 1 To UBound($avOut) - 1 ;from existing array
    For $y = 0 To 1
        $aData[$y][0] = $avOut[$x][8] ;desired column
        $aData[$y][1] = $avOut[$x][9] ;desired column
    Next
Next

Share this post


Link to post
Share on other sites
weaponx

You mustn't change the order of the columns. If you need columns 8 and 9, you must copy them to columns 8 and 9.

You have to INSERT the same number of values as there is columns, even if they are empty. So my script takes the empty array values and makes them like this:

INSERT INTO table(a,b,c,d,e) VALUES ('','value1','','value2','')

Also to get only unique values returned you use GROUP BY column at the end of your SELECT statement.

Edited by weaponx

Share this post


Link to post
Share on other sites
gcue

so i cant change it to this?:

Dim $hQuery, $aRow, $sMsg

Const $iColumns = 2

Dim $aColumns[$iColumns]

$aColumns[0] = 'Site'

$aColumns[1] = 'Floor'

Share this post


Link to post
Share on other sites
weaponx

I guess you could. How is your original array even created?

Share this post


Link to post
Share on other sites
gcue

filetoarray

hmm i still get an error:

U:\scripts\Dashboard\do\2. Altiris Location Add\extract_dimensions.au3 (94) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

$sMsg &= $aRow[0] & " " & $aRow[1] & @CRLF

$sMsg &= $aRow[0] & " " & ^ ERROR

Dim $hQuery, $aRow, $sMsg
Const $iColumns = 2

Dim $aColumns[$iColumns]
$aColumns[0] = 'Site'
$aColumns[1] = 'Floor'

;Implode column names into comma separated string
$sColumns = _ArrayToString($aColumns, ",")

;2 Dimensional array containing information on all systems
Dim $aData[2][$iColumns]

For $x = 1 To UBound($avOut) - 1 ;from existing array
    For $y = 0 To 1
        $aData[$y][0] = $avOut[$x][8] ;desired column
        $aData[$y][1] = $avOut[$x][9] ;desired column
    Next
Next

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
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.