Profiling evolution

[gcr]

Hi,

I have tested a little program.
This program :

- connect to Oracle
- use a 'Select' Query
- Use function First in Query object
- check recordcount
- loop for all record with Next function.


The code is

procedure TForm1.Button1Click(Sender: TObject);
var
ZConnection1: TZConnection;
Query1: TZQuery;
Datasource1: TDatasource;
I: Integer;
ICount: Integer;
// Time: TDateTime;
begin
screen.Cursor := crSQLWait;
ZConnection1 := TZConnection.Create(Self);
ZConnection1.Protocol := 'oracle';
ZConnection1.Database := 'HPNDCW5';
ZConnection1.User := 'pnd';
ZConnection1.Password := 'pnd';
ZConnection1.Connect ;

Query1 := TZQuery.Create(Self);
Query1.Connection := ZConnection1;
Query1.Active := false;
Label2.Caption := 'Before Select : ' + TimeToStr(Time);
Query1.SQL.Clear;
Query1.SQL.Add('select NOMSCR,CHPSCR,IDTENT,CHPDIC from cwd16') ;
Query1.Active := true;
Label3.Caption := 'After Select : ' + TimeToStr(Time);

Query1.First;
Label4.Caption := 'After First: ' + TimeToStr(Time);
ICount := Query1.RecordCount;
Label5.Caption := 'After RecordCount: ' + TimeToStr(Time);
for I := 0 to ICount do
begin

// Label1.Caption := Query1.Fields[0].Text;
Query1.Next;

end;
Label6.Caption := 'After for : ' + TimeToStr(Time);


{ Datasource1 := TDatasource.Create(Self) ;
Datasource1.AutoEdit := false;
Datasource1.Enabled := true;
Datasource1.DataSet := Query1;
DBGrid1.DataSource := Datasource1;
}
screen.Cursor := crDefault;

end;

The result give :

Before Select : 9:36:51
After Select: 9:36:51
After First: 9:36:51
After RecordCount: 9:38:02
After for: 9:38:02




I don't understand the elapsed time for the recordcount function.
This is problematic because DBGrid and datasource means using recordcount. It's not possible to receive this value on select function ?

Thank you
gilles

[mdaems]

Record counting implies fetching all rows... This makes it a bad idea to get all records. I read some times setting filtering to true

It could maybe be avoided if Oracle OCI has a way of getting the number of result rows directly? No idea about that. Maybe it would require some redesign of the general design.

A separate select has some serious disadvantages :
- Query time doubles.
- Parsing problems when making the count(*) query
- Doesn't work for stored procedures returning resultsets (I know that sounds like madness to an oracle dev but other databases do support it)

Mark

[gcr]

Hi,


If i understand correctly, the 'query.active := true' not receive all record and only the fetch give records ?

[mdaems]

The 'First' call is a fetch already. That doesn't read all records. So if you simply loop using first + next until EOF you read only record by record.
It's the recordcount which starts fetching all the records.

[gcr]

With Oracle, it's possible to find this recordcount without fetch all records .


[equote]

OCIAttrGet(ps_stmt, OCI_HTYPE_STMT, &nbr_rows, (ub4*) sizeof(ub4),
OCI_ATTR_ROW_COUNT, pherrd);

[/equote]


The slower process is on Fetch.
When i have big records, the execution time is more longer than BDE.

It's not really a problem, i need just create query with some optimisation.
Some time, a 'compute analysis table' with Oracle is just done.

thank you
gilles