I use something that sounds similar. But why do you store users ids for a connection? The connection should be closed while the user is doing nothing. Only opened when db operations are being executed and close when done. This then takes advantage of connection pooling. Unclear how you use this.
To illustrate, I have a UserSessionManager, but it runs outside the database in code in memory. When the user logs in with user/pass, it obviously requires some authentication. In my case read(s) from the USER table. If authenticated, I create a SessionKey and then put this SessionKey and connectionstring and UserId into a dictionary. All subsequent calls to my services layer then takes the SessionKey as a parameter. The services layer call then looks up the SessionKey in the dictionary, therefore knows the UserId and connectionstring and creates and opens a new db connection. This would then come from the pool. As soon as the work in the db is done, the connection is closed and it goes back to the pool. The UserSessionManager can also time out sessions, i.e. drop stale SessionKeys from the dictionary. Everything is outside the DB so that I can remain DB independent… Louis From: [email protected] [mailto:[email protected]] Sent: 23 September 2015 11:43 AM To: [email protected] Subject: [firebird-support] RDB$Set_Context v GTT v Disk writes Hello, My middle tier application uses a pool of firebird connections to do its work. In order to identify users for audit purposes the application currently fills a global temporary table with the user id for a connection before doing any actual work. This results in an overhead of one transaction and one stored procedure call per client call to my stateless server. Using process monitor I can see that this equals 5 disk write operations. I have experimented with RDB$SET_CONTEXT as well and it is more efficient. It needs 3 disk writes for the equivalent operation. I'm guessing that these come from the transaction rather then the SET_CONTEXT call. Out of curiosity, and in a quest to minimise disk writes, is there a better way to do this? Is there some way to call RDB$SET_CONTEXT without a transaction? or is it feasible to implement some kind of in memory callback mechanism using UDF's. The idea being that the database will only call the udf when it needs to. Many thanks in advance. Will. [Non-text portions of this message have been removed]
