如何处理繁重的查询作为轻和快速版本

Question

我一直在使用Fetch Next(而不是linq查询skip.take())，但下面的查询非常慢，我怎么能处理这个慢的查询。我想让它更快。

如何重写这个sql查询和linq版本(如果它更快)

var ciftciDestekler = ctx.tblDestek.SqlQuery(
    " select d.ParselId,d.DestekId,d.Urun,d.KullanimSekli,d.TarimSekli,d.EkiliAlan,d.DesteklenenAlan,d.Aciklama,d.TarifeId,d.VeriSurumu "+
    "from tblCiftci c "+
    "inner join  tblParsel p  on c.CiftciId = p.CiftciId "+
    "inner join tblDestek d on p.ParselId = d.ParselId where c.UretimSezonu = 2016 "+
    "order by d.DestekId "+
    "OFFSET 0 ROWS -- skip 10 rows "+
    "FETCH NEXT 10 ROWS ONLY; --").ToList();  

Answer 1

我想指出，您的查询不选择只有10行-它选择所有行，因为FETCH NEXT部分是可选的，你已经用"——跳过10行"注释了它。

你的查询是一个巨大的单行没有换行，所以它实际上不是:

select d.ParselId,d.DestekId,d.Urun,d.KullanimSekli,d.TarimSekli,d.EkiliAlan,d.DesteklenenAlan,d.Aciklama,d.TarifeId,d.VeriSurumu 
from tblCiftci c 
inner join  tblParsel p  on c.CiftciId = p.CiftciId 
inner join tblDestek d on p.ParselId = d.ParselId where c.UretimSezonu = 2016 
order by d.DestekId 
OFFSET 0 ROWS -- skip 10 rows 
FETCH NEXT 10 ROWS ONLY; --

而是a:

select d.ParselId,d.DestekId,d.Urun,d.KullanimSekli,d.TarimSekli,d.EkiliAlan,d.DesteklenenAlan,d.Aciklama,d.TarifeId,d.VeriSurumu from tblCiftci c inner join  tblParsel p  on c.CiftciId = p.CiftciId inner join tblDestek d on p.ParselId = d.ParselId where c.UretimSezonu = 2016 order by d.DestekId OFFSET 0 ROWS -- skip 10 rows FETCH NEXT 10 ROWS ONLY; --

您应该删除该注释或将其更改为不会影响以下代码的斜杠-星号注释