Slow performance when using OFFSET/FETCH with Fulltext in SQL Server 2012

Question

I'm trying to write a database-size pagination of query results. As SQL Server 2012 offers OFFSET/FETCH, I'm using it. But after I add the statement to my query, it takes 10 times longer.

The queries:

SELECT 
    p.ShopId, 
    count(1) as ProductsQuantity, 
    MIN(LastPrice) as MinPrice, 
    MAX(LastPrice) as MaxPrice
FROM Product2 p WITH (NOLOCK)
INNER JOIN
   CONTAINSTABLE(Product2, ProductName, 'czarny') AS KEY_TBL
ON KEY_TBL.[key]=p.Id
WHERE 
    (p.LastStatus > 0 OR p.LastStatus = -1) 
GROUP BY p.ShopId
ORDER BY p.ShopId asc



SELECT 
    p.ShopId, 
    count(1) as ProductsQuantity, 
    MIN(LastPrice) as MinPrice, 
    MAX(LastPrice) as MaxPrice
FROM Product2 p WITH (NOLOCK)
INNER JOIN
   CONTAINSTABLE(Product2, ProductName, 'czarny') AS KEY_TBL
ON KEY_TBL.[key]=p.Id
WHERE 
    (p.LastStatus > 0 OR p.LastStatus = -1)   
GROUP BY p.ShopId
ORDER BY p.ShopId asc
OFFSET  10 ROWS
FETCH NEXT 10 ROWS ONLY

First query returns results in 3 seconds, the second one in 47 seconds. Execution plan are different, and the second's cost is evaluated only as 7%, what totally makes no sense to me:

I need help how to improve performance of the pagination.

Answer 1

It's hard to advise without having your schema and data in hands. There is at least one thing that you should be able to do with those 3 sec. for first query and 47 sec. for second, which is put results of the first query into temporary table and then use it for order by ... offset fetch next:

create table #tmp (Id int not NULL, Quantity int, MinPrice decimal(10,4), MaxPrice decimal(10,4), primary key clustered (Id))

insert into #tmp
SELECT 
    p.ShopId, 
    count(1) as ProductsQuantity, 
    MIN(LastPrice) as MinPrice, 
    MAX(LastPrice) as MaxPrice
FROM Product2 p WITH (NOLOCK)
INNER JOIN
   CONTAINSTABLE(Product2, ProductName, 'czarny') AS KEY_TBL
ON KEY_TBL.[key]=p.Id
WHERE 
    (p.LastStatus > 0 OR p.LastStatus = -1) 
GROUP BY p.ShopId

select ShopId, ProductsQuantity, MinPrice, MaxPrice
from #tmp
ORDER BY ShopId asc
OFFSET  10 ROWS
FETCH NEXT 10 ROWS ONLY