Stored procedure re-compiles on setting variable

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 2000 Forums

SQL Server Development (2000)

Stored procedure re-compiles on setting variable

Author

Topic

bluecjh
Starting Member

7 Posts

Posted - 2005-03-08 : 10:49:37

(SQL Server 2000)

I have run a trace on my sp and it re-compiles on the line:


SET @iNextRowId = (SELECT count(*) FROM dbo.t_test)

essentially the sp

consists of:


TRUNCATE TABLE dbo.t_test


DECLARE ... 

SET ...

INSERT INTO dbo.t_test

SELECT	...

	
FROM
	...

WHERE
	...

ORDER BY 	s.ref_supervision, l.contact_dt

------------------------------------------------------------------------------

SET @iLoopControl = 1

SELECT @iNextRowId = Min(ID) FROM dbo.t_test
...

This does not fit in to my expectation of when
a recompile should occur, it would seem from trial
and error that the MIN aggregate function is the offending
bit, but why?

can anyone clarify this?

Thanks

Chris.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-08 : 10:51:38

truncate removes data and indexes thus the recompile occurs to find the best execution method.

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-08 : 10:56:45

Yep. But (and I'm sure Spirit1 didn't mean this way) the indexes aren't dropped, just the data in them.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-08 : 11:13:11

oh yeah true... thanx TG.
sometimes i think that some things are obvious to anyone... have to work on that...

Go with the flow & have fun! Else fight the flow

bluecjh
Starting Member

7 Posts

Posted - 2005-03-08 : 12:56:46

That doesn't follow, for example
If I replace:
SET @iNextRowId = (SELECT count(*) FROM dbo.t_test)
with
SET @iNextRowId = 1
no recompile occurs, in fact I tried removing the truncate line... no difference?
Any ideas?

Chris

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-08 : 13:09:49

interesting...can you post the entire sp?

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-08 : 13:11:30

maybe your insert rearanges the datapages of the index?
don't know if that would recompile the sproc...
you're not using with recompile option, right? just to be sure

Go with the flow & have fun! Else fight the flow

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-08 : 18:35:59

Are you using the variable @iNextRowId in the subsequent WHERE clause or JOIN?

HTH

=================================================================
In order to improve the mind, we ought less to learn than to contemplate.
-Rene Descartes, philosopher and mathematician (1596-1650)

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-08 : 22:44:00

what happens if you use a temp table instead of truncate/insert into dbo.t_test?

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-08 : 22:45:45

also, how is @iNextRowId used in your query?

-ec

EDIT:
Looks like I asked the same thing as Bustaz.

bluecjh
Starting Member

7 Posts

Posted - 2005-03-09 : 04:01:35

Here is the sp,

note that my trace shows that on starting the statement

'SET @iNextRowId = (SELECT count(*) FROM dbo.t_test)'

[@iNextRowId is used to hold the id number in my loop]

that the recompile occurs, in fact you can cut out all that follows
post this line and the recompile still occurs. anyway
here you are: .. Thanks

USE ReportsV4

IF EXISTS(select name from sysobjects where name='p_test')
DROP PROCEDURE p_test

go

CREATE PROCEDURE p_test

AS

SET NOCOUNT ON

TRUNCATE TABLE dbo.t_test
TRUNCATE TABLE dbo.t_test2

DECLARE @nulldate DATETIME,
@returnvalue INT,
@bolcountabsences BIT,
@bolreqrecord BIT,
@appliedfortargetdate DATETIME,
@bolactionoutstanding BIT,
@eventid varchar(40),
@iCurrentRowId INT,
@iLastRowId INT,
@bolbreach TINYINT,
@dpreviousudate DATETIME,
@Earliest_Supervision DATETIME,

@iLoopControl INT,
@iNextRowId INT,

@ucount INT,
@bolappoccurred BIT,
@bolnewsuper BIT,
@2nducontactdate DATETIME,

@v_bolappoccurred BIT,
@v_bolreqrecord BIT,
@v_bolnewsuper BIT,
@v_surname VARCHAR(40),
@v_forename VARCHAR(40),
@v_crn VARCHAR(20),
@v_teamname VARCHAR(250),
@v_supervisiontype VARCHAR(2),
@v_commencement DATETIME,
@v_eventid VARCHAR(32),
@v_termdate DATETIME,
@v_ordertype VARCHAR(250),
@v_apptkept VARCHAR(12),
@v_action VARCHAR(50),
@v_contactdate DATETIME,
@v_contacttype CHAR(12),
@v_officerid VARCHAR(10),
@v_offsurname VARCHAR(40),
@v_offforename VARCHAR(40),
@v_supervisioncount INT,
@v_breachcount INT,
@v_targetdate DATETIME,
@v_noactionoccurred INT,
@v_bolbreach TINYINT,
@v_orderswithbreachcount INT

SET @bolbreach = 0
SET @bolcountabsences = 1
SET @bolappoccurred = 0
SET @bolreqrecord = 0
SET @ucount = 0
SET @bolnewsuper = 0
SET @nulldate = NULL
SET @bolactionoutstanding = 0
SET @Earliest_Supervision = DateAdd(dd,-750,Getdate())

INSERT INTO dbo.t_test

SELECT
@bolappoccurred,
@bolreqrecord,
@bolnewsuper,
c.surname,
c.forename,
c.crn,
d_t.description,
s.commencement_dt,
s.ref_supervision,
s.termination_dt,
l.dec_yesno_apptkept,
l.action,
l.contact_dt AS action_date,
l.dec_contacttype,
sp.pocode AS officerid,
st.surname AS offsurname,
st.forename AS offforename,
0,
0,
@nulldate,
0,
@bolbreach,
0

FROM
dbo.client c
INNER JOIN dbo.supervision s ON c.ref_client = s.ref_client
INNER JOIN dbo.contactlog l ON l.ref_event = s.ref_supervision AND l.dec_eventtype = 'supervision'
LEFT JOIN dbo.staffpost sp ON s.ref_staffpost = sp.ref_staffpost
LEFT JOIN dbo.staff st ON sp.ref_staff = st.ref_staff
LEFT JOIN dbo.decode_items d_t ON d_t.code1=sp.dec_team AND d_t.class= 'team'

WHERE
s.commencement_dt > @Earliest_Supervision
AND s.dec_supervision ='01'
AND s.isdeleted IS NULL
AND l.isdeleted IS NULL
AND c.isdeleted IS NULL
AND s.transferout_dt IS NULL --not transferred out (of area)
AND (
l.dec_yesno_apptkept = 'u'
OR (
(l.action LIKE '%summ%%appl%' AND l.action NOT LIKE '%void%')
OR l.action IN ('%warrant applied%','%to contin%','%otc%','%br%%exe%') --breach exempt ammended
OR ((l.action IN ('%withdrawn%','%breach%','%gymc%','%wnmc%','%cnmc%'))
AND (l.action NOT IN ('%tf%','%think first%','%ltr%','%letter%')))
)
)
AND
(SELECT COUNT(*)
FROM
dbo.supervision s1
INNER JOIN dbo.contactlog l1 ON l1.ref_event = s1.ref_supervision AND l1.dec_eventtype = 'supervision'
WHERE
s1.ref_supervision = s.ref_supervision
AND l1.dec_yesno_apptkept = 'u'
)>1

ORDER BY s.ref_supervision, l.contact_dt

------------------------------------------------------------------------------

SET @iLoopControl = 1

SELECT @iNextRowId = Min(ID) FROM dbo.t_test

SELECT @iCurrentRowId = id,
@v_bolappoccurred = cbolappoccurred,
@v_bolreqrecord = cbolreqrecord,
@v_bolnewsuper = cbolnewsuper,
@v_surname = csurname,
@v_forename = cforename,
@v_crn = ccrn,
@v_teamname = cteamname,
@v_commencement = ccommencement,
@v_eventid = ceventid,
@v_termdate = ctermdate,
@v_apptkept = capptkept,
@v_action = caction,
@v_contactdate = ccontactdate,
@v_contacttype = ccontacttype,
@v_officerid = cofficerid,
@v_offsurname = coffsurname,
@v_offforename = coffforename,
@v_supervisioncount = csupervisioncount,
@v_breachcount = cbreachcount,
@v_targetdate = ctargetdate,
@v_noactionoccurred = cnoactionoccurred,
@v_bolbreach = cbolbreach,
@v_orderswithbreachcount = corderswithbreachcount

FROM dbo.t_test

WHERE id = @iNextRowId

WHILE @iLoopControl = 1

--now iterate through the table to establish the target date (within working 10 days)for the referral to
--court after the 2nd 'u'. Establish if they hit it or not and flag the record as required.

BEGIN
SET @bolreqrecord = 0
IF @v_eventid <> @eventid OR @eventid IS NULL --have we moved on to a new crn?
BEGIN
IF @bolactionoutstanding = 1 AND @eventid IS NOT NULL --no breach action has been taken
BEGIN
SET @iLastRowId = (SELECT MAX(id) FROM dbo.t_test WHERE ceventid = @eventid)
UPDATE t_test SET cbolreqrecord = 1, cnoactionoccurred = 1 WHERE IDENTITYCOL = @iLastRowId
END
SET @bolactionoutstanding = 0
SET @appliedfortargetdate = GETDATE() -- set to a neutral date
SET @eventid = @v_eventid
SET @bolcountabsences = 1
SET @ucount = 0
SET @2nducontactdate = NULL
END

IF (@v_action IN ('%otc%','%to contin%','%br%%ex%'))
OR (@v_action LIKE '%withdrawn%' AND @v_action not IN ('%ins%','%nvq%' ))
--court appearance with an otc etc..
--so start counting u's from 0
BEGIN
IF @bolactionoutstanding = 1 AND @v_contactdate > @appliedfortargetdate
BEGIN
SET @bolreqrecord = 1
SET @v_noactionoccurred = 1
END
SET @bolactionoutstanding = 0
SET @appliedfortargetdate = GETDATE() -- set to a neutral date
SET @bolcountabsences = 1
SET @2nducontactdate = NULL
SET @ucount = 0
END
IF @bolcountabsences = 1
BEGIN
IF @v_apptkept = 'u' AND @ucount < 2
BEGIN
--ignore lapsed u's i.e. older than 1 year
IF @ucount = 0
BEGIN
SET @dpreviousudate = @v_contactdate
END
IF @ucount = 1
BEGIN
IF @v_contactdate > dateadd(yy,1,@dpreviousudate)
BEGIN
SET @ucount = 0
SET @dpreviousudate = @v_contactdate
END
END
SET @ucount = @ucount + 1
IF @ucount = 2 --2nd 'u'
BEGIN
SET @v_bolbreach = 1
SET @bolactionoutstanding = 1 --no action has been taken as yet so set to 1
EXEC @returnvalue = p_ch_calc_working_days @datefrom = @v_contactdate, @number = 10
SET @appliedfortargetdate = DATEADD(dd,@returnvalue,@v_contactdate)
SET @v_targetdate = @appliedfortargetdate
SET @2nducontactdate = @v_contactdate
END
END
IF @v_apptkept = '-' AND ( @v_action IN ('%summ%%appl%', '%warrant applied%', '%breach%%action%%reque%', '%warrant req%'))
BEGIN
SET @bolcountabsences = 0
SET @bolactionoutstanding = 0 --action has been taken
IF @2nducontactdate IS NOT NULL
BEGIN
IF @v_contactdate >= @2nducontactdate
BEGIN
IF @v_contactdate > @appliedfortargetdate
BEGIN
SET @bolreqrecord = 1
END
SET @appliedfortargetdate = GETDATE() --set to a neutral date we no longer need this value
--as it has served it's purpose (housekeeping)
END
END
END
END

UPDATE dbo.t_test

SET

cbolappoccurred = @bolappoccurred,
cbolreqrecord = @bolreqrecord,
cbolnewsuper = @v_bolnewsuper,
csurname = @v_surname,
cforename = @v_forename,
ccrn = @v_crn,
cteamname = @v_teamname,
ccommencement = @v_commencement,
ceventid = @v_eventid,
ctermdate = @v_termdate,
capptkept = @v_apptkept,
caction = @v_action,
ccontactdate = @v_contactdate,
ccontacttype = @v_contacttype,
cofficerid = @v_officerid,
coffsurname = @v_offsurname,
coffforename = @v_offforename,
csupervisioncount = @v_supervisioncount,
cbreachcount = @v_breachcount,
ctargetdate = @v_targetdate,
cnoactionoccurred = @v_noactionoccurred ,
cbolbreach = @v_bolbreach,
corderswithbreachcount = @v_orderswithbreachcount

WHERE
id = @iNextRowId

SELECT @iNextRowId = NULL,
@iNextRowId = MIN(id)

FROM dbo.t_test

WHERE id > @iCurrentRowId

IF ISNULL(@iNextRowId,0) = 0
BEGIN
IF @bolactionoutstanding = 1 AND @eventid IS NOT NULL
BEGIN
--SET @iLastRowId = (SELECT MAX(id) FROM dbo.t_test)
UPDATE t_test SET cbolreqrecord = 1, cnoactionoccurred = 1 WHERE IDENTITYCOL = @iCurrentRowId
END
BREAK
END
SELECT
@iCurrentRowId = id,
@v_bolappoccurred = cbolappoccurred,
@v_bolreqrecord = cbolreqrecord,
@v_bolnewsuper = cbolnewsuper,
@v_surname = csurname,
@v_forename = cforename,
@v_crn = ccrn,
@v_teamname = cteamname,
@v_commencement = ccommencement,
@v_eventid = ceventid,
@v_termdate = ctermdate,
@v_apptkept = capptkept,
@v_action = caction,
@v_contactdate = ccontactdate,
@v_contacttype = ccontacttype,
@v_officerid = cofficerid,
@v_offsurname = coffsurname,
@v_offforename = coffforename,
@v_supervisioncount = csupervisioncount,
@v_breachcount = cbreachcount,
@v_targetdate = ctargetdate,
@v_noactionoccurred = cnoactionoccurred,
@v_bolbreach = cbolbreach,
@v_orderswithbreachcount = corderswithbreachcount

FROM dbo.t_test

WHERE id = @iNextRowId

END
INSERT INTO dbo.t_test2

SELECT

cbolappoccurred,
cbolreqrecord,
cbolnewsuper,
csurname,
cforename,
ccrn,
cteamname,
ccommencement,
ceventid,
ctermdate,
capptkept,
caction,
ccontactdate,
ccontacttype,
cofficerid,
coffsurname,
coffforename,
csupervisioncount,
cbreachcount,
ctargetdate,
cnoactionoccurred,
cbolbreach,
corderswithbreachcount
FROM
dbo.t_test e2
WHERE

(select
COUNT(id)

FROM dbo.t_test

WHERE
ceventid = e2.ceventid AND cbolreqrecord = 1 AND cbolnewsuper = 0) >0
OR
cforename IS NULL -- add the po and breach totals

RETURN 1

bluecjh
Starting Member

7 Posts

Posted - 2005-03-09 : 05:18:54

OK the SP below is a very much cut down version of the original but
has exactly the same problem and will help to clarify things:

with SELECT @iNextRowId = 1, no problem occurs otherwise with
SELECT @iNextRowId = Max(ID) FROM dbo.t_test
there is always a recompile.

however if I remove the line 'TRUNCATE TABLE dbo.t_test'
then the problem persists but with a proviso,

that if I achieve a faultless execution using
SELECT @iNextRowId = 1 and then switch back to using
SELECT @iNextRowId = Max(ID) FROM dbo.t_test (the
problem line) the proc will execute with a recompile
on alternate executions?

DROP PROCEDURE p_test
go
CREATE PROCEDURE p_test
AS
SET NOCOUNT ON
TRUNCATE TABLE dbo.t_test
DECLARE @iNextRowId INT

INSERT INTO dbo.t_test

SELECT
c.surname
FROM
dbo.client c

SELECT @iNextRowId = Max(ID) FROM dbo.t_test--SELECT @iNextRowId = 1

Return

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-09 : 08:24:07

Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table. My guess is that the insert to t_test changed the statistics of the table sufficiently to force a recompile by any SP that references it. The truncate, the insert, and the Select themselves may not have specifically force the recompile, but the cumulative effect did. Enough rows were changed then a reference to the changed table all combined to force a recompile.

edit:
You could consider seperating the truncate and insert from the update with 2 different SPs. Also, I haven't studied it yet but I'd be surprised if you couldn't replace your loop with a set based update.

Be One with the Optimizer
TG

bluecjh
Starting Member

7 Posts

Posted - 2005-03-09 : 08:48:24

I think I'll accept your explanation TG, however
I was unable to figure out a set based way to replace the loop.
If you can see one I'll take my hat off to you!
Thanks for this
Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources