Performance issues with a large number of UDT's

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Posts: 5
Joined: Fri 24 Aug 2012 07:20

Performance issues with a large number of UDT's

Post by cosullivan » Wed 12 Dec 2012 02:28


We have recently switched from ODP.NET to dotConnect but have noticed quite a large difference in performance when dealing with a large number of UDT's as a parameter to a stored procedure.

When comparing ODP.NET with dotConnect we get the following results (time in seconds to complete the command);

3,300 Objects, dotConnect = 1.9s, ODP.NET 2.6s
6,600 Objects, dotConnect = 3.7s, ODP.NET 3.7s
9,900 Objects, dotConnect = 5.9s, ODP.NET 4.4s
13,200 Objects, dotConnect = 9.1s, ODP.NET 5.0s
16,500 Objects, dotConnect = 27.7s, ODP.NET 5.9s
19,800 Objects, dotConnect = 45.9s, ODP.NET 7.5s
23,100 Objects, dotConnect = 72.1s, ODP.NET 7.5s
26,400 Objects, dotConnect = 91.3s, ODP.NET 9.7s
29,700 Objects, dotConnect = 113.8s, ODP.NET 10.9s
33,000 Objects, dotConnect = 135.0s, ODP.NET 11.3s

Note that our comparisson consisted of a parent/child relationship with the UDT where the child nested table contained 33 rows. Both method were near identical code (with the difference being related to the implementation of ODP.NET v dotConnect) and the stored procedure that was being called effectively did nothing.

As you can see with the performance, dotConnect was faster for small amounts of data but at a certain point the performance of dotConnect degraded in a linear fashion.

When running a profiler over the application it appears to spend the majority of the time making calls to the OCIObjectSetAttr method.

Do you have any suggestions? We are using the 7.3 express edition for testing.

Cain O'Sullivan

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

Re: Performance issues with a large number of UDT's

Post by Pinturiccio » Tue 18 Dec 2012 15:20

Could you please create and send us a small test project with the corresponding DDL/DML scripts of your UDTs and stored procedure for reproducing the issue?

Post Reply