0

Consider the following query:

select top 50 p.value, p.pointid from point p
    where exists (select c.collectionid from collection c
        where c.parentid = 1
        and c.somebool = 0 and c.someotherbool = 1
        and p.collectionid = c.collectionid)
    order by p.value desc

In a given schema where the collection table has points and a parent is referenced from the collection table (part of this query).
Something about this query and the indexes I have on the more complex version of this results in Sql Server thinking its a good idea to execute the "order by" FIRST on a table of ~500k rows instead of executing the where and working on 4k rows instead.
This results in a spill (btw would a spill kill the cache?) which just destroys performance seemingly across the board.

I created this a smaller example of this but couldn't reproduce the problem making me believe there is some sort of issue with another index or materialized view or something that is confusing the execution plan.

On the more complex version of this I can get the desired result by forcing it to use an index that is on the equivalent of point.collectionid. Sadly I'm constrained by an ORM that doesn't output nice SQL otherwise I'd use the hint or temp table.
Amusingly enough top(3250) causes the 500k sort but top(3500) doesn't.

Any ideas about the rationale? Could this be a bug in Sql Server?

EDIT: An image showing the original execution plan and my attempt to recreate the problem.
We can see from this that in the "madness" it begins the query off the PK clustered of the 500k row table, in the recreation (there are a million rows in that one) it correctly executes the WHERE part of the clause first starting with the PK of the other table to reduce its subset to 4k as opposed to sorting the whole thing.

Sorry I can't post the lot but there is a large amount of noise in this db and I don't want to post internals.

  • You should mention your RDBMS when you you talk about plans - these vary greatly from server to server! – Vérace Nov 15 '17 at 18:01
  • are statistics up to date? is auto-update stats turned on? – SqlACID Nov 15 '17 at 20:25
  • The plan would help. Possibly it's sorting to make the EXISTS step faster to join up. Does it make a difference if you JOIN instead of EXISTS? select top 50 p.value, p.pointid from point p JOIN collection c ON c.parentid = 1 and c.somebool = 0 and c.someotherbool = 1 and p.collectionid = c.collectionid order by p.value desc – indiri Nov 15 '17 at 21:43
  • 1
    You're asking a very general question which can only be answered with a generic answer which you probably won't find helpful. If the performance problem that you're experiencing is caused by the sort and the spill I would focus on that particular problem and ask how you can prevent the spill. I appreciate you taking the effort to try to simplify the problem, but how can we reproduce a problem without your data when you can't reproduce it even with your data? – Joe Obbish Nov 16 '17 at 2:08
  • 2
    There is nothing obviously wrong about sorting, even a 500K rows table. Assuming that there is no index on point (value) and that the optimizer assumes (wrongly, maybe due to statistics) that the join will return many more rows than it actually does, it estimates that the sort-then-join will be cheaper than join-then-sort. If there was an index on point (value) (or other similar index) and/or different statistics it might have chosen a different plan. The above two are not the only possible plans for a query like this. – ypercubeᵀᴹ Nov 16 '17 at 11:54
2

The general answer here is: Because it does not know.

The Query Optimizer makes assumptions based on expense based on statistics. Missing indices, outdated statistics and some other things (too complex query -> running out of time to find the optimal plan, more rare) can lead to a bad execution plan.

Missing indices and outdated statistics are the most likely culprits.

For a detailed analysis you need to look at the plan - not a picture of the plan. Of an actual execution (so you can compare initial estimated rows with actual rows, which gives an idea how far off the query optimizer's assumptions were).

If you have a top clause, then sql server really gets a little into murky water. It knows the distribution of foreign keys in the whole data set via statistics (histogram), but how far are those represented in your top results? This can leave sql server on a bad path because statistical mathematics may break down. Which results in a non optimal query path.

Query optimizers are also under constant development ;) Not saying bug - more like "edge cases better handled in newer versions".

  • Thanks for the response. Yeah sorry only having the picture, these people are kinda picky about information sharing so I didn't want to expose too much, plus there is a ton of noise in there. – Quibblesome Nov 16 '17 at 11:34

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.