SP speed slow

[wawalter]

Hello,

I just moved some SPs from our development box, which is SQL2000 to our production box, which is SQL2005. They are both hitting identical data sets.

The first time the SPs are executed, they run pretty slow. But, on the development server, subsequent executions are very fast. Unfortunately, on the production box they remain very slow.

Any ideas on why this is? I can post an SP, but I'm not sure if the SP code is the problem.

Thanks,
Bill

 

[vbkris]

hmm, are you executing dynamic sqls? or recompile option???

the info that you have provided is not enough to procede...

Known is handfull, Unknown is worldfull

 

[wawalter]

This SP is actually replacing one that had dynamic SQL in it. I'm not sure about the recomile option. Is that a server setting or SP setting?

Here is one of the SP's...

ALTER PROCEDURE [dbo].[usp_HH2EchoMailerSearch_NotOwns]
	(
	@rc				CHAR(6),
	@getAll			VARCHAR(4),
	@Rank			CHAR(1) = '',
	@LastName		VARCHAR(30),
	@FirstName		VARCHAR(30),
	@BeginRow		INT = 1,
	@EndRow			INT = 10000,
	@prodcat		VARCHAR(10),
	@ownpolicies	char(1) = '',
	@productid		VARCHAR(10)
	)

AS

SET NOCOUNT ON

IF @getall = 'Yes'
BEGIN
	SET @BeginRow = 1
	SET ROWCOUNT 10000
END
ELSE
BEGIN
	SET ROWCOUNT @endrow
END
	
DECLARE @rankMin DECIMAL (9,2), @rankMax DECIMAL (9,2)
IF @rank = 'H'
BEGIN
	SET @rankMin = 0.0
	SET @rankMax = 33.0
END
ELSE IF @rank = 'M' 
BEGIN
	SET @rankMin = 33.1
	SET @rankMax = 66.0
END
ELSE IF @rank = 'L'
BEGIN
	SET @rankMin = 66.1
	SET @rankMax = 100.0
END
ELSE
BEGIN
	SET @rankMin = 0
	SET @rankMax = 100.0
END

DECLARE @sortTable TABLE (
	ident			INT NOT NULL IDENTITY(1, 1),
	HHLD_ID		CHAR(11) NOT NULL,
	ST_AGT_CD		CHAR(6) NOT NULL,
	PCT			NUMERIC(9, 2) NULL,
	Score			TINYINT NULL,
	TOP_3_RANK		INT NOT NULL,
	TOP_3_ORDER		INT NOT NULL,
	Type1			VARCHAR(50) NOT NULL,
	Type2			VARCHAR(50) NOT NULL,
	Type3			VARCHAR(50) NOT NULL,
	Score1			NUMERIC(9, 2) NULL,
	Score2			NUMERIC(9, 2) NULL,
	Score3			NUMERIC(9, 2) NULL,
	variable1		CHAR(6) NOT NULL,
	ECHO_Key		CHAR(24) NOT NULL,
	Addr1			VARCHAR(150) NOT NULL,
	Addr2			VARCHAR(150) NULL,
	City			VARCHAR(19) NOT NULL,
	State			CHAR(2) NOT NULL,
	Zip			VARCHAR(6) NOT NULL,
	HOH			BIT NOT NULL,
	Last			VARCHAR(120) NOT NULL,
	First			VARCHAR(45) NULL,
	HPhone			CHAR(10) NULL,
	WPhone		CHAR(10) NULL,
	CLIENT_ID		CHAR(11) NULL,
	DoNotMail		CHAR(1) NULL,
	BirthDate		CHAR(10) NULL,
	Gender			CHAR(1) NULL
)

INSERT INTO @sortTable (HHLD_ID, ST_AGT_CD, PCT, Score, TOP_3_RANK, TOP_3_ORDER, Type1, Type2, Type3,
						Score1, Score2, Score3, variable1, ECHO_Key, Addr1, Addr2, City, State, Zip, HOH,
						Last, First, HPhone, WPhone, CLIENT_ID, DoNotMail, BirthDate, Gender)
SELECT TOP 100 PERCENT hh.HHLD_ID, hh.ST_AGT_CD, COALESCE(p.PCT, 100) AS PCT, 0 AS Score,
		hh.TOP_3_RANK, hh.TOP_3_ORDER,
		m1.ModelAgentLabel AS Type1, m2.ModelAgentLabel AS Type2, m3.ModelAgentLabel AS Type3,
		p1.PCT AS Score1, p2.PCT AS Score2, p3.PCT AS Score3,
		'      ' AS variable1, l.HHKey AS ECHO_KEY, l.Address AS ADDR1,
		' ' AS ADDR2, l.City, l.ST AS STATE, l.ZipCode AS ZIP,
		l.HHInd AS HOH, c.LastName AS LAST, c.FirstName AS FIRST,
		l.HomePhone AS HPHONE, l.WorkPhone AS WPHONE, c.ClientID AS CLIENT_ID,
		c.Record_Type AS DoNotMail, ' ' AS BirthDate, ' ' AS Gender
	FROM CLNT.dbo.Clients c WITH (NOLOCK)
	INNER JOIN CLNT.dbo.LOB l WITH (NOLOCK)
		ON (c.State = l.State
			AND c.Code = l.Code
			AND c.ClientID = l.ClientID)
	INNER JOIN HMGR.dbo.HH_Data hh WITH (NOLOCK)
		ON (l.HHID = hh.HHld_ID
			AND LEFT(hh.ST_AGT_CD, 2) = l.State
			AND RIGHT(hh.ST_AGT_CD, 4) = l.Code)
	INNER JOIN HMGR.dbo.HH_Data_PRODUCTS p WITH (NOLOCK)
		ON (p.ST_AGT_CD = hh.ST_AGT_CD
			AND p.HHLD_ID = hh.HHLD_ID
			AND p.Product = @Prodcat)
	INNER JOIN Models m1 WITH (NOLOCK)
		ON (hh.Score1 = m1.ModelID)
	INNER JOIN Models m2 WITH (NOLOCK)
		ON (hh.Score2 = m2.ModelID)
	INNER JOIN Models m3 WITH (NOLOCK)
		ON (hh.Score3 = m3.ModelID)
	INNER JOIN HMGR.dbo.HH_Data_PRODUCTS p1 WITH (NOLOCK)
		ON (p1.ST_AGT_CD = hh.ST_AGT_CD
			AND p1.HHLD_ID = hh.HHLD_ID
			AND p1.Product = dbo.fnGetTPFieldNameByMailerID(hh.Score1))
	INNER JOIN HMGR.dbo.HH_Data_PRODUCTS p2 WITH (NOLOCK)
		ON (p2.ST_AGT_CD = hh.ST_AGT_CD
			AND p2.HHLD_ID = hh.HHLD_ID
			AND p2.Product = dbo.fnGetTPFieldNameByMailerID(hh.Score2))
	INNER JOIN HMGR.dbo.HH_Data_PRODUCTS p3 WITH (NOLOCK)
		ON (p3.ST_AGT_CD = hh.ST_AGT_CD
			AND p3.HHLD_ID = hh.HHLD_ID
			AND p3.Product = dbo.fnGetTPFieldNameByMailerID(hh.Score3))
	WHERE c.State = LEFT(@rc, 2)
	AND c.Code = RIGHT(@rc, 4)
	AND l.HHInd = 1
	AND (c.Record_Type IS NULL
			OR c.Record_Type = 0)
	AND NOT EXISTS (SELECT *
						FROM RestrictedZipCodes r WITH (NOLOCK)
						WHERE LEFT(l.ZipCode, 3) = LEFT(r.ZipCode, 3)
						OR RTRIM(l.ZipCode) = r.ZipCode)
	AND NOT EXISTS (SELECT *
						FROM Products pr WITH (NOLOCK)
						INNER JOIN sfel.dbo.tStateLookup sl WITH (NOLOCK)
							ON (sl.StateAbbr = l.St
								AND chvRestrict LIKE '%' + sl.sStateCode + '%')
						WHERE pr.chrProductID = @productid)
	AND (@LastName IS NULL
			OR c.LastName LIKE @LastName + '%')
	AND (@FirstName IS NULL
			OR c.FirstName LIKE @FirstName + '%')
	AND p.PCT IS NOT NULL
	AND p.PCT <> 0
	AND p.PCT BETWEEN @rankMin AND @rankMax
	GROUP BY hh.HHLD_ID, hh.ST_AGT_CD, p.PCT, hh.TOP_3_RANK, hh.TOP_3_ORDER,
		m1.ModelAgentLabel, m2.ModelAgentLabel, m3.ModelAgentLabel, p1.PCT, p2.PCT, p3.PCT,
		l.HHKey, l.Address, l.City, l.ST, l.ZipCode, l.HHInd, c.LastName, c.FirstName,
		l.HomePhone, l.WorkPhone, c.ClientID, c.Record_Type
	ORDER BY p.PCT, hh.TOP_3_RANK, hh.TOP_3_ORDER, c.LastName

SET ROWCOUNT 0

SELECT	HHLD_ID, ST_AGT_CD, PCT, Score, TOP_3_RANK, TOP_3_ORDER, Type1, Type2, Type3,
		Score1, Score2, Score3, variable1, ECHO_Key, Addr1, Addr2, City, State, Zip, HOH,
		Last, First, HPhone, WPhone, CLIENT_ID, DoNotMail, BirthDate, Gender
	FROM @sortTable
	WHERE ident >= @BeginRow
	ORDER BY PCT, TOP_3_RANK, TOP_3_ORDER, Last