I use TSQLQuery to connect to remote Firebird 3.0.3 database.If possible, update Lazarus to the latest stable version.
Lazarus ver 1.8.4 on Windows 7 pro.
Here is the sql for TSQLQueryIf possible, evolve your code syntax from SQL-89 standard to something better readable.
SELECT a.BRCD,b.ALS_NM || ' ' || c.ALS_CL || ' ' || a.SZ as art from M_FNGD_VW a, M_ART b, M_CLR c where b.ID = a.ID_ART and c.ID = a.ID_CLR and b.ALS_NM is not NULL and c.ALS_CL is not NULL and a.UNT = 'PSG' order by 2,1
It took about 5 secs to execute that query.Both of them should have a similar performance.
On flamerobin, the same query , execute about 0.5 sec, so, it is 10x faster.
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.
Is this normal?No, 10x difference in performance between FlameRobin and TSQLQuery is not acceptable at all.
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.
It took about 5 secs to execute that query.It can also depend on what else you do with the TSQLQuery.
On flamerobin, the same query , execute about 0.5 sec, so, it is 10x faster.
It took about 5 secs to execute that query. On flamerobin, the same query , execute about 0.5 sec, so, it is 10x faster.
Please, prepare a small sample example so we can understand what you are doing as default.
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.
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".
There is a TDBLookupComboBox attached to the query and yes it is unidirectional.Try to disconnect all visual components.
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.
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.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 you your real project.And normally, when you are preparing such small sample project, you find the problem yourself 8-)
Are you sure transactions are being committed as soon as possible?It can also depend on what else you do with the TSQLQuery.TSQLQuery connected to TDBLookupComboBox.
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.
Not doing anything just Close and Open, but, the SQL statement change when user clicked a checkbox.
Code for checkbox clicked
if (checkbox .Checked = True) then begin Qry.Close; Qry.SQL.Clear; Qry.SQL.Add('select field1,field2 from table1'); Qry.Open; end else begin Qry.Close; Qry.SQL.Clear; Qry.SQL.Add('Select field1,field2 from table2'); Qry.Open; end;
I use Lazarus with Firebird 3.0 on a daily basis and I can't confirm that slowness, so far.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 don't know firebird that well, but exceptionally slow queries are sometimes related with leaving transactions open.Correct.
Can't restart database server, it is a live database.The bad smelling of misleading transaction control is increasing...
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.
Same query in Flamerobin, load in second.How many records are fetched in FlameRobin (which you can see in the statusbar)?
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)?
TSQLQuery could also be replaced by IBX for Lazarus, which is specialized on Firebird access: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
Tested again to Open TSQLQuery, and here are the result :So 1. is the same as FlameRobin (or even faster because nothing visual is present)
- 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.
In C++ builder I used TDBLookupComboBox where it load thousands of Firebird data without any problem, so does in flamerobin.Yes, in Delphi it is a lot faster too.
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.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.
Hope Lazarus team could improve this in the future. Version 2.0.0 is also slow.
O, wait. I see it is already reported in 2014.Should I report it again?
https://bugs.freepascal.org/view.php?id=26008
But the discussion there seems to have died down.
So, the problem is from TDBLookUp, I will change thread title then.No, you should collaborate in the discussion in the already existent bug report.O, wait. I see it is already reported in 2014.Should I report it again?
https://bugs.freepascal.org/view.php?id=26008
But the discussion there seems to have died down.