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;