Ich versuche mich gerade daran eine CSV Datenstruktur in eine SQLite DB zu wandeln. Das funktioniert auch bestens und gerade was das Durchsuchen und Filtern angeht sind die Geschwindigkeitsvorteile enorm, allerdings ist das initiale Einlesen aller Datensätze zum Befüllen eines Listviews extrem langsam verglichen mit meiner alten Variante die CSV Struktur in ein 2D Array einzulesen und darüber zeitgleich das Listview zu befüllen.
Die Daten:
Ich habe 6 Spalten und ungefähr 3000 Zeilen, also eine CSV Datei mit 3000 solcher Zeilen
Die entsprechende SQL DB hat dementsprechend ebenfalls eine Tabelle mit 6 Spalten und 3000 Zeilen.
Die Dauer:
Das einlesen der CSV Datei inklusive der Listview Befüllung benötigt etwa 300ms.
Das einlesen der SQL Datei inklusive der Listview Befüllung benötigt etwa 900ms, also dreimal solange.
Mein Bench Script:
Anmerkung: Bei der ersten Ausführung werden die CSV und DB Datei erzeugt, was leider auf meinem System 2 Minuten dauert. Das ist letzlich aber egal, denn die CSV->DB Transformation muss ich ohnehin nur einmalig beim finalen Umstieg erledigen. thx BugFix
Spoiler anzeigen
#include <file.au3>
#include <array.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#Include <GuiListView.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
Global $csvFile = @ScriptDir & "\bench.csv"
Global $sqlFile = @ScriptDir & "\bench.db"
If Not FileExists($csvFile) Then createCSV()
If Not FileExists($sqlFile) Then createDB()
Global $sSQliteDll = _SQLite_Startup()
If @error Then
ConsoleWrite("SQLite Error: SQLite.dll Can't be Loaded! Errorcode(" & @error & ")" & @CRLF)
Exit -1
EndIf
Global $hDB = _SQLite_Open($sqlFile)
If $hDB = 0 Then
ConsoleWrite("SQLite Error: openDB File failed! Errorcode(" & @error & " / " & @extended & ")" & @CRLF)
_exit($hdb)
EndIf
csv()
sql_LWjoinFast("benchTable")
sql_LWjoin("benchTable")
sql_Fetch("benchTable")
sql_2DArray_LWArrAdd("benchTable")
sql_2DArray("benchTable")
_exit($hdb)
[/autoit] [autoit][/autoit] [autoit]Func createCSV()
Local $exampleData = "Spalte1,Spalte2,Spalte3,Spalte4,Spalte5,Spalte6"
Local $temp = ""
For $i = 1 to 3000
$temp &= $exampleData & @CRLF
Next
Local $hFile = FileOpen($csvFile,2+8)
FileWrite($hFile,StringReplace($temp,@CRLF,"",-1))
FileClose($hFile)
EndFunc
Func createDB()
Local $sSQliteDll = _SQLite_Startup()
If @error Then
ConsoleWrite("SQLite Error: SQLite.dll Can't be Loaded! Errorcode(" & @error & ")" & @CRLF)
Exit -1
EndIf
Local $hDB = _SQLite_Open($sqlFile)
If $hDB = 0 Then
ConsoleWrite("SQLite Error: openDB File failed! Errorcode(" & @error & " / " & @extended & ")" & @CRLF)
_exit($hdb)
EndIf
Local $aCSVfile
_FileReadToArray($csvFile,$aCSVfile)
Local $temp
; Tabelle erstellen
If Not _SQLite_Exec ($hDB, "CREATE TABLE benchTable ('Spalte1' TEXT,'Spalte2' TEXT,'Spalte3' TEXT,'Spalte4' TEXT,'Spalte5' TEXT,'Spalte6' TEXT);") = $SQLITE_OK Then
ConsoleWrite("SQLite Error: CREATE Table failed! " & _SQLite_ErrMsg () & @CRLF)
_exit($hdb)
EndIf
; Tabelle füllen
$temp = "BEGIN TRANSACTION;"
For $i = 1 To $aCSVfile[0]
$temp &= "INSERT INTO benchTable VALUES (" & "'" & StringReplace($aCSVfile[$i],",","','") & "');"
Next
$temp &= "COMMIT;"
If Not _SQLite_Exec ($hDB, $temp) = $SQLITE_OK Then
ConsoleWrite("SQLite Error: INSERT ROW's failed! " & _SQLite_ErrMsg () & @CRLF)
_exit($hdb)
EndIf
_SQLite_Close($hDB)
_SQLite_Shutdown()
EndFunc
func csv()
Local $time = TimerInit()
Local $aCSVfile
Local $myGUI = GUICreate("",1200,800)
_FileReadToArray($csvFile,$aCSVfile)
Local $myList = GUICtrlCreateListView(StringReplace($aCSVfile[1],",","|"),10,10,1180,780)
Local $a2D[UBound($aCSVfile)-1][6]
_GUICtrlListView_BeginUpdate($myList)
For $i=1 To UBound($aCSVfile)-1
$temp =""
$aSplit=StringSplit($aCSVfile[$i],",")
For $j = 1 To UBound($aSplit)-1
$a2D[$i-1][$j-1]=$aSplit[$j]
$temp &= $aSplit[$j] & "|"
Next
GUICtrlCreateListViewItem(StringTrimRight($temp,1),$myList)
Next
_GUICtrlListView_EndUpdate($myList)
GUISetState(@SW_SHOW)
ConsoleWrite("CSV-Methode Time: " & TimerDiff($time) & @CRLF)
;MsgBox(0,"CSV-Methode","done")
GUIDelete($myGUI)
Return $a2D
endfunc
Func sql_Fetch($tablename)
Local $time=timerinit()
Local $hQuery, $iResultRowCount=0, $iResultColumnCount=0, $allColumNames, $aResult, $temp
Local $myGUI = GUICreate("",1200,800)
;getRows
_SQLite_Query ($hDB, "SELECT count(*) FROM " & $tablename, $hQuery)
while _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK
$iResultRowCount = Number($aResult[0])
WEnd
; getColumns
_SQLite_Query ($hDB, "PRAGMA table_info(" & $tablename & ")", $hQuery)
while _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK
$iResultColumnCount = Number($aResult[0])+1
$allColumNames &= $aResult[1] & "|"
WEnd
;ConsoleWrite("Spalten: " & $iResultColumnCount & @CRLF & "Zeilen: " & $iResultRowCount & @CRLF)
If $iResultRowCount < 1 Then return "ERROR: rowCount failed!"
If $iResultColumnCount < 1 Then Return "ERROR: columnCount failed!"
Local $myList = GUICtrlCreateListView(StringTrimRight($allColumNames,1),10,10,1180,780)
[/autoit] [autoit][/autoit] [autoit]; Daten einlesen
_SQLite_Query ( $hDB, "SELECT * FROM " & $tablename, $hQuery )
$i = 1
_GUICtrlListView_BeginUpdate($myList)
;while _SQLite_FetchData($hQuery,$aResult,False,False) = $SQLITE_OK
while _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK
$temp = ""
For $j = 0 to UBound($aResult)-1
$temp &= $aResult[$j] & "|"
Next
GUICtrlCreateListViewItem(StringTrimRight($temp,1),$myList)
WEnd
;_SQLite_QueryFinalize($hQuery)
_GUICtrlListView_EndUpdate($myList)
GUISetState(@SW_SHOW)
ConsoleWrite("SQL-Fetch-Methode Time: " & TimerDiff($time) & @CRLF)
;MsgBox(0,"SQL-Fetch-Methode","done")
GUIDelete($myGUI)
EndFunc
Func sql_LWjoinFast($tablename)
Local $time=timerinit()
Local $hQuery, $iResultRowCount=0, $iResultColumnCount=0, $allColumNames, $aResult, $temp
Local $myGUI = GUICreate("",1200,800)
;getRows
_SQLite_Query ($hDB, "SELECT count(*) FROM " & $tablename, $hQuery)
while _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK
$iResultRowCount = Number($aResult[0])
WEnd
; getColumns
_SQLite_Query ($hDB, "PRAGMA table_info(" & $tablename & ")", $hQuery)
while _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK
$iResultColumnCount = Number($aResult[0])+1
$allColumNames &= $aResult[1] & "|"
WEnd
;ConsoleWrite("Spalten: " & $iResultColumnCount & @CRLF & "Zeilen: " & $iResultRowCount & @CRLF)
If $iResultRowCount < 1 Then return "ERROR: rowCount failed!"
If $iResultColumnCount < 1 Then Return "ERROR: columnCount failed!"
$allColumNames = StringTrimRight($allColumNames,1)
Local $myList = GUICtrlCreateListView($allColumNames,10,10,1180,780)
; Daten einlesen
Local $subquery="SELECT " & StringReplace($allColumNames,"|"," '|' ") & " AS Result FROM " & $tablename
_SQLite_Query ( $hDB, "Select group_concat(Result) FROM (" & $subquery & ")", $hQuery )
_GUICtrlListView_BeginUpdate($myList)
;$t=TimerInit()
If _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK Then
Local $aTemp = StringSplit($aResult[0],",")
For $i = 1 To $aTemp[0]
GUICtrlCreateListViewItem($aTemp[$i],$myList)
Next
;ConsoleWrite(@TAB & "fetch+lwfill: " & TimerDiff($t) & @CRLF)
Else
ConsoleWrite("SQLite Error: Reading Table failed! Errorcode(" & @error & " / " & @extended & ")" & @CRLF)
EndIf
_SQLite_QueryFinalize($hQuery)
_GUICtrlListView_EndUpdate($myList)
GUISetState(@SW_SHOW)
ConsoleWrite("SQL-LWjoinFast-Methode Time: " & TimerDiff($time) & @CRLF)
;MsgBox(0,"SQL-LWjoinFast-Methode","done")
GUIDelete($myGUI)
EndFunc
Func sql_LWjoin($tablename)
Local $time=timerinit()
Local $hQuery, $iResultRowCount=0, $iResultColumnCount=0, $allColumNames, $aResult, $temp
Local $myGUI = GUICreate("",1200,800)
;getRows
_SQLite_Query ($hDB, "SELECT count(*) FROM " & $tablename, $hQuery)
while _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK
$iResultRowCount = Number($aResult[0])
WEnd
; getColumns
_SQLite_Query ($hDB, "PRAGMA table_info(" & $tablename & ")", $hQuery)
while _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK
$iResultColumnCount = Number($aResult[0])+1
$allColumNames &= $aResult[1] & "|"
WEnd
;ConsoleWrite("Spalten: " & $iResultColumnCount & @CRLF & "Zeilen: " & $iResultRowCount & @CRLF)
If $iResultRowCount < 1 Then return "ERROR: rowCount failed!"
If $iResultColumnCount < 1 Then Return "ERROR: columnCount failed!"
$allColumNames = StringTrimRight($allColumNames,1)
Local $myList = GUICtrlCreateListView($allColumNames,10,10,1180,780)
; Daten einlesen
_SQLite_Query ( $hDB, "SELECT " & StringReplace($allColumNames,"|"," '|' ") & " FROM " & $tablename, $hQuery )
_GUICtrlListView_BeginUpdate($myList)
;$t=TimerInit()
while _SQLite_FetchData($hQuery,$aResult) = $SQLITE_OK
GUICtrlCreateListViewItem($aResult[0],$myList)
WEnd
;ConsoleWrite(@TAB & "fetch+lwfill: " & TimerDiff($t) & @CRLF)
_GUICtrlListView_EndUpdate($myList)
GUISetState(@SW_SHOW)
ConsoleWrite("SQL-LWjoin-Methode Time: " & TimerDiff($time) & @CRLF)
;MsgBox(0,"SQL-LWjoin-Methode","done")
GUIDelete($myGUI)
EndFunc
Func sql_2DArray($tablename)
Local $time=timerinit()
Local $iResultRowCount=0, $iResultColumnCount=0, $allColumNames, $aResult, $temp
Local $myGUI = GUICreate("",1200,800)
; getColumnNames
If Not _SQLite_GetTable2d($hDB, "PRAGMA table_info(" & $tablename & ")", $aResult, $iResultRowCount, $iResultColumnCount) = $SQLITE_OK Then
ConsoleWrite("SQLite Error: Reading Table failed! Errorcode(" & @error & " / " & @extended & ")" & @CRLF)
GUIDelete($myGUI)
Return -1
Else
For $i=1 To UBound($aResult)-1
$allColumNames &= $aResult[$i][1] & "|"
Next
EndIf
Local $myList = GUICtrlCreateListView(StringTrimRight($allColumNames,1),10,10,1180,780)
[/autoit] [autoit][/autoit] [autoit]If Not _SQLite_GetTable2d($hDB, "SELECT * FROM " & $tablename, $aResult, $iResultRowCount, $iResultColumnCount) = $SQLITE_OK Then
ConsoleWrite("SQLite Error: Reading Table failed! Errorcode(" & @error & " / " & @extended & ")" & @CRLF)
GUIDelete($myGUI)
Return -1
Else
For $i = 1 To UBound($aResult)-1
$temp = ""
For $j = 0 To UBound($aResult,2)-1
$temp &= $aResult[$i][$j] & "|"
Next
GUICtrlCreateListViewItem(StringTrimRight($temp,1),$myList)
Next
EndIf
GUISetState(@SW_SHOW)
ConsoleWrite("SQL-2DArray-Methode Time: " & TimerDiff($time) & @CRLF)
;MsgBox(0,"SQL-2DArray-Methode","done")
GUIDelete($myGUI)
Return $aResult
EndFunc
Func sql_2DArray_LWArrAdd($tablename)
Local $time=timerinit()
Local $iResultRowCount=0, $iResultColumnCount=0, $allColumNames, $aResult, $temp
Local $myGUI = GUICreate("",1200,800)
; getColumnNames
If Not _SQLite_GetTable2d($hDB, "PRAGMA table_info(" & $tablename & ")", $aResult, $iResultRowCount, $iResultColumnCount) = $SQLITE_OK Then
ConsoleWrite("SQLite Error: Reading Table failed! Errorcode(" & @error & " / " & @extended & ")" & @CRLF)
GUIDelete($myGUI)
Return -1
Else
For $i=1 To UBound($aResult)-1
$allColumNames &= $aResult[$i][1] & "|"
Next
EndIf
Local $myList = GUICtrlCreateListView(StringTrimRight($allColumNames,1),10,10,1180,780)
[/autoit] [autoit][/autoit] [autoit]If Not _SQLite_GetTable2d($hDB, "SELECT * FROM " & $tablename, $aResult, $iResultRowCount, $iResultColumnCount) = $SQLITE_OK Then
ConsoleWrite("SQLite Error: Reading Table failed! Errorcode(" & @error & " / " & @extended & ")" & @CRLF)
GUIDelete($myGUI)
Return -1
Else
_GUICtrlListView_AddArray($myList,$aResult)
EndIf
GUISetState(@SW_SHOW)
ConsoleWrite("SQL-2DArray_LWArrAdd-Methode Time: " & TimerDiff($time) & @CRLF)
;MsgBox(0,"SQL-2DArray_LWArrAdd-Methode","done")
GUIDelete($myGUI)
Return $aResult
EndFunc
Func _exit($hdb)
If $hdb <> 0 then _SQLite_Close($hDB)
_SQLite_Shutdown()
Exit
EndFunc
Die Frage:
Woran liegts? Kann man das in irgendeinerweise noch beschleunigen? Eine Sekunde ist mir eigentlich zu lange, zumal es in den nächsten Jahren eher mehr Daten werden und die Zeit vermutlich nochmals entsprechend ansteigen dürfte. Je nach Filterung muss das Listview immer mal wieder komplett erstellt werden, was dann jeweils in einer 1-Sekunden Pause resultieren dürfte.