0

So here is the original query I'm working with

SELECT TOP(10) * 
FROM Orders o
WHERE (o.DateAdded >= DATEADD(DAY, - 30, getutcdate())) AND (o.DateAdded <= GETUTCDATE())
ORDER BY o.DateAdded ASC,
o.Price ASC
o.Quantity DESC

Datatype:

  • DateAdded - smalldatetime
  • Price - decimal(19,8)
  • Quantity - int

I have an index on the Orders table with the same 3 columns in the same order, so when I run this, it's perfect. Time < 0ms, Live Query Statistics shows it only reads the 10 rows. Awesome.

However, as soon as I add this line to the WHERE clause

AND o.Price BETWEEN convert(decimal(19,8), 0) AND @BuyPrice

It all goes to hell (and unfortunately I need that line). It also behaves the same if it's just o.Price <= @BuyPrice. Live Query Statistics shows the number of rows read is ~30k. It also shows that the o.Price comparison isn't being used as a seek predicate, and I'm having a hard time understanding why it isn't. I've verified @BuyPrice is the right datatype, as I found several articles that discuss issues with implicit conversions. At first I thought it was because I had two ranges: first the dateAdded then Price, but I have other queries doing with multi column indexes and multiple ranges and they all perform just fine. I'm absolutely baffled as to why this one has decided to be a burden. I've tried changing the order of columns in the index, changing them from ASC to DESC, but nada.

Would highly appreciate anyone telling me what I'm missing. Thanks

Jacob FW
  • 153
  • 8

2 Answers2

1

It is impossible for the optimizer to use two range predicates at the same time.

Think about it: It starts scanning from a certain spot in the index sorted by DateAdded. It now needs, within each individual DateAdded value to seek to a particular Price, start scanning, and stop at another Price, then jump to the next DateAdded.

This is called skip-scanning, it is only efficient when the first predicate is not very many values, otherwise it is inefficient, and because of this, only Oracle has implemented it, not SQL Server.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
0

I think this is due to the TOP 10 which cannot take place before the ORDER BY. And this ORDER BY must wait until the result set is ready.

Without your additional price range, the TOP 10 can be taken from the existing index directly. But adding the second range will force another operation to be run first.

In short:

  • First your filter must get the rows for the price range together with the date range.
  • The resulting set is sorted and the top 10 rows are taken.

Did you try to add a separate index on your price column? This should speed up the first filter.

We cannot predict the execution plan in many cases, but you might try to

  • write an intermediate set, filtered by the date range, into a temp table and proceed from there. You might even create an index on the price column there (Depends on the expected row count. Probably the best option).
  • use a CTE to define a set filtered by the the date range and use this set to apply your price range. But a CTE is not the same as a temp table. The final execution plan might be the same as before...
  • use two CTEs to define two sets (one per range) and use INNER JOIN as a way to get the same as with WHERE condition1 AND condition2.
Shnugo
  • 66,100
  • 9
  • 53
  • 114