TOP 1 running very slow vs TOP 2 or more running fast
TOP anything > 1 runs in a millisecond, TOP 1 runs in 12 minutes.
This is what the query looks like:
SELECT TOP ? Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8 AS Column9, Column10, Column11, Column12 AS Column13, Column14, Object1.Column15 AS Column16, ? AS Column17, Column18 FROM Object2 Object1 INNER JOIN CONTAINSTABLE(Object2, *, ?, ?) key_table ON Object1.Column19 = Object3.Column20 WHERE(Column6 IN ( SELECT DISTINCT Object4.Column21 FROM Object4 INNER JOIN Object5 ON Object4.Column22 = Object5.Column22 WHERE Object5.Column23 = ? AND Object4.Column24 = ? )) ORDER BY Column2 ASC;
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?