troubleshooting Question

Performance of a stored procedure vs ad-hoc SQL

Avatar of Jeff Tennessen
Jeff TennessenFlag for United States of America asked on
Microsoft SQL Server
9 Comments1 Solution810 ViewsLast Modified:
Hello,

I have a stored procedure that is taking 6-7 minutes to complete. If I copy the SQL from the proc and run it ad hoc in SQL Server Management Studio with the same variable values, it completes in 4-5 seconds.

I am running SQL Server 2005 Enterprise Edition 64-bit (SP2) on Windows Server 2003 Enterprise Edition 64-bit (SP2). The box has a Xeon quad-core processor (E5405 at 2 GHz) and 8 GB of RAM. It has 5 physical SCSI disks: 1 for data files, 1 for log files, 1 for the tempdb (which also holds the system pagefile), 1 for backups, and the boot drive. None are configured as RAID at present.

I've seen some posts both here and elsewhere from people having the same issue, but none of the suggested solutions seem to have any effect on my situation. I've tried dropping and recreating the stored procedure, but no luck. I've compared the execution plans of both, and they are different, but they are so complex that I'm at a loss to figure out how to use them to solve the problem. Just in case they would be helpful, I've attached them in a zip file. (Note: I had to change the extension of the plans to .txt to get them to upload, so if you want to look at them, just change them back to .sqlplan before opening them.)

Does anyone have a general idea why ad-hoc SQL would run faster than the exact same code compiled into a stored procedure? If anything, I would assume the sp would be faster, but in any case I would never expect it to take almost 100x longer.

Any suggestions are very much appreciated!

Jeff


--These are local variables to "stand in" for the parameters
DECLARE @SiteID AS bigint
DECLARE @BeginDate AS datetime
DECLARE @EndDate AS datetime
DECLARE @CategoryID AS int
 
SET @SiteID = 27917287432
SET @BeginDate = '20090101'
SET @EndDate = '20090106'
SET @CategoryID = 155
 
--This is the body of the stored proc
DECLARE @Stores table
	(StoreCode varchar(20) NOT NULL PRIMARY KEY, 
	 HideInReports bit NOT NULL DEFAULT (0))
DECLARE @Period table 
	(SiteID bigint, 
	 ListID bigint, 
	 Unsubscribes int, 
	 Kickouts int)
 
SET @BeginDate = CAST(ROUND(CAST(@BeginDate AS float), 0, 1) AS datetime)
SET @EndDate = ISNULL(CAST(ROUND(CAST(@EndDate AS float), 0, 1) AS datetime), @BeginDate)
 
INSERT INTO @Stores
SELECT s.StoreCode, 
	s.HideInReports 
FROM StoreManagement.dbo.Store s 
	INNER JOIN StoreManagement.dbo.Brand b ON 
		s.BrandID = b.BrandID 
WHERE b.SiteID = @SiteID
 
INSERT INTO @Period 
SELECT sa.SiteID, 
	sa.ListID, 
	SUM(sa.DailyUnsubscribed) AS Unsubscribes, 
	k.Kickouts 
FROM SubscriptionAgg sa 
	LEFT JOIN 
			(SELECT SiteID, 
				ListID, 
				ISNULL(SUM(CASE 
					WHEN Totaled > DATEADD(d, 0 - DAY(@EndDate), @EndDate) AND 
						Totaled <= @EndDate THEN DailyMaxBounce 
					ELSE 0 
				END), 0) AS Kickouts 
			 FROM MaxBounceAgg mba 
			 WHERE SiteID = @SiteID AND 
				(StoreCode IN (SELECT StoreCode FROM @Stores WHERE HideInReports = 0) OR 
				 StoreCode NOT IN (SELECT StoreCode FROM @Stores)) AND 
				Totaled <= @EndDate 
			 GROUP BY SiteID, 
				ListID) k ON 
		sa.SiteID = k.SiteID AND 
		sa.ListID = k.ListID 
WHERE sa.SiteID = @SiteID AND 
	(sa.StoreCode IN (SELECT StoreCode FROM @Stores WHERE HideInReports = 0) OR 
	 sa.StoreCode NOT IN (SELECT StoreCode FROM @Stores)) AND 
	sa.Totaled BETWEEN @BeginDate AND @EndDate 
GROUP BY sa.SiteID, 
	sa.ListID, 
	k.Kickouts
 
SELECT m.ClientName, 
	m.ListName, 
	CONVERT(varchar, @BeginDate, 101) AS FromDate, 
	CONVERT(varchar, @EndDate, 101) AS ToDate, 
	m.StoreCode AS Code, 
	m.StoreName AS [Store Name], 
	m.StoreGroupName AS [Group Name], 
	m.NumberOfGroups, 
	ISNULL(d.Unsubscribes, 0) AS Unsubs, 
	ISNULL(k.Kickouts, 0) AS Kickouts, 
	ISNULL(d.Unsubscribes, 0) + ISNULL(k.Kickouts, 0) AS TotalChurn, 
	ISNULL(p.Unsubscribes, 0) AS PeriodUnsubscribed, 
	ISNULL(p.Kickouts, 0) AS PeriodKickouts, 
	ISNULL(p.Unsubscribes, 0) + ISNULL(p.Kickouts, 0) AS PeriodChurn 
FROM 
			(SELECT l.SiteID, 
				l.ClientName, 
				l.ListID, 
				l.ListName, 
				s.StoreCode, 
				s.StoreName, 
				s.StoreGroupName, 
				s.NumberOfGroups 
			 FROM 
				(SELECT DISTINCT st1.SiteID, 
					st1.Name AS ClientName, 
					ls1.ListID, 
					ls1.Name AS ListName 
				 FROM SubscriptionAgg sa1 
					INNER JOIN QToolsMaster.dbo.Site st1 ON 
						sa1.SiteID = st1.SiteID 
					INNER JOIN QToolsMaster.dbo.List ls1 ON 
						sa1.ListID = ls1.ListID 
				 WHERE sa1.SiteID = @SiteID AND 
					(sa1.StoreCode IN (SELECT StoreCode FROM @Stores WHERE HideInReports = 0) OR 
					 sa1.StoreCode NOT IN (SELECT StoreCode FROM @Stores)) AND 
					sa1.Totaled BETWEEN @BeginDate AND @EndDate) l, 
				(SELECT sa2.SiteID, 
					CASE WHEN LEN(ISNULL(sa2.StoreCode, '')) = 0 THEN 'UNID' ELSE sa2.StoreCode END AS StoreCode, 
					CASE WHEN LEN(ISNULL(sa2.StoreCode, '')) = 0 THEN 'Unidentified' ELSE s2.StoreName END AS StoreName, 
					ISNULL(g2.GroupDesc, 'Unidentified') AS StoreGroupName, 
					ISNULL(NULL, 1) AS NumberOfGroups 
				 FROM SubscriptionAgg sa2 
					LEFT JOIN 
							(SELECT b3.SiteID, 
								s3.StoreID, 
								s3.StoreCode, 
								s3.StoreName 
							 FROM StoreManagement.dbo.Brand b3 
								INNER JOIN StoreManagement.dbo.Store s3 ON 
									b3.BrandID = s3.BrandID 
							 WHERE b3.SiteID = @SiteID) s2 ON 
						sa2.SiteID = s2.SiteID AND 
						sa2.StoreCode = ISNULL(s2.StoreCode, '') 
					LEFT JOIN 
							(SELECT gs2.StoreID, 
								gp2.GroupDesc 
							 FROM 
										(SELECT StoreID, 
											MIN(GroupID) AS FirstGroupID 
										 FROM StoreManagement.dbo.GroupStore 
										 GROUP BY StoreID) gs2 
								INNER JOIN StoreManagement.dbo.[Group] gp2 ON 
									gs2.FirstGroupID = gp2.GroupID 
							 WHERE gp2.CategoryID = @CategoryID) g2 ON 
						s2.StoreID = g2.StoreID 
				 WHERE sa2.SiteID = @SiteID AND 
					(sa2.StoreCode IN (SELECT StoreCode FROM @Stores WHERE HideInReports = 0) OR 
					 sa2.StoreCode NOT IN (SELECT StoreCode FROM @Stores)) AND 
					sa2.Totaled <= @EndDate 
				 GROUP BY sa2.SiteID, 
					CASE WHEN LEN(ISNULL(sa2.StoreCode, '')) = 0 THEN 'UNID' ELSE sa2.StoreCode END, 
					CASE WHEN LEN(ISNULL(sa2.StoreCode, '')) = 0 THEN 'Unidentified' ELSE s2.StoreName END, 
					ISNULL(g2.GroupDesc, 'Unidentified')) s) m 
	LEFT JOIN 
			(SELECT SiteID, 
				ListID, 
				CASE WHEN LEN(StoreCode) = 0 THEN 'UNID' ELSE StoreCode END AS StoreCode, 
				SUM(DailyUnsubscribed) AS Unsubscribes 
			 FROM SubscriptionAgg 
			 WHERE SiteID = @SiteID AND 
				(StoreCode IN (SELECT StoreCode FROM @Stores WHERE HideInReports = 0) OR 
				 StoreCode NOT IN (SELECT StoreCode FROM @Stores)) AND 
				Totaled BETWEEN @BeginDate AND @EndDate 
			 GROUP BY SiteID, 
				ListID, 
				CASE WHEN LEN(StoreCode) = 0 THEN 'UNID' ELSE StoreCode END) d ON 
		m.SiteID = d.SiteID AND 
		m.ListID = d.ListID AND 
		m.StoreCode = d.StoreCode 
	LEFT JOIN 
			(SELECT mba.SiteID, 
				mba.ListID, 
				lk.Name AS ListName, 
				mba.StoreCode, 
				ISNULL(SUM(CASE 
					WHEN mba.Totaled > DATEADD(d, 0 - DAY(@EndDate), @EndDate) AND 
						mba.Totaled <= @EndDate THEN mba.DailyMaxBounce 
					ELSE 0 
				END), 0) AS Kickouts 
			 FROM MaxBounceAgg mba 
				LEFT JOIN QToolsMaster.dbo.List lk ON 
					mba.SiteID = lk.SiteID AND 
					mba.ListID = lk.ListID 
			 WHERE mba.SiteID = @SiteID AND 
				(mba.StoreCode IN (SELECT StoreCode FROM @Stores WHERE HideInReports = 0) OR 
				 mba.StoreCode NOT IN (SELECT StoreCode FROM @Stores)) AND 
				mba.Totaled <= @EndDate 
			 GROUP BY mba.SiteID, 
				mba.ListID, 
				lk.Name, 
				mba.StoreCode) k ON 
		m.SiteID = k.SiteID AND 
		m.ListID = k.ListID AND 
		m.StoreCode = k.StoreCode 
	LEFT JOIN @Period p ON 
		m.SiteID = p.SiteID AND 
		m.ListID = p.ListID 
ORDER BY m.StoreCode
ExecutionPlans.zip
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros