Performance issues with fetching UDT's

[chloeff]

Hi,

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 11.2.0.3.0 and dotConnect for Oracle 8.3.115

Thanks,
Christoph Löffler

[Pinturiccio]

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.