SQL Server Performance

Calling SP from ASP versus Query Analyzer

Discussion in 'General DBA Questions' started by larrykl, Oct 11, 2004.

  1. larrykl New Member

    Hi Experts!<br /><br />Here is my situation:<br /><br />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:<br /><br />ConnStr = "Provider=SQLOLEDB;Network=DBMSSOCN;Data Source=localhost;Initial Catalog=mydata;User ID=uid<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=mypassword"<br /><br />To call the SP from ASP I do the following:<br /><br />Set DB = Server.CreateObject("ADODB.Connection")<br /><br />DB.Open(ConnStr)<br /><br />Set RS=DB.Execute("exec dbo.spMySP 1,2,3,4")<br /><br />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?<br /><br />Any ideas would be greatly appreciated!<br /><br />Thanks!<br />
  2. satya Moderator

    When was the last time the stored procedure was compiled?
    Also run UPDATE STATISTICS on the involved tables to get optimum performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. larrykl New Member

    Wow,
    Quick response!
    How do I find out the last compile time of the SP?
    I have a Maintenance plan that runs the standard integrity checks and optimization jobs on my database. Do these do UPDATE STATISTICS?

    Would either of those things be an issue if this SP runs nicely from Query Analyzer but not from ASP? I was thinking that maybe there is an issue with IIS communicating to SQL Server related to something else, DNS or some other issue. What do you think?
  4. satya Moderator

    You can re-issue the SP_RECOMPILE for this SP and update stats for interim solution and test.
    Yes the maint. plan should take care of the updations, but after that if there is a substantial difference in the database updation then its better to reissue the update stats occassionally.

    http://www.sql-server-performance.com/stored_procedures.asp
    http://www.sql-server-performance.com/asp_sql_server.asp
    ,... links for your reference on performance tuning.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. larrykl New Member

    Thanks for the links Satya.

    Any idea about why calling from ASP would be slower than calling from Query Analyzer? This seems to be a key difference. I've read a bit more about Audit Logout and don't think that is a symptom of anything.
    Would re-compile/update stats issues cause an SP to perform differently between calling from ASP and calling from query analyzer?
    Thanks!
  6. satya Moderator

    <i>I was thinking that maybe there is an issue with IIS communicating to SQL Server related to something else, DNS or some other issue.</i><br />You've answered your question <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />The above links I've provided will update you more on this information.<br /><br />HTH<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  7. larrykl New Member

    I read the link with the ASP tips pretty carefully and came up with nothing apparent in my implemenation. I'm using QA remotely using SQL Auth. vs. ASP talking to the SQL Server on the same local machine using OLE DB + ADO. There is no apparent reason why ASP should get the SQL timeout. Is this related to my connection string or the way ASP connects to SQL Server during the time the problem occurs?
    Thanks!
  8. Argyle New Member

    If the query is fast in QA but not ASP it sounds like a code issue in your ASP page. First create an almost blank ASP page that does nothing except running the SP and response.write the result. Is it still slow? If not it could be processing in a loop when you response.write the results. A common issue is string concatination that is really slow in ASP and it gets slower the more data you have.

    How much data do you return. 10 rows or 100000?
  9. larrykl New Member

    Hi Argyle,
    Thanks for the response!

    Unfortunately, the specific error I see on the page is an OLE DB SQL Timeout. I did indeed attempt what you suggested. I have IIS on my local machine that I'm also running QA on for this testing to connect to the remote server, so I made a simple ASP page on the local machine that connects to the remote server and calls that stored procedure and that's it. ASP was SLOW, timeed out (I believe the default timeout is 30 seconds and it was about that long) and QA was fast, about 1 second.
    I'm returning 40 rows. The stored procedures does a select for each column of each row I return. Nothing too complicated.

    Any other ideas?
    Thanks!
  10. Argyle New Member

    Upgrade to latest MDAC (2.<img src='/community/emoticons/emotion-11.gif' alt='8)' /> on the client and server if possible.<br /><br />Make sure you do not have ODBC tracing enabled (check ODBC settings in control panel)<br /><br />For details on different timeouts see:<br /<a target="_blank" href=http://vyaskn.tripod.com/watch_your_timeouts.htm>http://vyaskn.tripod.com/watch_your_timeouts.htm</a><br /><br />Test executing the SP the "correct" way with the ADODB.Command etc. and not "EXEC dbo.mySP".
  11. larrykl New Member

    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!
  12. satya Moderator

    Redesign queries to work on smaller sets of data at a time. Break one large transaction into several smaller transactions if possible.

    In SQL2000 try table varibles instead of temporary tables. These varaibles are handled in memory not in tempdb. Expand the tempdb by adding files or by moving it to another hardrive.

    http://www.sql-server-performance.com/tempdb.asp - tune tempdb.
    http://www.sql-server-performance.com/reducing_locks.asp - reduce locking.

    HTH

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  13. larrykl New Member

    My SP is simple. I looks kinda like this:

    CREATE Procedure spUpdateWinsByPool
    @userid int
    As
    set nocount on
    select a,b,c,
    (select x1 from table1 where val1=main.val1),
    (select y1 from table1, where val1=main.val1),
    (select z1 from table2 where val1=main.val1),
    (select x2 from table1 where val1=main.val1),
    (select y2 from table1, where val1=main.val1),
    (select z2 from table2 where val1=main.val1),
    .
    .
    .
    (select x21 from table1 where val1=main.val1),
    (select y21 from table1, where val1=main.val1),
    (select z21 from table2 where val1=main.val1)
    from table3 where val1=@userid
    GO

    Basically its a large select from 3 tables where the data is formatted into the desired row set result.
    Does this use tempdb even though I'm not explicitly creating a tempdb and inserting data? The seems to be locking the tables after a while and there appear to be locks on tempdb.

    What's wrong here?

  14. Argyle New Member

    This is most likely due to ADO Cursors. Use the ADODB.Command object to execute your query and set it to return a so called client side disconnected recordset instead of the default server side cursors being used since they use tempDB. You also have a lot more control over the cursor and lock types if you use the Command object.<br /><br />Example:<br /><pre>Function GetAuthor(authorID)<br />Dim rs, cmd<br /><br />'Instantiate objects<br />Set rs = Server.CreateObject("adodb.Recordset")<br />Set cmd = Server.CreateObject("adodb.Command")<br /><br />'Open connection <br />cmd.ActiveConnection = GetConnectionString()<br /> <br />'Specify SP<br />cmd.CommandText = "dbo.GetAuthorByAuthorID"<br />cmd.CommandType = adCmdStoredProc<br /><br />'Specify parameters<br />cmd.Parameters.Append cmd.CreateParameter("@authorID", adInteger, adParamInput, , authorID)<br /> <br />'Execute the query with clientside cursor<br />rs.CursorLocation = adUseClient<br />rs.Open cmd, , adOpenStatic, adLockReadOnly<br /><br />'Disconnect the recordset and clean up<br />Set cmd.ActiveConnection = Nothing<br />Set cmd = Nothing<br /><br />'Return the recordset<br />Set GetAuthor = rs<br />End Function<br /><br />Function GetConnectionString() <br /> GetConnectionString = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDB;User Id=MyUser<img src='/community/emoticons/emotion-4.gif' alt=';P' />assword=MyPass;App=MyApp"<br />End Function <br /></pre><br />The above code will not use tempDB for storage for the data but will stream over the data to ADO's own cursor engine and use the memory on the web server instead.
  15. larrykl New Member

    Hey Argyle,

    I like your suggestion. I will definitely give it a try but I'm concerned about the SP itself. Maybe I sould be creating a table variable and inserting into it first and then selecting all rows from it rather than the large select I'm using? Does my large select actually get written to tempdb first before its sent? That would still be the case even if I change the ASP side, right?

    I'll make the change you suggest but I'll have to wait until the trouble occurs again.

    Thanks in advance!
  16. dhilditch New Member

    The lowest level of granularity in SQL Server for locking is row level locking, but you have your code looking at the same row within the same transaction.

    select a,b,c,
    (select x1 from table1 where val1=main.val1),
    (select y1 from table1, where val1=main.val1),
    ...

    just looking at those 2 lines,
    (select x1 from table1 where val1=main.val1) -- lock row where val1 = main.val1
    (select y1 from table1, where val1=main.val1) -- lock row where val1 = main.val1 -- same row!!

    If you add (nolock) after each and every table reference you should see the time evaporate for this query from ASP. Mind you, ADO will determine your lock because you are using a cursor, so change the lock type on ado to 'read only' and just that itself should actually fix your problem.

    Dave Hilditch.



    Ask a SQL Server Question
    www.matiogi.com
  17. larrykl New Member

    Hi Dave,

    Those are execellent points. I think you've hit on both of my main concerns about the current implementation. I do need to clarify something regarding the query though. It actually looks more like this:

    select a,b,c,
    (select x1 from table1 where val1=main.val1 and weeknum=1),
    (select y1 from table1, where val1=main.val1 and weeknum=1),
    (select z1 from table2, where val1=main.val1 and weeknum=1),
    (select x1 from table1 where val1=main.val1 and weeknum=2),
    (select y1 from table1, where val1=main.val1 and weeknum=2),
    (select z1 from table2, where val1=main.val1 and weeknum=2),
    ...
    (select x1 from table1 where val1=main.val1 and weeknum=21),
    (select y1 from table1, where val1=main.val1 and weeknum=21),
    (select z1 from table2, where val1=main.val1 and weeknum=21)
    from main where val1=@userid

    In the the table1 and table2, each weeknum is a unique row. So this probably would change your assessment slightly. Since this query works fine for a while, it would seem that maybe the locking escalates from row to table and thats when my troubles start, don't you think? If that is the case, would your suggestion about changing the lock type of ADO still be valid? I thought that the way I was calling this SP from ASP would give me a read only cursor. If not, how do I change the lock type of ADO? Would following Argyle's suggestion of using the Command object instead of the Connection object do the trick? Also, how do I specify nolock for each individual table reference?

    I think this is definitely the right track! I want to have as much info so that I can do a number of test and try different possible solutions when the problem occurs again.

    Thanks in advance!
  18. larrykl New Member

    OK People! I've got some interesting info! The problem is happening right now as I type this. When the SP with the large SELECT I've described is called, I'm getting the SQL OLE DB timeout when called from ASP but NO timeout when called from Query Analyzer and the call returns immediately with the data.
    I tried calling the SP from ASP using the Command object instead, calling it as a stored procedure with parameters and no luck.
    During the call from ASP, the CPU usage jumps to 50+% for the duration until the timeout.
    I then made an identical SP but rather than returning the data directly from the SELECT, I instead create a table variable, INSERT into it with the EXACT SAME select statement and then do a SELECT on the table variable to return the results. NO PROBLEM!!
    The query remained as fast in ASP as it does from Query Analyzer. I could alternate between the two calls by changing my ASP page and the original SP times out but the new SP does not.
    So I got all excited that I was onto something but then, I then thought that maybe I should test to see that this does not have to do with simply creating a new SP, so I created another new SP with the EXACT same content as the troublesome SP but changed the name by appending "_test" to the name. I modified my ASP to call the new SP and IT DID NOT FAIL EITHER! I could then alternate between the two SPs with the SAME EXACT contect and one would fail while the other would not!
    What could cause this?!? I checked Perfmon and didn't notice any big jumps in any counters between calling one versus calling the other!
    Any ideas?!?
    Thanks in advance!
  19. Argyle New Member

    quote:Originally posted by larrykl
    During the call from ASP, the CPU usage jumps to 50+% for the duration until the timeout.
    On the web server or the sql server?

    If it works with just creating a new SP it could have been a bad query plan. Try running DBCC FREEPROCCACHE and test the old SP as well.
  20. larrykl New Member

    Hi Argyle, the web server and SQL server are on the same box.
    So would the query plan just eventually go stale? The SP works fine for quite a while when I restart SQL Server then eventually has this trouble.
    I will definitely try running DBCC FREEPROCCACHE the next time the problem occurs.
    How frequently should that be run?
  21. Argyle New Member

    Well is it the IIS service or SQL service using the cpu? You can see that in perfmon or task manager.

    That DBCC FREEPROCCACHE command should only be run when testing. It could be that you have problems with your parameters in the SP (if it uses parameters). The plan created will be based on the parameters used on the first run. This plan might not be good for other parameters. Create the SP with the "WITH RECOMPILE" option and see if that helps. Another optin to get around parameter issues is to assign all stored procedure parameters to local variables in the SP itself and then use the local variables in your queries.
  22. larrykl New Member

    Hi Argyle,

    It's SQL service that is using CPU. I can try WITH RCOMPILE but isn't this guaranteed to cause a performance slowdown?
    I can also try your idea about assigning the params to local variables but what is the idea behind doing that? Also, it still doesn't explain the difference between ASP and QA. During my testing, I could call the SP from either with the same params.
  23. Argyle New Member

    The difference could be that when executing the command from ASP it's not identified exactly the same as the one from QA and thus won't use the same query plan. For example if not both queries are executed as 'dbo.' or if the ASP application use login 'myweblogin' and in QA you are logged in as 'sa'. It's worth a try. If the recompile doesn't take long time it's no big issue to use it to avoid having some queries taking very long time.

    The deal with local variables has to do with so called parameter sniffing:
    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5051
  24. larrykl New Member

    Hey Argyle!
    That all makes sense! I was calling the SP the same way with the same login from ASP and QA, but this certainly sounds like something to confirm. I should be able to attempt this as soon as the problem occurs again.

    I haven't seen the problem yet since I starting using the table variable version of the SP. I'm wondering if that also has affected things so that the problem is not apparent any more.

    I'll keep you posted!
  25. larrykl New Member

    OK!!!
    The problem happended AGAIN, this time with the table variable version of my SP. I could call the SP from query anaylzer and it was FAST. When I tried to call if from my ASP page, it would timeout.
    I called this from QA:

    DBCC FREEPROCACHE

    I could then call the SP again from ASP without the timeout. This occurred a couple of hours after my optimization job ran. My optimization plan rebuilds indexes. Why would this issue occur?
    Do I need to run DBCC FREEPROCACHE as part of my optimization plan?!?

    Only this one stored procedure seems to have the problem.
    Does DBCC FREEPROCACHE free all of the SP cached plans?

    Any light shed on this issue would be VERY helpful.
    Thanks!
  26. Argyle New Member

    Do not use DBCC FREEPROCACHE, it will empty the cache on the entire server. Instead try and fix the specific SP.

    Again it sounds like a bad query plan. It's good for some parameters but not others. Do you use the same inparameters when testing from AQ and ASP? Run Profiler when you execute from AQ and from ASP and compare the data that is actually being executed.

    In any case I would try adding "WITH RECOMPILE" after the "CREATE PROCEDURE" statment to see if it helps. I would also do a test with assigning the parameters to local variables.
  27. larrykl New Member

    Hi Argyle,

    I'll try the local variable experiment first. Won't adding the WITH RECOMPILE statement hurt performance also? Doesn't that force a recompile each time?
    I use the same exact parameters when calling via ASP compared to calling via QA so parameters are not likely the issue, right?
    The next time the problem occurs, I will force a recomiple on the SP rather than FREEPROCCACHE to see if that helps. My guess is that it will also work. Doesn't FREEPROCCACHE cause a recompile anyway?

    Thanks....
  28. larrykl New Member

    NEW INFO!

    OK, The problem hasn't been happening much lately but I'm now copying this entire database to a new SQL Server and it again sprung its ugly head. This time I saw a couple of different symptoms. Sometimes the Stored Proc would timeout when called from ASP but NOT from query analyzer and sometimes it would timeout from both. This time, I could not get it to clear up by forcing a recompile of the stored proc or by rebuilding indexes or by calling FREEPROCCACHE. When the problem occurs, there is be lots of locking going on in tempdb and the three tables involved in the query.

    I then tried Argyle's suggestion and copied the input parameters to local variables and used the local variables throughout the stored proc instead of the passed in variables and this WORKED!

    My guess is that this is an indication that the query plan was getting confused in some way by the use of the input parameters. Any ideas about this?!?
  29. larrykl New Member

    One extra piece of info I just discovered:

    In my large query statement, I have a in the where clause "WHERE rank>(((@pg-1)*@items_per_page)+1) and rank<=(@pg*@items_per_page)"

    @pg and @items_per_page are passed in parameters. If calculate these values into local variables @min and @max and make the where clase "WHERE rank>@min AND rank<=@max", this by itself seems to make the problem go away. Does using input parameters like this screw up the query plan?!?
  30. Adriaan New Member

    The calculation in itself seems to be the culprit. If you include a calculation in a WHERE statement, it must be evaluated for each row in the table that contains the 'rank' column. If you use a variable instead of a calculation on a variable, you have a much better chance that indexes can be used.

    Avoid doing any calculation within a query statement if the result of the calculation does not depend on the data being queried.

Share This Page