TOP 1 running very slow vs TOP 2 or more running fast

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:

 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?

avatar image By mja1175 1 asked Mar 16, 2016 at 03:42 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x46
x24
x18

asked: Mar 16, 2016 at 03:42 PM

Seen: 164 times

Last Updated: Mar 16, 2016 at 03:45 PM