From: tfs@dslextreme.com
I have found lately, that CTE's can be a problem in many cases.
I have read the it is
"Erland Sommarskog" wrote in message
news:Xns9E166642948DDYazorman@127.0.0.1...
> tshad (tfs@dslextreme.com) writes:
>> It's just curious as to what it is doing since all the time is being
>> done by the SELECT after the CTE definition.
>
> Yes, that's the way it is. The CTE is just a definition. The CTE does
> nothing on it own.
>
>> It is almost like it is doing the Select in the definition for each row
>> in
>> the SELECT statement.
>
> Yes, that is what is going on.
>
>> I would have assumed that the Select with the Temp table would work the
>> same as the CTE but obviously that is not the case.
>
> There are other products that are smart enough to do this, but SQL Server
> is
> not there yet.
>
I agree there.
I started using CTEs after reading an article by John Papa where is states:
"Many projects that developers work on involve writing complex SQL
statements that deviate from basic SELECT/FROM/WHERE types of statements.
One such scenario involves writing Transact-SQL (T-SQL) queries that use
derived tables (also known as inline views) inside a FROM clause. This
common practice allows a developer to grab a rowset and immediately join
that rowset to other tables, views, and user-defined functions in a SELECT
statement. Another option is to use a view instead of a derived table. Both
of these options have their advantages and disadvantages.
When working with SQL ServerT 2005, I prefer a third option of using Common
Table Expressions (CTEs). CTEs can help improve the readability (and thus
the maintainability) of the code without compromising performance."
I am finding that this is not the case in many (most cases where I and
others on my team have been using them).
I agree with him that it is useful with recursion. I have taken changed
recursive code that took 5 minutes down to 10 seconds just by changing the
code to a CTE.
But in other cases, I am finding that changing the CTE to a temp table will
fix or speed up the query.
I also am finding that where the CTE will work fine (for months), it
sometimes quits working. After much investigation, I end up changing the
CTE to temp tables to solve the issue.
For example, we had a CTE that worked fine taking about 2 or 3 minutes to
run that was really 2 CTEs joined together. This was working for about 2
months. Then last week it started to take 30 minutes plus ending up with an
error (something lile illegal syntax near '-'). Then we would run it again
and it would take 8 minutes to run but no error. Later, it wouldn't return
at all and after about an hour and half we would just kill it. We did see
that the tempDB had increased to 120GB (not sure if this query caused it).
It looked something like:
WITH myCTE
(
SELECT ...
), myCTE2
(
SELECT ...
)
SELECT .. (joining the 2 CTEs)
We thought maybe there was something with the server but after I changed it
to: 2 SELECT/INTO statments with 2 temp tables, it worked fine and took
about a minute to run.
We also converted a couple regular CTEs that were taking about 50 seconds to
using a temp table (SELECT/INTO):
WITH myCTE
(
SELECT...
)
SELECT ...
to
SELECT/INTO
and it took about 20 seconds.
Not a lot of difference but not quite what John was saying.
My concern is taking the time to write the CTE when it may just be better to
write it the old ways.
Thanks,
Tom
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
