SQL Server Speed Issue

Post Reply

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
jmarkley Members Profile Find Members Posts Newbie Joined: 09 August 2005 Status: Offline Points: 1	Post Options Post Reply Quote jmarkley Report Post Thanks(0) Quote Reply Topic: SQL Server Speed Issue 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?

dj air Members Profile Find Members Posts Senior Member Joined: 05 April 2002 Location: United Kingdom Status: Offline Points: 3627	Post Options Post Reply Quote dj air Report Post Thanks(0) Quote Reply 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.

JJLatWebWiz Members Profile Find Members Posts Groupie Joined: 02 March 2005 Location: United States Status: Offline Points: 136	Post Options Post Reply Quote JJLatWebWiz Report Post Thanks(0) Quote Reply 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.

ricardohzsz Members Profile Find Members Posts Newbie Joined: 05 August 2005 Status: Offline Points: 2	Post Options Post Reply Quote ricardohzsz Report Post Thanks(0) Quote Reply 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

JJLatWebWiz Members Profile Find Members Posts Groupie Joined: 02 March 2005 Location: United States Status: Offline Points: 136	Post Options Post Reply Quote JJLatWebWiz Report Post Thanks(0) Quote Reply 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?

ricardohzsz Members Profile Find Members Posts Newbie Joined: 05 August 2005 Status: Offline Points: 2	Post Options Post Reply Quote ricardohzsz Report Post Thanks(0) Quote Reply Posted: 02 September 2005 at 4:22am
	I'm an administrator of the forum 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.

Post Reply

Forum Jump

Forum Permissions View Drop Down

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum