SQL Server Query Optimisation - Unexpected slowness in a simple query

Question

Possible explanation is here in the comment

In SQL Server 2014 Enterprise Edition (64-bit) - I am trying to read from a View. A standard query contains just an ORDER BYand OFFSET-FETCH clause like this.

Approach 1

SELECT

    *

FROM Metadata
ORDER BY
    AgeInHours      ASC, 
    RankingPoint    DESC, 
    PublishDate     DESC
OFFSET 150000 ROWS
FETCH NEXT 40 ROWS ONLY

However, this fairly simple query performs almost 9 times slower (noticable when skipping large number of rows like 150k) than the following query which returns the same result.

In this case I am reading the primary key first and then using that as a parameter for WHERE...IN function

Approach 2

SELECT 
    * 
FROM Metadata
WHERE NewsId IN (
    SELECT

        NewsId

    FROM Metadata
    ORDER BY
        AgeInHours      ASC, 
        RankingPoint    DESC, 
        PublishDate     DESC
    OFFSET 150000 ROWS
    FETCH NEXT 40 ROWS ONLY
)
ORDER BY
    AgeInHours      ASC, 
    RankingPoint    DESC, 
    PublishDate     DESC

Bench-marking these two shows this difference

(40 row(s) affected)

 SQL Server Execution Times:
   CPU time = 14748 ms,  elapsed time = 3329 ms.

(40 row(s) affected)

 SQL Server Execution Times:
   CPU time = 3828 ms,  elapsed time = 469 ms.

I have indexes on the primary key, PubilshDate and their fragmentation is very low. I have also tried to run similar queries against the database table, but in every cases the second approach yields great performance gains. I have also tested this on SQL Server 2012.

Can someone explain what is going on?

Schema

Approach 1: Execution Plan

Approach 2: Execution Plan (Left part)

Approach 2: Execution Plan (Right part)

Answer 1

For differently structured queries with even same result set you get different query plans with different approach and query cost. That is common for variety of SQL RDBMS implementations.

Basically in sample above when selecting small part of data from large table is good approach first to reduce and minimize number of rows in result and then select full rows with all columns just like your 2. query.

Another approach is to build exact proper index for reducing result set in first step. In query above probably columns from ORDER BY clause in just same column and sort order could be a solution.

(You didn't sent structure of indexes mentioned in query plans I can just imagine what is hidden behind their names.)

You can also use SQL index hinting to direct SQL optimizer to specific index which you consider as best for task in case SQL optimizer doesn't do the job.