Simple query won't run as a stored procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Simple query won’t run as a stored procedure

I have a fairly simple query that when I run it directly it returns the proper dates in 2 seconds or so. If I take this same query and put it in a stored procedure with a single parameter it no longer works. I’ve let the stored proc run for up to 30 minutes w/o it ever returning any data. I’ve freed proc cache, updated statistics, recompiled, and all were no help. Here’s the query: CREATE PROCEDURE spSelect_ODSTree_BV
@date smalldatetime = null
AS set nocount on –declare @date smalldatetime if(@date is null)
begin
select @date = convert(varchar(10), getDate(), 101)
end select pkBVMap,
pkIndustry as ‘pk1’, Industry as ‘Level1’,
pkClientGroup as ‘pk2’, ClientGroup as ‘Level2’,
pkPortfolioType as ‘pk3’, PortfolioType as ‘Level3’,
pkPortfolio as ‘pk4’, Portfolio as ‘Level4’,
pkSubPortfolio as ‘pk5’, SubPortfolio as ‘Level5’
from enterprise_bv.dbo.v_BusinessView
where exists (select COL_ACTIVITY_KEY
from tblODS_COL_ACTIVITY
inner join enterprise_bv.dbo.tblWINItem on branchid = stuff(act_br_id,1,2,’0′)
and clientid = act_cl_id
inner join enterprise_bv.dbo.tblBVMapWIN on pkWINItem = fkWINItem
where fkBVMap = pkBVMap
and @date between StartDate and isnull(EndDate, @date)
)
order by Industry, pkIndustry,
ClientGroup, pkClientGroup,
PortfolioType, pkPortfolioType,
Portfolio, pkPortfolio,
SubPortfolio, pkSubPortfolio
GO Any ideas would be appreciated. Thanks,
Isn’t parameter sniffing an interesting feature? [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Check this out:<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11611>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11611</a><br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
when you said the sproc would not run
did you mean from management studio (QA) or from a client app
in any case
for the sql query and the stored proc
generate the execution plan as xml
paste the xml content as a standalone reply
quote:Originally posted by joechang when you said the sproc would not run
did you mean from management studio (QA) or from a client app
in any case
for the sql query and the stored proc
generate the execution plan as xml
paste the xml content as a standalone reply

Times out from the application and never seems to return from the Mgmt Studio or from the old Query Analyzer.
then why don’t you post the xml execution plan
Looks like it was a form of parameter sniffing that was killing the query. If I remove the @date parameter default of null it fixes the problem. Also, if I create a new date variable inside the stored procedure and use that variable in the query after setting it to a value things work. This way I can keep my default parameter of null while allowing the parameter sniffing to work properly. Thanks for everyones help!
]]>