Are there any way to force QuerySpec using OFFSET ... ROWS FETCH NEXT?

Posts   
 
    
sasaki
User
Posts: 6
Joined: 23-May-2019
# Posted on: 23-Apr-2020 07:29:39   

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?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Apr-2020 09:23:53   

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.

https://www.llblgen.com/Documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_applicationconfiguration.htm#dqe-compatibility-mode-sql-server-only

Frans Bouma | Lead developer LLBLGen Pro
sasaki
User
Posts: 6
Joined: 23-May-2019
# Posted on: 23-Apr-2020 12:40:19   

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

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Apr-2020 17:00:40   

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?

Frans Bouma | Lead developer LLBLGen Pro
sasaki
User
Posts: 6
Joined: 23-May-2019
# Posted on: 23-Apr-2020 19:46:02   

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.