Performance issues with fetching UDT's

Performance issues with fetching UDT's

Postby chloeff » Tue 25 Mar 2014 14:21

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


Return to dotConnect for Oracle