The following SQL command returns approximately 4.500 records and contains integer-, string- and blob (text) values. All indexes are set properly. Furthermore we know that the in clause is not the best but that shouldn't bother us right now. The SQL command is executed on a firebird 3.0 server:
Select
distinct O.Nr, O.Betreff, O.Text, O.Absenderadresse, O.Gelesen, O.Datum, O.Ordner, O.TextNotiz, M.ID, R.PosNr as RPosNr, R.AddressType, R.Address, R.Name, A.Nr as AttachmentNr, M.Bezeichnung as Mailordner, 0 as Archived
from Table1 O
left join Table2 R on (R.Nr = O.IDNr)
join Table3 M on (M.Nr = O.Ordner)
and (M.PersonalNr=O.PersonalNr)
left join Table4 A on (A.Nr = O.IDNr)
where (O.PersonalNr = 12)
and O.Ordner in (608,7440,7441,7444,6144,7091,5467,617,2751,710,6580,2812,609,7054,7194,7479,614,620,7030,615,3434,4883,619,6465,7613)
We executed the SQL command in an external application (from which we know that this application uses the FIBPlus components) and in our very basic sample Delphi7 application (which uses the original AnyDAC database components Version 8.0.5). If we fetch all records into a grid, we have the following performance:
- External Application with FIBPlus ~ 200 – 400 milliseconds
- Delphi7 with AnyDAC ~ 3.500 – 4.500 milliseconds
In our Delphi7 program we have connected a TADQuery to it's own TADTransaction. The default settings are used for both components, except the ReadOnly property of the TADTransaction, which we changed to True
Now we wonder why the external application is approximately 10 times faster than our Delphi7 program? Are there any properties which we can modify to speed up our Delphi 7 program?
Any help is appreciated...