CTE vs. Table Variable (Paging)

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 i
n
@.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.>> I would like to know if anyone here has a definitive answer on why and in
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.
Anith|||There are far better options than table variables.
http://www.aspfaq.com/2120
I have not played with CTEs in this case.
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> 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.
>
>|||".. 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 CT
E
works and how it differs from the above example.
Thank you again for all your help and prompt reply.
Eyal Zinder.
"Anith Sen" wrote:

> 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 snippet
s
> that demostrates your paging attempts.
> --
> Anith
>
>|||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.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
> 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.
>
>|||>> I am looking for what pros / cons does CTE offer over the above solution
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.
Anith|||Aaron,
Thank you. But the scale of this site does not allow for middle-tier paging
.
"Aaron Bertrand [SQL Server MVP]" wrote:

> There are far better options than table variables.
> http://www.aspfaq.com/2120
> I have not played with CTEs in this case.
>
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||Tony,
Could you provide an example of using Meta Data for such a scanario?
"Tony Rogerson" wrote:

> Hi Eyal,
> Make sure you keep it server side and only pass back the page the user wan
ts
> 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 outp
ut
> 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.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Eyal" <ezinder@.yahoo.com> wrote in message
> news:0183F6D1-F2B5-411F-954C-65FE3F84AE49@.microsoft.com...
>
>|||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
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>|||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()
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Eyal" <ezinder@.yahoo.com> wrote in message
news:3960701C-287D-450B-B610-3C36919B9ABF@.microsoft.com...
> Tony,
> Could you provide an example of using Meta Data for such a scanario?
>
> "Tony Rogerson" wrote:
>