SQLite как увеличить скорость _SQLite_GetTable2d

pvnn · 2 Фев 2016

Всем доброго времени суток.
Переделываю досовскую программу-справочник на FoxPro. Вместо Dbf использую SQLite, а отображаю в GUI в виртуальном Listview. Количество записей в таблице 70000. Отсюда и проблемы:
Медленно работает функция _SQLite_GetTable2d, т.е. сам запрос отрабатывается быстро, а вот массив создается долго (Win7 около 13 сек.). Как можно увеличить скорость?

П.С. Пока сделал тестовый запрос, который выводит все записи из таблицы с сортировкой по возрастанию-убыванию. В консоль выводятся результаты скорости.
Autoit v3.3.12.0

Код:

#include <Timers.au3>
#include <SQLite.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>

_SQLite_Startup()
If @error Then Exit MsgBox(16, "Ошибка SQLite", "Не удалось загрузить SQLite.dll")

Global $sFileDB = @ScriptDir & '\Data.db'
Global $aResult,$iRows,$iColumns
Global $Row=70000
$TableName = 'table1'
Global $aRow,$hQuery,$Flag=0,$Sort




; Создание тестовой БД если ее нет
; FileDelete(@ScriptDir & '\Data.db') ; Чтобы пересоздать БД
If Not FileExists($sFileDB) Then
	$hStarttime=_Timer_Init()
	$Line='дарановтрансовковранвичыээысывжыхгорsdcsd'
	$sExec = 'BEGIN; Create Table table1 ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [f_1] TEXT,[f_2] TEXT,[f_3] TEXT,[f_4] TEXT,[f_5] TEXT );'
	; Составить строку данных
	For $i=0 To $Row-1
		$ind=Random(5,25,1)
		$sData=StringMid($Line,$ind)
		$sExec &= 'INSERT INTO '&$TableName&' (f_1,f_2,f_3,f_4,f_5) VALUES (' & _SQLite_Escape($sData)&','&_SQLite_Escape($sData)&','&_SQLite_Escape($sData)&','&_SQLite_Escape($sData)&','&_SQLite_Escape($sData)& ');'
	Next
	$sExec &= 'COMMIT;'
	ConsoleWrite(@CRLF&'Создание строки: '&_Timer_Diff($hStarttime)/1000&@CRLF)
	; Заполнить таблицу
	$hStarttime=_Timer_Init()
	$hDB = _SQLite_Open($sFileDB)
	_SQLite_Exec($hDB, $sExec)
	_SQLite_Close($hDB)
	ConsoleWrite(@CRLF&'Добавление записей в БД: '&_Timer_Diff($hStarttime)/1000&@CRLF)
EndIf



; Выполнить запрос
$hStarttime=_Timer_Init()
Query()
ConsoleWrite(@CRLF&'Выполнить SOL-запрос: '&_Timer_Diff($hStarttime)/1000&@CRLF)
$aItems=$aResult


; GUI
$Form1 = GUICreate("Form1", 803, 457, -1, -1)
$Button1 = GUICtrlCreateButton("Запрос", 8, 416, 75, 25)
$hListView= GUICtrlCreateListView("", 8, 24, 785, 383, BitOR($LVS_OWNERDATA,$LVS_SHOWSELALWAYS))
_GUICtrlListView_SetExtendedListViewStyle(-1, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES))
  $hLV = GUICtrlGetHandle( $hListView ) ;    Virtual listview    Reduces flicker
  _GUICtrlListView_AddColumn( $hLV, "Column1",  50 )
  _GUICtrlListView_AddColumn( $hLV, "Column2",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column3",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column4",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column5",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column6",  100 )

; Указать количество строк для отображения в виртуальном ListView
  GUICtrlSendMsg( $hListView, $LVM_SETITEMCOUNT, $Row+1, 0 )

$hStarttime=_Timer_Init()
  GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )

GUISetState(@SW_SHOW)
ConsoleWrite(@CRLF&'Заполнить Виртуальный список: '&_Timer_Diff($hStarttime)/1000&@CRLF)

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
    Case $GUI_EVENT_CLOSE
         Exit
	Case $Button1
		GUICtrlSetState($Button1,$GUI_DISABLE)
		$hStarttime=_Timer_Init()
		Query()
		ConsoleWrite(@CRLF&'Выполнить SOL-запрос: '&_Timer_Diff($hStarttime)/1000&@CRLF)
		$aItems=$aResult
		GUICtrlSendMsg( $hListView, $LVM_SETITEMCOUNT, $Row+1, 0 )  ; Обновить виртуальный ListView, указав новое количесто записей в массиве
		GUICtrlSetState($Button1,$GUI_ENABLE)
    EndSwitch
WEnd


Func Query()
	If $Flag=0 Then
		$Sort='ASC'
		$Flag=1
	Else
		$Sort='DESC'
		$Flag=0
	EndIf
	$hDB=_SQLite_Open($sFileDB)
	_SQLite_GetTable2d($hDB,'SELECT * FROM '&$TableName&' ORDER BY ID '&$Sort&';',$aResult,$iRows,$iColumns)
	If @error Then Exit	MsgBox(16,'Ошибка SELECT','Не удалось выполнить запрос'&@CRLF&_SQLite_ErrMsg())
	_SQLite_Close($hDB)
EndFunc



Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
  Local Static $tText = DllStructCreate( "wchar[100]" ) ; Размерность данных в столбцах
  Local Static $pText = DllStructGetPtr( $tText )           ; Указатель структуры

  Local $tNMHDR, $hWndFrom, $iCode
  $tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
  $hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
  $iCode = DllStructGetData( $tNMHDR, "Code" )

  Switch $hWndFrom
    Case $hLV
      Switch $iCode
        ; Отобразить виртуальный ListView
        Case $LVN_GETDISPINFOW
          Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
            ; Если valid text buffer
            If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
                ; Опрос всего массива
                $nRow = DllStructGetData($tNMLVDISPINFO, "item")    ; Номер строки
                $nCol = DllStructGetData($tNMLVDISPINFO, "subitem") ; Номер столбца
                  Local $sItem = $aItems[$nRow][$nCol] ; Данные в столбце
                ; Заполняем структуру
                DllStructSetData( $tText, 1, $sItem )                               ; Текст
                DllStructSetData( $tNMLVDISPINFO, "Text", $pText )                  ; Указатель структуры
                DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )  ; Длина строки
            EndIf

      EndSwitch
 EndSwitch
  Return $GUI_RUNDEFMSG
EndFunc

inververs · 2 Фев 2016

По правильному нужно делать пагинацию или ленивую выгрузку, выгрузите 200 записей, а если понадобились другие - догружайте.

pvnn · 2 Фев 2016

По правильному нужно делать пагинацию или ленивую выгрузку, выгрузите 200 записей, а если понадобились другие - догружайте.

А есть рабочий пример. Кто-нибудь это уже делал?
Часть записей я могу вытащить запросом с Limit итд, а как потом все это корректно отобразить в GUI, в частности придется отказаться от полосы прокрутки, ведь ее масштаб будет не на 70000 строк, а на количество записей в буфере...

Naisho · 2 Фев 2016

посмотрите на официальном форуме тему "Virtual listviews for huge number of rows" - работает очень быстро
тема - https://www.autoitscript.com/forum/topic/169101-virtual-listviews-for-huge-number-of-rows/#comment-1234533

pvnn · 4 Фев 2016

посмотрите на официальном форуме тему "Virtual listviews for huge number of rows" - работает очень быстро

Naisho, спасибо. Очень хорошая статья. Немного подшаманил и все получилось.
Правда, если писать запросы с Like, то наверное придется создавать представление в БД CREATE VIEW, чтобы узнать количество записей, которое необходимо обновить в виртуальном ListView

Код:

#include <Timers.au3>
#include <SQLite.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>
#include <GUIListBox.au3>
#include <WindowsConstants.au3>
#include <GuiListView.au3>

_SQLite_Startup()
If @error Then Exit MsgBox(16, "Ошибка SQLite", "Не удалось загрузить SQLite.dll")

$sFileDB = @ScriptDir & '\Data.db'
$TableName = 'table1'
Global $sSQL ; Неполный Текст SQL-запроса
; Для отображения в Виртуальном ListView. Структура $tagNMLVCACHEHINT используется для вставки строки в массиве кэша, прежде чем отобразиться с сообщениями $LVN_GETDISPINFO (WM_NOTIFY)
Global  $tagNMLVCACHEHINT = $tagNMHDR & ";int iFrom"
Global $Flag=0,$Sort


; Создание тестовой БД
;~  FileDelete(@ScriptDir & '\Data.db') ; Чтобы пересоздать БД
If Not FileExists($sFileDB) Then
	$Row=70000
	$hStarttime=_Timer_Init()
	$Line='дарановтрансовковранвичыээысывжыхгорsdcsd'
	$sExec = 'BEGIN; Create Table table1 ([ID] INTEGER PRIMARY KEY AUTOINCREMENT, [f_1] TEXT,[f_2] TEXT,[f_3] TEXT,[f_4] TEXT,[f_5] TEXT );'
	; Составить строку данных
	For $i=0 To $Row-1
		$ind=Random(5,25,1)
		$sData=StringMid($Line,$ind)
		$sExec &= 'INSERT INTO '&$TableName&' (f_1,f_2,f_3,f_4,f_5) VALUES ("' & $sData&'","'&$sData&'","'&$sData&'","'&$sData&'","'&$sData& '");'
	Next
	$sExec &= 'COMMIT;'
	ConsoleWrite(@CRLF&'Создание строки: '&_Timer_Diff($hStarttime)/1000&@CRLF)
	; Заполнить таблицу
	$hStarttime=_Timer_Init()
	$hDB = _SQLite_Open($sFileDB)
	_SQLite_Exec( -1, "PRAGMA synchronous = OFF;" ) ; sqlite не будет ждать подтверждения записи
	_SQLite_Exec($hDB, $sExec)
	_SQLite_Close($hDB)
	ConsoleWrite(@CRLF&'Добавление записей в БД: '&_Timer_Diff($hStarttime)/1000&@CRLF)
EndIf


; GUI
$Form1 = GUICreate("Form1", 803, 457, -1, -1)
$Button1 = GUICtrlCreateButton("Запрос", 8, 416, 75, 25)
Global $hListView= GUICtrlCreateListView("", 8, 24, 785, 383, BitOR($LVS_OWNERDATA,$LVS_SHOWSELALWAYS))
_GUICtrlListView_SetExtendedListViewStyle(-1, BitOR($LVS_EX_GRIDLINES, $LVS_EX_FULLROWSELECT, $LVS_EX_SUBITEMIMAGES))
  $hLV = GUICtrlGetHandle( $hListView ) ;    Virtual listview    Reduces flicker
  _GUICtrlListView_AddColumn( $hLV, "Column1",  50 )
  _GUICtrlListView_AddColumn( $hLV, "Column2",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column3",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column4",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column5",  100 )
  _GUICtrlListView_AddColumn( $hLV, "Column6",  100 )

$hStarttime=_Timer_Init()
GUIRegisterMsg( $WM_NOTIFY, "WM_NOTIFY" )
GUISetState(@SW_SHOW)


; Составить SQL-запрос
Query()
;~ GUICtrlSendMsg( $hListView, $LVM_SETITEMCOUNT, $iRows, 0 ) ; Указать количество строк для отображения в виртуальном ListView


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
    Case $GUI_EVENT_CLOSE
         Exit
	Case $Button1
		GUICtrlSetState($Button1,$GUI_DISABLE)
		$hStarttime=_Timer_Init()
		Query()
		GUICtrlSetState($Button1,$GUI_ENABLE)
    EndSwitch
WEnd


Func Query()
	If $Flag=0 Then
		$Sort='ASC'
		$Flag=1
	Else
		$Sort='DESC'
		$Flag=0
	EndIf
	$sSQL='SELECT * FROM  table1 order by id '&$Sort
    $iRows = CheckDB() ; Количество строк
	If $iRows Then $hDB=_SQLite_Open($sFileDB)
	GUICtrlSendMsg( $hListView, $LVM_SETITEMCOUNT, $iRows, 0 ) ; Указать количество строк для отображения в виртуальном ListView
EndFunc

Func CheckDB()
	Local $aRow, $iRows = 0
		_SQLite_Open($sFileDB)
		_SQLite_QuerySingleRow( -1, 'SELECT COUNT(*) FROM '&$TableName&' LIMIT 1;', $aRow )
		_SQLite_Close( -1 )
	If IsArray( $aRow ) Then $iRows = $aRow[0] + 1
	Return $iRows ; Количество записей в таблице
EndFunc


Func WM_NOTIFY( $hWnd, $iMsg, $wParam, $lParam )
	Local Static $tText = DllStructCreate( "wchar[50]" )
	Local Static $pText = DllStructGetPtr( $tText )
	Local Static $aResult, $iRows, $iFrom
	Local $VisibleRow

	Local $tNMHDR, $hWndFrom, $iCode
	$tNMHDR = DllStructCreate( $tagNMHDR, $lParam )
	$hWndFrom = HWnd( DllStructGetData( $tNMHDR, "hWndFrom" ) )
	$iCode = DllStructGetData( $tNMHDR, "Code" )

  Switch $hWndFrom
    Case $hLV
      Switch $iCode

				Case $LVN_ODCACHEHINT ; $aResult является кэш массива. Цель кэша, чтобы ограничить число строк. Виртуальный ListView никогда не обновлять более видимой числа строк за один раз.
					Local $tNMLVCACHEHINT = DllStructCreate( $tagNMLVCACHEHINT, $lParam ), $iColumns
					$iFrom = DllStructGetData( $tNMLVCACHEHINT, "iFrom" )
					$VisibleRow=_GUICtrlListView_GetCounterPage($hListView) ; Вычисляет количество пунктов, которые могут вертикально поместиться в видимой области
					Local $sSQL2=$sSQL&' limit '&$VisibleRow&' OFFSET '&$iFrom& ';'
					_SQLite_GetTable2d( -1, $sSQL2, $aResult, $iRows, $iColumns )


				Case $LVN_GETDISPINFOW
					Local $tNMLVDISPINFO = DllStructCreate( $tagNMLVDISPINFO, $lParam )
					If BitAND( DllStructGetData( $tNMLVDISPINFO, "Mask" ), $LVIF_TEXT ) Then
						Local $iIndex = DllStructGetData($tNMLVDISPINFO, "item") - $iFrom + 1   ; Номер строки
						Local $nCol = DllStructGetData($tNMLVDISPINFO, "subitem") 			 	; Номер столбца
						If $iIndex > 0 And $iIndex < $iRows + 1 Then
							Local $sItem = $aResult[$iIndex][$nCol]
							DllStructSetData( $tText, 1, $sItem )
							DllStructSetData( $tNMLVDISPINFO, "Text", $pText )
							DllStructSetData( $tNMLVDISPINFO, "TextMax", StringLen( $sItem ) )
						EndIf
					EndIf

      EndSwitch
 EndSwitch
  Return $GUI_RUNDEFMSG
EndFunc

Автор темы	Похожие темы	Форум	Ответы	Дата
P	Элементы GUI Виртуальный список (ListView) на основе базы данных (SQLite), как покрасить по условию	Общие вопросы по AutoIt	0	28 Окт 2022
T	Как перехватить SQL-запросы к БД SQLite	Общие вопросы по AutoIt	2	18 Июл 2020
S	Работа с базой sqlite через ftp возможно?	Общие вопросы по AutoIt	5	22 Июл 2019
	Работа с Sqlite	Общие вопросы по AutoIt	3	4 Мар 2019
V	Существует лив autoIt реализация SQLite функции Count(*)?	Общие вопросы по AutoIt	1	1 Сен 2018

Поиск

Поиск

SQLite как увеличить скорость _SQLite_GetTable2d

pvnn

Осваивающий

inververs

AutoIT Гуру

pvnn

Осваивающий

Naisho

Знающий

pvnn

Осваивающий