• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

SQL Stored Procedure Timing out in Web Application, BUT NOT in SQL Studio

I am running in to an issue that I can't figure out how to trouble shoot.  It is very weird.  I have a fairly complex stored procedure.  When I execute the SP from Server Management Studio, it works perfectly.  When I pass values that should return no records, I receive a result in less than a second.  However, if I call that same stored proc from code in my web application, it times out.  I've tried executing the code from classic .asp and from an asp.net 2.0 application.  Both time out.  If I bump up the execution time for these web apps, after about 2 minutes, I get the desired results.

I am sending the exact same parameters to the exact same SP from the apps as with Management Studio.  Very weird.  Anyone know how to start troubleshooting something like this?  I am at a loss.  I've copied and pasted the exact syntax of the SP into a text string and executed that from my web pages, and they work perfectly.  
0
Steve Krile
Asked:
Steve Krile
  • 19
  • 7
  • 2
  • +3
1 Solution
 
Ram4020Commented:
Start the profiler and see what is happening if exequted from asp page.
Sql script in the profiler should be same as you try in SSMS.
Try coping the sql from profiler and run the sql in SSMS. Use the same user as found in profiler. Check the time taken.
Check the Duration in the profiler. If the duration is same as in SSMS then the issue is not with database but with asp page.

0
 
rajvjaCommented:
Hi,

  Try setting the more connectiontimeout and see.

But dont give high value as it affects the performance of your site and server
0
 
Steve KrileAuthor Commented:
Well, I have a problem running profiler because I am not an admin on the Production server where this problem rears its ugly head.  However, I have tried and failed to run this SP from three different approaches via code (two different approaches in classic .asp and using an ApplicationBlock approach in .aspx .net 2.0 page).  I am logging on to the Production server through SSMS using the exact same credentials as I use when connecting to the DB through the web pages.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Steve KrileAuthor Commented:
@rajvja - The SP returns no values in less than a second when executed from SSMS.  As I say in my original question, I DID up the timeout value and eventually the SP returns no values when executed from the web pages.
0
 
Steve KrileAuthor Commented:
@Ram4020 - I'm pretty sure the code to execute the SP from my web pages is sound.  I've replaced the problem proc name with another, and it executes just fine.
0
 
rajvjaCommented:
Whats your sproc name?
0
 
Steve KrileAuthor Commented:
[dbo].[usp_PercentReport]
0
 
Steve KrileAuthor Commented:
OH, I should mention that this is a new problem.  The proc has been working just fine until about 3 days ago.  No major changes to the DB that I know of.  And all my other procs seem to be firing just fine.
0
 
Steve KrileAuthor Commented:
Also, the proc works just fine when I point to our Test, Stage, and Development environments.
0
 
rajvjaCommented:
How big the sproc? I mean is it possible to declare a string and assign the script to that. Try to display and execute using that variable without calling sproc.
0
 
Steve KrileAuthor Commented:
Yeah, as I said in my original question ("I've copied and pasted the exact syntax of the SP into a text string and executed that from my web pages, and they work perfectly.  ")
0
 
rajvjaCommented:
can you post the code you are executing?
0
 
Steve KrileAuthor Commented:
Sure - This is the SP



ALTER PROCEDURE [dbo].[usp_PercentReport] 
(@TrainingID int, @EmployeeID int,@FacilityID int, @TrainingCategoryID int, @TrainingSubCategoryID int)
	
AS
	SET NOCOUNT ON

	DECLARE @thisDate datetime
	SET @thisDate = getDate()
	
	SELECT
		DISTINCT(pr.EmployeeID),
		pr.TrainingiD,
		pr.TrainingTitle,
		pr.FullName,
		dbo.udf_isCurrent_text(
			pr.LastTaken,
			dbo.udf_GetExpiration(pr.LastTakenTRID),
			(SELECT top 1(Attendance) from TrainingAttendee where TrainingRecordID = pr.LastTakenTRID AND EmployeeID = pr.EmployeeID),
			@thisDate,
			(SELECT top 1(PracticalScore) from TrainingAttendee where TrainingRecordID = pr.LastTakenTRID AND EmployeeID = pr.EmployeeID)),
		pr.LastTaken,
		pr.LastTakenTrid,
		e.EmployeeNumber
	FROM 
		dbo.udf_PercentReport(@TrainingID,@EmployeeID,@FacilityID,@TrainingCategoryID,@TrainingSubCategoryID,@thisDate) as pr
		INNER JOIN Employee as E with (nolock) on pr.EmployeeID = E.EmployeeID
	ORDER BY TrainingTitle, FullName

	RETURN

Open in new window

0
 
Steve KrileAuthor Commented:
This is the .net code
Dim TrainingID As Integer = 0

        Dim ds As New DataSet()
        Try
            ds = SqlHelper.ExecuteDataset(SetConnStr(CType(ddConn.SelectedValue, Integer)), _
                                          CommandType.StoredProcedure, _
                                          "usp_PercentReport", _
                                          params)
            If ds.Tables(0).Rows.Count = 0 Then
                Dim i As Integer = 1
            End If
            lbl1.Text = Now().ToLongTimeString
        Catch ex As Exception
            Dim err As String = ex.Message
            lbl1.Text = err
        End Try

Open in new window

0
 
rajvjaCommented:
No, front-end code which calls the sproc
0
 
Steve KrileAuthor Commented:
I am using this same approach (SqlHelper.ExecuteDataset) for all of my db interactions.  If I swap out another Stored Proc name and tweak the params everything works fine.
0
 
Steve KrileAuthor Commented:
Just for fun, I did this and I get my result back in 1 second with no timeout.
        Dim str As String = "SELECT " & _
      "   DISTINCT(pr.EmployeeID), " & _
      "   pr.TrainingiD, " & _
      "   pr.TrainingTitle, " & _
      "   pr.FullName, " & _
      "dbo.udf_isCurrent_text( " & _
      "	pr.LastTaken, " & _
      "	dbo.udf_GetExpiration(pr.LastTakenTRID), " & _
      "	(SELECT top 1(Attendance) from TrainingAttendee where TrainingRecordID = pr.LastTakenTRID AND EmployeeID = pr.EmployeeID), " & _
      "	getdate()," & _
      "	(SELECT top 1(PracticalScore) from TrainingAttendee where TrainingRecordID = pr.LastTakenTRID AND EmployeeID = pr.EmployeeID)), " & _
      "pr.LastTaken, " & _
      "pr.LastTakenTrid, " & _
      "e.EmployeeNumber " & _
      "FROM dbo.udf_PercentReport(0,NULL,NULL,NULL,NULL,getDate()) as PR INNER JOIN Employee as E on pr.EmployeeID = E.EmployeeID"




        Dim ds As New DataSet()
        Try
            ds = SqlHelper.ExecuteDataset(SetConnStr(CType(ddConn.SelectedValue, Integer)), _
                                          CommandType.Text, _
                                          str)

            lbl1.Text = Now().ToLongTimeString
        Catch ex As Exception
            Dim err As String = ex.Message
            lbl1.Text = err

Open in new window

0
 
rajvjaCommented:
try commenting the try catch block both places(sqlHelper and here) whether any error generates or not
0
 
Steve KrileAuthor Commented:
An error does generate. "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. "
0
 
Steve KrileAuthor Commented:
More fun.  I've set up the three ways on my .aspx page to execute this thing, and two ways fail.  Below is the code.  If I choose Case 1 or Case 3, I get the timeout.  Case three is the text generated by SSMS when you right-click/execute the SP.
Private Sub btn1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn1.Click
        Dim TrainingID As Integer = 0

        Dim sb As StringBuilder = New StringBuilder()
        sb.Append("DECLARE	@return_value int ")
        sb.Append("EXEC	@return_value = [dbo].[usp_PercentReport] ")
        sb.Append("@TrainingID = 0, ")
        sb.Append("@EmployeeID = NULL, ")
        sb.Append("@FacilityID = NULL, ")
        sb.Append("@TrainingCategoryID = NULL, ")
        sb.Append("@TrainingSubCategoryID = NULL ")
        sb.Append("SELECT	'Return Value' = @return_value")


        Dim params(4) As Data.SqlClient.SqlParameter
        params(0) = New SqlClient.SqlParameter("@TrainingID", TrainingID)
        params(1) = New SqlClient.SqlParameter("@EmployeeID", DBNull.Value)
        params(2) = New SqlClient.SqlParameter("@FacilityID", DBNull.Value)
        params(3) = New SqlClient.SqlParameter("@TrainingCategoryID", DBNull.Value)
        params(4) = New SqlClient.SqlParameter("@TrainingSubCategoryID", DBNull.Value)

        Dim str As String = "DECLARE @thisDate datetime   SET @thisDate = getDate()   " & _
        " SELECT " & _
      "   DISTINCT(pr.EmployeeID), " & _
      "   pr.TrainingiD, " & _
      "   pr.TrainingTitle, " & _
      "   pr.FullName, " & _
      "dbo.udf_isCurrent_text( " & _
      "	pr.LastTaken, " & _
      "	dbo.udf_GetExpiration(pr.LastTakenTRID), " & _
      "	(SELECT top 1(Attendance) from TrainingAttendee where TrainingRecordID = pr.LastTakenTRID AND EmployeeID = pr.EmployeeID), " & _
      "	getdate()," & _
      "	(SELECT top 1(PracticalScore) from TrainingAttendee where TrainingRecordID = pr.LastTakenTRID AND EmployeeID = pr.EmployeeID)), " & _
      "pr.LastTaken, " & _
      "pr.LastTakenTrid, " & _
      "e.EmployeeNumber " & _
      "FROM dbo.udf_PercentReport(0,NULL,NULL,NULL,NULL,@thisDate) as PR INNER JOIN Employee as E on pr.EmployeeID = E.EmployeeID"




        Dim ds As New DataSet()

        Select Case ddUse.SelectedValue
            Case 1
                Try
                    ds = SqlHelper.ExecuteDataset(SetConnStr(CType(ddConn.SelectedValue, Integer)), _
                                                  CommandType.StoredProcedure, _
                                                  "usp_PercentReport", _
                                                  params)

                    lbl1.Text = Now().ToLongTimeString
                Catch ex As Exception
                    Dim err As String = ex.Message
                    lbl1.Text = err
                End Try
            Case 2
                Try
                    ds = SqlHelper.ExecuteDataset(SetConnStr(CType(ddConn.SelectedValue, Integer)), _
                                                  CommandType.Text, _
                                                  str)

                    lbl1.Text = Now().ToLongTimeString
                Catch ex As Exception
                    Dim err As String = ex.Message
                    lbl1.Text = err
                End Try
            Case 3
                Try
                    ds = SqlHelper.ExecuteDataset(SetConnStr(CType(ddConn.SelectedValue, Integer)), _
                                                  CommandType.Text, _
                                                  sb.ToString)

                    lbl1.Text = Now().ToLongTimeString
                Catch ex As Exception
                    Dim err As String = ex.Message
                    lbl1.Text = err
                End Try

        End Select


    End Sub

Open in new window

0
 
rajvjaCommented:
This is strange.

Any permissions set on this sproc? can you check?
0
 
Steve KrileAuthor Commented:
Nope, no permissions.
0
 
TleeyahooCommented:
Is this SQL 2005 or 2008?  If so, set the MaxDegreesOfParallelism to 1.  It is a server option you can set via SSMS in the Advanced tab of the Server Properties window.
0
 
Steve KrileAuthor Commented:
2005.
0
 
Ram4020Commented:
Why cant you ask the dba to start profiler with xml statistics on. Then you know the real reason why it is slow.
0
 
Steve KrileAuthor Commented:
Right @Ram4020.  I'm jumping through those hoops now (IT Request submission, yadda yadda yadda).  In the meantime, I'm setting up an .aspx page to trigger good and bad results.
0
 
Anthony PerkinsCommented:
If I was to guess, I would say that your problem is parameter sniffing.

I suggest you re-write your Stored Procedure as follows:

ALTER PROCEDURE [dbo].[usp_PercentReport] (
    @TrainingID int,
    @EmployeeID int,
    @FacilityID int,
    @TrainingCategoryID int,
    @TrainingSubCategoryID int)
AS

DECLARE @thisDate datetime,
      @Attendance int,          -- Change data type appropriately
      @PracticalScore int          -- Change data type appropriately

SET NOCOUNT ON
 
CREATE TABLE #PercentReport (          -- Change all data types appropriately
      EmployeeID int,         
      TrainingID int,
      TrainingTitle varchar(50),
      FullName varchar(50),
      IsCurrentText bit,
      LastTaken datetime,
      LastTakenTRID int)

CREATE CLUSTERED INDEX #IX_PercentReport ON #PercentReport (EmployeeID)
CREATE NONCLUSTERED INDEX #IX_PercentReport_1 ON #PercentReport (TrainingTitle, FullName)

SET @thisDate = GETDATE()

SELECT      TOP 1                      -- No ORDER BY so don't care which one is returned
      @Attendance = Attendance,
      @PracticalScore = PracticalScore
FROM    TrainingAttendee
WHERE   TrainingRecordID = pr.LastTakenTRID
        AND EmployeeID = pr.EmployeeID

INSERT      #PercentReport(EmployeeID, TrainingID, TrainingTitle, FullName, IsCurrentText, LastTaken, LastTakenTRID)
SELECT      EmployeeID,
      TrainingID,
      TrainingTitle,
      FullName,
      dbo.udf_isCurrent_text(pr.LastTaken, dbo.udf_GetExpiration(pr.LastTakenTRID), @Attendance,  @thisDate, @PracticalScore),
      LastTaken,
      LastTakenTRID
FROM      dbo.udf_PercentReport(@TrainingID, @EmployeeID, @FacilityID,
                              @TrainingCategoryID, @TrainingSubCategoryID,
                              @thisDate)
                             
SELECT      DISTINCT
        pr.EmployeeID,
        pr.TrainingiD,
        pr.TrainingTitle,
        pr.FullName,
        IsCurrent_text,
        pr.LastTaken,
        pr.LastTakenTrid,
        e.EmployeeNumber
FROM    #PercentReport pr
        INNER JOIN Employee E WITH (NOLOCK) ON pr.EmployeeID = E.EmployeeID
ORDER BY
      TrainingTitle,
        FullName
 
DROP TABLE #PercentReport

RETURN
0
 
Steve KrileAuthor Commented:
@acperkins - Could you elaborate on "Parameter sniffing"?  I am not familiar with that term.
0
 
Steve KrileAuthor Commented:
OK, did some reading re:Parameter sniffing.  That was likely the issue.  I appreciate all of the code acperkins, and I'm pretty sure your approach would work.  However, I did something suggested by this post that worked (http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/)

I just added the following lines to my initial stored proc:

        DECLARE @fid int
        SET @fid = @FacilityID


And changed the reference lower down in the code:

 FROM dbo.udf_PercentReport(@TrainingID,@EmployeeID,@fid,@TrainingCategoryID,@TrainingSubCategoryID,@thisDate)




And now everything is right as rain.
ALTER PROCEDURE [dbo].[usp_PercentReport] 
(@TrainingID int, @EmployeeID int,@FacilityID int, @TrainingCategoryID int, @TrainingSubCategoryID int)
        
AS
        SET NOCOUNT ON

        DECLARE @thisDate datetime
        SET @thisDate = getDate()

        DECLARE @fid int
        SET @fid = @FacilityID
        
        SELECT
                DISTINCT(pr.EmployeeID),
                pr.TrainingiD,
                pr.TrainingTitle,
                pr.FullName,
                dbo.udf_isCurrent_text(
                        pr.LastTaken,
                        dbo.udf_GetExpiration(pr.LastTakenTRID),
                        (SELECT top 1(Attendance) from TrainingAttendee where TrainingRecordID = pr.LastTakenTRID AND EmployeeID = pr.EmployeeID),
                        @thisDate,
                        (SELECT top 1(PracticalScore) from TrainingAttendee where TrainingRecordID = pr.LastTakenTRID AND EmployeeID = pr.EmployeeID)),
                pr.LastTaken,
                pr.LastTakenTrid,
                e.EmployeeNumber
        FROM 
                dbo.udf_PercentReport(@TrainingID,@EmployeeID,@fid,@TrainingCategoryID,@TrainingSubCategoryID,@thisDate) as pr
                INNER JOIN Employee as E with (nolock) on pr.EmployeeID = E.EmployeeID
        ORDER BY TrainingTitle, FullName

        RETURN

Open in new window

0
 
mastooCommented:
I'm agreeing with ac but I didn't quite follow the solution.  Your proc gets optimized for the parameters passed the first time it runs.  These plans get cached based on connection characteristics, so your SSMS and application are likely using different cached plans.  You can flush the cache and run both ways with the same parameters to verify this.  An easy way to avoid this is to fool the query optimizer by only using inputs indirectly:

ALTER PROCEDURE [dbo].[usp_PercentReport]
(@TrainingID int, @EmployeeID int,@FacilityID int, @TrainingCategoryID int, @TrainingSubCategoryID int)
      
AS
  Declare @TrainingIDLocal int
  Set @TrainingIDLocal = @TrainingID

and do similar for the other variables, and then use the Local guys in your sql instead of the input parameters directly.

0
 
Steve KrileAuthor Commented:
You're right mastoo.  That is almost exactly what I did.  I'm pretty sure the execution plan was causing the delay, and after changing my SP I got a new execution plan (i'm also using the "fooling" logic you suggest).  
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 19
  • 7
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now