Parameters vs. No Parameters

  • I was asked to look at a query today that was taking a very long time to return. I waited 10 minutes and nothing was happening. It wasn't blocked by any other process (in fact the query was running against a backup of the live database and therefore nothing else was happening in the database). The query plan also looked fine - no table scans or index scans and no suggested missing indexes.

    The query was filtered on a date field like so:

    a.actDateAdded between DATEADD(month, DATEDIFF(month, 0, GETDATE())-3, 0) and

    DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

    In an effort to improve the performance, I changed this to use parameters instead and set the parameters to the exact same values as above:

    a.actDateAdded between @start and @end

    This time the query ran in less than a minute.

    So my question is, why the dramatic improvement?

  • Can you provide table structure, sample data and query

  • Maybe this[/url] article explains it.

    Your GETDATE() function is being re-evaluated many times - your faster solution avoids this.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • That article makes sense, thank you 🙂

  • It won't be due to multiple executions of getdate. The function is fast, but besides that it will only be evaluated once in the query execution (otherwise you'd get multiple different dates in a long-running query, which you don;'t).

    Probably has to do with row estimations, difference between how the optimiser handles the constant values vs the parameters (or were they variables?).

    Any chance of seeing the exec plan of both?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (4/11/2013)


    It won't be due to multiple executions of getdate. The function is fast, but besides that it will only be evaluated once in the query execution (otherwise you'd get multiple different dates in a long-running query, which you don;'t).

    Probably has to do with row estimations, difference between how the optimiser handles the constant values vs the parameters (or were they variables?).

    Any chance of seeing the exec plan of both?

    Hmm, I just knew that I should have kept my mouth shut on this one! 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • It won't be due to multiple executions of getdate. The function is fast, but besides that it will only be evaluated once in the query execution (otherwise you'd get multiple different dates in a long-running query, which you don;'t).

    Now that you mention it, the query plans do look similar and there are no index scans in either, so I suspect you are correct.

    And yes it is variables, not parameters.

    I've attached the query plans for both.

  • Actual plans please, not estimated.

    Index and table scans are not necessarily bad, so the absence of them is not necessarily good.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Here is the actual plan when using variables.

    I'm still waiting for the query without variables to complete. 20 minutes and counting....

  • The query did not return after 60 minutes. My process was killed.

    Can there really be that much difference in such a small alteration to a query?

  • Yes, absolutely.

    I suspect you have stale stats. The version with the getdate, the optimiser can sniff the values and use those values to get an estimate from the row distribution in the statistics, whereas with variables if can't see the values and hence can only get a generic row estimation. If your stats are off (very common on a table with an ascending datetime column), the version with the function could get a completely incorrect (way low) estimate and generate an exec plan that's optimal for very few rows. When the query runs and gets lots of rows the plan is very, very, very bad indeed.

    I've seen a query go from 30 minutes to not finishing after 4 hours because of that problem. The exec plan, if you look only at the estimated plan, looks fine, seeks, key/rid lookups, nested loop joins and low row estimations (like the one row estimated in the plan you attached). It's only with the actual plan you can see the extreme mis-estimate on the row count.

    http://sqlinthewild.co.za/index.php/2011/03/22/statistics-row-estimations-and-the-ascending-date-column/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Very interesting indeed.

    I checked my stats, they were almost 48 hours out of date when I was running the query yesterday (and have not been updated as of yet). There are about 40,000 rows added to this table on a daily basis and its has over 13 million rows in total.

    The thing is, the filter on my query looks at the last 3 months but starting at the 1st of the month. So its not concerned with the last 11 days.

    I'm still confused...

  • Try updating stats with full scan and then the query with the getdates again.

    To diagnose the problem, I really need to see the actual plan of that query.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I updated the statistics on each of the 4 tables in the query, on by one.

    The first 3 updates had no impact, but when I updated the 4th table stats, it ran in 40 seconds!

    But I still don't understand how this made such a difference. The actual query plan is attached...

  • I might be able to explain, if I knew which table the '4th table' was. 😉

    Is that the actual plan from before the stats update or after?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply