Hello Experts.

I'm trying to find the pros / cons of using CTEs (Common Table Expressions)
vs. Table Variables for 'Paging' through data.

Here is the scanario:
A.  Table Variable Example:
Select Query returns 100k posible matching rows, I store the matching key
column along with an identity column in a table varable (i.e. DECLARE @tmp
TABLE (rowid int identity, userid int) ), then I left join users to userid in
@tmp where @tmp.rowid between N and N.

B. CTE Example:
WITH CTEUsers AS (select row_number() as rowid,  userid ... ) SELECT * from
CTEUsers LEFT JOIN Users WHERE CTEUsers.rowid between N and N

What I noticed so far is:
CTE compares ROW_NUMBER() expression to the values of the BETWEEN expression
and returns only matching rows,

While Table Variables are first saving all userids in memory, then going
back for a table scan for a match for the BETWEEN expression.


I would like to know if anyone here has a definitive answer on why and in
what scanario can CTEs be faster then Table Variables and vice versa.

Thank you in advance for all your help.

- Eyal Zinder.

In general, unless you have an environment where every factor that affects
performance is controlled, comparisons are moot.  Also, there could be
several other ways one could achieve similar results. There could be even
different approaches for paging rows using a CTE or table variable as well.
So simply asking which construct/structure is "faster" doesn't mean much.

Your posted examples are not very clear either. Please post a sample table
structure, a few sample data as insert statements and legible code snippets
that demostrates your paging attempts.

".. unless you have an environment where every factor that affects
performance is controlled, comparisons are moot.. "

The database environment I work with currently exceeds 500,000 hits per
second.
I am very much concerned with the smallest difference in performance.

As for my examples, here is a detailed view:
/* TABLE VARIABLE EXAMPLE: */

SET NOCOUNT ON
DECLARE @tmp TABLE (rowid int identity, userid int)

INSERT INTO @tmp (userid)
SELECT userid
FROM users (nolock)
WHERE userStatus = @N1
ORDER BY userLastLoginDate

SELECT u.*
FROM @tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid

WHERE t.rowid BETWEEN @N2 and @N3
ORDER BY t.rowid


/* CTE EXAMPLE */

SET NOCOUNT ON

WITH tmp AS
(
SELECT userid, ROW_NUMBER() OVER (ORDER BY u.userLastLoginDate) AS rowid
FROM users (nolock)
WHERE userStatus = @N1
)
SELECT u.*
FROM tmp t
LEFT JOIN users u (nolock)
ON u.userid = t.userid

WHERE t.rowid BETWEEN @N2 and @N3
ORDER BY t.rowid


Again,  I am NOT looking for new / better ways to page through data or any
Cursor based paging.  I am looking for what pros / cons does CTE offer over
the above solution (variable table example).  I am interested to know how CTE
works and how it differs from the above example.

Thank you again for all your help and prompt reply.

Eyal Zinder.





Show quoteHide quote

There is no empirical evidence that suggests one approach is always better
than the other. In your specific situation, you should evaluate and compare
the query plans and execution times and decide which one performs better.

You can think of CTE as a temporary resultset/virtual table that lasts only
for the duration of the query. The primary benefits of a CTE include
generation of recursive queries, allowance of multiple references in the
same query and overall simplicity ( many complex queries can be simplified
with a well written CTE )

In your example, CTE offers nothing additional to the overall paging
functionality of the code. In other words, you can avoid the CTE altogether
and use a derived table to achieve similar results. In a small sample I
tested, the plans with a derived table and with a CTE were mostly similar
and provided similar performance.

In general, the "paging" methods are the SQL are an extension of a class of
queries called Quota queries in relational literature. Quota queries sort
the rows based on some explicit sequence of values in a column and then
identify the top/bottom subset (quota). You might want to research on that
if you'd like some background on such formulations.

There are several different approaches to this problem and Aaron's website
offers some of the best SQL 2000 methods that are frequently posted in this
newsgroup.

I'm not sure you need the self-join in your
CTE example. Try something like

WITH tmp AS
(
SELECT userid, ROW_NUMBER() OVER (ORDER BY userLastLoginDate) AS rowid,
ucol1, ucol2, ucol3
FROM users (nolock)
WHERE userStatus = @N1
)
SELECT userid,ucol1, ucol2, ucol3
FROM tmp
WHERE rowid BETWEEN @N2 and @N3
ORDER BY rowid

Here ucol1, ucol2 and  ucol3 are simply the columns from the
users table which you'll need to specify explicitly.

There are far better options than table variables.
http://www.aspfaq.com/2120

I have not played with CTEs in this case.




Show quoteHide quote

Aaron,

Thank you.  But the scale of this site does not allow for middle-tier paging.

Show quoteHide quote

Hi Eyal,

Make sure you keep it server side and only pass back the page the user wants
to the client, that will save significanly on resources especially the
network.

I, personally, don't tend to use CTE for paging because it does the whole
query first and the way I design schema I only need to join for those rows
on my page to get the 'meta' data - basically, I search on surrogate keys
where possible.

Personally I'd be inclided to use the ROWNUMBER() method and pump the output
into a table variable and join that table variable out to the base tables to
get my 'meta' as described above.

The reason is simple, it cuts down on IO.

Tony.

Show quoteHide quote

Tony,

Could you provide an example of using Meta Data for such a scanario?


Show quoteHide quote

The code below runs on my site http://sqlserverfaq.com and performs the
listing and searching of Articles.

You will see I use a temporary table with mostly id's in there and then at
the very end join only for those rows I'm throwing back to the client.

Tony.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER               proc [dbo].[ukug3_selGetKBArticles]
    @max_pages        int output,
    @required_page    int = 1,
    @rows_per_page    int = 2,

    @FileType    varchar(10) = '',
    @idEvents    int = 0,
    @SearchKeywords varchar(200) = '',
    @OpType char(1) = 'F',
    @member_group_id          int     = NULL,
    @is_member_group_restrict char(1) = 'N'

as
begin
    set nocount on

    create table #results (
        idrow        int    not null    identity,
        idKBArticle  int    not null,
        Rank         int    not null,
        Characterization varchar(500) not null default( '' )
        )

    declare @from_row    int
    declare @to_row      int

    set @from_row = ( (@required_page-1) * @rows_per_page ) + 1
    set @to_row   = @from_row + ( @rows_per_page - 1 )

    declare @sql nvarchar(4000)

    if @SearchKeywords > ''
    begin
        DECLARE @user_search_text varchar(300)
        SET @user_search_text = @SearchKeywords

        SET @OpType = 'C'    -- Done for performance
        SET @SearchKeywords = dbo.fn_search_cleanse( @SearchKeywords,
'AND' )    -- Gets rid of noise words and adds 'AND'

        IF @SearchKeywords = ''    -- Bad search, give it another chance and
use freetext instead.
        BEGIN
            SET @OpType = 'F'
            SET @SearchKeywords = @user_search_text
        END

        -- If restricting to a member group then add the additional search
clause for that group
        IF @is_member_group_restrict = 'Y'
        BEGIN
            SELECT @SearchKeywords = @SearchKeywords + ' ' + search_clause
            FROM member_group
            WHERE id = @member_group_id

        END

        SET @sql = 'SELECT TOP 50 *
                    FROM (
                        SELECT DISTINCT
                            kba.idKBArticle,
                            [Rank],
                            Characterization
                  FROM (  SELECT DISTINCT TOP 50 [FileName],
                                    [Rank],
                                                Characterization
                       FROM OPENQUERY( lsIndexServer,
                     ''SELECT FileName, Rank, Characterization
                      FROM TORVERSRVH3.SQLServerUG2..SCOPE() WHERE ' + CASE
WHEN @OpType='C' THEN 'CONTAINS' ELSE 'FREETEXT' END +
                                                    '( ''''' +
@SearchKeywords + ''''' )'' )
                       WHERE LEFT( Characterization, 12 ) <>
''vti_encoding''
                   ) AS qry
                    INNER JOIN KBArticle kba   ON kba.ArticleFileName =
qry.[FileName]'


--        IF @member_group_id > 0
--            SET @sql = @sql + ' WHERE EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=kba.idKBArticle AND
x.member_group_id=' + CAST( @member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'

        SET @sql = @sql + '
                        UNION ALL
                        SELECT
                            kba.idKBArticle,
                            [Rank] = 9999,
                            ''''
                  FROM KBArticle kba
                        WHERE kba.ArticleFileName = ''' + @SearchKeywords +
''' ) AS dt
                    ORDER BY Rank DESC'
    end
    else
    begin
        set @sql = N'
            select idKBArticle, 9999, ''''
            from kbarticle k
            where 1=1
              and is_external_url_link_broken = ''N''
    '

        --    Search clause
        if @FileType <> ''
            set @sql = @sql + N' and FileType=@FileType'
        else if @idEvents = 0
            set @sql = @sql + N' and FileType<>''wmv'''    -- WMV is dealt
with in its own control now so without this we would duplicate content (ok
on the search though!)

        if @idEvents > 0
            set @sql = @sql + N' and k.idEvents = @idEvents'
        else
            set @sql = @sql + N' and FileType <> ''ZIP'''


        IF @member_group_id > 0
            SET @sql = @sql + ' AND EXISTS ( SELECT * FROM
KBArticle_MemberGroup_Xref x WHERE x.idKBArticle=k.idKBArticle AND
x.member_group_id=' + CAST( @member_group_id AS Varchar(10) ) + ' AND
x.is_released=''Y'' )'

        set @sql = @sql + N'
        order by EntryDate desc'

    end
print @sql
    insert #results ( idKBArticle, Rank, Characterization )
        exec sp_executesql @sql,
                           N'@FileType varchar(10), @idEvents int',
                           @FileType, @idEvents

    set @max_pages = ( @@rowcount + ( @rows_per_page - 1 ) ) /
@rows_per_page

    select id,
           title,
           author_name,
           entry_date,
           article_summary = dt.article_summary + case when len(
dt.article_summary ) = 100 then '....' else '' end,
           FileType,
           ArticleFileName,
           CompressedSize,
           UncompressedSize,
           movie_length,
           external_url_link
    from (
        select t.idRow,
               id             = t.idKBArticle,
               title          = k.KBArticleTitle,
               author_name    = case when k.external_url_link = '' or
k.external_url_link = 'HTTP://' then isnull( r.fullname, '' ) else '' end,
               entry_date     = CONVERT( varchar(20), k.ModifiedDate, 106 ),
               article_summary= SUBSTRING( CASE WHEN t.Characterization = ''
THEN k.KBArticleAbstract ELSE t.Characterization END, 1, 100 ),
               FileType       = ISNULL( FileType, '' ),
               ArticleFileName= ISNULL( ArticleFileName, '' ),
               CompressedSize,
               UncompressedSize,
               movie_length,
               external_url_link
        from #results t
            inner join kbarticle k on k.idKBArticle = t.idKBArticle
            left outer join registrations r on r.idregistrations =
k.idregistrations
        where idrow between @from_row and @to_row ) as dt
    order by idrow

end




Show quoteHide quote

And the ASP.NET (VB.NET) to call the proc....


        Dim dbConn As New
SqlClient.SqlConnection(ConfigurationSettings.AppSettings("DBConnection"))
        dbConn.Open()

        Dim cmdSQL As SqlClient.SqlCommand
        Dim daSQL As New SqlDataAdapter
        Dim dsSQL As New DataSet

        '   Get Articles
        cmdSQL = New SqlCommand("ukug3_selGetKBArticles", dbConn)
        cmdSQL.CommandType = CommandType.StoredProcedure

        cmdSQL.Parameters.Add(New SqlParameter("@required_page",
Me.ResultsPageNumber))
        cmdSQL.Parameters.Add(New SqlParameter("@rows_per_page",
ConfigurationSettings.AppSettings("KBRowsPerPage")))

        If Me.Search_FileType <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@FileType", Me.Search_FileType))
        If Me.Search_EventId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@idEvents", Me.Search_EventId))
        If Me.Search_Keywords <> "" Then cmdSQL.Parameters.Add(New
SqlParameter("@SearchKeywords", Me.Search_Keywords))
        If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@member_group_id", Me.MemberGroupId))
        If Me.MemberGroupId > 0 Then cmdSQL.Parameters.Add(New
SqlParameter("@is_member_group_restrict", IIf(Me.isRestrictToMemberGroup,
"Y", "N")))

        Dim sqlParm As SqlParameter
        sqlParm = cmdSQL.Parameters.Add(New SqlParameter("@max_pages",
CInt(0)))
        sqlParm.Direction = ParameterDirection.Output

        daSQL.SelectCommand = cmdSQL
        Dim iPages As Integer

        Try
            daSQL.Fill(dsSQL)

            datlArt.DataSource = dsSQL
            datlArt.DataBind()

            iPages = cmdSQL.Parameters("@max_pages").Value

        Catch ex As Exception
            iPages = 0

        End Try

        trNoArticles.Visible = (datlArt.Items.Count = 0)

        If iPages = 0 Then
            tdPage.Visible = False

        Else
            lbtnPageNext.Visible = (Me.ResultsPageNumber < iPages)
            lbtnPagePrev.Visible = Me.ResultsPageNumber > 1

            lblPageCur.Text = Me.ResultsPageNumber.ToString
            lblPageLast.Text = iPages.ToString

            tdPage.Visible = True

        End If

        dbConn.Close()
        dbConn.Dispose()



Show quoteHide quote

can't insert into ...because of nulls problme!
Return random number from trigger
Help on "splitting up" data in a field
Can You SQL Inject this code?
Unicode encoding problem ?