FrancescoDiMuro

SQLite Query for populating combobox

7 posts in this topic

#1 ·  Posted

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



#2 ·  Posted

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

#4 ·  Posted

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

#5 ·  Posted

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

#7 ·  Posted

_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

    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning
      I'm working on a little project, and I was wondering if there's a way ( sure there is ) to insert data to a ComboBox control everytime the user insert some text in the ComboBox which is not in the ComboBox data. I'll try to explain with an example:
      - Form with some data ( ComboBox is empty );
      - User prompt something in the ComboBox: if what the user prompts is not in the ComboBox values ( at the moment empty ), then insert the value in the ComboBox values, in order to have, next time the user prompts the form, the value that he entered.
      More pratical example:
      - First call of the form, ComboBox empty;
      - I prompt "A" in the ComboBox;
      - Second call, in the ComboBox I should see "A";
      - I prompt "B" in the ComboBox;
      - Third call, I should see "A"
                                                 "B";
      - And so on...
      Everytime the form is called, I re-create it, so I think I can't use _GUICtrlComboBox_AddStrings().
      I tried with
      Global $strCboStrings = "" ; When the user prompt the form, the code below is executed. ; I.E. : User prompt "A", after the if I should have "A"; ; In the second call, If I write "B", I should see "A" ; "B"; ; If I write "A" again, in the combobox values should not be any changes. If Not StringInStr($strCboStrings, GUICtrlRead($cboVoiceCategory)) <> 0 Then $strCboStrings &= $strCboStrings & "|" & GUICtrlRead($cboVoiceCategory) EndIf Any suggestion?
      Thanks
    • kstriyhon
      By kstriyhon
      hello Comunity i am new to autoit and i bump into a pitfall regarding the creation of objects in a dinamyc way but more especific  creating combobox at runtime
      i need many comboboxes and sofar i createdthem manually 
      like this 
      $idCombo_1 = GUICtrlCreateCombo("", 500, 40, 130, 21)     $idCombo_2 = GUICtrlCreateCombo("", 500, 70, 130, 21)     $idCombo_3 = GUICtrlCreateCombo("", 500, 100, 130, 21)     $idCombo_4 = GUICtrlCreateCombo("", 500, 130, 130, 21)     $idCombo_5 = GUICtrlCreateCombo("", 500, 160, 130, 21)     $idCombo_6 = GUICtrlCreateCombo("", 500, 190, 130, 21)     $idCombo_7 = GUICtrlCreateCombo("", 500, 220, 130, 21)     $idCombo_8 = GUICtrlCreateCombo("", 500, 250, 130, 21)     $idCombo_9 = GUICtrlCreateCombo("", 500, 280, 130, 21)     GUICtrlSetData($idCombo_1, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_2, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_3, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_4, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_5, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_6, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_7, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_8, "Item1|Item2|Item3|Item4|Item5")     GUICtrlSetData($idCombo_9, "Item1|Item2|Item3|Item4|Item5") now i need this example but create them at runtime as there are more than 9 combos what i need thanks in advance kind regards
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning community!
      I am working on a script which read from a text file ( .txt ) and should import all the content in a SQLite3 DB, in order to execute some queries that should be difficult to execute on a text file.
      So, I was looking for something very very fast, because the file could be very large ( I don't know exaclty how much can became big, but I know a lot of rows, it's a log file ... )
      I found the "Import method", but I don't know If I can implement it in a query ( @jchd, it's your turn!  )
      Do you know some methods that I can implement in my script to have a very very fast import of thousands and thousands rows in a SQLite3 DB?
      Thanks a lot
      Francesco
    • FrancescoDiMuro
      By FrancescoDiMuro
      Good morning guys
      I was trying to use again SQLite in AutoIt, and I've again an issue: I can't startup SQLite...
      Can you please show me the way to set the enviornment for a SQLIte usage in AutoIt? Step by step, I'll follow your suggestion(s).

      Thanks for everyone will reply!

      Francesco
    • satanico64
      By satanico64
      Hi huys !
       how are you ? family ? dog, cat ? well..
       
      I've got a problem: _GUICtrlComboBox_GetCueBanner
      i can't get it to work.
      Simple: even the example from help does'nt work...
       
      I insist, it's excactly the example from the help, no modifications
      #include <GuiComboBox.au3> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> Global $g_idMemo Example() Func Example() Local $idCombo ; Create GUI GUICreate("ComboBox Get Count", 400, 296) $idCombo = GUICtrlCreateCombo("", 2, 2, 396, 296) _GUICtrlComboBox_SetCueBanner($idCombo, "Select an Item") $g_idMemo = GUICtrlCreateEdit("", 10, 50, 376, 234, $WS_VSCROLL) GUICtrlSetFont($g_idMemo, 9, 400, 0, "Courier New") GUISetState(@SW_SHOW) ; Add files _GUICtrlComboBox_BeginUpdate($idCombo) _GUICtrlComboBox_AddDir($idCombo, @WindowsDir & "\*.exe") _GUICtrlComboBox_EndUpdate($idCombo) MemoWrite("Cue Banner: " & _GUICtrlComboBox_GetCueBanner($idCombo)) ; Loop until the user exits. Do Until GUIGetMsg() = $GUI_EVENT_CLOSE GUIDelete() EndFunc ;==>Example ; Write a line to the memo control Func MemoWrite($sMessage) GUICtrlSetData($g_idMemo, $sMessage & @CRLF, 1) EndFunc ;==>MemoWrite I also added the display of the cuebanner in the loop.
      It never display what actually appears in the combo
      If you can show me any king of working example.
      Thanks Guys !
      Nicolas.
      Actually autoit v3.3.14.2