Table variable

[SQL2005 latest sp]

If you have the belief that CTEs are always faster than Table variables, its
wrong.

Below are two queries, the first using a table variable and the second uses
a CTE. The first runs in a flash while the second one takes forever. The
execution plans suggest, it has to do with the CTE using nested loops
because it evaluates everything first which is very costly in this scenario.

However, this is not always the case. There are queries when CTEs are
actually faster. The bottomline is to test a query which uses a CTE or a
table variable and check the execution plans rather than to blindly believe
that CTEs are faster.

declare @t table (MemNo varchar(100))
insert into @t
select Common.SqlClr.fnEncryptPICString(MemNo) as MemNo from TrueMem
(nolock) where can = 6
select Common.SqlClr.fnDecryptPICString(TrueMemID)
from @t t
join Mem m (nolock)
on t.MemNo = m.MemNo


;with cte as
(
select Common.SqlClr.fnEncryptPICString(MemNo) as MemNo from TrueMem
(nolock) where can = 6
)
select Common.SqlClr.fnDecryptPICString(m.TrueMemID) as TrueMemID
from cte
join Mem m (nolock)
on cte.MemNo = m.MemNo


This post was a FYI, unless Iam missing something in my queries. If anyone
is interested to dig deep, I can give the DDL.


Cheers,
AbbA

> If you have the belief that CTEs are always faster than Table variables,
> its wrong.

Agreed, but I've personally never had such a belief. CTEs and table
variables are completely different beasts. Functions are especially
difficult to optimize and can result in cursor-like performance.

> This post was a FYI, unless Iam missing something in my queries. If anyone
> is interested to dig deep, I can give the DDL.

I think DDL for the Mem and TrueMem tables might be enough for readers to
reproduce the issue. I can't say for sure but I suspect T-SQL function
stubs could be substituted for the CLR functions to simplify the repro.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


"Abba" wrote in message

> [SQL2005 latest sp]
>
> If you have the belief that CTEs are always faster than Table variables,
> its wrong.
>
> Below are two queries, the first using a table variable and the second
> uses a CTE. The first runs in a flash while the second one takes forever.
> The execution plans suggest, it has to do with the CTE using nested loops
> because it evaluates everything first which is very costly in this
> scenario.
>
> However, this is not always the case. There are queries when CTEs are
> actually faster. The bottomline is to test a query which uses a CTE or a
> table variable and check the execution plans rather than to blindly
> believe that CTEs are faster.
>
> declare @t table (MemNo varchar(100))
> insert into @t
> select Common.SqlClr.fnEncryptPICString(MemNo) as MemNo from TrueMem
> (nolock) where can = 6
> select Common.SqlClr.fnDecryptPICString(TrueMemID)
> from @t t
> join Mem m (nolock)
> on t.MemNo = m.MemNo
>
>
> ;with cte as
> (
> select Common.SqlClr.fnEncryptPICString(MemNo) as MemNo from TrueMem
> (nolock) where can = 6
> )
> select Common.SqlClr.fnDecryptPICString(m.TrueMemID) as TrueMemID
> from cte
> join Mem m (nolock)
> on cte.MemNo = m.MemNo
>
>
> This post was a FYI, unless Iam missing something in my queries. If anyone
> is interested to dig deep, I can give the DDL.
>
>
> Cheers,
> AbbA
>