Slow performance when using OFFSET/FETCH with Fulltext in SQL Server 2012
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 #tmpSELECT p.ShopId, count(1) as ProductsQuantity, MIN(LastPrice) as MinPrice, MAX(LastPrice) as MaxPriceFROM Product2 p WITH (NOLOCK)INNER JOIN CONTAINSTABLE(Product2, ProductName, 'czarny') AS KEY_TBLON KEY_TBL.[key]=p.IdWHERE (p.LastStatus > 0 OR p.LastStatus = -1) GROUP BY p.ShopIdselect ShopId, ProductsQuantity, MinPrice, MaxPricefrom #tmpORDER BY ShopId ascOFFSET 10 ROWSFETCH NEXT 10 ROWS ONLY