Re: Censoring SQL access -- ideas?

[CRPence <CRPbottle@xxxxxxxxx>]

On 3/17/11 8:09 AM, Charles Wilt wrote:
> Me neither...learned something new today...but I'd bet Tommy's
> correct about the performance concerns...
>
> Multi-row fetch (and insert) would help, but I'd bet you'd have to
> fetch quite a few rows to overcome the overhead of reconnecting.
>
> <<SNIP>>

  The SET CONNECTION is neither a CONNECT nor a DISCONNECT so there is 
essentially little of "reconnecting" involved; most of the overhead of 
CONNECT was already expensed, much like returning to an already opened 
file to request additional position or read requests from the existing 
ODP obtained during prior open processing similarly already expensed. 
To set\switch the connection from one open connection to another open 
connection is just telling the SQL processor which one of the 
already-existing-connections is to be used.  Thus the SET CONNECTION 
should not, beyond normal cost of any statement processing, involve much 
more than setting a pointer to the local storage previously allocated 
for that specific existing remote connection environment named on the 
SET CONNECTION, such that any upcoming SQL requests are properly 
directed there.  So while there is a definite benefit in using multiple 
row FETCH to limit the relatively low cost of the SET activity, the 
bigger benefit from using a bigger fetch buffer would typically come 
from having packaged more data over the wire on each FETCH, thus 
reducing communication\conversation overhead for those fetches performed 
over the the [remote] connection.

  I could not test what I presume above, but should be easy enough to 
see the difference.  Remove the INSERT from the test code, then run 
twice.  Then also remove the SET CONNECTION requests and run twice. 
Compare\contrast the second run from both tests.

Regards, Chuck