BrewManNH Posted May 13, 2013 Share Posted May 13, 2013 (edited) I have been playing around with various methods to sort a listview with about 4000 rows and 5 columns. My problem is that to sort a listview using any of the methods I've tried are all painfully slow, or give inferior sort results. Using the callback method of _GUICtrlListView_SortItems it takes about 30 seconds to sort the LV, but gives the best results. Using _GUICtrlListView_SimpleSort, it takes anywhere from 20 to 30 seconds on the same list. Plus with these first 2, I still have to rebuild the array that holds the contents of the listview so I can access the items correctly. Using ArraySort and rebuilding the ListView gives the best results at around 6 seconds, but the results are the same as doing it with _SimpleSort (which makes sense as it just uses _ArraySort under the hood), but I don't have to rebuild the array I use to hold the LV items. The problem with using the fastest method is that if I sort on more than one column after another, the results are all unsorted except for the last column sorted. An example, I'm using the listview in a media player, I want to have the song list showing the Track numbers in order, the Albums listed together, the Artist in alphabetical order, and the year of release in order. Using _GUICtrlListView_SortItems, I can sort each of those columns and when I sort the other columns those items stay in order, Track 2 comes after Track 1, all the albums are listed together, all the artists are listed alphabetically, and year of release is listed in order. But it takes me over 2.5 minutes to sort and rebuild the ListView to get it to sort this way. So, I'm looking for something that sorts as well as SortItems, but isn't so dog slow. Any ideas? EDIT: Changed the title to more along the lines of a question, rather than making it look like a statement. Edited May 13, 2013 by BrewManNH If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
jdelaney Posted May 14, 2013 Share Posted May 14, 2013 (edited) Maybe try with sql? Then you can add as many sorts as you want (first piece has single, second has dual...same 1.13 seconds for me on both...(only 1333 music files)...that's the query, with sorts, and re-print of all data to listview Uses modified functions to print the data quicker: expandcollapse popup#include <Array.au3> #include <File.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <GUIConstantsEx.au3> #include <GuiListView.au3> GUICreate("ListView Add Array", 500, 300) $hListView = GUICtrlCreateListView("", 2, 2, 494, 268) _GUICtrlListView_AddColumn($hListView, "artist", 100) _GUICtrlListView_AddColumn($hListView, "album", 100) _GUICtrlListView_AddColumn($hListView, "Year", 100) _GUICtrlListView_AddColumn($hListView, "Genre", 100) _GUICtrlListView_AddColumn($hListView, "Title", 100) GUISetState() Local $hQuery, $aRow, $sMsg _SQLite_Startup() ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) _SQLite_Open() ; open :memory: Database _SQLite_Exec(-1, "CREATE TABLE aTest (artist,album,year,genre,title);") ; CREATE a Table ;~ 13 - artist ;~ 14 - album ;~ 15 - year ;~ 16 - genre ;~ 21 - title $array = _FileListToArray(@UserProfileDir & "\Music","*",2) $oFSO = ObjCreate("Scripting.FileSystemObject") $oFSO = ObjCreate("Scripting.FileSystemObject") $oShellApp = ObjCreate("shell.application") Local $aParams[5]=[13,14,15,16,21] Local $aPropNames[5]=["artist","album","year","genre","title"] For $i = 1 To UBound($array) - 1 $arraySub = _FileListToArray(@UserProfileDir & "\Music\" & $array[$i],"*",2) For $j = 1 To UBound($arraySub) - 1 $oFolder = $oShellApp.Namespace(@UserProfileDir & "\Music\" & $array[$i] & "\" & $arraySub[$j]) For $oFile In $oFolder.Items $string = "" For $l = 0 to UBound($aParams)-1 If $l=0 Then $string = StringReplace($oFolder.GetDetailsOf($oFile,$aParams[$l]), "'", "") $string = "'" & $string & "'" Else $new = StringReplace($oFolder.GetDetailsOf($oFile,$aParams[$l]), "'", "") $new = "'" & $new & "'" $string &= "," & $new EndIf Next _SQLite_Exec(-1, "INSERT INTO aTest(artist,album,year,genre,title) VALUES (" & $string & ");") ; INSERT Data Next Next Next Local $aRow $iTime = TimerInit() _SQLite_Query(-1, "SELECT * FROM aTest ORDER BY 1;", $hQuery) ; the query $iCounter = 0 While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK _GUICtrlListView_Add1dArray($hListView, $aRow, $iCounter) $iCounter+=1 WEnd ConsoleWrite(@CRLF & " " & TimerDiff($iTime) & @CRLF ) MsgBox(1,1, TimerDiff($iTime) & " UBound=" & $iCounter ) _GUICtrlListView_DeleteAllItems($hListView) Local $aRow $iTime = TimerInit() _SQLite_Query(-1, "SELECT * FROM aTest ORDER BY 3,1;", $hQuery) ; the query $iCounter = 0 While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK _GUICtrlListView_Add1dArray($hListView, $aRow, $iCounter) $iCounter+=1 WEnd ConsoleWrite(@CRLF & " " & TimerDiff($iTime) & @CRLF ) MsgBox(1,1, TimerDiff($iTime) & " UBound=" & $iCounter ) _SQLite_Exec(-1, "DROP TABLE aTest;") ; Remove the table _SQLite_Close() _SQLite_Shutdown() Func _GUICtrlListView_Add1dArray($hWnd, ByRef $aItems,$iLastItem) If $Debug_LV Then __UDF_ValidateClassName($hWnd, $__LISTVIEWCONSTANT_ClassName) Local $fUnicode = _GUICtrlListView_GetUnicodeFormat($hWnd) Local $tItem = DllStructCreate($tagLVITEM) Local $tBuffer If $fUnicode Then $tBuffer = DllStructCreate("wchar Text[4096]") Else $tBuffer = DllStructCreate("char Text[4096]") EndIf DllStructSetData($tItem, "Mask", $LVIF_TEXT) DllStructSetData($tItem, "Text", DllStructGetPtr($tBuffer)) DllStructSetData($tItem, "TextMax", 4096) ;~ Local $iLastItem = _GUICtrlListView_GetItemCount($hWnd) _GUICtrlListView_BeginUpdate($hWnd) Local $pItem = DllStructGetPtr($tItem) DllStructSetData($tItem, "Item", 0 + $iLastItem) DllStructSetData($tItem, "SubItem", 0) DllStructSetData($tBuffer, "Text", $aItems[0]) If $fUnicode Then GUICtrlSendMsg($hWnd, $LVM_INSERTITEMW, 0, $pItem) Else GUICtrlSendMsg($hWnd, $LVM_INSERTITEMA, 0, $pItem) EndIf For $iJ = 1 To UBound($aItems) - 1 DllStructSetData($tItem, "SubItem", $iJ) DllStructSetData($tBuffer, "Text", $aItems[$iJ]) If $fUnicode Then GUICtrlSendMsg($hWnd, $LVM_SETITEMW, 0, $pItem) Else GUICtrlSendMsg($hWnd, $LVM_SETITEMA, 0, $pItem) EndIf Next _GUICtrlListView_EndUpdate($hWnd) EndFunc ;==>_GUICtrlListView_AddArray Edited May 14, 2013 by jdelaney IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
BrewManNH Posted May 14, 2013 Author Share Posted May 14, 2013 My point was how to do it with a ListView, the code is already there, I just need to make it not take so long to sort. I'd have to completely rewrite it to use a database, then I'd have to learn how to use SQLite just to save a few seconds in sorting a listview, I'm not sure taking weeks to learn something new is a good way to go about shaving off those seconds, especially on this project which is just a continuous work in progress. Besides, using the _ArraySort method, it takes about 1.5 - 1.7 seconds to sort and rebuild the listview with 1367 items in it. Using the _SortItems with callback method it takes 7 - 8 seconds. Using the _SimpleSort method takes approx. 5 seconds. It doesn't seem like it's saving much over the array sort method, but I don't know what the results would look like using SQLite. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
jdelaney Posted May 14, 2013 Share Posted May 14, 2013 (edited) This was the first time I used sqllte functions, and it took 10 min for me to pick up (though I have db background) If you want to have multiple sorts criteria, there would be (probably) no easier/quicker way than sql...like I said, the complete sort and rebuild for 1 vs 2 sort criteria was, near, exactly the same (within thousandths of a second) Edited May 14, 2013 by jdelaney IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
BrewManNH Posted May 14, 2013 Author Share Posted May 14, 2013 Unfortunately, I have zero experience with any type of database software, and although I'm sure the learning experience will benefit in the long run, it still doesn't help me in the here and now. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
jdelaney Posted May 14, 2013 Share Posted May 14, 2013 (edited) Ok, don't mean to drill in the point, but to do your sort with rebuild of listview is the same 1.142 seconds: The problem with using the fastest method is that if I sort on more than one column after another, the results are all unsorted except for the last column sorted. An example, I'm using the listview in a media player, I want to have the song list showing the Track numbers in order, the Albums listed together, the Artist in alphabetical order, and the year of release in order. Using _GUICtrlListView_SortItems, I can sort each of those columns and when I sort the other columns those items stay in order, Track 2 comes after Track 1, all the albums are listed together, all the artists are listed alphabetically, and year of release is listed in order. But it takes me over 2.5 minutes to sort and rebuild the ListView to get it to sort this way. Although, it's currently sorting the track number as a string...which shouldn't be too hard to correct expandcollapse popup#include <Array.au3> #include <File.au3> #include <SQLite.au3> #include <SQLite.dll.au3> #include <GUIConstantsEx.au3> #include <GuiListView.au3> GUICreate("ListView Add Array", 500, 300) $hListView = GUICtrlCreateListView("", 2, 2, 494, 268) _GUICtrlListView_AddColumn($hListView, "artist", 100) _GUICtrlListView_AddColumn($hListView, "album", 100) _GUICtrlListView_AddColumn($hListView, "Year", 100) _GUICtrlListView_AddColumn($hListView, "track", 100) _GUICtrlListView_AddColumn($hListView, "Title", 100) GUISetState() Local $hQuery, $aRow, $sMsg _SQLite_Startup() ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF) _SQLite_Open() ; open :memory: Database _SQLite_Exec(-1, "CREATE TABLE aTest (artist,album,year,track,title);") ; CREATE a Table ;~ 13 - artist ;~ 14 - album ;~ 15 - year ;~ 16 - genre ;~ 21 - title $array = _FileListToArray(@UserProfileDir & "\Music","*",2) $oFSO = ObjCreate("Scripting.FileSystemObject") $oFSO = ObjCreate("Scripting.FileSystemObject") $oShellApp = ObjCreate("shell.application") Local $aParams[5]=[13,14,15,26,21] Local $aPropNames[5]=["artist","album","year","Track","title"] For $i = 1 To UBound($array) - 1 $arraySub = _FileListToArray(@UserProfileDir & "\Music\" & $array[$i],"*",2) For $j = 1 To UBound($arraySub) - 1 $oFolder = $oShellApp.Namespace(@UserProfileDir & "\Music\" & $array[$i] & "\" & $arraySub[$j]) For $oFile In $oFolder.Items $string = "" For $l = 0 to UBound($aParams)-1 If $l=0 Then $string = StringReplace($oFolder.GetDetailsOf($oFile,$aParams[$l]), "'", "") $string = "'" & $string & "'" Else If $l = 3 Then $new = StringReplace($oFolder.GetDetailsOf($oFile,$aParams[$l]), "'", "") $new = "'" & Int($new) & "'" $string &= "," & $new Else $new = StringReplace($oFolder.GetDetailsOf($oFile,$aParams[$l]), "'", "") $new = "'" & $new & "'" $string &= "," & $new EndIf EndIf Next _SQLite_Exec(-1, "INSERT INTO aTest(artist,album,year,track,title) VALUES (" & $string & ");") ; INSERT Data Next Next Next Local $aRow $iTime = TimerInit() _SQLite_Query(-1, "SELECT * FROM aTest ORDER BY 1,4,2,3;", $hQuery) ; the query $iCounter = 0 While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK _GUICtrlListView_Add1dArray($hListView, $aRow, $iCounter) $iCounter+=1 WEnd ConsoleWrite(@CRLF & " " & TimerDiff($iTime) & @CRLF ) MsgBox(1,1, TimerDiff($iTime) & " UBound=" & $iCounter ) _GUICtrlListView_DeleteAllItems($hListView) _SQLite_Exec(-1, "DROP TABLE aTest;") ; Remove the table _SQLite_Close() _SQLite_Shutdown() Func _GUICtrlListView_Add1dArray($hWnd, ByRef $aItems,$iLastItem) If $Debug_LV Then __UDF_ValidateClassName($hWnd, $__LISTVIEWCONSTANT_ClassName) Local $fUnicode = _GUICtrlListView_GetUnicodeFormat($hWnd) Local $tItem = DllStructCreate($tagLVITEM) Local $tBuffer If $fUnicode Then $tBuffer = DllStructCreate("wchar Text[4096]") Else $tBuffer = DllStructCreate("char Text[4096]") EndIf DllStructSetData($tItem, "Mask", $LVIF_TEXT) DllStructSetData($tItem, "Text", DllStructGetPtr($tBuffer)) DllStructSetData($tItem, "TextMax", 4096) ;~ Local $iLastItem = _GUICtrlListView_GetItemCount($hWnd) _GUICtrlListView_BeginUpdate($hWnd) Local $pItem = DllStructGetPtr($tItem) DllStructSetData($tItem, "Item", 0 + $iLastItem) DllStructSetData($tItem, "SubItem", 0) DllStructSetData($tBuffer, "Text", $aItems[0]) If $fUnicode Then GUICtrlSendMsg($hWnd, $LVM_INSERTITEMW, 0, $pItem) Else GUICtrlSendMsg($hWnd, $LVM_INSERTITEMA, 0, $pItem) EndIf For $iJ = 1 To UBound($aItems) - 1 DllStructSetData($tItem, "SubItem", $iJ) DllStructSetData($tBuffer, "Text", $aItems[$iJ]) If $fUnicode Then GUICtrlSendMsg($hWnd, $LVM_SETITEMW, 0, $pItem) Else GUICtrlSendMsg($hWnd, $LVM_SETITEMA, 0, $pItem) EndIf Next _GUICtrlListView_EndUpdate($hWnd) EndFunc ;==>_GUICtrlListView_AddArray _SQLite_Query(-1, "SELECT * FROM aTest ORDER BY 1,4,2,3;", $hQuery) ; the query updated to allow sort of int by track num: For $i = 1 To UBound($array) - 1 $arraySub = _FileListToArray(@UserProfileDir & "\Music\" & $array[$i],"*",2) For $j = 1 To UBound($arraySub) - 1 $oFolder = $oShellApp.Namespace(@UserProfileDir & "\Music\" & $array[$i] & "\" & $arraySub[$j]) For $oFile In $oFolder.Items $string = "" $1 = StringReplace($oFolder.GetDetailsOf($oFile,$aParams[0]), "'", "") $2 = StringReplace($oFolder.GetDetailsOf($oFile,$aParams[1]), "'", "") $3 = Int(StringReplace($oFolder.GetDetailsOf($oFile,$aParams[2]), "'", "")) $4 = Int(StringReplace($oFolder.GetDetailsOf($oFile,$aParams[3]), "'", "")) $5 = StringReplace($oFolder.GetDetailsOf($oFile,$aParams[4]), "'", "") _SQLite_Exec(-1, "INSERT INTO aTest(artist,album,year,track,title) VALUES ('" & $1 & "','" & $2 & "'," & $3 & "," & $4 & ",'" & $5 & "');") ; INSERT Data Next Next Next Edited May 14, 2013 by jdelaney IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
BrewManNH Posted May 14, 2013 Author Share Posted May 14, 2013 I ran this script, but I'm not sure what I'm supposed to get from it. What field is it sorting on, and what are the times given after it runs telling me? If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
Solution jdelaney Posted May 14, 2013 Solution Share Posted May 14, 2013 (edited) So this script runs through your logged in user's 'music' folder...searching specifically for WMA tracks (structure is userprofilemusicbandalbumsong)...if you don't have that structure, it won't be picked up. It then grabs 5 pieces of info - album, band, track number, song, year (each track) and inserts into a db. The timer shows how long it takes to perform a query, and rebuild the list view...so the initial wait is data populating into the db. The query is: _SQLite_Query(-1, "SELECT * FROM aTest ORDER BY 1,4,2,3;", $hQuery) ; the query which means the sort is by column 1, then column 4, then 2, then 3 where columns (in order), are (1 based): _GUICtrlListView_AddColumn($hListView, "artist", 100)_GUICtrlListView_AddColumn($hListView, "album", 100)_GUICtrlListView_AddColumn($hListView, "Year", 100)_GUICtrlListView_AddColumn($hListView, "track", 100)_GUICtrlListView_AddColumn($hListView, "Title", 100) Which isn't exactly what you wanted...but you can change it to 1, 3, 2, 4 (I think that's what you want) You can also use the column name instead...default sorts are column [asc], but you can add column desc ORDER BY artist,year,album,track ORDER BY artist,year,album desc,track Edited May 14, 2013 by jdelaney IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
BrewManNH Posted May 14, 2013 Author Share Posted May 14, 2013 After studying this, I think I have it finally figured out, and it (using the SQLite queries) may actually sort the listview the way I want it to be sorted, meaning the same way that _SortItems sorts the listview items. Now I just have to figure out how to incorporate this into the script without blowing everything else up. Of course, this is kind of what I was getting at before, I don't know SQLite and it's syntax, or even most of it's commands, so to have come up with this using SQLite would have taken me about a week of my time to just learn the basics to realize that it would have solved the problem. Of course, if AutoIt had an efficient sorting routine for listviews, this wouldn't have been an issue. If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator 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