'FireDAC: possibility to make query faster - Delphi
I use bind variables in Delphi and on the other side there is Oracle database with Database link (@dblink). When I build a SELECT statement without bind variables, it has no problem with performance. Only if I query SELECT statement with bind variables it takes very long (sometimes 1-2 hour). Is it possible in FireDAC to make the query faster without changing SQL-query? I need to use bind variables to avoid SQL injection.
This SQL is very Fast in Delphi:
SELECT
DLGH_START_D Datum,
TRUNC((d.DLGH_ENDE_D - d.DLGH_START_D) * 24 * 60)||' Min '||
TRUNC(MOD(((d.DLGH_ENDE_D - d.DLGH_START_D) * 24 * 3600), 60))||' Sek' Dauer
FROM
dialoghistory d
WHERE
d.DLGH_PARAMETER_C = 'Name of Parameter' AND <--
d.dlgh_funktion_c = 'SQLS' AND
d.DLGH_START_D > '01.02.2020' <--
order by 1
This SQL is very slow in Delphi:
SELECT
DLGH_START_D Datum,
TRUNC((d.DLGH_ENDE_D - d.DLGH_START_D) * 24 * 60)||' Min '||
TRUNC(MOD(((d.DLGH_ENDE_D - d.DLGH_START_D) * 24 * 3600), 60))||' Sek' Dauer
FROM
dialoghistory d
WHERE
d.DLGH_PARAMETER_C = :B_Name AND <--
d.dlgh_funktion_c = 'SQLS' AND
d.DLGH_START_D > :Datum <--
order by 1
---------------------------
//slow execution period , because of bind variables (1 h)
qry := TFDQuery.CreateSQL(Application, sSqlText_.Text);
with qry do begin
...
Param.AsString := value; //set value of bind variable
...
Open;
Solution 1:[1]
My Solution:
tmpQuery := TFDQuery.Create(self); // Create Query
tmpQuery.Sql.Text := 'SELECT * FROM dlgHistory d where d.DLGH_PARAMETER = :par';
...
// Set Data Type, Param Type and Size yourself
with tmpQuery.Params do begin
Clear;
with Add do begin
Name := 'par';
DataType := ftString;
Size := 128;
ParamType := ptInput;
end;
end;
tmpQuery.Params[0].AsString := 'Value'; //assign a value
tmpQuery.Prepare;
tmpQuery.Open; //And it works perfect!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Niko |

