asked on

ASP.NET SqlDataRead Stored Procedure Issue

Have a weird issue. I have a piece of code that calls a stored procedure. The code has been running fine for months, but all of a sudden it keeps timing out. Neither the code or the SP has been changed. If I run the SP directly in SQL Server Manager it runs in under a second, but if i try and access it via ASP.NET it times out.

I spent about 4 hours yesterday debugging this issue and then all of a sudden it started to work again. When I debug, it's timing out at the

using (SqlDataReader myReader = cmd.ExecuteReader())

line. But this morning it started to do it again (timing out). I just dropped and recreated the SP and everything has started to work again. But I'm still not sure what is causing the problem. Other SP, in the same process are running just fine. Any thoughts?

Example of SP call:

        DataTable st = new DataTable();
        using (SqlConnection conn = new SqlConnection(dbConn))
        {
            using (SqlCommand cmd = new SqlCommand("categoryDailyLogGet", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@LocationID", locationID));
                cmd.Parameters.Add(new SqlParameter("@LogDate", Convert.ToDateTime(yesterday)));
                conn.Open();
                using (SqlDataReader myReader = cmd.ExecuteReader())
                {
                    st.TableName = "Logs";
                    st.Load(myReader);
                    conn.Close();
                    return st;
                }
            }
        }

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Unfortunately, without seeing the specific Stored Procedure it is difficult to say. But if I was to hazard a guess I would concur with angelIII diagnostic of parameter sniffing.

I also agree with angelIII in that you have to get beyond "Neither the code or the SP has been changed", otherwise you will not be able to resolve this. The only time that could potentially apply, if it was a read-only database and the data had not changed. I suspect that is not your case.

pangotek

ASKER

Added the DECLARE inside the SP (SP1) to re-declare the parameters and this seemed to work. The SP isn't that complex and runs in about a second if there is a lot of data and sub-second if there is very little data from SSMS. Before I added the RE-DECLARE inside the SP it would work for a long time and then would start to time out and would continue to time out until I deleted and recreated the SP (call to SP from ASP.NET code is included in original question). This has just started to happen to another SP (SP2, called from ASP.NET the same way as above SP). Do I need to go through all my SP's and apply this solution in fear that they will randomly start timing out? I've checked my indexes on the table and they are all working. Before I changed the SP's if I would call them from SSMS directly with the same parameters as being called from ASP.NET they would run just fine (sub-second to just over a second). Any help would be appreciated. Thanks!

SP1:

USE [sn]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[categoryDailyLogGet]
	@LocationID int,
	@LogDate date
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @LocationID1 int = @LocationID
	DECLARE @LogDate1 date = @LogDate

	DECLARE @DailyLog TABLE
	(
		categoryEntryID int,
		createdDate datetime,
		logDate date,
		entryText varchar(MAX),
		isPriority bit,
		parentEntryID int,
		categoryID int,
		isMeetingTopic bit,
		locationID int,
		author varchar(100),
		path varchar(500),
		name varchar(200)
	)

	IF(EXISTS(SELECT TOP 1 ce.categoryEntryID FROM categoryEntries ce INNER JOIN categoryEntriesLocations cel ON ce.categoryEntryID = cel.categoryEntryID WHERE ce.logDate = @LogDate1 AND cel.locationID = @LocationID1))
	BEGIN
		INSERT INTO @DailyLog
		SELECT ce.categoryEntryID,ce.createdDate,ce.logDate,ce.entryText,ce.isPriority,ce.parentEntryID,cel.categoryID,cel.isMeetingTopic,cel.locationID,u.firstName + ' ' + LEFT(u.lastName,1) + '.' as author,doc.[path],doc.name
		FROM categoryEntries ce
		INNER JOIN categoryEntriesLocations cel
		ON ce.categoryEntryID = cel.categoryEntryID
		INNER JOIN users u
		ON ce.authorUserID = u.userID
		LEFT OUTER JOIN documentsToCategory dtc
		ON ce.categoryEntryID = dtc.categoryEntryID
		LEFT OUTER JOIN documents doc
		ON dtc.documentID = doc.documentID
		WHERE cel.locationID = @LocationID1
		AND ce.status = 2
		AND ce.logDate = @LogDate1

		INSERT INTO @DailyLog
		SELECT ce.categoryEntryID,ce.createdDate,ce.logDate,ce.entryText,ce.isPriority,ce.parentEntryID,cel.categoryID,cel.isMeetingTopic,cel.locationID,u.firstName + ' ' + LEFT(u.lastName,1) + '.' as author,doc.[path],doc.name
		FROM categoryEntries ce
		INNER JOIN categoryEntriesLocations cel
		ON ce.categoryEntryID = cel.categoryEntryID
		INNER JOIN users u
		ON ce.authorUserID = u.userID
		LEFT OUTER JOIN documentsToCategory dtc
		ON ce.categoryEntryID = dtc.categoryEntryID
		LEFT OUTER JOIN documents doc
		ON dtc.documentID = doc.documentID
		WHERE cel.locationID = @LocationID1
		AND ce.status = 2	
		AND ce.categoryEntryID in (SELECT parentEntryID FROM @DailyLog WHERE parentEntryID IS NOT NULL)

		SELECT c.categoryID,c.categoryName,CASE WHEN ent.parentEntryID IS NULL THEN 'Log' ELSE 'Reply' END as Type,ent.categoryEntryID
		,ent.author,ent.createdDate,ent.logDate,ent.entryText,ent.isPriority,ent.isMeetingTopic,ent.parentEntryID,cd.categoryOrder,ent.[path],ent.name,dbo.HaveChildern(ent.categoryEntryID) as hasChildern
		FROM categoryDisplay cd
		INNER JOIN categories c
		ON cd.categoryID = c.categoryID
		LEFT OUTER JOIN (SELECT DISTINCT *
		FROM @DailyLog) ent
		ON cd.categoryID = ent.categoryID
		AND cd.locationID = ent.locationID
		WHERE @LogDate1 BETWEEN cd.startDate AND cd.endDate
		AND c.statusID = 2
		AND cd.locationID = @LocationID1
		ORDER BY cd.categoryOrder,ent.createdDate
	END
	ELSE
	BEGIN
		SELECT c.categoryID,c.categoryName,NULL as Type,NULL as categoryEntryID
		,NULL as author,NULL as createdDate,NULL as logDate,NULL as entryText,NULL as isPriority
		,NULL as isMeetingTopic,NULL as parentEntryID,NULL as categoryOrder,NULL as [path],NULL as name,NULL as hasChildern
		FROM categoryDisplay cd
		INNER JOIN categories c
		ON cd.categoryID = c.categoryID
		WHERE @LogDate1 BETWEEN cd.startDate AND cd.endDate
		AND c.statusID = 2
		AND cd.locationID = @LocationID1
		ORDER BY cd.categoryOrder
	END

END

Open in new window

SP2

USE [sn]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[aaDailyEntriesGet]
	@LocationID INT,
	@LogDate Date,
	@CategoryIDs VARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;
		DECLARE @LocationID1 INT = @LocationID
		DECLARE @LogDate1 Date = @LogDate
		DECLARE @CategoryIDs1 VARCHAR(MAX) = @CategoryIDs

		SELECT ce.categoryEntryID,ce.createdDate,ce.logDate,ce.entryText,ce.isPriority,ce.parentEntryID,cel.categoryID,cel.isMeetingTopic,cel.locationID,u.firstName + ' ' + LEFT(u.lastName,1) + '.' as author,doc.documentID,doc.[path],doc.name,CAST(0 as BIT) as hasChildern
		FROM categoryEntries ce
		INNER JOIN categoryEntriesLocations cel
		ON ce.categoryEntryID = cel.categoryEntryID
		INNER JOIN users u
		ON ce.authorUserID = u.userID
		INNER JOIN dbo.List2Table(@CategoryIDs1,',') l2t
		ON cel.categoryID = l2t.item
		LEFT OUTER JOIN documentsToCategory dtc
		ON ce.categoryEntryID = dtc.categoryEntryID
		LEFT OUTER JOIN documents doc
		ON dtc.documentID = doc.documentID
		WHERE cel.locationID = @LocationID1
		AND ce.status = 2
		AND ce.logDate = @LogDate1
		AND ce.parentEntryID IS NULL
		AND ce.categoryEntryID NOT IN (SELECT ce.parentEntryID 
		FROM categoryEntries ce
		INNER JOIN categoryEntriesLocations cel
		ON ce.categoryEntryID = cel.categoryEntryID
		INNER JOIN dbo.List2Table(@CategoryIDs1,',') l2t
		ON cel.categoryID = l2t.item
		WHERE ce.status = 2
		AND ce.logDate = @LogDate1
		AND cel.locationID = @LocationID1
		AND ce.parentEntryID IS NOT NULL)
		UNION
		SELECT ce.categoryEntryID,ce.createdDate,ce.logDate,ce.entryText,ce.isPriority,ce.parentEntryID,cel.categoryID,cel.isMeetingTopic,cel.locationID,u.firstName + ' ' + LEFT(u.lastName,1) + '.' as author,doc.documentID,doc.[path],doc.name,CAST(1 as BIT) as hasChildern
		FROM categoryEntries ce
		INNER JOIN categoryEntriesLocations cel
		ON ce.categoryEntryID = cel.categoryEntryID
		INNER JOIN users u
		ON ce.authorUserID = u.userID
		LEFT OUTER JOIN documentsToCategory dtc
		ON ce.categoryEntryID = dtc.categoryEntryID
		LEFT OUTER JOIN documents doc
		ON dtc.documentID = doc.documentID
		WHERE cel.locationID = @LocationID1
		AND ce.status = 2	
		AND ce.categoryEntryID IN (SELECT ce.parentEntryID 
		FROM categoryEntries ce
		INNER JOIN categoryEntriesLocations cel
		ON ce.categoryEntryID = cel.categoryEntryID
		INNER JOIN dbo.List2Table(@CategoryIDs1,',') l2t
		ON cel.categoryID = l2t.item
		WHERE ce.status = 2
		AND ce.logDate = @LogDate1
		AND cel.locationID = @LocationID1
		AND ce.parentEntryID IS NOT NULL)
END

Open in new window

Anthony Perkins

>> Do I need to go through all my SP's and apply this solution in fear that they will randomly start timing out?<<
This is one of the more difficult problems to resolve, not only for us Developers, but also for the SQL Server team that works on the SQL optimizer. There is no easy answer. But before you do any of that, I would consider optimizing your query. Just from a cursory look I see a number of improvements that can be made:
1. If the @DailyLog table is large use a tempory table.
2. Index parentEntryID
3. Use INNER JOIN instead of IN
4. Change SELECT DISTINCT * to SELECT ... GROUP BY and index the appropriate columns.
5. Make sure you have appropriate indexes on all the permanent tables.

You should also always identify the specific query that is taking a long time, that way you can focus on that.