gcue Posted August 17, 2009 Share Posted August 17, 2009 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) Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 (edited) 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 August 17, 2009 by weaponx Link to comment Share on other sites More sharing options...
gcue Posted August 17, 2009 Author Share Posted August 17, 2009 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? Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 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? Link to comment Share on other sites More sharing options...
gcue Posted August 17, 2009 Author Share Posted August 17, 2009 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 Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 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) bldgsI'm thinking you need to use SQLite instead of one big array. It will be so much nicer and a lot faster. Link to comment Share on other sites More sharing options...
gcue Posted August 17, 2009 Author Share Posted August 17, 2009 sqllite? you mean out of autoit right? hmm im trying to integrate this functionality to an existing autoit script. Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 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. Link to comment Share on other sites More sharing options...
gcue Posted August 17, 2009 Author Share Posted August 17, 2009 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?) Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 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? Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 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. expandcollapse popup#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() Link to comment Share on other sites More sharing options...
gcue Posted August 17, 2009 Author Share Posted August 17, 2009 (edited) 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 August 17, 2009 by gcue Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 Well you are still filling the table with a few thousand entries right? Link to comment Share on other sites More sharing options...
gcue Posted August 17, 2009 Author Share Posted August 17, 2009 yep =) so looks ok? Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 (edited) 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 August 17, 2009 by weaponx Link to comment Share on other sites More sharing options...
gcue Posted August 17, 2009 Author Share Posted August 17, 2009 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 Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 (edited) 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 August 17, 2009 by weaponx Link to comment Share on other sites More sharing options...
gcue Posted August 17, 2009 Author Share Posted August 17, 2009 so i cant change it to this?: Dim $hQuery, $aRow, $sMsg Const $iColumns = 2 Dim $aColumns[$iColumns] $aColumns[0] = 'Site' $aColumns[1] = 'Floor' Link to comment Share on other sites More sharing options...
weaponx Posted August 17, 2009 Share Posted August 17, 2009 I guess you could. How is your original array even created? Link to comment Share on other sites More sharing options...
gcue Posted August 18, 2009 Author Share Posted August 18, 2009 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now