TEST enviroment :
ODAC 9.6.22 (Full Source License by purchasing and using)
XE6(delphi20)
oracle 11g
TEST Method:
Test Program is a call to Simple SQL
ADO(dbgo Component) Response Time : 129 milisecond
ODAC Direct Mode : 481 milisecond
Why ODAC is slower than ADO?
Why is that?
Do not have a workaround?
Why ODAC is slower than ADO?
Re: Why ODAC is slower than ADO?
We have checked performance of ODAC in comparison with ADO. The following table was used in our internal test:
CREATE TABLE BATCH_TEST
(
ID NUMBER(9,0),
F_INTEGER NUMBER(9,0),
F_FLOAT NUMBER(12,7),
F_STRING VARCHAR2(250),
F_DATE DATE,
CONSTRAINT PK_BATCH_TEST PRIMARY KEY (ID)
)
We populated it with random data — 25 000 rows at all. The test is a sequential loop through all the records in the dataset:
var
RecordCount: Integer;
begin
...
ADOQuery.Open;
while not(ADOQuery.EOF) do
begin
Inc(RecordCount);
ADOQuery.Next;
end;
Finally, we got the following performance results:
ADO (OCI Mode) : 5.28 sec
ODAC (Direct Mode) : 0.05 sec
For the further investigation of the issue with performance, please send us a script for creating the table used in your sample, as well as the project you used to check performance.
CREATE TABLE BATCH_TEST
(
ID NUMBER(9,0),
F_INTEGER NUMBER(9,0),
F_FLOAT NUMBER(12,7),
F_STRING VARCHAR2(250),
F_DATE DATE,
CONSTRAINT PK_BATCH_TEST PRIMARY KEY (ID)
)
We populated it with random data — 25 000 rows at all. The test is a sequential loop through all the records in the dataset:
var
RecordCount: Integer;
begin
...
ADOQuery.Open;
while not(ADOQuery.EOF) do
begin
Inc(RecordCount);
ADOQuery.Next;
end;
Finally, we got the following performance results:
ADO (OCI Mode) : 5.28 sec
ODAC (Direct Mode) : 0.05 sec
For the further investigation of the issue with performance, please send us a script for creating the table used in your sample, as well as the project you used to check performance.
Re: Why ODAC is slower than ADO?
Would you be so kind to please post basic optimization parameters used in your Oracle testbed installation as this time is unachievable by even our production server with looping thru 25k records via .next with control disabled .. it would help a lot as most of us are not really professional oracle admins and the bottleneck in performance may be incorrect oracle db settings and optimization. I do understand those are very app specific and per server, but any pro tips would be greatly appreciated. Thanks
Re: Why ODAC is slower than ADO?
In our test environment, we do not use any specific Oracle server settings. Perhaps the speed of ODAC is related to the use of LOB fields referred in your request. In this case, check whether the DeferredLobRead option is set to True ( https://www.devart.com/odac/docs/devart ... obread.htm )
If it is not so, to investigate the speed problem we need a DDL script to create a table whose speed of data retrieving is unsatisfactory for you, and a sample of its filling with test data
If it is not so, to investigate the speed problem we need a DDL script to create a table whose speed of data retrieving is unsatisfactory for you, and a sample of its filling with test data