RE: FireDAC: Möglichkeit die Abfrage schneller zu machen? ( in Delphi)

[Dmitry Arefiev 99]

I saw this post in German Delphi-Praxis: https://www.delphipraxis.net/209869-firedac-moeglichkeit-die-abfrage-schneller-zu-machen-delphi.html
So, anybody, who have account on both forums can answer there ...

 

"Normally" constants vs parameters slowness is due to bad query execution plan when the parameters are in use. For example, Oracle (and not only) will stop to use indexes, when a parameter data type is not the same as a field one in a WHERE clause. This is because a type conversion is required, even if it looks "obvious". For example, a field is VARCHAR2 and a parameter is defined as ftWideString (eg, assigning it using Value / AsWideString instead of AsAnsiString). There are more examples ...

From Oracle doc (https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#i3305):

Quote

 

Even if the schemas are the same, the optimizer can choose different execution plans if the costs are different. Some factors that affect the costs include the following:

• Data volume and statistics

• Bind variable types and values

• Initialization parameters - set globally or at session level

PS: As always, a Delphi version will be good to know ...

Edited February 3, 2022 by Dmitry Arefiev