- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Are there any way to force QuerySpec using OFFSET ... ROWS FETCH NEXT?
Joined: 23-May-2019
I'm using LLBLGen 5.3 with SQL Server 2012.
I create a query that select only Entity's ID field. This query also has joins. When I specific Offset() and Limit() in query spec, It does not generate OFFSET ... ROWS FETCH NEXT in final query. I guess due to joins that cause engine switch to client paging instead of server paging. However, I really want to force query spec using server paging instead of client paging. My dataset is large, I think client paging is not good for performance.
Are there any way to force query spec using server paging?
Joined: 17-Aug-2003
If you only fetch entity id's then it shouldn't switch to client side paging. It only switches to client-side paging if there are fields in the projection that conflict with 'distinct' (image/text/varchar(max) etc.).
Could you give us some code and the query that resulted from it? Also make sure the compatibility level is set to 2012 as it otherwise will use a windowing function based paging. It's not slow, but if you want offset fetch next you need to use compatibility level 2012. By default it's 2005.
Joined: 23-May-2019
Thanks Otis,
I already set compatible mode in Startup.cs:
CommonDaoBase.SetSqlServerCompatibilityLevel(SqlServerCompatibilityLevel.SqlServer2012);
My query as below:
QueryFactory qf = new QueryFactory();
var query = qf.OnlineArticle
.Distinct()
.Include(OnlineArticleFields.ArticleId)
.From(relations)
.Where(filters)
.OrderBy(sorter)
.Offset((pageIndex - 1) * pageSize)
.Limit(pageSize);
When running, here is generated Sql Query:
SELECT NULL AS
[F1_0],
[CMSOnlineBLD].[dbo].[Article].[ApproverID] AS [F1_1],
NULL AS [F1_2],
NULL AS [F1_3],
NULL AS [F1_4],
[CMSOnlineBLD].[dbo].[Article].[ArticleID] AS [F1_5],
NULL AS [F1_6],
NULL AS [F1_7],
[CMSOnlineBLD].[dbo].[Article].[CheckOutForEditorID] AS [F1_8],
NULL AS [F1_9],
[CMSOnlineBLD].[dbo].[Article].[CreatorID] AS [F1_10],
NULL AS [F1_11],
[CMSOnlineBLD].[dbo].[Article].[FromDepartmentID] AS [F1_12],
[CMSOnlineBLD].[dbo].[Article].[FromLocalUserID] AS [F1_13],
[CMSOnlineBLD].[dbo].[Article].[FromUserID] AS [F1_14],
[CMSOnlineBLD].[dbo].[Article].[GenreID] AS [F1_15],
NULL AS [F1_16],
NULL AS [F1_17],
NULL AS [F1_18],
NULL AS [F1_19],
NULL AS [F1_20],
NULL AS [F1_21],
[CMSOnlineBLD].[dbo].[Article].[LastAgentID] AS [F1_22],
NULL AS [F1_23],
NULL AS [F1_24],
NULL AS [F1_25],
NULL AS [F1_26],
NULL AS [F1_27],
NULL AS [F1_28],
NULL AS [F1_29],
NULL AS [F1_30],
NULL AS [F1_31],
[CMSOnlineBLD].[dbo].[Article].[ScaleArticleID] AS [F1_32],
NULL AS [F1_33],
NULL AS [F1_34],
NULL AS [F1_35],
[CMSOnlineBLD].[dbo].[Article].[SiteID] AS [F1_36],
NULL AS [F1_37],
NULL AS [F1_38],
NULL AS [F1_39],
[CMSOnlineBLD].[dbo].[Article].[ToDepartmentID] AS [F1_40],
[CMSOnlineBLD].[dbo].[Article].[ToLocalUserID] AS [F1_41],
[CMSOnlineBLD].[dbo].[Article].[ToUserID] AS [F1_42],
NULL AS [F1_43],
[CMSOnlineBLD].[dbo].[OnlineArticle].[OnlineArticleID] AS [F3_44],
NULL AS [F3_45],
NULL AS [F3_46],
NULL AS [F3_47],
[CMSOnlineBLD].[dbo].[OnlineArticle].[CodeTinID] AS [F3_48],
NULL AS [F3_49],
NULL AS [F3_50],
NULL AS [F3_51],
NULL AS [F3_52],
NULL AS [F3_53],
NULL AS [F3_54],
[CMSOnlineBLD].[dbo].[OnlineArticle].[DefaultCategoryID] AS [F3_55],
[CMSOnlineBLD].[dbo].[OnlineArticle].[DisplayIconID] AS [F3_56],
[CMSOnlineBLD].[dbo].[OnlineArticle].[DisplayID] AS [F3_57],
NULL AS [F3_58],
NULL AS [F3_59],
NULL AS [F3_60],
NULL AS [F3_61],
NULL AS [F3_62],
NULL AS [F3_63],
NULL AS [F3_64],
NULL AS [F3_65],
NULL AS [F3_66],
NULL AS [F3_67],
NULL AS [F3_68],
NULL AS [F3_69],
NULL AS [F3_70],
NULL AS [F3_71],
NULL AS [F3_72],
NULL AS [F3_73],
[CMSOnlineBLD].[dbo].[OnlineArticle].[OnlinePressID] AS [F3_74],
NULL AS [F3_75],
NULL AS [F3_76],
NULL AS [F3_77],
[CMSOnlineBLD].[dbo].[OnlineArticle].[SiteNewsPortalID] AS [F3_78],
NULL AS [F3_79],
NULL AS [F3_80],
NULL AS [F3_81],
NULL AS [F3_82],
NULL AS [F3_83],
NULL AS [F3_84],
NULL AS [F3_85],
NULL AS [F3_86],
NULL AS [F3_87],
NULL AS [F3_88],
[CMSOnlineBLD].[dbo].[OldOnlineArticle].[OnlineArticleID] AS [F2_89],
NULL AS [F2_90],
NULL AS [F2_91],
NULL AS [F2_92],
NULL AS [F2_93]
FROM
((((
[CMSOnlineBLD].[dbo].[Article] WITH ( NOLOCK )
INNER JOIN [CMSOnlineBLD].[dbo].[OnlineArticle] WITH ( NOLOCK ) ON [CMSOnlineBLD].[dbo].[Article].[ArticleID] = [CMSOnlineBLD].[dbo].[OnlineArticle].[OnlineArticleID]
)
INNER JOIN [CMSOnlineBLD].[dbo].[CategoryOnlineArticle] WITH ( NOLOCK ) ON [CMSOnlineBLD].[dbo].[OnlineArticle].[OnlineArticleID] = [CMSOnlineBLD].[dbo].[CategoryOnlineArticle].[OnlineArticleID]
)
INNER JOIN [CMSOnlineBLD].[dbo].[Category] WITH ( NOLOCK ) ON [CMSOnlineBLD].[dbo].[Category].[CategoryID] = [CMSOnlineBLD].[dbo].[CategoryOnlineArticle].[CategoryID]
)
LEFT JOIN [CMSOnlineBLD].[dbo].[OldOnlineArticle] WITH ( NOLOCK ) ON [CMSOnlineBLD].[dbo].[OnlineArticle].[OnlineArticleID] = [CMSOnlineBLD].[dbo].[OldOnlineArticle].[OnlineArticleID]
)
WHERE
(
(
(
(
[CMSOnlineBLD].[dbo].[Article].[IsDeleted] = @p1
AND [CMSOnlineBLD].[dbo].[Article].[IsArchive] = @p2
AND [CMSOnlineBLD].[dbo].[Article].[NavigateType] = @p3
AND [CMSOnlineBLD].[dbo].[Article].[Status] = @p4
AND [CMSOnlineBLD].[dbo].[OnlineArticle].[IsOnlinePublished] = @p5
AND [CMSOnlineBLD].[dbo].[OnlineArticle].[OnlinePublishDateUtc] <= @p6
)
AND [CMSOnlineBLD].[dbo].[OnlineArticle].[SiteNewsPortalID] = @p7
AND [CMSOnlineBLD].[dbo].[Category].[IsDeleted] = @p8
AND [CMSOnlineBLD].[dbo].[Category].[IsEnable] = @p9
AND [CMSOnlineBLD].[dbo].[Category].[CategoryType] = @p10
AND [CMSOnlineBLD].[dbo].[Category].[CategoryID] = @p11
AND [CMSOnlineBLD].[dbo].[CategoryOnlineArticle].[IsDeleted] = @p12
))
AND ( [CMSOnlineBLD].[dbo].[OnlineArticle].[OnlineArticleID] IS NOT NULL ))
ORDER BY
[CMSOnlineBLD].[dbo].[OnlineArticle].[RefreshDateUtc] DESC,
[CMSOnlineBLD].[dbo].[OnlineArticle].[OnlinePublishDateUtc] DESC
Joined: 17-Aug-2003
I think it's the order by, as SQL Server requires the fields in an order by to be in the projection. If that's not the case (it looks like the fields you order on aren't in the projection) the database can't apply 'distinct' on the resultset (as it adds the fields sorted on as hidden columns to the resultset before applying distinct), and as distinct is required to avoid duplicates, it will flip to client side paging.
So if you add the two fields you sort on to the Include fields, they're part of the projection and therefore distinct can be applied. Could you try that please?
Joined: 23-May-2019
Otis wrote:
I think it's the order by, as SQL Server requires the fields in an order by to be in the projection. If that's not the case (it looks like the fields you order on aren't in the projection) the database can't apply 'distinct' on the resultset (as it adds the fields sorted on as hidden columns to the resultset before applying distinct), and as distinct is required to avoid duplicates, it will flip to client side paging.
So if you add the two fields you sort on to the Include fields, they're part of the projection and therefore distinct can be applied. Could you try that please?
Thanks @Otis
Adding sort fields to projection as your suggestion work perfect.