Solved

ASP.NET SqlDataRead Stored Procedure Issue

Posted on 2011-09-10
4
324 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:pangotek
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36516245
"nothing has changed" except eventually the explain plan inside the query.
aka the data amount, it's statistics etc ...

there are usually 2 situations:
* index missing on the conditions in the procedure
* parameter sniffing in the procedure:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1258-Speedier-Execution-of-Stored-Procedures-in-SQL-Server.html

hope this helps
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36520022
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.
0
 

Author Comment

by:pangotek
ID: 36541845
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

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36543425
>> 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.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now