Re: Censoring SQL access -- ideas?

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