Devart Forums

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?

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.

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

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