none
Timeout Expired | Server Error | Query issues ? To Cache, or not to cache ? RRS feed

  • Question

  • Howdy! 
    I am working with an application that goes out from our server to grab data from a school's database and return a list of students for that particular school. I am having issues when I try to search for students by "@Lastname". The code base has been put together over the course of a decade and I just inherited it and all of its problems.
    I think that the query is asking a little too much of SQL and causing it to throw the exception. I am very new to how this is interacting with SQL and after doing some digging, I feel that cacheing the student data on our server on page load may be an ideal approach to this issue? Please correct me if I'm wrong! 
    Here is the Stack Trace Issue that I am given after the server times out:

    Source Error:

    Line 174:            GridView1.DataBind()
    Line 175:        Catch ex As Exception
    Line 176:            Throw ex
    Line 177:        Finally
    Line 178:            con.Close()

    Stack Trace:

    [Win32Exception (0x80004005): The wait operation timed out]

    [SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
       Search.GetData() in C:\Users\User.Name\Desktop\Development\requestTool\FPSvII\FPS\RoutingServices\Search.aspx.vb:176
       Search.btnSearchView1_Click(Object sender, EventArgs e) in C:\Users\User.Name\Desktop\Development\requestTool\FPSvII\FPS\RoutingServices\Search.aspx.vb:117
       System.Web.UI.WebControls.Button.OnClick(EventArgs e) +11750641
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +150
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +6016

    The GetData() Function:

    Private Function GetData() As DataTable
            Dim dt As New DataTable()
            Dim cmd As New SqlCommand()
            Dim strConnString As String = ConfigurationManager.ConnectionStrings("TrackingConnectionString").ConnectionString
            Dim con As New SqlConnection(strConnString)
            Dim sda As New SqlDataAdapter()
    
            cmd.CommandTimeout = 30
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "spSearchGridView"
            cmd.Parameters.Add("@txtRequestID", Data.SqlDbType.VarChar).Value = txtRequestID.Text
            cmd.Parameters.Add("@RadioButtonIndexID", Data.SqlDbType.Int).Value = RadioButtonList1.SelectedIndex
    
            If txtFromDateView1.Text = "" Then
                cmd.Parameters.Add("@FromDateView1", Data.SqlDbType.DateTime2).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@FromDateView1", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtFromDateView1.Text)
            End If
    
            If txtFromDateView2.Text = "" Then
                cmd.Parameters.Add("@FromDateView2", Data.SqlDbType.DateTime2).Value = DBNull.Value
            Else
                cmd.Parameters.Add("@FromDateView2", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtFromDateView2.Text)
            End If
    
            If txtToDateView1.Text = "" Then
                cmd.Parameters.Add("@ToDateView1", Data.SqlDbType.DateTime2).Value = Date.Now
            Else
                cmd.Parameters.Add("@toDateView1", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtToDateView1.Text)
            End If
    
            If txtToDateView2.Text = "" Then
                cmd.Parameters.Add("@ToDateView2", Data.SqlDbType.DateTime2).Value = Date.Now
            Else
                cmd.Parameters.Add("@toDateView2", Data.SqlDbType.DateTime2).Value = FormatDateTime(txtToDateView2.Text)
            End If
    
            cmd.Parameters.Add("@LocationIDView1", Data.SqlDbType.VarChar).Value = HiddenField2.Value
            cmd.Parameters.Add("@LocationIDView2", Data.SqlDbType.VarChar).Value = ddlLocationNameView2.SelectedValue
            cmd.Parameters.Add("@LocationIDView3", Data.SqlDbType.VarChar).Value = ddlLocationNameView3.SelectedValue
            cmd.Parameters.Add("@LastName", Data.SqlDbType.NVarChar).Value = txtLastName.Text
            cmd.Parameters.Add("@Role", Data.SqlDbType.VarChar).Value = HiddenField1.Value
            cmd.Connection = con
    
            Try
                con.Open()
                sda.SelectCommand = cmd
                sda.Fill(dt)
                Return dt
                GridView1.DataSource = cmd.ExecuteScalar
                GridView1.DataBind()
            Catch ex As Exception
                Throw ex
            Finally
                con.Close()
                sda.Dispose()
                con.Dispose()
            End Try
        End Function

    Any and all input is appreciated; so sorry for the novice question! 
    Thank you so much for your time! 


    :)

    Thursday, October 19, 2017 5:33 PM

Answers

  • I'm not sure where Access fits in, because this sure looks like a SQL Server Stored Procedure. However, I wanted to point out a logic error that is making this Stored Procedure take longer than it should (not to mention probably returning data that is not what you're expecting to get).

    Take a look at the following:

    If (@RadioButtonIndexID=1)And (@Role='FPS') AND (@LocationIDView2 is not null) AND (@LastName is not Null) AND (@FirstName is not Null)
    BEGIN
        -- One SELECT statement here, etc.etc.etc.
    END
    
    If (@RadioButtonIndexID=1)And (@Role='FPS') AND (@LocationIDView2 is not null)
    BEGIN
        -- A different SELECT statement here, etc.etc.etc.
    END
    

    Note that you do *NOT* have an else. Also note that the If's are identical (if you don't look at the @LastName and @FirstName clauses). So, why is this a problem? Because both of those If's will be true, and so you will be running both SELECTs, one after the other. Now, maybe that's what you wanted, I don't know. If you structured that differently, it would only run one of the SELECTS. Yes, you could probably just stick an ELSE before the second IF ... but, for better readability, I would re-structure it like this instead:

    If (@RadioButtonIndexID=1)And (@Role='FPS') AND (@LocationIDView2 is not null)
    BEGIN
        IF (@LastName is not Null) AND (@FirstName is not Null)
            -- One SELECT statement here
        ELSE
            -- A different SELECT statement here
    END

    Notice that only one SELECT or the other will execute, not both. You have at least one other set of Ifs like that.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, October 24, 2017 10:23 PM

All replies

  • This code returns the students by last name on the local server but does not work on the live implementation of the web site ! :( 

    :)

    Thursday, October 19, 2017 6:13 PM
  • Maybe the live server has much more students and spSearchGridView requires more time to complete. Perhaps, optimising the queries and indices would help. How large is your live database?

    Friday, October 20, 2017 7:31 AM
  • Hi,

    Did you connect to the database successfully? I suspect you connect server failed.

    You can use SqlConnection.ConnectionTimeout Property to check check if the database server is accessible?

    sets the Connection Timeout to 500 milliseconds  in the connection string as below.

    Data Source=(local);Initial Catalog=AdventureWorks;"
            + "Integrated Security=SSPI;Connection Timeout=30"

    Yours sincerely,

    Bob


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 20, 2017 7:59 AM
  • I think you are right on the money, the local test database is about 500,000 kb and I'm not sure about the live one; (not an admin and our admin/IT/database manager is out till Monday) but I am certain it is much larger and takes longer to query into. I'll start looking at query and indices optimization in order to try and make this work! Would I tweak the Linq to SQL code in the aspx.vb or change the query in Server Management Studio? 

    :)

    Friday, October 20, 2017 2:25 PM
  • Hello again Bob! You are such a trooper and I appreciate it! 
    I was able to connect to the database successfully on my machine, but I'm not sure if the 'database/admin' was, I'm fairly certain he was because you can query into the database and search the students by request id, just not by their last name. So I think that the LastName query can be tweaked to improve performance. But I have been looking for a solution to Connection Timeout on the server as opposed by just changing it in the web.config so you did solve a problem for me! Thank you Bob!

    :)

    Friday, October 20, 2017 2:28 PM
  • Try this: from SSMS, access your live database and try running the Stored Procedure, spSearchGridView, with the same parameters that you're using in your application. See how long it takes to run the query on the live database.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, October 20, 2017 2:37 PM
  • Hello BonnieB! 
    thank you for the response :) 

    I did as directed and I'm still getting a timeout exception. I also am using a copy of the live database so I can rule out the amount of data as a potential problem causer. I attempted to modify the Stored Procedure with the 'WITH (NOLOCK)' workaround, and it works A-OK on my local machine, a lot quicker than before, but after awhile, I am greeted with a connection timeout on my local machine. A strange caveat though! If I am getting the exception timeout error and I go into SSMS and Execute the Query from within the 'Modify' pane and try to run the request again, it works! Lightening fast, and without error. 

    The live site is accessing a database on a different server, so the query is going from client, to server, to server (access database) and then back again. Could this communication be causing the timeout error? I've checked the activity logs, and with the (NOLOCK), less resources are being used in order to complete the query on my local machine; the Average Duration (ms) of the query in question taking only 2404(ms)

    Here is the Stored Process in question if it will help inform you at all as to why these things are happening! Thank you so much for your patience, input, and expertise! 

    USE [Tracking]
    GO
    /****** Object:  StoredProcedure [dbo].[spSearchGridview]    Script Date: 10/24/2017 08:59:27 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[spSearchGridview]
    	-- Add the parameters for the stored procedure here
    	@txtRequestID nvarchar(50),
    	@RadioButtonIndexID int,
    	@LocationIDView1 nvarchar(15),
    	@LocationIDView2 nvarchar(15),
    	@LocationIDView3 nvarchar(15),
    	@LastName nvarchar(50),
    	@FirstName nvarchar(50),
    	@FromDateView1 datetime2,
    	@FromDateView2 datetime2,
    	@ToDateView1 datetime2,
    	@ToDateView2 datetime2,
    	@Role nvarchar(50)
    AS
    If (@RadioButtonIndexID=0) And (@Role='FPS')
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
                          RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
                          RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
                          RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    FROM         RSRequest WITH (NOLOCK) INNER JOIN 
                          Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
                          RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
                          RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    WHERE     (RSRequest.ID = @txtRequestID)
    END
    
    Else if (@RadioButtonIndexID=0)
    
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
                          RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
                          RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
                          RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    FROM         RSRequest WITH (NOLOCK) INNER JOIN
                          Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
                          RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
                          RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    WHERE     (RSRequest.ID = @txtRequestID) and (RSRequest.LocationID = @LocationIDView1)
    END
    
    
    
    If (@RadioButtonIndexID=1)And (@Role='FPS') AND (@LocationIDView2 is not null) AND (@LastName is not Null) AND (@FirstName is not Null) AND (@FromDateView1 is not null)
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
                          RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
                          RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
                          RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    FROM         RSRequest WITH (NOLOCK) INNER JOIN
                          Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
                          RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
                          RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    
    WHERE (RSRequest.LocationID = @LocationIDView2) AND (RSStudent.LastName Like '%' + @LastName + '%') AND (RSStudent.FirstName Like '%' + @FirstName + '%') AND (RSRequest.CreateDate between @FromDateView1 AND @ToDateView1)AND (RSRequest.RequestPage = 'Student')
    END
    
    
    If (@RadioButtonIndexID=1)And (@Role='FPS') AND (@LocationIDView2 is not null) AND (@LastName is not Null) AND (@FirstName is not Null)
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
                          RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
                          RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
                          RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    FROM         RSRequest WITH (NOLOCK) INNER JOIN
                          Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
                          RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
                          RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    
    WHERE (RSRequest.LocationID = @LocationIDView2) AND (RSStudent.LastName Like '%' + @LastName + '%') AND (RSStudent.FirstName Like '%' + @FirstName + '%') AND (RSRequest.RequestPage = 'Student')
    END
    
    If (@RadioButtonIndexID=1)And (@Role='FPS') AND (@LocationIDView2 is not null)
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
                          RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
                          RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
                          RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    FROM         RSRequest WITH (NOLOCK) INNER JOIN
                          Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
                          RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
                          RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    
    WHERE (RSRequest.LocationID = @LocationIDView2)AND (RSRequest.RequestPage = 'Student')
    END
    
    
    If (@RadioButtonIndexID=1)And (@Role<>'FPS') AND (@LastName is not Null) AND (@FirstName is not Null) AND (@FromDateView1 is not null)
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
                          RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
                          RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
                          RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    FROM         RSRequest WITH (NOLOCK) INNER JOIN
                          Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
                          RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
                          RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    
    WHERE (RSRequest.LocationID = @LocationIDView2) AND (RSStudent.LastName Like '%' + @LastName + '%') AND (RSStudent.FirstName Like '%' + @FirstName + '%') AND (RSRequest.CreateDate between @FromDateView1 AND @ToDateView1)AND (RSRequest.RequestPage = 'Student')
    END
    
    If (@RadioButtonIndexID=1)And (@Role<>'FPS') AND (@LastName is not Null) AND (@FirstName is not Null)
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
                          RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
                          RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
                          RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    FROM         RSRequest WITH (NOLOCK) INNER JOIN
                          Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
                          RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
                          RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    
    WHERE (RSRequest.LocationID = @LocationIDView2) AND (RSStudent.LastName Like '%' + @LastName + '%') AND (RSStudent.FirstName Like '%' + @FirstName + '%') AND (RSRequest.RequestPage = 'Student')
    END
    
    
    Else
    
    If (@RadioButtonIndexID=2)
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
        SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
                          RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
                          RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
                          RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    FROM         RSRequest WITH (NOLOCK) INNER JOIN
                          Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
                          RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
                          RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    WHERE     (RSRequest.LocationID = @LocationIDView3)AND (RSRequest.CreateDate between @FromDateView2 AND @ToDateView2)AND (RSRequest.RequestPage <> 'Student')
    END
    
    


    :)

    Tuesday, October 24, 2017 8:50 PM
  • I'm not sure where Access fits in, because this sure looks like a SQL Server Stored Procedure. However, I wanted to point out a logic error that is making this Stored Procedure take longer than it should (not to mention probably returning data that is not what you're expecting to get).

    Take a look at the following:

    If (@RadioButtonIndexID=1)And (@Role='FPS') AND (@LocationIDView2 is not null) AND (@LastName is not Null) AND (@FirstName is not Null)
    BEGIN
        -- One SELECT statement here, etc.etc.etc.
    END
    
    If (@RadioButtonIndexID=1)And (@Role='FPS') AND (@LocationIDView2 is not null)
    BEGIN
        -- A different SELECT statement here, etc.etc.etc.
    END
    

    Note that you do *NOT* have an else. Also note that the If's are identical (if you don't look at the @LastName and @FirstName clauses). So, why is this a problem? Because both of those If's will be true, and so you will be running both SELECTs, one after the other. Now, maybe that's what you wanted, I don't know. If you structured that differently, it would only run one of the SELECTS. Yes, you could probably just stick an ELSE before the second IF ... but, for better readability, I would re-structure it like this instead:

    If (@RadioButtonIndexID=1)And (@Role='FPS') AND (@LocationIDView2 is not null)
    BEGIN
        IF (@LastName is not Null) AND (@FirstName is not Null)
            -- One SELECT statement here
        ELSE
            -- A different SELECT statement here
    END

    Notice that only one SELECT or the other will execute, not both. You have at least one other set of Ifs like that.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, October 24, 2017 10:23 PM
  • So sorry about any 'Access' confusion, in my previous post I was wondering if having to 'access/fetch from' one server after another in order to obtain the data was slowing down the query. Thank you BonnieB for such an exquisite response; I really appreciate you taking the time and providing such thorough examples. 

    I have refactored the query a lil and it does indeed return the data requested in a somewhat timely manner :) 
    I attempted to remove redundancies and nest things a little more appropriately using If/Else's. 

    The refactored query is as follows in case you are interested or have any more sage critiques! You are a lifesaver and a wonderful teacher/trove of valuable information!

    USE [Tracking]
    GO
    /****** Object:  StoredProcedure [dbo].[spSearchGridview]    Script Date: 10/25/2017 08:40:16 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[spSearchGridview]
    	-- Add the parameters for the stored procedure here
    	@txtRequestID nvarchar(50),
    	@RadioButtonIndexID int,
    	@LocationIDView1 nvarchar(15),
    	@LocationIDView2 nvarchar(15),
    	@LocationIDView3 nvarchar(15),
    	@LastName nvarchar(50),
    	@FirstName nvarchar(50),
    	@FromDateView1 datetime2,
    	@FromDateView2 datetime2,
    	@ToDateView1 datetime2,
    	@ToDateView2 datetime2,
    	@Role nvarchar(50)
    AS
    IF (@RadioButtonIndexID=0) And (@Role='FPS')
    
    	BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    		-- Insert statements for procedure here
    		SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
    		           RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
    			   RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
    			   RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
                          
    		FROM         RSRequest WITH (NOLOCK) INNER JOIN 
    
    		                  Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
    			          RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
    				  RSStudent ON RSRequest.ID = RSStudent.RSRequestID
                          
    		WHERE     (RSRequest.ID = @txtRequestID)
    	END
    
    ELSE IF (@RadioButtonIndexID=0)
    
    	BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    		-- Insert statements for procedure here
    		SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
    			   RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
    			   RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
    			   RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
                          
    		FROM         RSRequest WITH (NOLOCK) INNER JOIN
    			              Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
    				      RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
    				      RSStudent ON RSRequest.ID = RSStudent.RSRequestID
                          
    		WHERE     (RSRequest.ID = @txtRequestID) and (RSRequest.LocationID = @LocationIDView1)
    	END
    
    
    IF (@RadioButtonIndexID=1) AND (@Role='FPS') 
    
    	BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    		IF(@LastName is not Null) AND (@FirstName is not Null) AND (@LocationIDView2 is not null) AND (@FromDateView1 is not null)
    		-- Insert statements for procedure here
    			SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
    				   RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
    				   RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
    				   RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
                          
    			FROM         RSRequest WITH (NOLOCK) INNER JOIN
    					     Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
    					     RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
    					     RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    
    			WHERE (RSRequest.LocationID = @LocationIDView2) AND (RSStudent.LastName Like '%' + @LastName + '%') AND (RSStudent.FirstName Like '%' + @FirstName + '%') AND (RSRequest.CreateDate between @FromDateView1 AND @ToDateView1)AND (RSRequest.RequestPage = 'Student')
    
    
    		ELSE IF (@RadioButtonIndexID=1) AND (@Role<>'FPS') AND (@FromDateView1 is not null)
    			BEGIN
    				IF (@LastName is not Null) AND (@FirstName is not Null)
    				-- SET NOCOUNT ON added to prevent extra result sets from
    				-- interfering with SELECT statements.
    				SET NOCOUNT ON;
    
    				-- Insert statements for procedure here
    				SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
    					   RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
    					   RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
    					   RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    			
    				FROM         RSRequest WITH (NOLOCK) INNER JOIN
    					          Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
    						  RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
    						  RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    
    				WHERE (RSRequest.LocationID = @LocationIDView2) AND (RSStudent.LastName Like '%' + @LastName + '%') AND (RSStudent.FirstName Like '%' + @FirstName + '%') AND (RSRequest.CreateDate between @FromDateView1 AND @ToDateView1)AND (RSRequest.RequestPage = 'Student')
    			END
    		ELSE IF (@RadioButtonIndexID=2)
    				BEGIN
    				-- SET NOCOUNT ON added to prevent extra result sets from
    				-- interfering with SELECT statements.
    				SET NOCOUNT ON;
    
    					-- Insert statements for procedure here
    					SELECT     CONVERT(VARCHAR(8), dbo.GetWorkingMin(RSRequest.CreateDate, GETDATE()) / 60) + ' Hrs' AS ElapsedTime, CONVERT(VARCHAR(8), DATEDIFF(Hour, 
    						   RSRequest.CreateDate, GETDATE()), 108) AS TotalTime, RSRequest.ID, CONVERT(varchar, RSRequest.CreateDate, 101) AS CreateDate, 
    						   RSRequest.RequestStatus, RSRequest.RequestType, N' - ' + RSStudent.LastName AS NewStudent, N' - ' + RSStudent.FirstName AS NewStudent, RSRequest.RequestPage, Location.Location, 
    						   RSContract.Description, RSContract.LocationID, RSStudent.RSRequestID, RSStudent.LastName, RSRequest.ClosedComments
    					FROM         RSRequest WITH (NOLOCK) INNER JOIN
    						     Location ON RSRequest.LocationID = Location.LocationID INNER JOIN
    					             RSContract ON RSRequest.ContractID = RSContract.ID LEFT OUTER JOIN
    						     RSStudent ON RSRequest.ID = RSStudent.RSRequestID
    					WHERE     (RSRequest.LocationID = @LocationIDView3)AND (RSRequest.CreateDate between @FromDateView2 AND @ToDateView2)AND (RSRequest.RequestPage <> 'Student')
    				END
    	END


    :)

    Wednesday, October 25, 2017 8:14 PM
  • Well, I'm glad that I could help! And here's some more advice:


    I thought it might be obvious when you refactored your Stored Proc, but I guess not. You don't have to issue a SET NOCOUNT ON statement every time. Put it once, at the top of the Procedure, and it's set for the whole thing.

    And, unfortunately, you still have a few problems:

    You have a "block" of statements that starts with this IF:

    IF (@RadioButtonIndexID=1) AND (@Role='FPS')
    BEGIN
        IF(@LastName is not Null) AND (@FirstName is not Null) AND (@LocationIDView2 is not null) AND (@FromDateView1 is not null)
      -- then your SELECT statement, etc.etc.
        ELSE IF (@RadioButtonIndexID=1) AND (@Role<>'FPS') AND (@FromDateView1 is not null)
            -- etc.etc.
    --etc.etc.

     

    Do you notice anything wrong with that ELSE IF? The @Role <>'FPS' will NEVER be true!!  Because the very first IF is checking that @Role='FPS' and everything in the "block" will be executed only when @Role='FPS'.  I'm sure you know this, but you probably just missed it.

    You also have, in that same "block" of statements, an IF (RadioButtonIndexID=2), which will also never be true, for the same reason.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Wednesday, October 25, 2017 10:55 PM
  • Hope you had a nice weekend BonnieB! 
    Just wanted to let you know that I have nocount happening only once at the top of the page and I have removed the redundant IF blocks and fixed a user authentication sign in error thanks to you! 

    You are my hero and I hope this week is kind to you :) 


    :)

    Monday, October 30, 2017 8:03 PM
  • I had a very nice weekend, thank you! Hope you did too!

    It's nice to see that you've got this all working now ... glad I could help!!   =0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, October 30, 2017 10:25 PM