Performance issues with fetching UDT's

Performance issues with fetching UDT's

Postby 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
Posts: 1
Joined: Tue 25 Mar 2014 12:37

Re: Performance issues with fetching UDT's

Postby 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.
Devart Team
Posts: 2062
Joined: Wed 02 Nov 2011 09:44

Return to dotConnect for Oracle