Performance issues with a large number of UDT's

[cosullivan]

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

[Pinturiccio]

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?