[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 357
  • Last Modified:

SQL queries taking forever to run (but only via ASP front end)

Hi Experts,

We are having major problems with an ASP application that has a SQL Server 2000 backend. We have started monitoring how long each request to the database is taking, and the results are quite staggering. When I run a reasonably simple query in Enterprise Manager (just something like SELECT * FROM vw_someview) it takes no time at all, perhaps just a split second. However, we can see from our logs that when the same query is being requested via the front end it's taking anything from a few seconds to 90 seconds. Some of the recordsets being returned are actually empty and still taking this long to come back!

This is a reasonably complex system with lots of different things going on at once, and anything from 150 to 350 users on at any one time, so it's very difficult to know what's happening here.

We're suffering serious performance issues with this system and we're trying desperately to work out what's causing them and what we can do to speed things up.

So, I think I have two questions:

- Does anyone have any ideas as to why a query would take much longer to run when called from an ASP page rather than directly in the database.
- Are there any methods I can use to try and monitor what's happening in the database at any one point? Or what can I do to try and enhance the performance of the database?

Any suggestions will be very gratefully received!

Many thanks in advance.

SWD
0
SurreyWebDesigner
Asked:
SurreyWebDesigner
  • 7
  • 7
  • 4
  • +1
1 Solution
 
grendel777Commented:
Hi SurreyWebDesigner, without knowing details about the system, I'd first ask if your queries are being generated in ASP as strings.  If so, you can speed things up (and increase security) by converting the queries into parameterized stored procedures on the server side and calling the sproc from ASP.  SQL Server can't optimize queries generated as strings in outside code.

Hope that helps!
0
 
hieloCommented:
IF your ASP is using a DSN connection, convert it to a DSN-less connection. Also, if you are doing multiple selects, make sure you release the memory of unneeded objects by setting them to Nothing. Ex:
' "free" a recordset object
Set rs=Nothing
0
 
SurreyWebDesignerAuthor Commented:
HI grendel777,

This sounds v. interesting. We currently use a function FetchRS to pull back each of our recordsets (although I have to admit, I didn't create it in the first place). Apologies if the code isn't formatted perfectly. We call this function using <% set rs = FetchRS("SELECT * FROM T_Anything",0).

How could I modify this function to fit in with your suggestion?

Many thanks
SWD
Function FetchRS(ByVal strSql,ByVal RecordsetLockTypeFlag) 
    
'---- CursorTypeEnum Values ----
'Const adOpenForwardOnly = 0
'Const adOpenKeyset = 1
'Const adOpenDynamic = 2
'Const adOpenStatic = 3
'---- LockTypeEnum Values ----
'Const adLockReadOnly = 1
'Const adLockPessimistic = 2
'Const adLockOptimistic = 3
'Const adLockBatchOptimistic = 4
 
'declare recordset
 
    Dim oRs
    Set oRs = Server.CreateObject("Adodb.Recordset")
    on error resume next
    Dim objCon  
	Set objCon = Server.CreateObject("Adodb.Connection")	
	objCon.CommandTimeout = 100 
	objCon.open sConnStr
	If err <> 0 then 
		Write Err.Description
		Call InformBreakdown(Err.Description)
		Write "An error has occurred."
		Response.End
	End If	
	on error goto 0
 
    If RecordsetLockTypeFlag = 0 then 'for disassociated readonly recordsets
	   	oRs.CursorLocation = 3 'adUseClient '3			'adUseClient    
		on error resume next		
		oRs.Open strSql,objCon, 0,1	'ideal case adOpenForwardOnly, adLockReadOnly '0,1
		If err <> 0 then 
				Write "Function Name = " & Request.ServerVariables("URL")
				Write "SQL = " &  strSql
				Write Err.Description	
				on error goto 0					
				Response.End 			
		End If	
		on error goto 0
	Else
		on error resume next				
		oRs.Open strSql,objCon, 1, RecordsetLockTypeFlag 'ideal case 1,3	'adOpenKeyset, adLockOptimistic
		If err <> 0 then 
				Write "Function Name = " & Request.ServerVariables("URL")
				Write "SQL = " &  strSql
				Write Err.Description	
			on error goto 0		
			Response.end
		End If	
		on error goto 0		
	End IF	
 
	'Call HandleError()
	    
    On error goto 0
    If RecordsetLockTypeFlag = 0 then 'return disassociated recordset
    ' Disconnect the recordsets and cleanup
		Set oRs.ActiveConnection = Nothing
		Set objCon = Nothing
	End If	
	'temp err handling
    Set FetchRS = oRs   
    
End Function

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Anthony PerkinsCommented:
In order to compare the performance of Enterprise Manager/Query Analyzer and your ASP code:
Your recordset should have the following properties:
1. Server based: CursorLocation = adUseServer and
2. Firehose (forward only: CursorType = adOpenForwardOnly, read only: LockType = adLockReadOnly)   Otherwise you are comparing apples to oranges.
0
 
SurreyWebDesignerAuthor Commented:
Sorry acperkins - you've gone over and above my level of knowledge in this field. Could you expand on these points a little?

Many thanks indeed.
SWD
0
 
Anthony PerkinsCommented:
It is all in your code, get your developer to look at it this way:

If RecordsetLockTypeFlag = 0 then 'for disassociated readonly recordsets
      oRs.CursorLocation = 3 'adUseClient '3
      oRs.Open strSql,objCon, 0,1      'ideal case adOpenForwardOnly, adLockReadOnly '0,1
Else
      oRs.Open strSql,objCon, 1, RecordsetLockTypeFlag 'ideal case 1,3
End IF      

If the recordset is pulled using the first condition (RecordsetLockTypeFlag = 0) then it is using CursorLocation = adUseClient
If the recordset is pulled using the second condition (RecordsetLockTypeFlag <> 0) then it is using adOpenKeyset and wahtever is the value of the RecordsetLockTypeFlag.  

It simply defies logic that both cases can be "ideal" performant cases.
0
 
SurreyWebDesignerAuthor Commented:
Unfortunately the developer who wrote this code has left us, and my knowledge isn't anywhere near his I'm afraid.

So you're saying that this function could be improved to get a performance boost? Which of the conditions is the ideal one?

Any chance of anyone re-writing the function to be super-efficient?? :-)

As always, many thanks.
SWD
0
 
Anthony PerkinsCommented:
The function is called two ways, you are first going to have to determine how and where it is called.
Second you are going to have to determine IF you can change it.  Perhaps there is a reason that they are using adOpenKeyset.  Unfortunately, without knowing all the details it is impossible to give you the right solution without breaking something.
0
 
SurreyWebDesignerAuthor Commented:
The only way it is called (as far as I know), is either:

set rs = fetchrs(sql,0) 'where 0 means the recordset won't be updateable

or

set rs = fetchrs(sql,3) 'where 3 means that it will be updateable

It's called multiple times on multiple pages, basically wherever we need to get a recordset back from the db. It's used thousands of times throughout the app, but only ever in this format.

Thanks
SWD
0
 
Anthony PerkinsCommented:
The only one that is relevant is the first (where RecordsetLockTypeFlag = 0).  See if commenting out the code
oRs.CursorLocation = 3 'adUseClient '3

Does not have any adverse side-effects and improves performance.
0
 
grendel777Commented:
First let me say that I'm a database and VBA guy, not an ASP guy; however...here is an example.  I might not have the ASP code right, but here's where you can start looking: http://support.microsoft.com/kb/306574/en-us.
In Sql Server, create a sproc that generates a recordset of the data you want: say your T_Anything table has a City column (nvarchar(50)) and you want everything from Sacramento:
 
USE [DatabaseName]
 
CREATE PROCEDURE usp_GetT_AnythingByCity
  --Input parameters:
  @city nvarchar(50)
  AS
BEGIN
  SET NOCOUNT ON
  SELECT * FROM T_Anything WHERE [City] = "'" + @City + "'"
  --(Need to encase strings in single quotes ('), not numbers)
END
 
Then, use ASP code to call the sproc while passing the @city input parameter:
 
Dim objConn, objCmd, objParam, objRST
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=macdb;uid=test;pwd=test"
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.CommandText = "usp_GetT_AnythingByCity"
objCmd.CommandType = adCmdStoredProc
objcmd.ActiveConnection = objConn
Set objParam = objCmd.CreateParameter ("@city",advarchar,adParamInput,50)
objCmd.Parameters.Append objParam
Set objRST = ObjCmd.Execute
objRST.MoveFirst
While Not objRST.EOF
  ...process the returned recordset

Open in new window

0
 
Anthony PerkinsCommented:
Did we get our wires crossed?
0
 
SurreyWebDesignerAuthor Commented:
I think Grendel777 was trying to think of another way I could try and improve performance - which is certainly appreciated. So are you thinking I can use this code to generate a stored proc that I can pass different sql statements to, rather than having to create a procedure for each recordset, which would obviously be a nightmare.

acperkins - what do you think commenting out that line will do? It's a business-critical application so I need to particularly careful that i don't break anything!! I guess if I were to replicate our fetchrs function and comment out this line, then for my userid only use the modified version of the function to bring back the recordsets. I just need to know under what circumstances you think commenting out that line would have a negative effect.

Many thanks to both of you.

SWD
0
 
Anthony PerkinsCommented:
>>So are you thinking I can use this code to generate a stored proc that I can pass different sql statements to<<
Nope.  It does not pass different sql statements, all it does is change the WHERE parameter. It is not using Dynamic SQL as you are suggesting.  Which is not to say that creating stored procedures may ultimately be your only avenue.

>>It's a business-critical application so I need to particularly careful that i don't break anything!! <<
I could not agree more.

>>what do you think commenting out that line will do?<<
I know what it will do:  It will change the cursor from a client based resultset to a server side resultset so that it is more comparable to what you would see in Enterprise Manager.  Which was your original question.

Beyond that I have no idea.  It will certainly render any reference to the RecordCount property as useless and there could well be other problems.

You need to make a decision:
1. Patch things as best you can so that performance is acceptable or
2. Hire someone who can come in and redesign the whole application.
0
 
SurreyWebDesignerAuthor Commented:
>>Nope.  It does not pass different sql statements, all it does is change the WHERE parameter. It is not using Dynamic SQL as you are suggesting.  Which is not to say that creating stored procedures may ultimately be your only avenue.<<
Is there another avenue? I'm willing to consider anything!!

>>I know what it will do:  It will change the cursor from a client based resultset to a server side resultset so that it is more comparable to what you would see in Enterprise Manager.  Which was your original question.<<
So in a nutshell, are you saying that removing this line will give me the same speed I enjoy in Enterprise Manager?

>>It will certainly render any reference to the RecordCount property as useless<<
I think this alone will mean that I'll need to leave the line in there. There are probably hundreds of places where RecordCount is used throughout the app.

With regard to the decision:

1. This is really what I'm trying to do. This is a massive application and we're getting more and more users every day. The system appears to be at breaking point on occasions. Sometimes we have over 300 active users (all with the potential of running large sql queries). When usage peaks the system will just grind to a halt. We've checked out whether there are hardware/memory problems and everything looks fine - well within normal limits. So really I'm trying to find out what the problem could be - as when these problems occur we can still run queries very quickly via Enterprise Manager (and yet from the front-end every page times out - regardless of complexity). Eventually the system will fall over altogether and we have to re-boot the box.

2. Not really an option I'm afraid.

Many thanks for your help so far - any suggestions as to how we can improve performance will be seriously well received!

SWD
0
 
grendel777Commented:
Sorry,  I wasn't trying to derail your conversation,  I was just answering a question on my initial post about using sprocs in place of dynamic sql. Here's some general advice, also not meant to derail your previous conversations - you might need to research some of these tips online to get to the real juice:

 Dynamic sql is (a) dangerous because someone who knows what they're doing can perform a "sql injection attack,"  and (b) slow because the native query optimizer can't work out the best way to return the requested data: it's like your server is a Starbucks, quickly and efficiently getting coffee-based drinks because the barista knows how to do it, and then someone comes in asking for an oil change.  Unfortunately, making the switch isn't a trivial task.  You'll need to change your code wherever you're currently generating dynamic sql, but I think it will really help your system and make your code more manageable.

Tuning your indexes is also critical, but it's hard to make suggestions without being able to study your system.  Here's a site with some great tips: http://www.sql-server-performance.com/tips/optimizing_indexes_general_p1.aspx
These are only useful if your tables are adequately normalized, so check that, too.

The general rule on cursors is "don't use them."  I use them, but very sparingly and only for one-time tasks.  They tend to be very slow so you should probably look to replacing them in your code, if possible.  acperkins knows more about them than I do, but server-side cursors are costly because the server has to process every row as a round trip - 300 rows means hitting the server 300 times!  This is called RBAR (pronounced "reebar") in the database world: Row By Agonizing Row.  You can create cursors in sprocs, incidentally, and there you can tune them a little (lookup "fast-forward").

Don't open large recordsets.  If the user wants to find stores in San Francisco, don't return all stores worldwide and have the client pare it down: just return the SF stores to conserve bandwidth.

Be careful with timeouts: your system could be "breaking" because the server stops a query that's taking too long to execute.

Don't use the TEXT datatype, use varchar(max) instead.  It's hard to query against a TEXT column and they're slow.

Hope this helps.  My best advice is to just hang in there - it can be a nightmare taking over from someone else who knew just enough to be dangerous.
0
 
grendel777Commented:
And again, for emphasis, ASP and I are not friends, so your problems could be coming from there and not the database.
0
 
Anthony PerkinsCommented:
I am afraid, I ahve not got good news for you.  There is no magic bullet.  Too many times first-time developers create a website that works fine in a demo (minimal data) mode, but then gets bogged down when the size of the table increases to some significant size AND the developer has long since departed.

Just to clarify the term "cursor" in ADO terminology is equivalent to recordset or resultset and has noting to do with the T-SQL CURSOR command.

But to address, your original question one possible reason the query is taking longer on the website than on the server has to do with the use of client side cursors.  That means that every time a user requests a page a recordset will need to be created on the web server.  So if at one point you have 100 users that translates to 100 resulsets on the webserver.  Contrast that with creating them on SQL Server itself and you should see the differnce.  That is the difference between the use if adUseClient and adUseServer.

So the question is can you just change it?  And the answer is no.  The developer in all their wisdom (or lack of) created the system relying on functionality that only exists in client based cursors.  So unfortunately, to use an over-used term this side of the pond:  You are between a rock and hard place.

Unfortunately, the solution is not just convert specific slow running queries to using Stored Porcedures.  Even if you could do that, it would probably not help you without breaking existing functionality.

You may be able to improve things somewhat by improving hardware, indexing where appropriate, but in the end these may just be short term fixes and the only real solution is a total re-write.

Sorry about the bad news and feel free to get a second opinion and post their diagnostic here.
0
 
SurreyWebDesignerAuthor Commented:
Many thanks for your help acperkins.

Cheers
SWD
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now