In my web app I have a HandleSpecialURL that checks for some special URLs and then calls different methods depending on whats been asked for.
At present each method opens a connection to the database but I was wondering what other thing about this and if it is better to open a single connection to the db at the app level for these app based methods? The requests only occur about once every 10 minutes so the load is by no means high.
Each request runs in a different thread. It’s always best to use individual connections when dealing with threads. Especially if you are doing any kind of transactions.
Every Session should have its own database connection. Every specialURL request should have its own database connection. Never share a database connection between disparate http connections.
For an API, you could create an APISession class, and sequence requests to a particular APISession with CriticalSection. Each APISession would handle login and manage one connection to the database. I’ve used this approach quite successfully in a couple of projects. Kind of a big task.
The easiest thing is to Dim a database object in the HandleSpecialURL event and make the connection right there. That ensures that each request gets its own connection.
That’s easy, but may also be time consuming. For something that occurs once every ten minutes I would do this. But for something with a higher load of connections which execute SQL that clears quickly, I would consider something like Brad described.
Yes. Good point. I was working on the assumption that this wasn’t time-critical, based on Nathan’s comment. But I fully agree with you regarding a higher load.
Thanks guys that is really helpful and is what I thought regarding my needs of low connections but Brads comment is interesting for more rapid connections and something I might have to think about for other projects.
yes, thank you for these interesting comments. I often feel that we need a session mechanism for handleSpecialURL. It may be necessary to publish a feature request! An external publisher could also market a module of this type.
I did some tests, and it’s not very time consuming. A new connection to the sqlite database for each HandleSpecialURL event takes one or two extra ticks 