0

I have a million records in one of my database table which my FDQuery is pointed to. Here's my SQL query:

SELECT DISTINCT variant FROM item_var
JOIN item ON item_var.item_id = item.list_id
WHERE item.brand = :itemselected
ORDER BY variant                              //I did not put a limit because the data here very limited say maximum  10 only

The parameter ":itemselected" is to be triggered by my TListView1 selected item.

The result of this query is populate to be populated to my TListView2 with this code:

if dmMain_u.dmMain.qryVariant.RecordCount >= 0 then
  begin
    with dmMain_u.dmMain do
    begin
      qryVariant.Active := False;
      qryVariant.Params.ParamByName('itemselected').AsString := lsvItems.Items[lsvItems.ItemIndex].Text;
      qryVariant.Active := True;
    end;
  end;

I am using livebindings for both TListViews and my database is SQLite embedded local.

The above codes seems to taking a while to populate the data to my TListView2 (like 5 seconds). I am hoping that you guys have better options to share. Perhaps, my codes are not efficient or optimize enough.

Juke
  • 135
  • 2
  • 9
  • 1
    What column type is `brand` in your `item` table and is the `item` table indexed on `brand`? – MartynA May 15 '20 at 11:09
  • Brand column type is "Text" in sqlite and no, I haven't indexed it in the item table. – Juke May 15 '20 at 11:13
  • 1
    Well, it is an elementary aspect of database design that any column that you use to access db rows (both to look rows up and join tables togeth) should be indexed, otherwise there will likely be performance problems just like the one you are asking about. Read and act on this article very carefully, in particular Section 1.7, 'Covering indices' : https://www.sqlite.org/queryplanner.html – MartynA May 15 '20 at 11:17
  • Thank you so much. It's way way better now. I'll read more about indices now. Thank you you answered this question! – Juke May 15 '20 at 11:24
  • Good, I will post it as an answer, with a bit more explanation, in a few minutes. – MartynA May 15 '20 at 11:25
  • @MartynA Can you post your answer please so I can mark this as answered. Thanks. – Juke May 15 '20 at 11:34

1 Answers1

1

It's a general rule of database design that any column that are used to access db rows (both to look rows up and join tables together) should be indexed. This is so that the server (or server process in the case of Sqllite) can find the relevant rows without having to examine all the rows in the table (aka a 'table scan'), which coud be hugely inefficient where only a small proportion of the rows in the table are relevant to (i.e. match) the requirenents of the task at hand. Without suitable indices, there will likely be performance problems just like the one you are asking about.

Most db engines, include a 'query optimiser' which tries to find the most efficient way to perform a query and one of the main strategies which they use is to look up matching rows using an index against the table(s) involved, as this can greatly reduce the number of rows which need to be read from the table. In some cases, known as 'covered queries', the db-engine does not even need to read the table rows at all, because it can retrieve all the information it needs (including the column data requested by the query) from an index on the table.

This is all explained with reference specifically to Sqlite in Section 1.7, 'Covering indices' of this article: sqlite.org/queryplanner.html

MartynA
  • 30,454
  • 4
  • 32
  • 73
  • 'Covering Indices' is very interesting topic. I am surprised that I can index not only the input field but also the output field as well and skip the original table scanning already. – Juke May 15 '20 at 12:05