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
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;
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.