LLBGen 5.3 & MS SQL Server 2012: paging query but no OFFSET xxx FETCH in generated SQL

Question

collection.GetMulti(filter, 0, sorter, relations, path, 1, 10);

Generated SQL:

    SELECT
        ...
    FROM
        ((((
                        [NewsDB].[dbo].[Article]
                        INNER JOIN [NewsDB].[dbo].[OnlineArticle] ON [NewsDB].[dbo].[Article].[ArticleID] = [NewsDB].[dbo].[OnlineArticle].[OnlineArticleID] 
                        )
                    INNER JOIN [NewsDB].[dbo].[CategoryOnlineArticle] ON [NewsDB].[dbo].[OnlineArticle].[OnlineArticleID] = [NewsDB].[dbo].[CategoryOnlineArticle].[OnlineArticleID] 
                    )
                INNER JOIN [NewsDB].[dbo].[Category] ON [NewsDB].[dbo].[Category].[CategoryID] = [NewsDB].[dbo].[CategoryOnlineArticle].[CategoryID] 
                )
            LEFT JOIN [NewsDB].[dbo].[OldOnlineArticle] ON [NewsDB].[dbo].[OnlineArticle].[OnlineArticleID] = [NewsDB].[dbo].[OldOnlineArticle].[OnlineArticleID] 
        ) 
    WHERE
        (
            (
                (
                    [NewsDB].[dbo].[Article].[IsDeleted] = @p1 
                    AND [NewsDB].[dbo].[Article].[IsArchive] = @p2 
                    AND [NewsDB].[dbo].[Article].[NavigateType] = @p3 
                    AND [NewsDB].[dbo].[Article].[Status] = @p4 
                    AND [NewsDB].[dbo].[OnlineArticle].[IsOnlinePublished] = @p5 
                    AND [NewsDB].[dbo].[OnlineArticle].[OnlinePublishDateUtc] <= @p6 
                ) 
                AND [NewsDB].[dbo].[OnlineArticle].[SiteNewsPortalID] = @p7 
                AND [NewsDB].[dbo].[Category].[IsDeleted] = @p8 
                AND [NewsDB].[dbo].[Category].[IsEnable] = @p9 
                AND [NewsDB].[dbo].[Category].[CategoryType] = @p10 
                AND [NewsDB].[dbo].[Category].[CategoryID] = @p11 
            ) 
        AND ( [NewsDB].[dbo].[OnlineArticle].[OnlineArticleID] IS NOT NULL )) 
    ORDER BY
        [NewsDB].[dbo].[OnlineArticle].[RefreshDateUtc] DESC,
        [NewsDB].[dbo].[OnlineArticle].[OnlinePublishDateUtc] DESC

I don't see OFFSET xxx FETCH xxx in generated SQL.

I also set compatible level to SqlServerCompatibilityLevel.SqlServer2005.

How to make OFFSET xxx FETCH available in generated SQL? Please help.

score 0 · Answer 1 · answered May 27 '19 at 15:57

OFFSET/FETCH is used when compatibility level is set to SqlServer2012. See more info at The SQL differences per compatibility mode.

Where are you looking the Generated SQL? Make sure you are viewing the final query to execute, as you may be seeing the query just before the paging in built.

And finally, there are some situations where is not possible to make the paging at server side level, so it's performed in client's side with a data reader. Quoting the documentation:

Paging in general happens on the server, however the LLBLGen Pro runtime framework switches to client-side paging at the datareader level if the paging query can't be generated without causing errors on the server. This happens if a DISTINCT violating construct is present in the SQL query, e.g. a type that's not to be used with DISTINCT or an order by on a field that's not in the projection. The datareader-level paging is efficient (it discards any rows before the page to read and stops reading data once the page to read is fully read) however can be slower than a server-side paging query.

To determine whether the framework switched to client-side paging / distinct filtering / row limiting, it will append to the SQL query output to the ORMQueryExecution tracer a string which signals this: "Requires client side paging", "Requires client side distinct filtering" and "Requires client side limiting", if the ORMQueryExecution tracer is set to level 4 (verbose)

Hope that helps. BTW, you can use the LLBLGen Pro Forums

LLBGen 5.3 & MS SQL Server 2012: paging query but no OFFSET xxx FETCH in generated SQL

1 Answers1