The flaw is not the connection pooling. Really, the connection pooling is working very well. The problem is the persistent connection. At least in my machine.

 

I've used the sql server profiler to find what's happening. Every time that asp pages performs an action using the persistent connection adoCon, in the profiler trace appears "audit" entrys with a high delay.

 

I made many changes y the database design but no results: clustered indexes, redundant indexes, ...

 

I'm not an expert on sql server and iis, but i think that sql server test if the persistent connection is still valid when asp pages uses it. And this test is (by large) more expensive than opening a new connection thanks to connection pooling. This is my opinnion.

 

The problem is not related to the dataset size. The speed problem appears in a completely new forum. Try a new instalation with only 1 category, only 1 forum, a some messages in this forum.

 

In your experience, was the performance hit mostly a problem during the message body search also?  How large is your data set?

 

To implement this change on the search function, open the search.asp page and look for this text:

 

Then change adoCon to strCon.

 

I've done this change on a couple different pages running on MySQL and MS SQL and both seem to work fine.  It's very difficult for me to compare performance based on this change because my datasets just aren't large enough to see a problem yet.  I wonder if this is a flaw in the ADO connection pooling system?

 

 

Old line:  rsCommon.Open strSQL, adoCon

New line: rsCommon.Open strSQL, strCon

 

I'm using the same machine for IIS and Sql Server. The perfomance are much better.

 

I've added this function to execute actions and sql sentences that don't return records

 

Public function ExecSp(byval strSQL)
 Dim cn
 
 set cn=createobject("ADODB.Connection")
 cn.Open strCon
 cn.execute strSQL, ,128
 set cn = nothing
End Function

 

 

Do hosts let clients run the MS SQL Query Analyzer in order gauge server responsiveness?

 

A few things are certain, on an active WWF forum, the SQL version will maintain more consistant performance with a much higher load.  Including loads that will break Access.  Also, having the MDB on the host machine is a security risk that is mitigated by SQL.