FrancescoDiMuro

SQLite Query for populating combobox

7 posts in this topic

Good evening guys :)

I am working on a little project, in which I have to retrieve 8000+ rows of data from a table, from a SQLite database, to populate a combobox :)

This is what I tried 'til now, but it still takes about 12 seconds to populate the combobox.

_SQLite_Exec($objDatabase, "BEGIN TRANSACTION;")
If _SQLite_GetTable($objDatabase, "SELECT DISTINCT Comune FROM LISTA_COMUNI;", $arrRisultatoQuery, $intRighe, $intColonne) = $SQLITE_OK Then
    For $i = 2 To UBound($arrRisultatoQuery) - 1
        If $i < UBound($arrRisultatoQuery) - 1 Then
            GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i] & "|")
        Else
            GUICtrlSetData($cbo_ComuneNascita, $arrRisultatoQuery[$i])
        EndIf
    Next
_SQLite_Exec($objDatabase, "COMMIT;")

Are there any other solution to retrieve 8000+ records from a SQLite database?

Thank you very much :) 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

Hello. Use _GUICtrlComboBox_AddString is faster than GUICtrlSetData

 

Saludos

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

@FrancescoDiMuro,

Remove BEGIN and COMMIT. You don't need to enclose a single select (or any other single SQL statement BTW) in a transaction. SQLite will do that for you under the hood.

Now why is selecting distinct values so slow? Do you have duplicates in your table? If yes you should remove extra copies. If you don't have duplicate rows but only several rows for the same "Comune" column, then the design is flawed: you shouldn't duplicate data like that. Use a separate table and foreign keys for that.

To make you understand what I mean, imagine you have a table of all streets in a country. A bad design would be:
Id, Region, City, Street
where Region and City will be repeated for every street.

A better design will use a Regions table, a Cities Table and a Streets table.

Regions:
RegionId, RegionName (Unique)

Cities:
CityId, CityName (Unique in a given region)

Streets:
Id, RegionId, CityId, StreetName
where RegionId is a foreign key to the Regions table and CityId a foreign key to the Cities table.

Of course this is just a simplified example to fix ideas. This process of avoiding data duplication is called DB normalization.


Besides these considerations, you can also use the power of SQL to return you a single string with all the data and delimiters in place, for instance:

_SQLite_QuerySingleRow($objDatabase, "select group_concat(Comune, '|') FROM (select distinct comune from LISTA_COMUNI) group by null;", $Row)

On my table of 75377 zip codes for 32 countries, this request takes 300ms on my very slow PC. This way you can feed $Row to the combobox all in once. Yet, I personally suspect that a combobox with 8000+ entries is definitely unpractical, but that is up to you.

Edited by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Good morning @jchd:)

The table I have does not contain more than a couple of duplicated rows...

What I'd like to have, is retrieve all the data from "Comune", and display them in a combobox, in order to let the user select what he wants to.

Do you have any pratical suggestion on how to display so many rows, and let select just one of them, in order to manage the data selected?

For SQLite: I can try with what you did suggest to me... But then, I have to split data, am I right? :)

Thank you a lot :) 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites

I find it irritating when I'm offered a choice in some overpopulated, endless and boring listbox of combobox.

Restricting the choices to the few which loosely match user input seems preferable. I've made available an SQLite extension DLL named "Unifuzz" which offers a number of Unicode string and collations functions, among which a typos() function returning the Damerau-Levenshtein distance between two strings. I often use it this way, loosely adapted to what I understand of your context:

select distinct comune from comuni where typos(comune, $input) < 3 order by comune;

This returns all distinct comune that are 4 character change away from the string in variable $input. For instance, searching cities in Belgium,
select distinct ville from codespostaux where paysiso = 'BE' and typos(ville, 'böan') < 3 order by ville;
returns:

Ville
BAAL
BOHAN
BOOM
BOST
BRA
BRAS
BRAY
DOHAN
MEAN
MOEN
ON


You can see that allowing as little as two typos already grabs a fair but manageable number of plausible choices. This function works by first folding case and accents on the whole BMP unicode range. Sorry I don't have a list of italian comuni to examplify.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

_ArrayToString is a possibility but using the power of fast SQL function will always reveal faster than UDF code.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • AdamUL
      By AdamUL
      I have been testing AutoIt 3.3.14.3 with SQLite, and the Help File examples.  The examples that I have tested are throwing errors or not doing anything.  I have sqlite3.dll, sqlite3_x64.dll, and sqlite3.exe in the directories with the testing script.  I am copying the examples directly from the help file into a test script for testing with no edits.  The _SQLite_GetTable2d example is returning an error ("Library used incorrectly") with each _SQLite_Exec command.  The _SQLite_FastEncode example returns an empty dialog box.  The _SQLite_Exec example only prints out the SQLite version, and nothing else in the SciTE console.  Currently, I'm still searching for what is causing this issue.  I'm on Windows 7 Enterprise 64-bit.  Is anyone else having this issue?  
       
      Adam
       
    • Xandy
      By Xandy
      I have a bunch of SDL_Surfaces loaded into memory.  I want to list them in a ComboBox.
      Using this code, I can load images to the combobox from file but not from existing SDL_Surface memory
      CODE ISN"T MEANT TO RUN
      ; Create combobox pic list example $aControl[$iControl_id][$eControl_data] = _GUICtrlComboBoxEx_Create($gui, $data_value, $data_x, $data_y, $data_w, $data_h, $CBS_DROPDOWNLIST) ; Image List for ComboboxEx Local $hImage = _GUIImageList_Create($gTile_w, $gTile_h, 6) ; Fill hImage with SDL_Surfaces stored in memory: aNPC_surf[scale][type][way][frame] For $i = 0 To 1 ; This works but only from file: ;_GUIImageList_AddBitmap($hImage, $gFolder_graphics & $gaWorld_info[$player.iWorld_cur][$eWi_filename] & "Tiles\" & $i & ".bmp") ; I've broken up a sprite sheet and want to insert into combobox from memory ;_GUIImageList_Add($hImage, $aNPC_surf[1][1][1][$i]) _GUIImageList_Add($hImage, _GDIPlus_BitmapCreateFromMemory($aNPC_surf[1][1][1][$i]), True) ;_GDIPlus_BitmapCreateFromMemory($aNPC_surf[1][1][1][$i]) ; Add the index number to combobox item _GUICtrlComboBoxEx_AddString($aControl[$eNPC_iPic_type][$eControl_data], $i, $i, $i) Next ; Set hImage list to combobox control _GUICtrlComboBoxEx_SetImageList($aControl[$eNPC_iPic_type][$eControl_data], $hImage) Anyone know if I can convert from SDL_Surface to hBitmap?  Maybe I'm doing something else wrong.
       
      I've seen hBitmap converted to SDL_Surface but I don't really understand it yet: 
       
      My full script can be found here:
       
    • Burgs
      By Burgs
      Greetings,
        I have SQLite setup within my AutoIT program...I'm trying to accomplish what should be a relatively simple task.  I want to be able to return an array of 'table' names for an established database...I believe this might be possible using the '_SQLite_SQLiteExe' command...since it seems to be able to access SQLite schemas...?  The ".tables" command is one of them...if I am not mistaken that command returns a list of all table names in the active database.
        I am attempting the following:
      #include <SQLite.au3> #include <SQLite.dll.au3> Global $hDb, $sIn, $sOut ... $sIn = ".tables" & @CRLF _SQLite_SQLiteExe($hDb, $sIn, $sOut) if @error == 0 Then ;Show Table (using SQLite3.dll) Else if @error == 2 Then ConsoleWrite("ERROR: Sqlite3.exe file not found" & @CRLF) Else ConsoleWrite("ERROR: @error=" & @error & " when calling _SQLite_SQLiteExe" & @CRLF) EndIf ;@error is "2"...OR NOT... EndIf ;@error is "0"...OR NOT... ...   The error being thrown is "ERROR: Sqlite3.exe file not found" ...
        Am I required to have the Sqlite3.exe installed in my directory (i.e. @ScriptsDir)...???  I do not have it in there at present because I did not believe it was necessary with the 'include' calls to "SQLite.au3" and "SQLite.dll.au3"...any advice appreciated.  Thanks in advance. 
      Regards
       
       
       
       
       
       
       
    • odaylton
      By odaylton
      I even understand that the Handle generated by _WinAPI_LoadCursor is not an Icon but how to display the image in the Combobox
      Here is the listing and please tell me how to make the current user current cursor image uncertainty
      #include <GUIConstantsEx.au3> #include <GuiImageList.au3> #include <GuiListView.au3> #include <WinAPI.au3> #include <WindowsConstants.au3> ;dados de mouse #include <WinAPIRes.au3> ;COMBO #include <GuiComboBoxEx.au3> Example() Func Example() Local $hGUI, $hImage, $hCombo Local $idListview, $hImage Local $sWow64 = "" Local $Pasta="C:\WINDOWS\Cursors\3dgarro.cur" $hGUI = GUICreate("ImageList Mouse Icons", 400, 300) $hCombo = _GUICtrlComboBoxEx_Create($hGUI, "", 2, 2, 394, 100) $idListview = GUICtrlCreateListView("", 2, 104, 394, 200, BitOR($LVS_SHOWSELALWAYS, $LVS_NOSORTHEADER, $LVS_REPORT)) _GUICtrlListView_SetExtendedListViewStyle($idListview, BitOR($LVS_EX_FULLROWSELECT, $LVS_EX_GRIDLINES, $LVS_EX_DOUBLEBUFFER)) GUISetState(@SW_SHOW) ; Load images $hImage = _GUIImageList_Create(16, 16) ;teste _GUIImageList_AddIcon($hImage, $Pasta) ;<<<<<<<<<<<<<<<<<<<<<this $hIcon= _WinAPI_CopyCursor(_WinAPI_LoadCursor(0,$IDC_HAND));32649 ; Hand cursor _GUIImageList_AddIcon($hImage, $hIcon);<<<<<<<<<<<<<<<<<< erro for $i=1 to 15 $hIcon= _WinAPI_CopyCursor(_WinAPI_LoadCursor(0, $i));<<<<<<<<<<<<<<<<< _GUIImageList_AddIcon($hImage, $hIcon);<<<<<<<<<<<< Next _GUIImageList_AddIcon($hImage, $Pasta) _GUICtrlListView_SetImageList($idListview, $hImage, 1) _GUICtrlComboBoxEx_SetImageList($hCombo, $hImage) ; Add columns _GUICtrlListView_AddColumn($idListview, "Items", 120) ; Add items $a=StringSplit("testeIcone|HAND|APPSTARTING|ARROW|CROSS|HELP|IBEAM|ICON|NO|SIZE|SIZEALL|SIZENESW|SIZENS|SIZENWSE|SIZEWE|UPARROW|WAIT|testefim","|") For $i=1 to $a[0] _GUICtrlListView_AddItem($idListview, $a[$i], $i-1) _GUICtrlComboBoxEx_AddString($hCombo, $a[$i], $i-1) Next ; Loop until the user exits. Do Until GUIGetMsg() = $GUI_EVENT_CLOSE GUIDelete() EndFunc ;==>Example Note that only the initial image is uncertain because it comes from a file
      And the one that would be the last enters as the second
      And the background is black how to transform into transparent
      imagem de cursor.bmp
    • Ian_Mac
      By Ian_Mac
      hello, i just wanted to ask if how........
       I have a gui combo box with a list and i wanted it to remember the last item that i selected, that when i close the gui and then open the gui again, my last selected item will be automatically show in the combobox as selected item already.
      thank you in advance.