Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Problem inserting to temporary table inside stored

Author  Topic 

echovault
Starting Member

20 Posts
Posted - 2007-09-25 : 14:57:08
I have a stored procedure which was working fine until recently. Suddenly the procedure just hangs indefinitely. Debugger and Profiler indicate that the procedure is hanging on an INSERT / SELECT to a temporary table created in the statement immediately proceeding the INSERT. The process itself (with no stored procedure defined around it) still runs fine from a query editor. Executing the stored procedure from the same query editor will hang. All the applications I've tried, (Crystal Enterprise, Toad, SQL MS) have the same result.

If anybody would like, I'll be happy to post the code, but for starters, I'll just explain the process involved.

The procedure is pretty basic.
1. Accept a datetime parameter, which can be NULL, the procedure uses SELECT ISNULL() to default the parameter to yesterday's date if NULL.
2. Set a few other local variables based on the parameter.
3. Create a temp table. Table has columns for daily, month-to-date and year-to-date summaries.
4. Insert all the records and the daily summaries. Summaries result in just a few records. (15-20) (This is the INSERT / SELECT where the procedure hangs. Keep in mind the SELECT runs subsecond, and the INSERT / SELECT as well, outside of the procedure.)
5. Update each row using UPDATE / SELECT , once for the month-to-date and once for the year-to-date
6. Select all rows from the temp table and send them to the client.
7. Drop the temp table.

A few items that might be of interest.
1. The INSERT and UPDATE statements all use SQL 2005 Common Table Expressions (CTEs). I've tried recreating the procedure using derived tables, and it still hangs on the INSERT / SELECT.
2. I've tried the procedure where it just returns the results of the first SELECT (no INSERT), and it works fines, with or without the CTE. This leads me to think it is the INSERT rather than the SELECT at issue.
3. Also, using Activity Monitor on SQL MS to check, the process stalls with Status = INSERTING.
4. Killing the process results in the temp table remaining in tempdb database. For this reason I have power cycled the box to clear up tempdb, but the problem persists.
5. There are no errors, warnings, beeps, burps or farts from SQL. The procedure just sits and never returns any data. It's been allowed to run for as long as 45 minutes. When it was working it took on average about 4 seconds.
6. There was one day during the period of not working when this did work. It started on Friday morning, troubleshooting and tweaking all day Friday. To avoid locks over the weekend, the scheduled report which uses the procedure was paused (Crystal Enterprise XI), and on Monday morning I noticed some rogue connections from a dev linked server. Thinking it possible that this was a factor I shut down the linked server, verified the connections were dropped, then tried the procedure. It worked, and it worked for the rest of Monday. HOWEVER, I did not try the procedure Monday morning before this, so I don't know that it was a factor. Problem returned Tuesday morning, at which point we power cycled the box, and still have the problem.
7. One other thing that happened around the same time this problem began. A scheduled job (SQL Agent) which ran Friday morning appeared to have completed (meaning the data it moves was in place and valid), but the job never stopped EXECUTING. Job History showed it as Succeeded, but viewing the status showed it as Executing. I stopped it and ran it again, at which time it completed, icluding showing a status of Succeeded. It has been running hourly ever since with no other issues.

8. After reading a post on this site, I tried using a table variable to replace the temp table, and as discussed, the performance was better, when the process was run outside the stored procedure. However the stored procedure still hangs.


Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2007-09-25 : 15:26:54
Please post code
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-09-25 : 15:29:12
quote:

The procedure is pretty basic.


You'd have better luck with us if you shortened your post. I didn't make it very far down your post when I gave up due to the length of it. Remember that we do this for free and have regular day jobs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

echovault
Starting Member

20 Posts
Posted - 2007-09-25 : 15:31:46
Here is the procedure that will not run:

CREATE PROCEDURE dbo.proc_GetLineBalanceData (	
	@GasDay datetime
) AS
BEGIN 	--PROCEDURE
	
	DECLARE @MTDBegin datetime
	DECLARE @YTDBegin datetime
	DECLARE @MTDDays int
	DECLARE @YTDDays int
	
	SELECT @GasDay = ISNULL(@GasDay, dbo.fn_DatePartOnly(DATEADD(DAY, -1, GETDATE())))
	SELECT @MTDBegin = ISNULL(@MTDBegin, dbo.fn_FirstDayOfMonth(@GasDay))
	SELECT @YTDBegin = ISNULL(@YTDBegin, DATEADD(MONTH, -DATEPART(MONTH, @MTDBegin)+1, @MTDBegin))
	SET @MTDDays = DATEDIFF(DAY, @MTDBegin, @GasDay) + 1
	SET @YTDDays = DATEDIFF(DAY, @YTDBegin, @GasDay) + 1
	
	CREATE TABLE #DMYData (
			GasDay datetime
		,	PipelineName varchar(10)
		, PipelineOrder int
		, MeterGroup varchar(15)
		, GOWP char(1)
		, ROD char(1)
		, DayKLBS float
		, DayMCF float
		, MTDKLBS float
		, MTDMCF float
		, YTDKLBS float
		, YTDMCF float
	)
	
	;WITH 
	DayVals (TagNameID, NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.PLTagNameID, N.NodeName, n.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp = @GasDay
	)
	INSERT #DMYData (GasDay, PipelineName, PipelineOrder, MeterGroup, GOWP, ROD, DayKLBS, DayMCF)
	SELECT @GasDay, C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
	FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C
		INNER JOIN DayVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN DayVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
	WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
	GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	ORDER BY C.PipelineOrder, C.ReceiptOrDelivery DESC, C.MeterGroupOrderWithinPipeline
	
	;WITH MTDVals (TagNameID, NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.PLTagNameID, N.NodeName, n.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp BETWEEN @MTDBegin AND @GasDay
	)
	UPDATE #DMYData SET MTDKLBS = KLBS, MTDMCF = MCF FROM #DMYData DP INNER JOIN (
		SELECT C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline GOWP, C.ReceiptOrDelivery ROD
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
		FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C 
		INNER JOIN MTDVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN MTDVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
		WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
		GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	) M	ON M.PipelineOrder = DP.PipelineOrder AND M.MeterGroup = DP.MeterGroup AND M.GOWP = DP.GOWP AND M.ROD = DP.ROD
	
	;WITH YTDVals (TagNameID, NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.PLTagNameID, N.NodeName, N.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp BETWEEN @YTDBegin AND @GasDay
	)
	UPDATE #DMYData SET YTDKLBS = KLBS, YTDMCF = MCF FROM #DMYData DP INNER JOIN (
		SELECT C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline GOWP, C.ReceiptOrDelivery ROD
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
		FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C 
		INNER JOIN YTDVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN YTDVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
		WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
		GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	) M	ON M.PipelineOrder = DP.PipelineOrder AND M.MeterGroup = DP.MeterGroup AND M.GOWP = DP.GOWP AND M.ROD = DP.ROD
	
	SELECT * FROM #DMYData
	DROP TABLE #DMYData
	
	RETURN 0

END 	--PROCEDURE




And the anonymous code block that runs perfectly. All I did was comment out the procedure definition statements and declare the parameter as a local variable...

--CREATE PROCEDURE dbo.proc_GetLineBalanceData (	
	DECLARE @GasDay datetime
--) AS
--BEGIN 	--PROCEDURE
	
	DECLARE @MTDBegin datetime
	DECLARE @YTDBegin datetime
	DECLARE @MTDDays int
	DECLARE @YTDDays int
	
	SELECT @GasDay = ISNULL(@GasDay, dbo.fn_DatePartOnly(DATEADD(DAY, -1, GETDATE())))
	SELECT @MTDBegin = ISNULL(@MTDBegin, dbo.fn_FirstDayOfMonth(@GasDay))
	SELECT @YTDBegin = ISNULL(@YTDBegin, DATEADD(MONTH, -DATEPART(MONTH, @MTDBegin)+1, @MTDBegin))
	SET @MTDDays = DATEDIFF(DAY, @MTDBegin, @GasDay) + 1
	SET @YTDDays = DATEDIFF(DAY, @YTDBegin, @GasDay) + 1
	
	CREATE TABLE #DMYData (
			GasDay datetime
		,	PipelineName varchar(10)
		, PipelineOrder int
		, MeterGroup varchar(15)
		, GOWP char(1)
		, ROD char(1)
		, DayKLBS float
		, DayMCF float
		, MTDKLBS float
		, MTDMCF float
		, YTDKLBS float
		, YTDMCF float
	)
	
	;WITH 
	DayVals (TagNameID, NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.PLTagNameID, N.NodeName, n.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp = @GasDay
	)
	INSERT #DMYData (GasDay, PipelineName, PipelineOrder, MeterGroup, GOWP, ROD, DayKLBS, DayMCF)
	SELECT @GasDay, C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
	FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C
		INNER JOIN DayVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN DayVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
	WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
	GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	ORDER BY C.PipelineOrder, C.ReceiptOrDelivery DESC, C.MeterGroupOrderWithinPipeline
	
	;WITH MTDVals (TagNameID, NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.PLTagNameID, N.NodeName, n.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp BETWEEN @MTDBegin AND @GasDay
	)
	UPDATE #DMYData SET MTDKLBS = KLBS, MTDMCF = MCF FROM #DMYData DP INNER JOIN (
		SELECT C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline GOWP, C.ReceiptOrDelivery ROD
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
		FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C 
		INNER JOIN MTDVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN MTDVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
		WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
		GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	) M	ON M.PipelineOrder = DP.PipelineOrder AND M.MeterGroup = DP.MeterGroup AND M.GOWP = DP.GOWP AND M.ROD = DP.ROD
	
	;WITH YTDVals (TagNameID, NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.PLTagNameID, N.NodeName, N.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp BETWEEN @YTDBegin AND @GasDay
	)
	UPDATE #DMYData SET YTDKLBS = KLBS, YTDMCF = MCF FROM #DMYData DP INNER JOIN (
		SELECT C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline GOWP, C.ReceiptOrDelivery ROD
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
		FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C 
		INNER JOIN YTDVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN YTDVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
		WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
		GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	) M	ON M.PipelineOrder = DP.PipelineOrder AND M.MeterGroup = DP.MeterGroup AND M.GOWP = DP.GOWP AND M.ROD = DP.ROD
	
	SELECT * FROM #DMYData
	DROP TABLE #DMYData
--	
--	RETURN 0
--
--END 	--PROCEDURE
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts
Posted - 2007-09-25 : 15:32:42
WTF Tara? You do this for free? I thought Graz had y'all in a sweatshop doing this and paying you pennies a day for it.
Go to Top of Page

echovault
Starting Member

20 Posts
Posted - 2007-09-25 : 15:37:08
Thanks for the heads up Tara. I guess I was just trying to present as much information as I could.

And thanks again to all who take the time.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-09-25 : 15:40:10
> WTF Tara? You do this for free? I thought Graz had y'all in a sweatshop doing this and paying you pennies a day for it.

it's accuatlly a penny per week.


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-09-25 : 15:40:55
echovault:
what is the error you get?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

echovault
Starting Member

20 Posts
Posted - 2007-09-25 : 15:51:59
I don't get an error. The procedure just runs and runs. No errors, warnings, nothing. But no results returned either. I've allowed it to run as long as 45 minutes, but the anonymous code always run in just a few seconds.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-09-25 : 15:54:16
well you'll have to find out which parts run that long and why.

first run
DBCC FREEPROCCACHE
and
DBCC DROPCLEANBUFFERS

then re-run your proc.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2007-09-25 : 16:01:51
try this and let me know if it works. Also I don't see any reason to use CTE's in this.

CREATE PROCEDURE dbo.proc_GetLineBalanceData (	
	@GasDay datetime
) AS
BEGIN 	--PROCEDURE
	
	DECLARE @MTDBegin datetime
	DECLARE @YTDBegin datetime
	DECLARE @MTDDays int
	DECLARE @YTDDays int
	
	SELECT @GasDay = ISNULL(@GasDay, dbo.fn_DatePartOnly(DATEADD(DAY, -1, GETDATE())))
	SELECT @MTDBegin = ISNULL(@MTDBegin, dbo.fn_FirstDayOfMonth(@GasDay))
	SELECT @YTDBegin = ISNULL(@YTDBegin, DATEADD(MONTH, -DATEPART(MONTH, @MTDBegin)+1, @MTDBegin))
	SET @MTDDays = DATEDIFF(DAY, @MTDBegin, @GasDay) + 1
	SET @YTDDays = DATEDIFF(DAY, @YTDBegin, @GasDay) + 1
	
	CREATE TABLE #DMYData (
			GasDay datetime
		,	PipelineName varchar(10)
		, PipelineOrder int
		, MeterGroup varchar(15)
		, GOWP char(1)
		, ROD char(1)
		, DayKLBS float
		, DayMCF float
		, MTDKLBS float
		, MTDMCF float
		, YTDKLBS float
		, YTDMCF float
	)
	Begin
	;WITH 
	DayVals (TagNameID, NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.PLTagNameID, N.NodeName, n.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp = @GasDay
	)
	INSERT #DMYData (GasDay, PipelineName, PipelineOrder, MeterGroup, GOWP, ROD, DayKLBS, DayMCF)
	SELECT @GasDay, C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
	FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C
		INNER JOIN DayVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN DayVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
	WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
	GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	ORDER BY C.PipelineOrder, C.ReceiptOrDelivery DESC, C.MeterGroupOrderWithinPipeline
	END
	BEGIN
	;WITH MTDVals (TagNameID, NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.PLTagNameID, N.NodeName, n.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp BETWEEN @MTDBegin AND @GasDay
	)
	UPDATE #DMYData SET MTDKLBS = KLBS, MTDMCF = MCF FROM #DMYData DP INNER JOIN (
		SELECT C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline GOWP, C.ReceiptOrDelivery ROD
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
		FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C 
		INNER JOIN MTDVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN MTDVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
		WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
		GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	) M	ON M.PipelineOrder = DP.PipelineOrder AND M.MeterGroup = DP.MeterGroup AND M.GOWP = DP.GOWP AND M.ROD = DP.ROD
	END
	BEGIN
	;WITH YTDVals (TagNameID, NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.PLTagNameID, N.NodeName, N.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp BETWEEN @YTDBegin AND @GasDay
	)
	UPDATE #DMYData SET YTDKLBS = KLBS, YTDMCF = MCF FROM #DMYData DP INNER JOIN (
		SELECT C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline GOWP, C.ReceiptOrDelivery ROD
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
		FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C 
		INNER JOIN YTDVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN YTDVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
		WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
		GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	) M	ON M.PipelineOrder = DP.PipelineOrder AND M.MeterGroup = DP.MeterGroup AND M.GOWP = DP.GOWP AND M.ROD = DP.ROD
	END
	SELECT * FROM #DMYData
	--DROP TABLE #DMYData
	
	RETURN 0

END
Go to Top of Page

echovault
Starting Member

20 Posts
Posted - 2007-09-25 : 16:03:36
Ran DBCC as advised. Profiler gave this message for both.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Running the procedure, profiler shows it is the INSERT / SELECT after the temp table is created that is hanging. It just stops there and never moves on. I've also used a debugger (Toad for SQL Server) that shows all steps completing, until the INSERT / SELECT, and then it just heads out into the great unknown.
Go to Top of Page

echovault
Starting Member

20 Posts
Posted - 2007-09-25 : 16:16:28
Vinnie881

Just to make sure I know what I'm doing here, basically you just wrapped each query in BEGIN/END blocks, right? Is there more to it?

Also, I just use the CTEs because it makes it a little more readable. Since they are new (at least to me), I suspected them right off the bat. So I tried changing it to just use derived tables, but the problem was the same.
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2007-09-25 : 16:25:17
I wanted to make sure a couple things. 1, in a stored procedure there is no need to drop the temp table, it will automatically do that upon the completion, so I first wanted to eliminate that as a possible hangup. Then I used the begin and ends to ensure that each block was running isolated in the correct order (in 2005, there's not much reason to do it on that code, but if it runs w/o issue when you run it as a query, I figured we would try to issolate in the procedure as well).

My next steps assuming that didn't resolve the issue would be to block out 1 CTE at a time(use /* */)until you can figure out what CTE is causing the hangup.
Go to Top of Page

echovault
Starting Member

20 Posts
Posted - 2007-09-25 : 16:44:00
That's what I'm doing at the moment. Trying to isolate a single table or derived table that is causing trouble. So far I've found that, in the first SELECT, where I pull the CTE DayVals in twice, if I pull it only the first time (alias K), it works. Pulling both still fails. Next I'm trying to pull in just the second (alias M).

Thanks again, and I'll keep you posted.
Go to Top of Page

echovault
Starting Member

20 Posts
Posted - 2007-09-25 : 16:54:56
Okay, it worked with only the second. So it would seem that pulling the CTE into the INSERT / SELECT twice is causing the problem.

I could definitely break it down to use derived tables and pull each separately, since CTEs are only in scope for one statement. And using derived tables didn't work trying to do both at once either.

What bugs me is that this works fine unless it's inside of a procedure. So strange...

Here is the code:

BEGIN
	;WITH DayVals (NodeName, TagType, TagName, GasDay, Val)
	AS (
		SELECT N.NodeName, N.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp = @GasDay
	)
	INSERT #DMYData (GasDay, PipelineName, PipelineOrder, MeterGroup, GOWP, ROD/*, DayKLBS*/, DayMCF)
	SELECT @GasDay, C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
--			, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
	FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C
--			INNER JOIN DayVals K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN DayVals M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType --AND K.GasDay = M.GasDay
	WHERE	M.GasDay >= C.InServiceStartDate AND (M.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
	GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	ORDER BY C.PipelineOrder, C.ReceiptOrDelivery DESC, C.MeterGroupOrderWithinPipeline
END
Go to Top of Page

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2007-09-25 : 16:55:34
Lets try w/o the CTE's


CREATE PROCEDURE dbo.proc_GetLineBalanceData (	
	@GasDay datetime
) AS
BEGIN 	--PROCEDURE
	
	DECLARE @MTDBegin datetime
	DECLARE @YTDBegin datetime
	DECLARE @MTDDays int
	DECLARE @YTDDays int
	
	SELECT @GasDay = coalesce(@GasDay, dbo.fn_DatePartOnly(DATEADD(DAY, -1, GETDATE())))
	SELECT @MTDBegin = coalesce(@MTDBegin, dbo.fn_FirstDayOfMonth(@GasDay))
	SELECT @YTDBegin = coalesce(@YTDBegin, DATEADD(MONTH, -DATEPART(MONTH, @MTDBegin)+1, @MTDBegin))
	SET @MTDDays = DATEDIFF(DAY, @MTDBegin, @GasDay) + 1
	SET @YTDDays = DATEDIFF(DAY, @YTDBegin, @GasDay) + 1
	
	CREATE TABLE #DMYData (
			GasDay datetime
		,	PipelineName varchar(10)
		, PipelineOrder int
		, MeterGroup varchar(15)
		, GOWP char(1)
		, ROD char(1)
		, DayKLBS float
		, DayMCF float
		, MTDKLBS float
		, MTDMCF float
		, YTDKLBS float
		, YTDMCF float
	)
	Begin
	
		SELECT N.PLTagNameID, N.NodeName, n.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		into #TMP1
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp = @GasDay
	
	INSERT #DMYData (GasDay, PipelineName, PipelineOrder, MeterGroup, GOWP, ROD, DayKLBS, DayMCF)
	SELECT @GasDay, C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
	FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C
		INNER JOIN #TMP1 K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN #TMP1 M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
	WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
	GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	ORDER BY C.PipelineOrder, C.ReceiptOrDelivery DESC, C.MeterGroupOrderWithinPipeline
	END
	BEGIN
		SELECT N.PLTagNameID, N.NodeName, n.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		INTO #TMP2
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp BETWEEN @MTDBegin AND @GasDay
	
	UPDATE #DMYData SET MTDKLBS = KLBS, MTDMCF = MCF FROM #DMYData DP INNER JOIN (
		SELECT C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline GOWP, C.ReceiptOrDelivery ROD
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
		FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C 
		INNER JOIN #TMP2 K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN #TMP2 M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
		WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
		GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	) M	ON M.PipelineOrder = DP.PipelineOrder AND M.MeterGroup = DP.MeterGroup AND M.GOWP = DP.GOWP AND M.ROD = DP.ROD
	END
	BEGIN
		SELECT N.PLTagNameID, N.NodeName, N.TagType, N.TagName, V.GasDayTimestamp, V.TagValue
		Into #TMP3
		FROM dbo.TPLTagValues V INNER JOIN dbo.TPLTagNames N ON V.FK_PLTagNameID = N.PLTagNameID
		WHERE V.GasDayTimestamp BETWEEN @YTDBegin AND @GasDay
	
	UPDATE #DMYData SET YTDKLBS = KLBS, YTDMCF = MCF FROM #DMYData DP INNER JOIN (
		SELECT C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline GOWP, C.ReceiptOrDelivery ROD
		, SUM(K.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) KLBS
		, SUM(M.Val * CASE C.ReceiptOrDelivery WHEN 'R' THEN -1 ELSE 1 END) MCF
		FROM dbo.T_PL_RPT_CONFIG_DailyProductionReport C 
		INNER JOIN #TMP3 K ON C.KLB_Tag = K.TagName AND C.KLB_TAG_NodeName = K.NodeName AND C.KLB_TAG_TagType = K.TagType
		INNER JOIN #TMP3 M ON C.MCF_Tag = M.TagName AND C.MCF_TAG_NodeName = M.NodeName AND C.MCF_TAG_TagType = M.TagType AND K.GasDay = M.GasDay
		WHERE	K.GasDay >= C.InServiceStartDate AND (K.GasDay <= C.InServiceEndDate OR  C.InServiceEndDate IS NULL)
		GROUP BY C.PipelineName, C.PipelineOrder, C.MeterGroup, C.MeterGroupOrderWithinPipeline, C.ReceiptOrDelivery
	) M	ON M.PipelineOrder = DP.PipelineOrder AND M.MeterGroup = DP.MeterGroup AND M.GOWP = DP.GOWP AND M.ROD = DP.ROD
	END
	SELECT * FROM #DMYData
	--DROP TABLE #DMYData
	
	RETURN 0

END
Go to Top of Page

echovault
Starting Member

20 Posts
Posted - 2007-09-25 : 17:32:02
Vinnie881,

Ran with your code using multiple temp tables. Had to change a couple of things since I had aliased fields in my CTEs. No big deal but I'll post the code back if you like.

So I ran a trace on this while it ran and it took about 2m30s the first time and 1m47s the second time. The trace has some things that kinda concern me, but it is about 300 lines, so I didn't want to post it here. If you're curious, let me know.

I think I have enough information from this interaction, that I can rebuild this procedure with performance similar to what I had before. I plan to use table variables, and only use two, since the second can be reused for daily, monthly and yearly numbers.

I'm still curious to know why the first process ran outside a procedure but not within it. Just seems like a bug to me. I just need to focus elsewhere for the time being.

Again THANKS to everyone, especially Vinnie881. This forum rocks!
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-26 : 06:12:24
You might want to change DMYData from a #TempTable to a @TableVar, and put a PK on it for whatever columns you are commonly JOINing to the other tables in the Sproc.

Personally I would put some

SET STATISTICS IO ON / OFF

before / after the query though to be slow and see what the LOGICAL I/O and scans is, and work on reducing them.

Or use SET SHOWPLAN_TEXT ON/OFF to see what the query plans are, and where indexes are NOT being used that you think could be.

The whole CTE thing scares the living daylights out of me for this sort of set-based processing, but that may just be because I'm an old codger ...

Kristen
Go to Top of Page

echovault
Starting Member

20 Posts
Posted - 2007-09-27 : 15:17:45
I actually have made the change from #temptable to @tablevar since this thread began. And noticed better performance.
I'm curious why the CTE draws such a strong reaction. Ever since moving to 2005, I've used them every place I would a derived table. It just seems to make the code more readable and maintainable. When I first started using them, I ran comparisons with derived tables, and in almost every case I saw better performance with CTEs.

For example using the temp tables as per Vinne881'a last post meant that a large amount of data was being inserted to a temp table (especially on the year-to-date step), and then joining to the config table for the update. With the CTE it seems like it performs more like a regular join, meaning the number of rows pulled in after the where clause is actually much less than what is being inserted to the temp table.

Is there something sinister about CTEs that I should know? I guess I just kind of understood it to be a re-usable subquery.

What scares the daylights out of me is that the original code I posted is now working fine. I tried it again after I built the new process, just on a whim and it works with excellent performance. It has been ever since. I did go ahead and change the temp table to a table variable. But other than that it is identical. I actually did that before I began this thread, and it wasn't working then. I left that code alone and started tweaking on a _New procedure. When I went back to the original at the end of the day. It worked great. Scary
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-27 : 15:26:24
"I'm curious why the CTE draws such a strong reaction"

In my case because I'm old

But also it introduces two things that bother me.

The first is a level of abstraction.

The second is a possible error, outside of SQL itself, per se, which may cause instability to the server as a whole. I expect MS say that they have this covered, but the stability and robustness of our servers is something that I strive hard to provide as a key benefit to our clients, and any time we've had "Call this COM object" as part of SQL 2000 we've had server crashes. just goes against the grain of Set-based data manipulation for me, and is MS trying to cover VB sequential-processing Gurus to SQL Server affectionardos.

"What scares the daylights out of me is that the original code I posted is now working fine"

That would scare me too, as an old timer, unless I could put my finger on a good-reason-why.

Kristen
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site