Lazarus

Programming => LCL => Topic started by: incendio on February 07, 2019, 03:33:45 am

Title: TDBLookUpComboBox, slow execution on Large Dataset
Post by: incendio on February 07, 2019, 03:33:45 am
Hi guys,

I use TSQLQuery to connect to remote Firebird 3.0.3 database.
Lazarus ver 1.8.4 on Windows 7 pro.

Here is the sql for TSQLQuery
Code: Pascal  [Select]
  1. SELECT a.BRCD,b.ALS_NM || ' ' || c.ALS_CL || ' ' || a.SZ as art
  2. from M_FNGD_VW a, M_ART b, M_CLR c
  3. where b.ID = a.ID_ART
  4. and   c.ID = a.ID_CLR
  5. and   b.ALS_NM is not NULL
  6. and   c.ALS_CL is not NULL
  7. and   a.UNT = 'PSG'
  8. order by 2,1
  9.  

It took about 5 secs to execute that query.

On flamerobin, the same query , execute about 0.5 sec, so, it is 10x faster.

TSQLQuery properties left to default except for SQl statement.

Is this normal? Or is there any properties that should be set to speed up the process?

EDIT Feb 02, 2019
----------------------
Jump to page 2-3 for summary of the problem
Title: Re: Slow execution on TSQLQuery
Post by: valdir.marcos on February 07, 2019, 09:21:36 am
I use TSQLQuery to connect to remote Firebird 3.0.3 database.
Lazarus ver 1.8.4 on Windows 7 pro.
If possible, update Lazarus to the latest stable version.

Quote
Here is the sql for TSQLQuery
Code: Pascal  [Select]
  1. SELECT a.BRCD,b.ALS_NM || ' ' || c.ALS_CL || ' ' || a.SZ as art
  2. from M_FNGD_VW a, M_ART b, M_CLR c
  3. where b.ID = a.ID_ART
  4. and   c.ID = a.ID_CLR
  5. and   b.ALS_NM is not NULL
  6. and   c.ALS_CL is not NULL
  7. and   a.UNT = 'PSG'
  8. order by 2,1
If possible, evolve your code syntax from SQL-89 standard to something better readable.

Quote
It took about 5 secs to execute that query.
On flamerobin, the same query , execute about 0.5 sec, so, it is 10x faster.
Both of them should have a similar performance.

Quote
TSQLQuery properties left to default except for SQl statement.
Please, prepare a small sample example so we can understand what you are doing as default.

Quote
Is this normal?
No, 10x difference in performance between FlameRobin and TSQLQuery is not acceptable at all.

Quote
Or is there any properties that should be set to speed up the process?
We need to check what you are doing before guessing what can be wrong.
Title: Re: Slow execution on TSQLQuery
Post by: rvk on February 07, 2019, 09:55:42 am
It took about 5 secs to execute that query.
On flamerobin, the same query , execute about 0.5 sec, so, it is 10x faster.
It can also depend on what else you do with the TSQLQuery.
Is it connected to a TDBGrid?
Are you doing anything else than Active := true or Open?
How many records are there total for that query?

Flamerobin only reads the first few result from the dataset. Only when you scroll to the end of the grid, new records are loaded. If you have a TSQLQuery.Last and TSQLQuery.First in your code, it can explain why it's so much slower.

Flamerobin should use the same index and PLAN as your query so that indexes shouldn't make that difference.

Title: Re: Slow execution on TSQLQuery
Post by: sash on February 07, 2019, 10:13:15 am
It took about 5 secs to execute that query. On flamerobin, the same query , execute about 0.5 sec, so, it is 10x faster.

How exactly did you measure these numbers?
What about stats on Prepare/Execute/Fetch times?
Do you have enabled controls / dataset events / calculated fields / master-detail sources, something else attached to TSQLQuery?
Is it unidirectional?
How much rows returned? Is there fetch (or SQL) limits in both cases?

Without these details it's pretty useless to calculate "faster-slower times".
Title: Re: Slow execution on TSQLQuery
Post by: incendio on February 08, 2019, 09:53:23 am
Please, prepare a small sample example so we can understand what you are doing as default.

It is a connection to a remote server, so kind of difficult to give a sample project. I am sure if it was local connection, it will be fast.

It can also depend on what else you do with the TSQLQuery.
Is it connected to a TDBGrid?
Are you doing anything else than Active := true or Open?
How many records are there total for that query?

Flamerobin only reads the first few result from the dataset. Only when you scroll to the end of the grid, new records are loaded. If you have a TSQLQuery.Last and TSQLQuery.First in your code, it can explain why it's so much slower.

Flamerobin should use the same index and PLAN as your query so that indexes shouldn't make that difference.

TSQLQuery connected to TDBLookupComboBox.
Not doing anything just Close and Open, but, the SQL statement change when user clicked a checkbox.

Code for checkbox clicked
Code: Pascal  [Select]
  1. if (checkbox .Checked = True) then
  2. begin
  3.    Qry.Close;
  4.    Qry.SQL.Clear;
  5.    Qry.SQL.Add('select field1,field2 from table1');
  6.    Qry.Open;
  7. end
  8. else
  9. begin
  10.    Qry.Close;
  11.    Qry.SQL.Clear;
  12.    Qry.SQL.Add('Select field1,field2 from table2');
  13.    Qry.Open;
  14. end;
  15.  

It took about 5 secs to execute that query. On flamerobin, the same query , execute about 0.5 sec, so, it is 10x faster.

How exactly did you measure these numbers?
What about stats on Prepare/Execute/Fetch times?
Do you have enabled controls / dataset events / calculated fields / master-detail sources, something else attached to TSQLQuery?
Is it unidirectional?
How much rows returned? Is there fetch (or SQL) limits in both cases?

Without these details it's pretty useless to calculate "faster-slower times".

I measured manually. Use F5 and F8 to step over through the codes. Don't know about Prepare/Execute/Fetch times, only calculated the whole time.

There is a TDBLookupComboBox attached to the query and yes it is unidirectional.

The records is about 12K rows x 2 columns, total width of columns is about 150 Varchar.

The same codes runs on Linux Mint 18, same version of Lazarus & Firebird, it was terribly slow.

On Object Inspector, only set this properties for TSQLQuery

Other properties remain unchanged.
Title: Re: Slow execution on TSQLQuery
Post by: rvk on February 08, 2019, 09:59:10 am
There is a TDBLookupComboBox attached to the query and yes it is unidirectional.
Try to disconnect all visual components.
Only try the TSQLQuery (NOT connected to anything but TIBConnection or TSQLConnector).
How is the speed then?

If you do Grid > Fetch all records in Flamerobin, you see the recordcount increasing at the bottom.
How long is it until all records are fetched?

Title: Re: Slow execution on TSQLQuery
Post by: incendio on February 08, 2019, 10:02:02 am
I will try it again when I'm back to my computer.

In flamerobin, fetch all took about a second.
Title: Re: Slow execution on TSQLQuery
Post by: rvk on February 08, 2019, 10:05:08 am
In flamerobin, fetch all took about a second.
If the recordcounter in the statusbar in Flamerobin is at 12K records in a second, then yes, there is definitely something wrong or inefficient in the Lazarus app.
Title: Re: Slow execution on TSQLQuery
Post by: marcov on February 08, 2019, 10:14:49 am
I don't know firebird that well, but exceptionally slow queries are sometimes related with leaving transactions open.

If you restart the database server, is the query fast for a while ?

Flamerobin might simply do autotransaction properly.
Title: Re: Slow execution on TSQLQuery
Post by: incendio on February 08, 2019, 10:17:10 am
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.
Title: Re: Slow execution on TSQLQuery
Post by: marcov on February 08, 2019, 12:56:13 pm
Use parameters to parametrize queries instead of editing them. Doing so frustrates preparation.

Still, that should only matter if you do really a lot of queries/s, not just one.
Title: Re: Slow execution on TSQLQuery
Post by: valdir.marcos on February 08, 2019, 03:37:06 pm
Please, prepare a small sample example so we can understand what you are doing as default.
It is a connection to a remote server, so kind of difficult to give a sample project. I am sure if it was local connection, it will be fast.
The idea of a small sample project is to check whether the slowness is related to Lazarus or to the way you use it, for instance in your real project.
Title: Re: Slow execution on TSQLQuery
Post by: rvk on February 08, 2019, 03:39:10 pm
Quote
The idea of a small sample project is to check whether the slowness is related to Lazarus or to the way you use it, for instance in you your real project.
And normally, when you are preparing such small sample project, you find the problem yourself  8-)
Title: Re: Slow execution on TSQLQuery
Post by: valdir.marcos on February 08, 2019, 03:44:29 pm
It can also depend on what else you do with the TSQLQuery.
Is it connected to a TDBGrid?
Are you doing anything else than Active := true or Open?
How many records are there total for that query?

Flamerobin only reads the first few result from the dataset. Only when you scroll to the end of the grid, new records are loaded. If you have a TSQLQuery.Last and TSQLQuery.First in your code, it can explain why it's so much slower.

Flamerobin should use the same index and PLAN as your query so that indexes shouldn't make that difference.
TSQLQuery connected to TDBLookupComboBox.
Not doing anything just Close and Open, but, the SQL statement change when user clicked a checkbox.

Code for checkbox clicked
Code: Pascal  [Select]
  1. if (checkbox .Checked = True) then
  2. begin
  3.    Qry.Close;
  4.    Qry.SQL.Clear;
  5.    Qry.SQL.Add('select field1,field2 from table1');
  6.    Qry.Open;
  7. end
  8. else
  9. begin
  10.    Qry.Close;
  11.    Qry.SQL.Clear;
  12.    Qry.SQL.Add('Select field1,field2 from table2');
  13.    Qry.Open;
  14. end;
Are you sure transactions are being committed as soon as possible?

Code: Pascal  [Select]
  1. SQLTransaction.Commit;
  2. Qry.Close;
  3. Qry.SQL.Clear;
  4. if (checkbox .Checked = True) then
  5. begin
  6.   Qry.SQL.Add('select field1,field2 from table1');
  7. end
  8. else
  9. begin
  10.   Qry.SQL.Add('Select field1,field2 from table2');
  11. end;
  12. Qry.Open;
Title: Re: Slow execution on TSQLQuery
Post by: valdir.marcos on February 08, 2019, 03:49:14 pm
In flamerobin, fetch all took about a second.
If the recordcounter in the statusbar in Flamerobin is at 12K records in a second, then yes, there is definitely something wrong or inefficient in the Lazarus app.
I use Lazarus with Firebird 3.0 on a daily basis and I can't confirm that slowness, so far.
Title: Re: Slow execution on TSQLQuery
Post by: valdir.marcos 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.
Title: Re: Slow execution on TSQLQuery
Post by: valdir.marcos 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...
Title: Re: Slow execution on TSQLQuery
Post by: incendio 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
Title: Re: Slow execution on TSQLQuery
Post by: rvk 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)
Title: Re: Slow execution on TSQLQuery
Post by: incendio 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.
Title: Re: Slow execution on TSQLQuery
Post by: rvk 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.
Title: Re: Slow execution on TSQLQuery
Post by: incendio 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.
Title: Re: Slow execution on TSQLQuery
Post by: rvk 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.
Title: Re: Slow execution on TSQLQuery
Post by: incendio 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. 
Title: Re: Slow execution on TSQLQuery
Post by: valdir.marcos 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
Title: Re: Slow execution on TSQLQuery
Post by: incendio on February 12, 2019, 02:46:39 am
Tested again to Open TSQLQuery, and here are the result :

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.
Title: Re: Slow execution on TSQLQuery
Post by: rvk 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.
Title: Re: Slow execution on TSQLQuery
Post by: incendio 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.
Title: Re: Slow execution on TSQLQuery
Post by: marcov on February 12, 2019, 10:37:30 am
Please file a bug.
Title: Re: Slow execution on TSQLQuery
Post by: rvk 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 (http://bugs.freepascal.org/view_all_bug_page.php?project_id=1). 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.
Title: Re: Slow execution on TSQLQuery
Post by: incendio on February 13, 2019, 03:22:35 am
So, the problem is from TDBLookUp, I will change thread title then.

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.
Should I report it again?
Title: Re: Slow execution on TSQLQuery
Post by: valdir.marcos on February 13, 2019, 07:25:56 am
So, the problem is from TDBLookUp, I will change thread title then.

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.
Should I report it again?
No, you should collaborate in the discussion in the already existent bug report.
Maybe, you can add extra information or examples to help solving that bug.