Why ODAC is slower than ADO?

Discussion of open issues, suggestions and bugs regarding ODAC (Oracle Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
changhunkim
Posts: 1
Joined: Thu 17 Mar 2016 01:32

Why ODAC is slower than ADO?

Post by changhunkim » Thu 17 Mar 2016 01:48

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?

MaximG
Devart Team
Posts: 1093
Joined: Mon 06 Jul 2015 11:34

Re: Why ODAC is slower than ADO?

Post by MaximG » Fri 18 Mar 2016 14:01

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.

Afrin
Posts: 1
Joined: Wed 25 Apr 2018 17:42

Re: Why ODAC is slower than ADO?

Post by Afrin » Wed 25 Apr 2018 17:48

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

MaximG
Devart Team
Posts: 1093
Joined: Mon 06 Jul 2015 11:34

Re: Why ODAC is slower than ADO?

Post by MaximG » Fri 27 Apr 2018 14:23

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

Post Reply