Performance issues with a large number of UDT's

Performance issues with a large number of UDT's

Postby cosullivan » Wed 12 Dec 2012 02:28

Hi,

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.

Thanks,
Cain O'Sullivan
cosullivan
 
Posts: 5
Joined: Fri 24 Aug 2012 07:20

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

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


Return to dotConnect for Oracle