On Sun, 24 Mar 2002, Andrew Ho wrote: >>What would be ideal is if the database would allow you to change the >>user on the current connection. I know PostgreSQL will allow this >>using the command line interface psql tool (just do \connect >><database> <user>), but I'm not sure if you can do this using DBI. >> >>Does anyone know if any datbases support this sort of thing? > > This occurred to me in the case of Oracle (one of my co-workers was > facing a very similar problem in the preliminary stages of one of his > designs), and I actually had asked our DBAs about this (since the > Oracle SQL*Plus also allows you to change users). As I suspected (from > the similar "connect" terminology), our DBAs confirmed that Oracle > just does a disconnect and reconnect under the hood. I would bet the > psql client does the same thing.
First, I'll suggest that there are hopefully other areas you can look at optimizing that will get you a bigger bang for your time - in my test environment (old hardware), it takes 7.4 ms per disconnect/reconnect/rebind and 4.8 ms per rebind. Admittedly, I'm dealing with LDAP instead of SQL, and I've no idea how they compare. If the TCP connection were retained, this could still be a significant win. *Any* reduction in the connection overhead is an improvement. If there are a million connects per day, and this saves a milli-second per connect (believable to me, as at least three packets don't need to be sent - syn, syn ack, and fin. My TCP's a bit fuzzy, but I think there's a couple more, and there's also the mod_perl disconnect/reconnect overhead), that's over 15 minutes of response time and about 560,000,000 bits of network bandwidth (assuming the DB is not on the same machine) saved. Admittedly, at 100Mb/s, that's only 6 seconds. It may, in some cases, still be necessary to move access control from the DB into ones application, so one can maintain a single connection which never rebinds, but I think it's better to utilize the security in the DB instead of coding ones own - more eyes have looked over it. We're talking about a fairly small unit of time; it may very well be better to throw money if you are near your performance limit. Ed
