-1

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;

Table dialoghistory looks like this Table dialoghistory

Niko
  • 69
  • 7
  • What is the actual query? What is the query plan when bind variables are used? What is the query plan when literals are used? What version(s) of Oracle are being used? – Justin Cave Feb 02 '22 at 09:59
  • query is very complex and it uses Database link. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 and Delphi 10.2. About plan I could not say anything, because I am beginner in SQL. – Niko Feb 02 '22 at 10:10
  • Since there is a database link, there are two databases involved. Are both source and target database are Oracle 19 Enterprise? Unfortunately, without knowing the query, query plan(s), table structures, indexes, etc. it is pretty hard to guess what the problem (and more importantly the solution) might be. My assumption is that you end up with different plans because the optimizer is making different cardinality estimates but there are lots of different potential reasons for that and lots of potential ways to fix that (and will depend on things like how your DBAs manage plan evolution). – Justin Cave Feb 02 '22 at 10:37
  • 1
    This is also why we ask for a minimal reproducible example. If you have a 2000 line SQL statement that has a problem, work to strip out as much as you can that still reproduces the problem. If you can produce the same problem with, say, a 20 line SQL statement, that makes it much more likely that someone can help you (and help you quickly) rather than speculating wildly and generically (i.e. "maybe you could add a hint" rather than "here is a specific hint that fixes the specific problem you're having"). – Justin Cave Feb 02 '22 at 10:57
  • Justin I have an example, maybe it explains a bit better my problem :) – Niko Feb 02 '22 at 13:03
  • 1
    Great! Is `dialoghistory` a local table? Or a synonym to a table on a remote machine? A view? Something else? What are the query plans of the two queries? What are the data types of the three columns in your `where` clause? How many rows in the table? How many match each predicate in the `where` clause for the values you provided? How many distinct values of each column are there? – Justin Cave Feb 02 '22 at 13:15
  • Yes, it is local Table. dialoghistory looks like this on screenshot. – Niko Feb 02 '22 at 13:34
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/241650/discussion-between-niko-and-justin-cave). – Niko Feb 02 '22 at 13:55

1 Answers1

0

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!
Niko
  • 69
  • 7