SQL Execution Overhead (Oracle ODP)

[Tim Smith]

I noticed some performance degradation on a section of my code and I
narrowed it down to calling Oracle for a sequence, which was executed
20 times or so.

Our current design we pull a connection from the pool (as opposed to
holding open a connection for the duration of that code) with each SQL
execution.

Writing a test program which loops 900 times, executing 'select
sequence_name.nextval from dual', using ODP net I see the following
average execution times

Connection from pool each select: 20 milliseconds
Hold connection open entire time: 15 milliseconds.

20 milliseconds does not seem long, but when you have 20 sequences
needed - that is almost 1/2 a second lost.

Sure I could change the design, but my question is whether 20 ms is a
reasonable time for something, which executed in a tight loop should
be extremely fast?

thanks

Tim

 

[Miha Markic [MVP C#]]

Hi Tim,

I noticed some performance degradation on a section of my code and I
narrowed it down to calling Oracle for a sequence, which was executed
20 times or so.

Our current design we pull a connection from the pool (as opposed to
holding open a connection for the duration of that code) with each SQL
execution.

Writing a test program which loops 900 times, executing 'select
sequence_name.nextval from dual', using ODP net I see the following
average execution times

Connection from pool each select: 20 milliseconds
Hold connection open entire time: 15 milliseconds.

20 milliseconds does not seem long, but when you have 20 sequences
needed - that is almost 1/2 a second lost.

Sure I could change the design, but my question is whether 20 ms is a
reasonable time for something, which executed in a tight loop should
be extremely fast?

If you have to do many selects in the row, you can use only one conneciton
and return it to the pool after you've done all selects.
Certainly, using pool has some minor performance hit, however the benefits
of pooling are far greater.

 

[David Browne]

I noticed some performance degradation on a section of my code and I
narrowed it down to calling Oracle for a sequence, which was executed
20 times or so.

Our current design we pull a connection from the pool (as opposed to
holding open a connection for the duration of that code) with each SQL
execution.

Writing a test program which loops 900 times, executing 'select
sequence_name.nextval from dual', using ODP net I see the following
average execution times

Connection from pool each select: 20 milliseconds
Hold connection open entire time: 15 milliseconds.

20 milliseconds does not seem long, but when you have 20 sequences
needed - that is almost 1/2 a second lost.

Sure I could change the design, but my question is whether 20 ms is a
reasonable time for something, which executed in a tight loop should
be extremely fast?

Yes 20ms is quite reasonable. If you want better speed, just grab all the
sequence values in one query:

select sequence_name.nextval id from all_objects where rownum <= 900

David