MS Sql-Server timeout
We have this stored procedure below in MS Sql Server. It is run by a job with the same parameters on a daily basis at 5pm. Some days it fails with a timeout error, other times it runs fine. I have tried to reproduce the error, but it is too random.
I know this is ALOT of code to paste in for a question, but I have tried many different things and still can not pinpoint where 开发者_开发知识库it could be occurring.
Also, I understand that I am calling some other functions too, but just ignore them for now. If nothing looks wrong with this stored proc, then I will post some of the other functions too.
Please let me know if you need any other info. Thanks.
EDIT: To the commentators below, I don't expect anyone to unravel this code. I am only looking for suggestions as to what could be causing the issue. Thanks to those who have already given me some suggestions.
USE [economy]
GO
/****** Object: StoredProcedure [dbo].[ec_mailcomponent_dismaldigest_html] Script Date: 05/02/2011 11:09:47 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ec_mailcomponent_dismaldigest_html]
@sid varchar(50),
@frequency int,
@listID int=1
AS
SET NOCOUNT ON
--//////////////////////////////////////////////////
--// Declare Variables
--//////////////////////////////////////////////////
DECLARE
@release varchar(50),
@location varchar(255),
@country varchar(50),
@intro varchar(100),
@value varchar(40),
@strDayName varchar(255),
@release_date smalldatetime,
@prior_release_date smalldatetime,
@title varchar(255),
@summary char(300),
@iAid int,
@text varchar(8000),
@sFrequency varchar(20),
@release_id varchar(50),
@sFirstTake varchar(800),
@sLink varchar(255),
@sTrackLink varchar(255),
@sLinkId varchar(50),
@sCorpLink varchar(255),
@tempDate varchar(100),
@ref_per varchar(30),
@freq int,
@watch_country varchar(40),
@consensus varchar(30),
@forecast varchar(30),
@ref_date smalldatetime,
@iEdition int,
@sEdition varchar(100),
@iPrevEdition int,
@edition varchar(50),
@i INT --int for number of days to go back. 0 for US -1 for Europe
--********************************************
--Create the temp table
--********************************************
SET @iPrevEdition = 0
--CREATE TABLE #return ( return_value text, orderby int IDENTITY(1,1))
DECLARE @return TABLE ( return_value text, orderby int IDENTITY(1,1))
--///////////////////////////////////////
--// Initialize Variables
--///////////////////////////////////////
SET @prior_release_date = NULL
SET @i=-1
IF ( @listID = 1 )
SET @edition = '1,2,3,4'
ELSE IF ( @listID = 36 )
SET @edition = '1'
ELSE IF ( @listID = 37 )
BEGIN
SET @edition = '2'
SET @i =-1
END
ELSE IF ( @listID = 38 )
SET @edition = '3'
ELSE IF ( @listID = 39 )
SET @edition = '4'
IF ( @frequency = 1 )
SET @sFrequency = 'Daily'
ELSE
SET @sFrequency = 'Weekly'
--///////////////////////////////////////
--// Determine if it is weekly or daily
--///////////////////////////////////////
IF ( @frequency = 2 )
DECLARE rstReleases CURSOR FOR SELECT ri.display_title, ri.release_id, ri.geo, rd.data_actual, rd.release_date, rd.abstract, rd.reference_date, rd.data_ec_forecast, rd.data_consensus, ri.frequency
FROM dismal_v8..ds_release_info ri WITH (NOLOCK)
INNER JOIN dismal_v8..ds_release_dates rd WITH (NOLOCK) ON ri.release_id=rd.release_id
WHERE ri.release_id IN ( SELECT mail_key FROM tps..sh_email_detail WITH (NOLOCK) WHERE sid = @sid ) AND rd.data_actual IS NOT NULL AND rd.release_date > DATEADD(day, -7, convert(varchar(10), GETDATE(), 101)) AND DATEDIFF(dd, rd.release_date, GETDATE()) >= 0
AND (tps.dbo.getDismalReleaseAccess(@sid,ri.release_id,getdate())=1)
ORDER by left(rd.release_date,11) desc, ri.geo ASC
ELSE
DECLARE rstReleases CURSOR FOR SELECT ri.display_title, ri.release_id, ri.geo, rd.data_actual, rd.release_date, rd.abstract, rd.reference_date,
rd.data_ec_forecast, rd.data_consensus, ri.frequency
FROM dismal_v8..ds_release_info ri WITH (NOLOCK)
INNER JOIN dismal_v8..ds_release_dates rd WITH (NOLOCK) ON ri.release_id=rd.release_id
WHERE ri.release_id IN ( SELECT mail_key FROM tps..sh_email_detail WITH (NOLOCK) WHERE sid = @sid and list_id=@listID )
AND rd.data_actual IS NOT NULL
--AND convert(varchar(10), rd.release_date, 101)=convert(varchar(10), GETDATE(), 101)
--Get things done in the last 24hrs
AND rd.release_date BETWEEN DATEADD(dd,@i,GETDATE()) AND GETDATE()
AND (tps.dbo.getDismalReleaseAccess(@sid,ri.release_id,getdate())=1)
/*AND rd.release_id in (
select distinct drd.release_id
from dismal_v8.dbo.ds_release_dates drd
inner join dismal_v8.dbo.ds_release_edition dre on drd.release_id = dre.release_id
where dre.edition in (
SELECT paramvalue
FROM rfa_intranet.dbo.VBSplit(@edition,',')
)
)*/
ORDER by left(rd.release_date,11) desc, ri.geo ASC
OPEN rstReleases
FETCH NEXT FROM rstReleases INTO @release, @release_id, @country, @value, @release_date, @sFirstTake, @ref_date, @forecast, @consensus, @freq
WHILE ( @@fetch_status = 0 )
BEGIN
IF ( DAY(@release_date) <> DAY(@prior_release_date) ) OR ( @prior_release_date IS NULL )
BEGIN
SET @strDayName = DATENAME(dw, @release_date) + ' ' + convert(varchar(10), @release_date, 101)
IF ( @frequency = 2 )
BEGIN
SET @tempDate = tps.dbo.FormatDate(@release_date, 'dddd, mmmm d1, yyyy' )
END
ELSE
BEGIN
SET @tempDate = 'Economic Indicators Released ' + tps.dbo.FormatDate(@release_date, 'mmmm d1, yyyy' )
END
INSERT INTO @return VALUES( '<font style="font-family: Arial; line-height: 120%;font-size: 12pt; font-weight: bold;color: #000000">' + @tempDate +'</font><br>')
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/clear.gif" height="15" alt="" /><br>' )
END
SET @ref_per = tps.dbo.DismalCoverageDateFormat(@ref_date,@freq)
IF ( @country <> '' )
BEGIN
IF @country != @watch_country
BEGIN
SET @watch_country = @country
IF @country = 'EURO ZONE' OR @country = 'OECD' OR @country = 'WORLD' OR UPPER(@country) = 'NORTH AMERICA'
BEGIN
INSERT INTO @return VALUES( '<b>' + @country + ' </b><br>' )
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/bar2.gif" height="2" width="600" alt="decorative"><br>' )
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/clear.gif" height="2" alt="" /><br>' )
END
ELSE
BEGIN
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/dismal/images/flags_ds/flag_i'+ left(@release_id, charindex('_',@release_id)-1) +'.gif" height="16" width="16" border="0" style="margin-bottom:-3px;"> <b>' + @country + ' </b><br>' )
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/bar2.gif" height="2" width="600" alt="decorative"><br>' )
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/clear.gif" height="2" alt="" /><br>' )
END
END
END
INSERT INTO @return VALUES( '<b>' + @release + '</b><br>' )
IF ( @release_id != 'usa_fomc_meeting' )
INSERT INTO @return VALUES( '<i>Coverage: ' + @ref_per + '</i><br>' )
INSERT INTO @return VALUES( '<i>Actual: ' + @value + '</i><br>' )
INSERT INTO @return VALUES( '<i>xxx''s xxx: ' + @forecast + '</i><br>' )
INSERT INTO @return VALUES( '<i>Consensus: ' + @consensus + '</i><br>' )
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/clear.gif" height="1" alt="" /><br>' )
INSERT INTO @return VALUES( @sFirstTake + '<br>' )
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/clear.gif" height="5" alt="" /><br>' )
SET @sLink = 'http://www.xxx.com/dismal/pro/release.asp?r=' + @release_id
SET @sCorpLink = 'http://xxx.com/dismal/pro/release.asp?r=' + @release_id
INSERT INTO @return VALUES( '<a href="' + @sLink + '">View the entire release</a><br>' )
--INSERT INTO @return VALUES( 'View the entire release: <a href="' + @sLink + '">Subscribers</a><br>' )
-- <a href="' + @sCorpLink + '">Site License Users</a><br>' )
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/clear.gif" height="15" alt="" /><br>' )
SET @prior_release_date = @release_date
FETCH NEXT FROM rstReleases INTO @release, @release_id, @country, @value, @release_date, @sFirstTake, @ref_date, @forecast, @consensus, @freq
END
CLOSE rstReleases
DEALLOCATE rstReleases
--///////////////////////////////////////
--// Write the thoughts pieces
--///////////////////////////////////////
--Changed to exclude Consumer flow articles. Added site check (JAG 3/2/05)
--SELECT TOP 15 title, convert(char(300),summary), content_id FROM dismal_v8..ds_content WHERE DATEDIFF(dy, publish_on, getdate()) < 5 AND status=2 and feature_id=0 ORDER BY publish_on DESC
DECLARE rs_thoughts CURSOR FOR
SELECT * FROM (
SELECT TOP 10 title, convert(char(300),summary) as summary, c.content_id, ei.edition, ei.description
FROM dismal_v8..ds_content c WITH (NOLOCK)
INNER JOIN dismal_v8.dbo.ds_content_site cs WITH (NOLOCK)
ON cs.content_id=c.content_id
INNER JOIN dismal_v8..ds_content_edition ce WITH (NOLOCK)
ON c.content_id = ce.content_id
INNER JOIN dismal_v8..ds_edition_info ei WITH (NOLOCK)
ON ce.edition = ei.edition
--include blogs type 0 4/19/2011 JAG
WHERE type IN (0,2) and DATEDIFF(dy, publish_on, getdate()) < 5 AND status=2 AND cs.site = 1
AND GETDATE() >= publish_on --Added check to ensure that these articles will be able to be viewed once the user navigates to Dismal's article.asp. JAP 22-Dec-2010
--Do not add Spotlights to email
AND c.type <> 8
ORDER BY publish_on DESC
) as s
WHERE edition in (SELECT paramvalue FROM rfa_intranet.dbo.VBSplit(@edition,','))
ORDER BY edition
OPEN rs_thoughts
FETCH NEXT FROM rs_thoughts into @title, @summary, @iAid, @iEdition, @sEdition
IF @@FETCH_STATUS = 0
BEGIN
INSERT INTO @return VALUES( '<span style="font-family: Arial; line-height: 120%;font-size: 12pt; font-weight: bold;color: #000000">Recent Commentary From xxx</span><br />' )
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/clear.gif" height="15" alt="" /><br>' )
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@iPrevEdition <> @iEdition)
BEGIN
INSERT INTO @return VALUES( '<span style="font-family: Arial; line-height: 120%;font-size: 10pt; font-weight: bold;color: #85A053">' + replace(@sEdition, 'Dismal ', '') + '</span><br />' )
--INSERT INTO @return VALUES( '<hr>' )
SET @iPrevEdition = @iEdition
END
IF left(@title,8) = 'Updated:' AND datepart(dw,CAST(right(@title, len(@title)-8) as smalldatetime)) = 2
INSERT INTO @return VALUES( '<b>xxx''s xxx Chartbook - ' + @title +'</b><br>')
ELSE
BEGIN
INSERT INTO @return VALUES( '<b>' + @title+'</b><br>' )
if @summary is not null
INSERT INTO @return VALUES( isNull(@summary, '') +'<br>')
END
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/clear.gif" height="5" alt="" /><br>' )
SET @sLink = 'http://www.xxx.com/dismal/pro/article.asp?cid=' + cast( @iAid as varchar(10) )
SET @sCorpLink = 'http://xxx.com/dismal/pro/article.asp?cid=' + cast( @iAid as varchar(10) )
--INSERT INTO @return VALUES( 'View the entire article: ' )
INSERT INTO @return VALUES( '<a href="' + @sLink +'">View the entire article</a><br>' )
--INSERT INTO @return VALUES( '<a href="' + @sLink +'">Individual Subscribers</a> ' )
--INSERT INTO @return VALUES( '<a href="' + @sCorpLink + '">Site License Users</a><br>')
INSERT INTO @return VALUES( '<img src="http://www.xxx.com/images/clear.gif" height="15" alt="" /><br>' )
FETCH NEXT FROM rs_thoughts into @title, @summary, @iAid, @iEdition, @sEdition
END
END
IF EXISTS(SELECT sid FROM tps..ds_sub_summary_view where sid=@sid AND freq_id=129 AND end_date>getdate() AND getdate()<'2005-06-01')
BEGIN
INSERT INTO @return VALUES( '<span style="font-family: Arial; line-height: 120%;font-size: 12pt; font-weight: bold;color: #85A053">blah blah blah...</span><br/>' )
INSERT INTO @return VALUES('blah blah blah...')
INSERT INTO @return VALUES('blah blah blah...')
INSERT INTO @return VALUES('blah blah blah...')
INSERT INTO @return VALUES('blah blah blah...')
INSERT INTO @return VALUES('blah blah blah...<br/>')
INSERT INTO @return VALUES('<a href="http://www.xxx.com/dismal/pro_account/dismal_upgrade.asp?tid=29EFA0B8-2033-4B66-9103-BDE1A0C7B874">blah blah blah...</a>')
END
CLOSE rs_thoughts
DEALLOCATE rs_thoughts
--*************************************************
-- Return the values held in the temp table
--*************************************************
SELECT return_value FROM @return ORDER BY orderby ASC
--*************************************************
--Drop the temp table
--*************************************************
--DROP TABLE @return
SET NOCOUNT OFF
http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them
There is no need that I can see to use cursors for this at all and they should not ever be your first choice in SQL Server anyway.
Likely the times it times out are when there are the most records to process.
There are two approaches you can take.
1) Increase the timeout.
2) Convert the cursor operations to Set Based SQL Statements. You can probably get significant performance improvements there.
I'm not seeing anything specific- but it's been a while since I've dug into cursors and such things. Two rules of thumb I generally follow (others here have disagreed with me in the past):
1) Cursors are evil. Use them only when you must, for as little as possible.
2) If you find yourself doing a great deal of logic in a SQL Stored Procedure, consider migrating the logic to external code somewhere, so that SQL is doing as little "processing" as possible.
YMMV.
Also, have you put a trace on the procedure (I know it's random, so the likely answer to that is 'no'), and/or have you eliminated resource contention as a possible answer?
I also noticed that you are using a "select...where...in" pointing to a table in another database. In my experience 'in' and 'not in' clauses are as evil as cursors (or even more), unless the table you are pointing to is guaranteed to have only a handful of records.
加载中,请稍侯......
精彩评论