Devart Forums

Posted: **Tue 12 Feb 2008 23:16**

While using multiple queries per one TOraSession:

Q1: Which case is more optimal ?
Q2: What resources are consumed on the server in each case ?
Q3: What resources are consumed on the client in each case ?
Q4: Any suggestions to make the more optimal case even more efficient ?

Regards,
Horace

CASE A:

Code: Select all

procedure PrepareQueriesA();
begin

  OraQuery1 := TOraQuery.Create(self);
  OraQuery1.Session := mOraSession;
  OraQuery1.FetchAll :=TRUE;
  OraQuery1.SQL.Text := 'SELECT name FROM emp WHERE name >= :name';
  if (OraQuery1.Session.Connected=FALSE) Then
    OraQuery1.Session.Connect;
  OraQuery1.Prepare; 
  
  OraQuery2 := TOraQuery.Create(self);
  OraQuery2.Session := mOraSession;
  OraQuery2.FetchAll :=TRUE;
  OraQuery2.SQL.Text := 'SELECT dob FROM emp WHERE dob >= :dob';
  if (OraQuery2.Session.Connected=FALSE) Then
    OraQuery2.Session.Connect;
  OraQuery2.Prepare; 
    
  OraQuery3 := TOraQuery.Create(self);
  OraQuery3.Session := mOraSession;
  OraQuery3.FetchAll :=TRUE;
  OraQuery3.SQL.Text := 'SELECT salary FROM emp WHERE salary >= :salary';
  if (OraQuery3.Session.Connected=FALSE) Then
    OraQuery3.Session.Connect;
  OraQuery3.Prepare;
 
end;
    


function DoQueryA(SortKey : Byte): Boolean;
begin  
      
  case SortKey of
  
  1:  begin
        OraQuery1.ParamByName('name').AsString := GlobalNameStart; 
        OraQuery1.Open; 
        While Not EOF Do
        begin
          result :=  result XOR ProcessRecordAsStr(...);
          Next;
        end; 
      end;
         
  2:  begin
        OraQuery2.ParamByName('dob').AsDate := GlobalDateStart; 
        OraQuery2.Open; 
        While Not EOF Do
        begin
          result :=  result XOR ProcessRecordAsDate(...);
          Next;
        end; 
      end;
         
  3:  begin
        OraQuery3.ParamByName('salary').AsInteger := GlobalSalaryStart; 
        OraQuery3.Open; 
        While Not EOF Do
        begin
          result := result XOR ProcessRecordAsInt(...);
          Next;
        end; 
      end;
         
    else              
      result := FALSE;
    
  end;  //case

end;

CASE B : (shorter code)

Code: Select all

procedure PrepareQueriesB();
begin

  OraQuery1 := TOraQuery.Create(self);
  OraQuery1.Session := mOraSession;
  OraQuery1.FetchAll :=TRUE;
  if (OraQuery1.Session.Connected=FALSE) Then
    OraQuery1.Session.Connect;

end;
    


function DoQueryB(SortKey : Byte): Boolean;
begin  
      
  case SortKey of
  
  1:  begin
        OraQuery1.SQL.Text := 'SELECT name FROM emp WHERE name >= :name';
        OraQuery1.Prepare; 
        OraQuery1.ParamByName('name').AsString := GlobalNameStart; 
        OraQuery1.Open; 
        While Not EOF Do
        begin
          result :=  result XOR ProcessRecordAsStr(...);
          Next;
        end; 
      end;
         
  2:  begin
        OraQuery1.SQL.Text := 'SELECT dob FROM emp WHERE dob >= :dob';
        OraQuery1.Prepare; 
        OraQuery1.ParamByName('dob').AsDate := GlobalDateStart; 
        OraQuery1.Open; 
        While Not EOF Do
        begin
          result :=  result XOR ProcessRecordAsDate(...);
          Next;
        end; 
      end;
         
  3:  begin
        OraQuery1.SQL.Text := 'SELECT salary FROM emp WHERE salary >= :salary';  
        OraQuery1.Prepare; 
        OraQuery1.ParamByName('salary').AsInteger := GlobalSalaryStart; 
        OraQuery1.Open; 
        While Not EOF Do
        begin
          result := result XOR ProcessRecordAsInt(...);
          Next;
        end; 
      end;
         
  else              
    result := FALSE;
    
  end;  //case

end;

Posted: **Wed 13 Feb 2008 08:50**

Usually if you place TOraQuery components on the form, it is more convenient to place separate TOraQuery component for each SQL statement. But if you setup TOraQuery components in the code, it is more convenient to use one TOraQuery component for all SQL statements.

So I recommend Case B.

In Case A queries can be opened slightly faster because they are already prepared. In Case B there is no sence to call Prepare method because a query will be unprepared when you change SQL text. But there is no significant difference in performance.

Resources on the server are consumed only when you open a query. In both cases you have only one opened query at a time. So in both cases the same server resources are used. Do not forget to close the query when you have reached EOF, to free the resources.

Resources on the client are consumed more in Case A because you have three prepared query. But there are no much resources required for that.

Posted: **Wed 13 Feb 2008 13:33**

Plash,

Thanks for a good answer.

Everything seems to be clear except the "prepare" issue:

Plash wrote:In Case B there is no sense to call Prepare method because a query will be unprepared when you change SQL text.

I thought that "prepare" ensured that the bind variables (parameters) are sent to the server separately from the SQL statement and soft-parsed on the server.

I want the Oracle Server to perform the variable substitiution (not in ODAC as literals !) without replanning the query (a.k.a. hardparsing).
For an example see:
http://www.oracle-base.com/articles/mis ... iables.php

Q: So how does "prepare" relate to bind variables and soft-parsing vs. hard-parsing queries, on the Oracle Server ?

Regards,
Horace

Posted: **Thu 14 Feb 2008 08:50**

Prepare method is not related to bind variables. ODAC never insert values of bind variables (parameters) in the text of SQL statement. Values of parameters are sent separately. So soft parsing is enabled.

Prepare method is used to allocate some data structures on the client side that are need to execute statement. SQL statement is also parsed on the client when you call Prepare method.
If you don't call Prepare method, ODAC calls it by itself before executing a statement, and after execute ODAC calls UnPrepare to free resorces allocated by Prepare.
If you call Prepare manually, or set Prepared property to True, or set AutoPrepare option to True, ODAC does not call UnPrepare after execute. So when you execute this statement next time, preparation is not required because the statement is already prepared. This is useful when you execute a statement many times in a loop with different parameters' values.

Posted: **Thu 14 Feb 2008 22:54**

At what point are the values of parameters sent to the server then ?

Plash wrote:Prepare method is not related to bind variables....Values of parameters are sent separately

Posted: **Fri 15 Feb 2008 12:46**

Parameters' values are sent to the server on executing the statement (call to Execute or Open methods). At the same time ODAC sends also the text of SQL statement. But values of parameters are not embeded to the SQL. They are sent separately.

Posted: **Fri 15 Feb 2008 17:15**

Thank you Plash.
I consider this topic closed.

Regards,
Horace

Devart Forums

Multiple Queries in one TOraSession

Multiple Queries in one TOraSession