SQL Server Performance

date format in query

Discussion in 'SQL Server 2008 General Developer Questions' started by ismailadar, Jan 18, 2011.

  1. ismailadar New Member

    Hi all,
    I notice a strange situation when i write son query.In these queries there is a big difference on execution time. I wonder what is the reason of this big difference?declare
    @StartDate datetime
    set @StartDate=DATEADD(DAY,-1,GETDATE())declare
    @FinishDate datetime
    set @FinishDate=DATEADD(DAY,1,GETDATE())select
    * from Tbl_Person where HDate between @StartDate and @FinishDateselect
    * from Tbl_Person where HDate between DATEADD(DAY,-1,GETDATE()) and DATEADD(DAY,1,GETDATE())
  2. FrankKalis Moderator

    Have you compared the execution plans? Which one is slower and is this consistent?
  3. ismailadar New Member

    hi all,
    the first one is slower than the second one.jdgonzalez thanks for your clear reply but can you give me a reference url?
  4. FrankKalis Moderator

  5. jdgonzalez New Member

    [quote user="ismailadar"]
    hi all,
    the first one is slower than the second one.jdgonzalez thanks for your clear reply but can you give me a reference url?
    [/quote]I don't have the link to the where I got the quote from. But this article from SQLMag does a very good job of explaining it. http://www.sqlmag.com/article/sql-s...int-to-solve-parameter-sniffing-problems.aspx

    I'm a bit surprised that the first one would be slower than the second one. In my experience, it's always been the first one that is faster than the second. That being said, it appears that you have two options to remedy this.

    1. Use the RECOMPILE option to force a recompile at runtime.
    2. Force the query to use the correct index. My guess is when you look at the execution plan for both of these queries they are different. Probably using using a different index that it should be or doing a table scan.

    Unfortunately, I don't know enough about sql server internals to explain why this happens. But these are techniques I've used to overcome that problem.

    J.D.
  6. FrankKalis Moderator

    I believe your quote is from the URL I've posted. Here is a great explanation of "Parameter Sniffing"
    http://sql-server-performance.com/Community/forums/p/14428/83652.aspx#83652
  7. satya Moderator

    ha ha .. the date master (Frank) is asking a wise question back here [:)], I'm interested to knwo the thoughts on this one.. will watch the thread.
  8. jdgonzalez New Member

    I had something similar happen a few months ago. From everything that I read, it was something called 'parameter sniffing'. Here's Microsoft's definition of it: “Parameter sniffing” refers to a process whereby SQL Server’s execution environment “sniffs” the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word “current” refers to the parameter values present in the statement call that caused a compilation or a recompilation.I was able to fix it by doing what you did in your query. I also thinks it makes it easier to read.

Share This Page