Что нового

[Данные, строки] Пример как можно ускорить работу с БД SQLite

YOgen

Знающий
Сообщения
58
Репутация
5
OffTopic:
Сразу оговорюсь - может не в тот раздел закинул... сильно ногами не пинать :smile:


Очень понадобилось мне вносить большие массивы данных в БД SQLite, а после их считывать.
И очень не нравилось, что посредством SQLite.au3 оператором "SELECT" 35000 строк вытягиваются секунд 8-9 (как уже ни крутил).
Вот нашел вариант с использованием sqlite3.exe (http://www.sqlite.org/2013/sqlite-shell-win32-x86-3071700.zip) как можно добиться очень даже хороших результатов.

В данном примере показано на сколько шустрее идет INSERT с использованием sqlite3.exe.

Код:
#include <Array.au3>
#include <Constants.au3>
#include <SQLite.dll.au3>
#include <SQLite.au3>

Opt("TrayIconDebug", 1)

$sCreateTable_Query = "CREATE TABLE IF NOT EXISTS Test(" & _
								"'Num1' INTEGER," & _
								"'Num2' INTEGER" & _
								");"
$iRepeatTimes = 50000

;================== _SQLiteUDF ====

_SQLite_Startup()
$hSQLiteDB = _SQLite_Open()
;_SQLite_Exec($hSQLiteDB, "PRAGMA synchronous = OFF;")

	$hTimer = TimerInit()
_SQLite_Exec($hSQLiteDB, $sCreateTable_Query)
	$iDiff = TimerDiff($hTimer)
	ConsoleWrite("_SQLiteUDF_CreateTable - " & $iDiff/1000 & @CRLF)

	$hTimer = TimerInit()
For $i = 1 To $iRepeatTimes
	_SQLite_Exec($hSQLiteDB, "INSERT INTO Test('Num1','Num2') VALUES('A_" & $i & "','B_" & $i & "');")
Next
	$iDiff = TimerDiff($hTimer)
	ConsoleWrite("_SQLiteUDF_InsertData - " & $iDiff/1000 & @CRLF)

;Local $aSQLResult[2], $iRows, $iColumns
;_SQLite_GetTable2d($hSQLiteDB, "SELECT * FROM Test;", $aSQLResult, $iRows, $iColumns)
;_ArrayDisplay($aSQLResult)

_SQLite_Close($hSQLiteDB)
_SQLite_Shutdown()

;==================================

;================== _SQLiteEXE ====

	$hTimer = TimerInit()
$iPID = Run(@ComSpec & " /c " & @ScriptDir & "\sqlite3.exe", "", @SW_HIDE, $STDIN_CHILD + $STDOUT_CHILD)
StdinWrite($iPID, $sCreateTable_Query)
	$iDiff = TimerDiff($hTimer)
	ConsoleWrite("_SQLiteEXE_CreateTable - " & $iDiff/1000 & @CRLF)

	$hTimer = TimerInit()
For $i = 1 To $iRepeatTimes
	StdinWrite($iPID, "INSERT INTO Test('Num1','Num2') VALUES('A_" & $i & "','B_" & $i & "');")
Next
	$iDiff = TimerDiff($hTimer)
	ConsoleWrite("_SQLiteEXE_InsertData - " & $iDiff/1000 & @CRLF)

;StdinWrite($iPID, "SELECT * FROM Test;")
StdinWrite($iPID)
;Local $sOut
;While 1
;	$sOut &= StdoutRead($iPID)
;	If @error Then ExitLoop
;Wend
;MsgBox(64, "", $sOut)
;$aResult = StringRegExp($sOut, "(.*)\R", 3)
;_ArrayDisplay($aResult)

;==================================


Мой результат (Phenom II 3.0 GHz):
_SQLiteUDF_InsertData - 8.86560758804427
_SQLiteEXE_InsertData - 0.483283505374842

Хотелось бы услышать мысли присутствующих по данному факту... :whistle:
 

AZJIO

Меценат
Меценат
Сообщения
2,874
Репутация
1,194
YOgen
Посмотри пример http://pastebin.com/v6g3RQig (строка 161)
Перед Insert добавь BEGIN TRANSACTION; а после COMMIT;, тогда множество Insert между этими командами выполнится в одной транзакции... как-то так.
 
Автор
YOgen

YOgen

Знающий
Сообщения
58
Репутация
5
AZJIO [?]
YOgenПосмотри пример http://pastebin.com/v6g3RQigПеред Insert добавь BEGIN TRANSACTION; а после COMMIT;, тогда множество Insert между этими командами выполнится в одной транзакции... как-то так.
"BEGIN TRANSACTION; COMMIT;" - как я понял по тестам действуют только на БД, которые хранятся в файлах. Если база в памяти, то у меня примерно равные значения выдавало.
Да тут речь не и только об INSERT-е... Любой вариант SELECT-а (через _SQLite_GetTable, _SQLite_GetTable2d и т.д.) у меня выдавал результат около 8 секунд, когда как через sqlite3.exe ("Run(@ComSpec..." и последующий StdinWrite/StdoutRead... а дальше еще и _Encoding_UTF8ToANSI(), т.к. русские символы есть) от силы 1.5 секунды на ~35.000 строках.
 

AZJIO

Меценат
Меценат
Сообщения
2,874
Репутация
1,194
YOgen
На офcайте KaFu говорил,что если вы делаете для себя то нужно взять функции UDF и оптимизировать под свои задачи. Функции сделаны универсальными, то есть проверяют все ошибки и совершают все действия для разового использования. К примеру в ListView при добавлении пункта проверяется включен ли юникод-поддержка в ListView и соответственно делать str или wstr при вызове, а запрос тратит время. А в программе обычно заранее в начале скрипта можно определить этот флаг и не проверять при каждом вызове.
 

Redline

AutoIT Гуру
Сообщения
506
Репутация
375
Конечно напрямую обращаться к SQLite будет быстрее, чем через прослойку в виде UDF и sqlite3.dll, но можно немного исправить ситуацию для INSERTа, заменив кучу маленьких вставок одной большой, тогда мы сэкономим на лишних обращениях к базе, а BEGIN/COMMIT дадут еще небольшой прирост в скорости:
Код:
#include <Array.au3>
#include <Constants.au3>
;~ #include <SQLite.dll.au3>
#include <SQLite.au3>

Opt("TrayIconDebug", 1)

$sCreateTable_Query = "CREATE TABLE IF NOT EXISTS Test(" & _
                                "'Num1' INTEGER," & _
                                "'Num2' INTEGER" & _
                                ");"
$iRepeatTimes = 50000

;================== _SQLiteUDF ====

_SQLite_Startup()
$hSQLiteDB = _SQLite_Open()
;_SQLite_Exec($hSQLiteDB, "PRAGMA synchronous = OFF;")

    $hTimer = TimerInit()
_SQLite_Exec($hSQLiteDB, $sCreateTable_Query)
    $iDiff = TimerDiff($hTimer)
    ConsoleWrite("_SQLiteUDF_CreateTable - " & $iDiff/1000 & @CRLF)

    $hTimer = TimerInit()
	$sSql = ''
For $i = 1 To $iRepeatTimes
    $sSql &= "INSERT INTO Test('Num1','Num2') VALUES('A_" & $i & "','B_" & $i & "');"
Next
	_SQLite_Exec($hSQLiteDB, "BEGIN;")
	_SQLite_Exec($hSQLiteDB, $sSql)
	_SQLite_Exec($hSQLiteDB, "COMMIT;")
    $iDiff = TimerDiff($hTimer)
    ConsoleWrite("_SQLiteUDF_InsertData - " & $iDiff/1000 & @CRLF)

;Local $aSQLResult[2], $iRows, $iColumns
;_SQLite_GetTable2d($hSQLiteDB, "SELECT * FROM Test;", $aSQLResult, $iRows, $iColumns)
;_ArrayDisplay($aSQLResult)

_SQLite_Close($hSQLiteDB)
_SQLite_Shutdown()

;==================================

;================== _SQLiteEXE ====

    $hTimer = TimerInit()
$iPID = Run(@ComSpec & " /c " & @ScriptDir & "\sqlite3.exe", "", @SW_HIDE, $STDIN_CHILD + $STDOUT_CHILD)
StdinWrite($iPID, $sCreateTable_Query)
    $iDiff = TimerDiff($hTimer)
    ConsoleWrite("_SQLiteEXE_CreateTable - " & $iDiff/1000 & @CRLF)

    $hTimer = TimerInit()
For $i = 1 To $iRepeatTimes
    StdinWrite($iPID, "INSERT INTO Test('Num1','Num2') VALUES('A_" & $i & "','B_" & $i & "');")
Next
    $iDiff = TimerDiff($hTimer)
    ConsoleWrite("_SQLiteEXE_InsertData - " & $iDiff/1000 & @CRLF)

;StdinWrite($iPID, "SELECT * FROM Test;")
StdinWrite($iPID)
;Local $sOut
;While 1
;   $sOut &= StdoutRead($iPID)
;   If @error Then ExitLoop
;Wend
;MsgBox(64, "", $sOut)
;$aResult = StringRegExp($sOut, "(.*)\R", 3)
;_ArrayDisplay($aResult)

;==================================
[box title=Before]_SQLiteUDF_CreateTable - 0.000382944044306049
_SQLiteUDF_InsertData - 5.55904955469124
_SQLiteEXE_CreateTable - 0.0274067985076087
_SQLiteEXE_InsertData - 0.296130244593481[/box]
[box title=After]_SQLiteUDF_CreateTable - 0.000440032854931238
_SQLiteUDF_InsertData - 0.555585097767795
_SQLiteEXE_CreateTable - 0.0272364942466987
_SQLiteEXE_InsertData - 0.278058749517712[/box]
i5 3.2 GHz
 
Верх