I am seeing vastly different query times on SELECT TOP 1 vs changing the TOP 1 to anything else, TOP 2, TOP 10000.
TOP anything > 1 runs in a millisecond, TOP 1 runs in 12 minutes.
This is what the query looks like:
AS you can see I am joining a full text query via CONTAINSTABLE on the tables PK. I am also checking that a resulting column is in a sub select. I have done the sub select in both the IN () select, and changed it to a few more INNER JOINS. I show it this was because its the way the code is. It does the same thing either way.
The issue I have found is the execution plan. If I ask for the TOP 2 or more it will join on the WHERE column completely differently. You can see the difference in the query plans I have added in the Index Seek Object4.Index3. In top_issue_over_one there are only 2 seek predicates. In the top_issue_one there are 3, the third being the column from the sub select. When this happens it will have to loop the tables to get the data, there are potentially millions of records in each of these tables. You can also see from the plans the indexes that are there. They all have indexes on all columns.
To answer a few questions I will be asked proactively, yes the sort column is indexed and yes if I take it off it will run fast on both. Also, if I remove the full text query it will run fast on both. Its the combination of all of this that makes it happen.
I'm curious if anyone has seen this and has any idea why it is doing the plan differently on TOP 1 vs TOP anything else?
By mja1175 1 asked Mar 16, 2016 at 03:42 PM