Recent

Author Topic: TDBLookUpComboBox, slow execution on Large Dataset  (Read 3621 times)

valdir.marcos

  • Hero Member
  • *****
  • Posts: 665
Re: Slow execution on TSQLQuery
« Reply #15 on: February 08, 2019, 03:50:38 pm »
I don't know firebird that well, but exceptionally slow queries are sometimes related with leaving transactions open.
Correct.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 665
Re: Slow execution on TSQLQuery
« Reply #16 on: February 08, 2019, 03:54:49 pm »
Can't restart database server, it is a live database.
I do have another application, same remote database, using TSQLQuery attached to DBGrid, it runs normal.
The difference is, this query does not change sql statement during run time, so no close and re open again the query.
The bad smelling of misleading transaction control is increasing...

incendio

  • Jr. Member
  • **
  • Posts: 51
Re: Slow execution on TSQLQuery
« Reply #17 on: February 11, 2019, 04:51:05 am »
I have removed all GUI control, left only TSQLQuery, and tried again.

It runs well, although not as fast as I would expect, but i think it was OK, only 1-2 secs to complete.

The problem is, when linked to TDBLookupCombobox, open & close Query is slow.

Closing the app when Query linked to to TDBLookupCombobox also slow, took a couple of secs.

Set these properties on TDBLookupCombobox
  • ScrollistDataset = True
  • ListSource
  • ListField
  • KeyField
« Last Edit: February 11, 2019, 05:18:35 am by incendio »

rvk

  • Hero Member
  • *****
  • Posts: 3641
Re: Slow execution on TSQLQuery
« Reply #18 on: February 11, 2019, 09:38:49 am »
What happens if you keep the TDBLookupCombobox disconnected and put a
Code: Pascal  [Select]
  1. TSQLQuery.Active := true; // or TSQLQuery.Open;
  2. TSQLQuery.Last;
  3. TSQLQuery.First;
  4.  
in there?

If you get the delay again, TDBLookupCombobox just reads all records and put's it in the pulldown.

(But then I'm puzzled as to why Flamerobin can still read all those records in a second)

incendio

  • Jr. Member
  • **
  • Posts: 51
Re: Slow execution on TSQLQuery
« Reply #19 on: February 11, 2019, 09:51:59 am »
You were right, after query last & first, there was a delay. So, it seem that TSQLQuery is rather slow when loading large data.

Same query in Flamerobin, load in second.

I will try again the same query with C++ Builder and see the result.

rvk

  • Hero Member
  • *****
  • Posts: 3641
Re: Slow execution on TSQLQuery
« Reply #20 on: February 11, 2019, 09:54:32 am »
Same query in Flamerobin, load in second.
How many records are fetched in FlameRobin (which you can see in the statusbar)?

It also might not be TSQLQuery that is slow with .Last / .First. But it can also be the TDBGrid.
Try disconnecting the TDBGrid and do the same.

incendio

  • Jr. Member
  • **
  • Posts: 51
Re: Slow execution on TSQLQuery
« Reply #21 on: February 11, 2019, 10:19:56 am »
Tried it with C++ Builder & third party VCL from IBDAC.

The speed is about the same with flamerobin, load in sec.

Records in flamerobin's status bar shows 11922.

So, it seem that TSQLQuery, for now is rather slow.

Well, at least, there is a room for improvement.

I will try Lazarus 2, but not in near time. One on my app in Lazarus use a package, and not sure if this package will runs OK in ver 2.

rvk

  • Hero Member
  • *****
  • Posts: 3641
Re: Slow execution on TSQLQuery
« Reply #22 on: February 11, 2019, 10:21:48 am »
So, it seem that TSQLQuery, for now is rather slow.
What happens if you disconnect the TDBGrid (any any other component, like a TDataSource etc)?
It's important to know if TSQLQuery is the problem or some visual component.

TSQLQuery could be substituted by Zoeslib for instance.

incendio

  • Jr. Member
  • **
  • Posts: 51
Re: Slow execution on TSQLQuery
« Reply #23 on: February 11, 2019, 10:36:20 am »
TSQLQuery only attached to TDatasource. I will try again with no attachment at akk when I am back to my computer. 
« Last Edit: February 11, 2019, 10:39:39 am by incendio »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 665
Re: Slow execution on TSQLQuery
« Reply #24 on: February 11, 2019, 11:07:18 am »
So, it seem that TSQLQuery, for now is rather slow.
What happens if you disconnect the TDBGrid (any any other component, like a TDataSource etc)?
It's important to know if TSQLQuery is the problem or some visual component.
TSQLQuery could be substituted by Zoeslib for instance.
TSQLQuery could also be replaced by IBX for Lazarus, which is specialized on Firebird access:
IBX 2.3.2 is now available for download
http://forum.lazarus.freepascal.org/index.php/topic,43456.0.html

incendio

  • Jr. Member
  • **
  • Posts: 51
Re: Slow execution on TSQLQuery
« Reply #25 on: February 12, 2019, 02:46:39 am »
Tested again to Open TSQLQuery, and here are the result :
  • TSQLQuery not connected to any GUI controls (or connected just to a TDataSource), almost instantly
  • Same condition with point 1, but after Open, executed command Last and First, took about 1-2 secs
  • Attached to TDBLookupComboBox (without executed Last & First), took about 4 secs.

Also, when TSQLQuery attached to TDBLookupComboBox, there was a delay when closing app.

TSQLQuery could also be replaced by IBX for Lazarus, which is specialized on Firebird access:
IBX 2.3.2 is now available for download
http://forum.lazarus.freepascal.org/index.php/topic,43456.0.html

Tested with IBX 2.3.3, the result was the same.

Same codes, compile in Linux Mint 64, when TSQLQuery was not attached to TDBComboBox, runs normal, but when it was attached, runs worse, took almost 13 secs to close query and almost 10 secs to open it.

rvk

  • Hero Member
  • *****
  • Posts: 3641
Re: Slow execution on TSQLQuery
« Reply #26 on: February 12, 2019, 10:16:12 am »
Tested again to Open TSQLQuery, and here are the result :
  • TSQLQuery not connected to any GUI controls (or connected just to a TDataSource), almost instantly
  • Same condition with point 1, but after Open, executed command Last and First, took about 1-2 secs
  • Attached to TDBLookupComboBox (without executed Last & First), took about 4 secs.
So 1. is the same as FlameRobin (or even faster because nothing visual is present)
2. is the same as FlameRobin (because Fetch all also takes a second there)
3. FlameRobin doesn't have a TDBLookupComboBox.

As I expected the TDBLookupComboBox is the problem.
You could try running outside the IDE and check if that makes a difference.

But the big question is... WHY do you have a TDBLookupComboBox with over 12.000 items in the dropdown?


The major problem with TDBLookupComboBox (and maybe TDBComboBox too?) is in TDBLookup.FetchLookupData (which the component uses when it becomes active).

Code: Pascal  [Select]
  1.   try
  2.     //needed to handle sqldb.TSQLQuery that does not has a reliable recordcount after Open
  3.     ListLinkDataSet.Last;
  4.     ListLinkDataSet.First;
  5.     SetLength(FListKeys, ListLinkDataSet.RecordCount);
  6.     KeyListCount := 0;
  7.     while not ListLinkDataSet.EOF do
  8.     begin
  9.       KeyIndex := FControlItems.Add(FListField.DisplayText);
  10.       //check if item was really added (in sorted list duplicate values are not added)
  11.       if FControlItems.Count > KeyListCount then
  12.       begin
  13.         if KeyIndex < KeyListCount then
  14.           SlideKeyList(FListKeys, KeyIndex, KeyListCount);
  15.         FListKeys[KeyIndex] := ListLinkDataSet.FieldValues[FKeyFieldNames];
  16.         Inc(KeyListCount);
  17.       end;
  18.       ListLinkDataSet.Next;
  19.     end;
  20.     SetLength(FListKeys, KeyListCount);
  21.  

It iterates through the complete dataset to get the keyvalues.

incendio

  • Jr. Member
  • **
  • Posts: 51
Re: Slow execution on TSQLQuery
« Reply #27 on: February 12, 2019, 10:27:53 am »
In C++ builder I used TDBLookupComboBox where it load thousands of Firebird data without any problem, so does in flamerobin.

I thought it won't be a problem too in Lazarus, apparently I was wrong.

There is an inefficiency in TDBLookupComboBox in Lazarus, especially in Linux OS.
I will avoid using it from now.

Hope Lazarus team could improve this in the future. Version 2.0.0 is also slow.

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 6928
Re: Slow execution on TSQLQuery
« Reply #28 on: February 12, 2019, 10:37:30 am »
Please file a bug.

rvk

  • Hero Member
  • *****
  • Posts: 3641
Re: Slow execution on TSQLQuery
« Reply #29 on: February 12, 2019, 10:54:13 am »
In C++ builder I used TDBLookupComboBox where it load thousands of Firebird data without any problem, so does in flamerobin.
I thought it won't be a problem too in Lazarus, apparently I was wrong.
There is an inefficiency in TDBLookupComboBox in Lazarus, especially in Linux OS.
Yes, in Delphi it is a lot faster too.
I'm not sure why Lazarus builds a FListKeys internally, with all the values. This is very inefficient, especially with large datasets.

I will avoid using it from now.
Hope Lazarus team could improve this in the future. Version 2.0.0 is also slow.
Like Macro suggested, please file a bug report in the bugtracker. Otherwise it won't get fixed.

O, wait. I see it is already reported in 2014.
https://bugs.freepascal.org/view.php?id=26008
But the discussion there seems to have died down.