Recent

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

incendio

  • Jr. Member
  • **
  • Posts: 54
TDBLookUpComboBox, slow execution on Large Dataset
« 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
« Last Edit: February 13, 2019, 03:25:49 am by incendio »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 685
Re: Slow execution on TSQLQuery
« Reply #1 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.

rvk

  • Hero Member
  • *****
  • Posts: 3661
Re: Slow execution on TSQLQuery
« Reply #2 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.


sash

  • Full Member
  • ***
  • Posts: 233
Re: Slow execution on TSQLQuery
« Reply #3 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".
Lazarus 2.0.2 FPC 3.0.4 x86_64-linux-gtk2 -- Ubuntu 19.04 XFCE

incendio

  • Jr. Member
  • **
  • Posts: 54
Re: Slow execution on TSQLQuery
« Reply #4 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
  • Database
  • FieldDefs
  • SQL
  • Transaction

Other properties remain unchanged.

rvk

  • Hero Member
  • *****
  • Posts: 3661
Re: Slow execution on TSQLQuery
« Reply #5 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?


incendio

  • Jr. Member
  • **
  • Posts: 54
Re: Slow execution on TSQLQuery
« Reply #6 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.

rvk

  • Hero Member
  • *****
  • Posts: 3661
Re: Slow execution on TSQLQuery
« Reply #7 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.

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 6993
Re: Slow execution on TSQLQuery
« Reply #8 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.

incendio

  • Jr. Member
  • **
  • Posts: 54
Re: Slow execution on TSQLQuery
« Reply #9 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.
« Last Edit: February 08, 2019, 10:21:30 am by incendio »

marcov

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 6993
Re: Slow execution on TSQLQuery
« Reply #10 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.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 685
Re: Slow execution on TSQLQuery
« Reply #11 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.
« Last Edit: February 08, 2019, 03:55:52 pm by valdir.marcos »

rvk

  • Hero Member
  • *****
  • Posts: 3661
Re: Slow execution on TSQLQuery
« Reply #12 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-)

valdir.marcos

  • Hero Member
  • *****
  • Posts: 685
Re: Slow execution on TSQLQuery
« Reply #13 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;

valdir.marcos

  • Hero Member
  • *****
  • Posts: 685
Re: Slow execution on TSQLQuery
« Reply #14 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.