Web Wiz Forums - SQL Server Speed Issue

Print Page | Close Window

SQL Server Speed Issue

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=16163
Printed Date: 13 April 2026 at 9:53pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Server Speed Issue

Posted By: jmarkley
Subject: SQL Server Speed Issue
Date Posted: 09 August 2005 at 5:16pm

Since migrating from Access to SQL Server, there seems to be a severe lag in response. Is this an existing issue? Are there any fixes?

Replies:

Posted By: dj air
Date Posted: 09 August 2005 at 6:13pm

this can be because of a number of issues

- distance between servers
- on same server as web
- overloaded server

but also note, it takes a while for the indexs to be effective.

Posted By: JJLatWebWiz
Date Posted: 09 August 2005 at 9:12pm

I bet server lag is a fairly common problem for new users testing the WWF forum. WWF for SQL uses a lot of Stored Procedures and has pretty good indexing which should make a very quick SQL server response, especially compared to Access. The lag would be more pronounced on hosted solutions where the SQL server is on a remote shared SQL Server, as opposed to a SQL on the same machine as the IIS server or at least one in the same network subnet and where one SQL Server is dedicated to each IIS server.

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.

Posted By: ricardohzsz
Date Posted: 17 August 2005 at 7:05pm

I've experimented the same problem. The problem was the persistent connection to the server "adoCon". I've deleted the adoCon variable and i'm using the connection string instead.

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

Posted By: JJLatWebWiz
Date Posted: 22 August 2005 at 6:15pm

Thanks ricardohzsz, that is a fascinating recommendation. I wonder if jmarkely got the message to try your suggestion.

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:

'Set the cursor type property of the record set to dynamic so we can naviagate through the record set
rsCommon.CursorType = 1

'Query the database
rsCommon.Open strSQLResultsQuery, adoCon

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?

Posted By: ricardohzsz
Date Posted: 02 September 2005 at 4:22am

I'm an administrator of the forum http://www.mvp-access.com/foroOur - http://www.mvp-access.com/foro

Our actual database is an 100+ Megabytes mdb. I've experimented this poor perfomance when using the 7.92 versi�n with the migrated database. But really, the speed issue is not related with database size.

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.