Performance issues with fetching UDT's

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Posts: 1
Joined: Tue 25 Mar 2014 12:37

Performance issues with fetching UDT's

Post by chloeff » Tue 25 Mar 2014 14:21


in our application we are fetching User-Defined Types through Cursors like this:

Code: Select all

Select Test_Type(empno, hiredate) from emp
We have observed a large performance difference compared to the statements without UDT's:

Code: Select all

Select empno, hiredate from emp

This is the result of our tests with different fetch sizes in Oci mode and in Direct mode:

FetchSize: 50000

Oci UDT: 0.75 s
Oci No UDT: 0.11 s

Direct UDT: 0.51 s
Direct No UDT: 0.12 s

FetchSize: 250000

Oci UDT: 3.57 s
Oci No UDT: 0.51 s

Direct UDT: 2.51 s
Direct No UDT: 0.66 s

FetchSize: 1250000

Oci UDT: 16.74 s
Oci No UDT: 3.06 s

Direct UDT: 12.21 s
Direct No UDT: 3.23 s

As you can see, the performance differs by factor 4 (Direct mode) to 6 (Oci mode).
Do you have any suggestions? We are using Oracle and dotConnect for Oracle 8.3.115

Christoph Löffler

Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Performance issues with fetching UDT's

Post by Pinturiccio » Fri 28 Mar 2014 16:07

This is designed behaviour. Using User Defined Types takes more time than queries without UDTs. Other providers have the same behavior. For example you may test is with ODP.NET.

Post Reply