Microsoft has published results of stress tests indicating the opposite of
what you say your results are. I suspect you were not actually taking
adbantage of session pooling when you ran your tests, but of course, this
is only a suspicion.
hmm thats interesting.
wouldn't be OLE DB connections pooled when using default settings? i ran the
tests ~3 years ago on IIS5 with connection code similar to the one below -
but of course with many different pages with different (more expensive)
queries and multiple requests.
-----------------------------------------
connection opens on each page:
FILE: TEST_NOSESSION.ASP:
<% Option Explicit
Const adOpenForwardOnly = 0
Dim oConn, rsRecords, sOutput
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.open
"provider=SQLOLEDB;server=localhost;uid=webuser;pwd=test123;database=movies;QuotedID=No"
Set rsRecords = Server.CreateObject("ADODB.Recordset")
rsRecords.Open "SELECT movies.mov_name FROM movies WHERE mov_id > 100",
oConn, adOpenForwardOnly
Do Until rsRecords.EOF
sOutput = sOutput & "<br>" & rsRecords("mov_name")
rsRecords.MoveNext
Loop
rsRecords.close
response.write sOutput
oConn.close
set rsRecords = nothing
set oConn = nothing
%>
---------------------------------------------
connection opened in global.asa:
FILE GLOBAL.ASA: (simplified)
<%
Sub Session_OnStart()
Call ConnectDB()
End Sub
Sub Session_OnEnd()
' some stuff here as well
End Sub
Function ConnectDB()
If (check here if for this user a Connection object already exists)
= false then
' new connection
Set Application("DB_xxx") =
Server.CreateObject("ADODB.Connection")
Application("DB_xxx").open
"provider=SQLOLEDB;server=localhost;uid=webuser;pwd=test123;database=movies;QuotedID=No"
End If
Set Session("DB") = Application("DB_xxx")
End Function
%>
FILE: TEST_WITHSESSION.ASP:
<% Option Explicit
Const adOpenForwardOnly = 0
Dim rsRecords, sOutput
Set rsRecords = Server.CreateObject("ADODB.Recordset")
rsRecords.Open "SELECT movies.mov_name FROM movies WHERE mov_id > 100",
Session("DB"), adOpenForwardOnly
Do Until rsRecords.EOF
sOutput = sOutput & "<br>" & rsRecords("mov_name")
rsRecords.MoveNext
Loop
rsRecords.close
response.write sOutput
set rsRecords = nothing
%>
The best suggestion I have is the one that comes from Microsoft: take
advantage of session pooling by instantiating and opening connections on
each page, closing them as soon as you are finished using them. Only the
first opening of a connection will take time. Once the connection is in
the pool, subsequent uses will not require the same time to open them.
what about the Server.CreateObject? i always thought object creation and
instantiation would add heavy overhead... and thus negatively affect
performance
By taking advantage of session pooling, you minimize the number of
connections to your database. Don't forget: each connection uses memory on
the database server as well as the web server (assuming you are using a
server-based dbms, which you must be with 900 concurrent users).
thats true. the db shows quite a lot of connections. but because of the 900
concurrent users are not all firing queries at the same time, the db is not
overloaded. of course the memory requirements are quite huge, db + web uses
around 2.5gb of memmory
interesting lecture for a boring sunday... erhm wait, its easter!
thanks for your input and happy easter from switzerland!
- thomas
