SQL Server Performance

Simple query won't run as a stored procedure

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by eastcoastsurfer, Oct 31, 2006.

  1. eastcoastsurfer New Member

    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,
  2. FrankKalis Moderator

    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>
  3. joechang New Member

    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
  4. eastcoastsurfer New Member

    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.
  5. joechang New Member

    then why don't you post the xml execution plan
  6. eastcoastsurfer New Member

    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!

Share This Page