October 11, 2004 at 1:34 am
Hi Experts!
Here is my situation:
Earlier today, I had an issue where a particular stored procedure was timing out when called from my ASP pages. The SP has 4 input params and is a basically a large select statement using those params to return a rowset. I attempted to call the SAME stored procedure from query analyzer using the same params and it is BLAZING fast. My connection string through ASP looks like this:
ConnStr = "Provider=SQLOLEDB;Network=DBMSSOCN;Data Source=localhost;Initial Catalog=mydata;User ID=uid;Password=mypassword"
To call the SP from ASP I do the following:
Set DB = Server.CreateObject("ADODB.Connection")
DB.Open(ConnStr)
Set RS=DB.Execute("exec dbo.spMySP 1,2,3,4")
This has worked fine for weeks. My database has been growing and then at some point today, this started timing out but as I mentioned, calling "exec dbo.spMySP 1,2,3,4" from query analyzer works and is FAST!. In profiler, I notice that the next log entry after each call to the SP is followed by "Audit Logout" which has a duration of 30,000+, about the same as the SP itself. As a matter of fact, I noticed quite a few "Audit Logout" entries with a VERY large druation, not all associated with this SP, one with 624,266. Is this an indication of what this problem might be?
Any ideas would be greatly appreciated!
Thanks!
October 11, 2004 at 2:14 am
If this procedure has been working fine with good performance then my first suspicion would be that something else is causing the problem. Perhaps some long running process that is locking the tables that your procedure needs.
As an additional note I would avoid using the exec dbo.spMySP 1,2,3,4 syntax. Use the command object and specify parameters. In ADO your method gets passed as a string that has to be interpretted at the server end thereby losing a little performance.
Given what you have said about passed performance it probably isn't a big deal for you, but it is worth knowing about.
October 11, 2004 at 2:26 am
Hi David,
Thanks for the tip regarding how to call the SP from ASP. I've thought about this and agree that it my current issue.
I think that the fact that this SP can be called from query analyzer without problem but is slow being called through ASP is definitely a key issue. If there were another process locking tables, then it wouldn't matter where this SP was called from.
I also noticed that the CPU usage was extremely high during this time. After a period of time, the problem went away. Its hard to tell if this was related to decreased user activity or what.
If you have any other ideas, please let me know.
Thanks!
October 11, 2004 at 3:25 am
When the proc is called from your application open up SQL Query Analyser and run sp_lock and sp_who to see what is going on.
Within Enterprise Manager have a look at management/current activity.
Run SQL Profiler for your database, remember to use the DBID because there is a bug in profiler that means the database name parameter doesn't work.
When you run the proc from SQL Query Analyser are you running it on your SQL box?
If you run your application and SQL Query Analyser from the same machine do you get the same performance issues?
October 11, 2004 at 3:38 am
Unfortunately, the problem is gone now, so I'll have to wait to see it again.
SQL Server and IIS are on the same machine, so accessing via a remote browser causes the ASP to call the SP on the same local machine as mentioned above in my connection string. When calling from query anaylzer, I'm connecting to the web server remotely using SQL Auth.
Strange because QA is fast and ASP is slow for the same SP call.
October 12, 2004 at 12:04 am
i had the same Problem .The stored procedure builds a temp file and the displays the selection.We seperated the Procedure and till now have had no Problems.
October 13, 2004 at 7:00 pm
OK, more info!
This seems to be a LOCKING problem. When this stored procedure is called from ASP, I see a TON of locks on the tempdb and on the 3 tables that it does selects from. When I do the same call from query analyzer which is a different process, I do NOT see any locks or at least they happen so fast and are gone that I can not see them in EM. I've save to text files the export of the lists for locks/spid, locks/objects etc.
When I stop SQL Server from the manager and then Restart it, the problem goes away. The stored procedure is blazing fast again like it is from QA. I know that everytime you start SQL Server, the tempdb is deleted and recreated.
What would be causing this type of problem?
Thanks in advance!
October 14, 2004 at 7:28 am
Look for blocking in Profiler and with sp_lock, it may simply be that there selects from the other tables are blocking each other. If so and those tables don't have much user input (especially during business hours for the application) consider using the NOLOCK hint for those and the locks will always be shared and may decrease the blocking.
September 14, 2005 at 7:45 am
Hi,
I’ve exactly the same problem calling an SP from ADO.NET differs from calling it from QA… When I shutdown the SQL Server the query works fast, but as the time pass it gets slower…
Will try the NOLOCK, but is always a problem to check if it solves the problem because we need to let the time flow …
ThX
NeuralC
September 14, 2005 at 1:07 pm
Hi There,
I never really found a solution to my problem. I ended up re-writing the query and haven't seen the problem again. Bad queries=bad performance!
I've recently re-written a particular query that was having the problem, so I don't have enough info to see if the changes worked. One problem with the query is that there was a redundant condition in the WHERE clause that I think may have caused some confusion.
I just couldn't find a specific reason for the query to timeout in ASP but not in QA. My suspicion is that the query plan in ASP was different than when called from QA, if that's even possible. Can anyone confirm if this ever happens?
Larry
September 16, 2005 at 1:27 pm
I've justed discovered, using ADO, from a middle-tier object, which is called from an ASP page is slower for us because we're running through COM+ with transaction required set on all the components in application DLL.
Through Profiler, it shows the SQLTransaction object takes about 30 seconds to do it's 2 commits + another 20 seconds or so to get the rest of the data. So, there's definitely substantial overhead using a transaction--plus going through a middle-tier object.
When run from Query Analyzer, with no transactional overhead, it takes about 9 seconds to return the result set. Since the stored procedure being called is read only, we really don't need the transaction. So, our fix is to rearchitect our middle-tier and put the read only calls into a dll that does not require transactions.
I'm also looking into changing the ADODB threading model. If you install the .NET Framework, apparently it changes the ADO threading model to freethread (multi-threaded apartment). There is some recommendation that if you are running ASP along with ASP.NET to ADO that single threaded apartment works out better--reduces locking contention etc.
There are 2 batch files that can switch between the
two. Unfortunately, I have not seen any clear recommendation either way. I just have to test it.
September 16, 2005 at 2:14 pm
How can I switch between multi-threaded and single-threaded? I'm running ASP and ASP.NET 2.0 beta on the same server.
September 21, 2005 at 2:21 pm
! I thought I posted a reply...sorry:
If you don't find these in their default locations, search for it, it's in several different CABs (IE 6 cabs / Project 10 cabs). If you're running IIS 6, please take a look at the ASP enhancements that allow ASP to run in a MTA (i.e. Both). The advice I've seen (from only 2 sources, though) is use Apartment.
MTA
Program Files\Common Files\System\ado\adofre15.reg
makfre15.bat / makapt15.bat makes these registry changes
adoapt15.reg
REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{00000507-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Apartment"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{00000514-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Apartment"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{0000050B-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Apartment"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{00000535-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Apartment"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{00000535-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Apartment"
adofre15.reg
REGEDIT4
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{00000507-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{00000514-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{0000050B-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{00000535-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Both"
[HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{00000541-0000-0010-8000-00AA006D2EA4}\InprocServer32]
"ThreadingModel"="Both"
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply