Classic ASP - using one connection for many requests?

Question

Consider a classic ASP site running on IIS6 with a dedicated SQL Server 2008 server ...

Scenario 1:

Open connection
Perform 15 queries, updates, etc. Via ASP page
Close connection

Scenario 2:

For each request, update, etc. open and close the connection

---

When pooling, my money will be the most efficient and scalable in Scenario 2.

Would I be right in this assumption?

Edit: Additional Information

These are database operations that apply to many asp-code in separate functions, performing individual actions, etc. These are not 15 queries executed quickly. Think of a large site with many features, including etc.

Answer 1

If I understand you correctly, you plan to share the connection object through complex code contained in various functions in different ways.

In that case, that would be a bad idea. It is difficult to guarantee the correct status and connection settings if other code may have seen the need to change them. In addition, you can sometimes have code that retrieves the firehose recordset and does not complete processing when another piece of code is called that also needs to be connected. In this case, you cannot use the connection.

If each atomic piece of code gets its own connection, it would be better. The compound will be in a clean known condition. With a parallel connection, several connections can work. As others have pointed out, the cost of creating a connection is almost entirely mitigated by the underlying pool of compounds.

Answer 2

Basically, ASP pages are synchronous. So, why not open the connection once to load the page and close it once to load the page? All other openings / closures seem unnecessary.

Answer 3

in your scenario 2, there is feedback between your application and SQLServer to execute each query that consumes your server resources, and the full execution time will increase.
but there is only one round in Scenario 1, and SQLServer will run all the queries in one go. therefore it is faster and less resource intensive

EDIT: Well, I thought you meant several queries at a time.
therefore, when the connection pool is turned on, there is no problem closing the connection after each transaction. so go with scenario 2

Answer 4

It’s best to open the connection once, read all your data and close the connection as soon as possible. AFTER you close the connection, you can do what you like with the data you received. In this case, you do not open too many connections, and you do not open the connection for too long.

Even though your code has database calls in several places, the overhead of creating a connection will be worse than waiting — unless you say your page takes many seconds to create on the server side? As a rule, even without controlled access to data and with many functions, your page should be in the order of a second for generation on the server.

Answer 5

I believe that the default connection pool is about 20 connections, but SQLServer can handle much more. Getting a connection to the server takes a lot of time (provided that you are not doing anything stupid with your commands), so I don’t see anything wrong with getting a connection on the page and killing it if it is used later.

For scalability, you might run into problems when your connection pool gets too busy and timeouts while your script is waiting for a connection to be available while your database is there with 100 spare connections, but no one is using them.

Create and kill on one page gets my vote.

Answer 6

In terms of performance, there is no noticeable difference. ADODB connection aggregation manages the actual connections to db. Adodb.connection.open and .close are just a facade in the connection pool. Creating instances of only 1 or 15 adodb.connection objects doesn't really matter much. Before using transactions, we used a connection string in conjunction with adodb.command (.activeConnection) and never opened or closed connections explicitly.

Reasons to explicitly refer to adodb.connection are transactions or connection-related functions such as mysql last_inserted_id (). In these cases, you must be absolutely sure that you get the same connection for each request.