0

On SQL Server, if I do this query:

select top(10) * from MyTable
order by id desc

it tooks only some ms to execute. (please note, every rows contains a field Message with value "Some Fancy String" string)

Now if I add a where clause, where the first 10 id returned above already match it:

select top(10) * from MyTable
where Message = 'Some Fancy String'
order by id desc

It tooks dozen seconds.

Why? I mean, when scanning with an ordered id, if the first 10 rows already match the where clause as for the first query, the sql procedure should just "exit" once reached the top N.

There is no scenario where scanning all the rows will change the result set (since Id is already ordered by id desc, and each row contain the desidered string).

Can you shine me about this?

Note: can't add a index on Message since its a nvarchar(4000), and it reaches the KB limits.

3
  • can't add a index on Message since its a nvarchar(4000), and it reaches the KB limits. It's enough to index by the column prefix whose length is equal or slightly greater than reference string value length. And even when the prefix is shorter then you may to get some profit nevertheless. From the other side if the index selectivity is low then its usage is not effective and may even make your query slower.
    – Akina
    Commented Mar 28 at 8:00
  • Oops.. You use SQL Server which does not support indexing by prefix.
    – Akina
    Commented Mar 28 at 9:04
  • 1
    Please share the execution plans for both queries via brentozar.com/pastetheplan, please also show the table and index definitions Commented Mar 28 at 22:44

1 Answer 1

2

Why? I mean, when scanning with an ordered id ...

What makes you think it will do things that way?
First thing to learn about databases is that they work out for themselves how best to process any query. In the absence of an index of Message, it will [probably] scan the whole table sequentially, filtering out the rows that match, then it will think about how you want those results sorted and, only then, will it worry about the fact that you only want the "first" ten of them.

3
  • Logically makes no sense, but I don't know the backbones of a SQL engine, of course I trust engineer. So, this way, since I can't place an index to Message, what's the best way to increase the performance?
    – markzzz
    Commented Mar 28 at 10:39
  • @markzzz It makes sense how it's designed because you're asking it to filter on a specific value. The engine doesn't know that the top 10 rows sorted by id happen to have that value. You, as a human, don't even know that until after you run the query and see that's the case. But the engine doesn't remember (cache) the past results of a query, (which makes sense because they can change between executions). So as an engine that needs to plan it's steps of how it's going to execute before it actually goes to execute, and without knowing the results, to guarantee accurate results, it has to...
    – J.D.
    Commented Mar 28 at 13:03
  • ...scan all the rows of the table first (since you don't have an index on Message), so that it can filter them down, and then guarantee that the top 10 when sorted by id definitely are rows that meet your filter's logic.
    – J.D.
    Commented Mar 28 at 13:04

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