7

Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being found.

I'm a bit new to this, but my understanding of Sargable and Non-Sargable SQL is that the offending function is operating on the column and thus obstructing the index (assuming one is present) which leads to a scan of the column and a performance hit. The code has several functions in the input, lots of minor stuff, like

Where a.date between 'inputdate' and dateadd(day,1,'inputdate')

The DBA has taken the stance that any query which contains a function in the WHERE clause is non-saragable. I'm going to drop the issue with him because arguing will get me nowhere except a pissed off admin and my access limited.

But for the sake of knowledge, is there a case where he's correct and any function present in the WHERE clause is a problem, regardless of function or placement?

  • 4
    No, if the query is what you show, he is totally wrong. It's pretty sargable as it is. (I wouldn't use BETWEEN with dates but that's another, unrelated issue) You only show us one query though. If you have like tens of queries with non-sargable conditions and function calls, they may have missed the one that is indeed sargable. – ypercubeᵀᴹ Jul 9 '14 at 14:44
  • 1
    That is likely the case, that I missed something among the code. But I pushed for clarification on the issue os Sargability specifically for my own benefit and was told that it mattered for ANY function in the WHERE or ON clause. That didn't make sense to me logically since the reason for the issue in the first place was to allow indexes to be used... and you can't index a single input. – sten Jul 9 '14 at 14:54
  • 1
    Doesn't SQL Server have functional indexes, or at least, computed columns with indexes? – Vérace Jul 9 '14 at 15:12
  • 2
    BTW, the term "sargable" usually relates to individual predicates, not to queries. – mustaccio Jul 9 '14 at 15:13
  • 1
    @BrentOzar has a pretty easily understandable take on sargability at brentozar.com/archive/2010/06/sargable-why-string-is-slow – Max Vernon Jul 9 '14 at 15:14
7

As ypercube commented

No, if the query is what you show, he is totally wrong. It's pretty sargable as it is.

You can verify this by:

  • Creating a simple test table with a [Date] column.
  • Insert a large number of rows with varying dates.
  • NOTE: In the above "large number" and "varying dates" is a precaution to ensure that your query is selective enough. Otherwise the optimiser may choose not to use your index in any case.
  • Generate a query plan for your query (once with an index on the Date column, and once without).
  • You can also use STATISTICS IO to show the difference.
  • If you have enough test data, the difference will be easily observable.

Once you've got the evidence, I suggest you do take it up with the DBA. However, don't get into an argument. Just show the test and data that demonstrates the index is used.

The point is you don't want to be forced to bend over backwards to avoid non-existant issues.

Fortunately in the case you demonstrated it won't be a problem to move the functions outside the query. E.g.

DECLARE @FromDate date ='inputdate',
        @ToDate date = DATEADD(day, 1, @FromDate)

In fact, the above may even be more maintainable in the long run.

However, there will come a time when you have something that cannot be trivially changed according to the DBA's wishes. Such as:

--Granted this probably belongs in a JOIN clause, but is primarily for illustrative purposes.
--Also the issue of sargability applies just as much to JOIN clauses as WHERE clauses
WHERE a.date >= b.date
  AND a.date < DATEADD(day, 1, b.date)

The only way to get this function out of the WHERE clause would be to precalculate another column b.NextDay. Which is exactly why you need the DBA's to understand Sargability correctly. I.e. that the above:

  • Would be able to leverage an index on a.date.
  • But not be able to leverage an index on b.date.
  • So the most selective column/index should not have a function applied, but the other can.
  • Attempting to hack a solution without a function in the WHERE clause will reduce both maintainability and performance.

If you really can't get buy-in from the DBA's perhaps the following will work and bypass their cargo-cult rules:

;WITH CTE_B AS (
    SELECT  b.Date, DATEADD(day, 1, b.DATE) AS NextDay
    FROM    b
    )
SELECT  ...
WHERE   a.Date >= CTE_B.Date
    AND a.Date < CTE_B.NextDay

The optimiser will almost certainly optimise this in the same way as if the function were in the WHERE clause, so you shouldn't get a performance knock. But it's certainly an unnecessary reduction in maintainability.

  • This is gonna sound bad, but I'm living in the stone age. CTE's aren't going to work. WITH isn't going to work... at least not for now. All the queries are run through a reporting system that takes canned statements. The same ones I've inherited. My ultimate plan is to move it all over to scripts which do the work automatically which is currently run manually. Yes the DBAs can use CTE's, and actually they suggested a plan like yours. And I don't want to get in a pissing contest with them because they can make life difficult. – sten Jul 9 '14 at 17:49
  • I also don't want to lose the goodwill I gained by being the first of the Analysis guys to actually go to them and ask what they think before running code against the production server. Politics. – sten Jul 9 '14 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.