If I look at the query plan of 2 simple queries that are essentially
the same, but one uses a CTE, the plans are the same and so is the
logical reads to execute it. Here is an example:

WITH x as (SELECT titleid, authorid FROM books)
SELECT * from x where authorid=99

SELECT titleid, authorid FROM books where authorid=99

These have the same plan and I/O so obviously it isn't evaluating the
CTE and then applying the where clause

Sometimes, it seems like the entire CTE is executed before applying
any WHERE clause data in the actual select. Does anyone know why this
is and what triggers it? I have a very complicated query using 3
ctes, and based on the performance, I can tell it is evaluating 1 of
the CTEs before the WHERE clause is applied to it.

How are the CTEs joined? And if the where clause *could* be applied within
the CTE, why isn't it applied there?



On 7/16/08 3:29 PM, in article
85f33c3e-13bd-4698-9a16-a8ce5d56d680...oglegroups.com, "Mike C"
<michaeljc70@hotmail.com> wrote:

> If I look at the query plan of 2 simple queries that are essentially
> the same, but one uses a CTE, the plans are the same and so is the
> logical reads to execute it. Here is an example:
>
> WITH x as (SELECT titleid, authorid FROM books)
> SELECT * from x where authorid=99
>
> SELECT titleid, authorid FROM books where authorid=99
>
> These have the same plan and I/O so obviously it isn't evaluating the
> CTE and then applying the where clause
>
> Sometimes, it seems like the entire CTE is executed before applying
> any WHERE clause data in the actual select. Does anyone know why this
> is and what triggers it? I have a very complicated query using 3
> ctes, and based on the performance, I can tell it is evaluating 1 of
> the CTEs before the WHERE clause is applied to it.
>
>
>

On Jul 16, 3:02pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> How are the CTEs joined? And if the where clause *could* be applied within
> the CTE, why isn't it applied there?
>
> On 7/16/08 3:29 PM, in article
> 85f33c3e-13bd-4698-9a16-a8ce5d56d...@a70g2000hsh.googlegroups.com, "Mike C"
>
> <michaelj...@hotmail.com> wrote:
> > If I look at the query plan of 2 simple queries that are essentially
> > the same, but one uses a CTE, the plans are the same and so is the
> > logical reads to execute it. Here is an example:
>
> > WITH x as (SELECT titleid, authorid FROM books)
> > SELECT * from x where authorid=99
>
> > SELECT titleid, authorid FROM books where authorid=99
>
> > These have the same plan and I/O so obviously it isn't evaluating the
> > CTE and then applying the where clause
>
> > Sometimes, it seems like the entire CTE is executed before applying
> > any WHERE clause data in the actual select. Does anyone know why this
> > is and what triggers it? I have a very complicated query using 3
> > ctes, and based on the performance, I can tell it is evaluating 1 of
> > the CTEs before the WHERE clause is applied to it.

The where clause can't be applied there because it is dynamic. If I
take a specific instance though and move it in the CTE, it is much
faster (20 sec vs 230 sec).

The query is very complicated- 3 CTEs and a total of around 40 tables
(some repeated in diff ctes). Too make a very long story short, the
only reason I am using this CTE in issue is that the user can select
criteria dynamically. This consists of 14 different fields. I cannot
apply those directly to the query because you cannot use aliases in
the where clause. I am therefore wrapping it in a CTE and then
applying this dynamic where clause.

Here's an example: I get in something that could be like "WHERE foo
BETWEEN '01/01/2007' AND '02/01/2007'". My select looks like this
(obviously simplified):

SELECT CASE WHEN xxxxx THEN blah WHEN yyyyy THEN fdfdfd WHEN ....
END as foo
FROM tttt

I am wrapping this select in the CTE so I can just apply the where
clause as is.

> criteria dynamically. This consists of 14 different fields. I cannot
> apply those directly to the query because you cannot use aliases in
> the where clause.

But you can use them in a subquery / derived table. This is a common
workaround to not having to repeat calculations, e.g.

SELECT fullname
FROM (SELECT fullname = FirstName + ' ' + LastName FROM Customers) x
GROUP BY fullname;

A

Since you are doing dynamic sql, are you SURE you can't do the entire
excutable statement in dynamic sql without CTEs (or perhaps with them but
collapse the wheres/sargs)??

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Mike C" <michaeljc70@hotmail.com> wrote in message
news:e662a0dd-efc1-41d9-bc90-c21ba724d5d8@34g2000hsh.googlegroups.com...
On Jul 16, 3:02 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> How are the CTEs joined? And if the where clause *could* be applied within
> the CTE, why isn't it applied there?
>
> On 7/16/08 3:29 PM, in article
> 85f33c3e-13bd-4698-9a16-a8ce5d56d...@a70g2000hsh.googlegroups.com, "Mike
> C"
>
> <michaelj...@hotmail.com> wrote:
> > If I look at the query plan of 2 simple queries that are essentially
> > the same, but one uses a CTE, the plans are the same and so is the
> > logical reads to execute it. Here is an example:
>
> > WITH x as (SELECT titleid, authorid FROM books)
> > SELECT * from x where authorid=99
>
> > SELECT titleid, authorid FROM books where authorid=99
>
> > These have the same plan and I/O so obviously it isn't evaluating the
> > CTE and then applying the where clause
>
> > Sometimes, it seems like the entire CTE is executed before applying
> > any WHERE clause data in the actual select. Does anyone know why this
> > is and what triggers it? I have a very complicated query using 3
> > ctes, and based on the performance, I can tell it is evaluating 1 of
> > the CTEs before the WHERE clause is applied to it.

The where clause can't be applied there because it is dynamic. If I
take a specific instance though and move it in the CTE, it is much
faster (20 sec vs 230 sec).

The query is very complicated- 3 CTEs and a total of around 40 tables
(some repeated in diff ctes). Too make a very long story short, the
only reason I am using this CTE in issue is that the user can select
criteria dynamically. This consists of 14 different fields. I cannot
apply those directly to the query because you cannot use aliases in
the where clause. I am therefore wrapping it in a CTE and then
applying this dynamic where clause.

Here's an example: I get in something that could be like "WHERE foo
BETWEEN '01/01/2007' AND '02/01/2007'". My select looks like this
(obviously simplified):

SELECT CASE WHEN xxxxx THEN blah WHEN yyyyy THEN fdfdfd WHEN ....
END as foo
FROM tttt

I am wrapping this select in the CTE so I can just apply the where
clause as is.

On Jul 16, 3:40pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> > criteria dynamically. This consists of 14 different fields. I cannot
> > apply those directly to the query because you cannot use aliases in
> > the where clause.
>
> But you can use them in a subquery / derived table. This is a common
> workaround to not having to repeat calculations, e.g.
>
> SELECT fullname
> FROM (SELECT fullname = FirstName + ' ' + LastName FROM Customers) x
> GROUP BY fullname;
>
> A

Well, isn't a derived table and CTE essentially the same thing? To me
it is.

Keep in mind I have at least 80 fields, about 12 of which are CASE
statements.

On Jul 16, 3:44pm, "TheSQLGuru" <kgbo...@earthlink.net> wrote:
> Since you are doing dynamic sql, are you SURE you can't do the entire
> excutable statement in dynamic sql without CTEs (or perhaps with them but
> collapse the wheres/sargs)??
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Mike C" <michaelj...@hotmail.com> wrote in message
>
> news:e662a0dd-efc1-41d9-bc90-c21ba724d5d8@34g2000hsh.googlegroups.com...
> On Jul 16, 3:02 pm, "Aaron Bertrand [SQL Server MVP]"
>
>
>
> <ten....@dnartreb.noraa> wrote:
> > How are the CTEs joined? And if the where clause *could* be applied within
> > the CTE, why isn't it applied there?
>
> > On 7/16/08 3:29 PM, in article
> > 85f33c3e-13bd-4698-9a16-a8ce5d56d...@a70g2000hsh.googlegroups.com, "Mike
> > C"
>
> > <michaelj...@hotmail.com> wrote:
> > > If I look at the query plan of 2 simple queries that are essentially
> > > the same, but one uses a CTE, the plans are the same and so is the
> > > logical reads to execute it. Here is an example:
>
> > > WITH x as (SELECT titleid, authorid FROM books)
> > > SELECT * from x where authorid=99
>
> > > SELECT titleid, authorid FROM books where authorid=99
>
> > > These have the same plan and I/O so obviously it isn't evaluating the
> > > CTE and then applying the where clause
>
> > > Sometimes, it seems like the entire CTE is executed before applying
> > > any WHERE clause data in the actual select. Does anyone know why this
> > > is and what triggers it? I have a very complicated query using 3
> > > ctes, and based on the performance, I can tell it is evaluating 1 of
> > > the CTEs before the WHERE clause is applied to it.
>
> The where clause can't be applied there because it is dynamic. If I
> take a specific instance though and move it in the CTE, it is much
> faster (20 sec vs 230 sec).
>
> The query is very complicated- 3 CTEs and a total of around 40 tables
> (some repeated in diff ctes). Too make a very long story short, the
> only reason I am using this CTE in issue is that the user can select
> criteria dynamically. This consists of 14 different fields. I cannot
> apply those directly to the query because you cannot use aliases in
> the where clause. I am therefore wrapping it in a CTE and then
> applying this dynamic where clause.
>
> Here's an example: I get in something that could be like "WHERE foo
> BETWEEN '01/01/2007' AND '02/01/2007'". My select looks like this
> (obviously simplified):
>
> SELECT CASE WHEN xxxxx THEN blah WHEN yyyyy THEN fdfdfd WHEN ....
> END as foo
> FROM tttt
>
> I am wrapping this select in the CTE so I can just apply the where
> clause as is.

I could use a view or derived table, but don't see how that is going
to performance.....they are all functionally the same thing.

On Jul 16, 4:12pm, Mike C <michaelj...@hotmail.com> wrote:
> On Jul 16, 3:44pm, "TheSQLGuru" <kgbo...@earthlink.net> wrote:
>
>
>
> > Since you are doing dynamic sql, are you SURE you can't do the entire
> > excutable statement in dynamic sql without CTEs (or perhaps with them but
> > collapse the wheres/sargs)??
>
> > --
> > Kevin G. Boles
> > Indicium Resources, Inc.
> > SQL Server MVP
> > kgboles a earthlink dt net
>
> > "Mike C" <michaelj...@hotmail.com> wrote in message
>
> >news:e662a0dd-efc1-41d9-bc90-c21ba724d5d8@34g2000hsh.googlegroups.com...
> > On Jul 16, 3:02 pm, "Aaron Bertrand [SQL Server MVP]"
>
> > <ten....@dnartreb.noraa> wrote:
> > > How are the CTEs joined? And if the where clause *could* be applied within
> > > the CTE, why isn't it applied there?
>
> > > On 7/16/08 3:29 PM, in article
> > > 85f33c3e-13bd-4698-9a16-a8ce5d56d...@a70g2000hsh.googlegroups.com, "Mike
> > > C"
>
> > > <michaelj...@hotmail.com> wrote:
> > > > If I look at the query plan of 2 simple queries that are essentially
> > > > the same, but one uses a CTE, the plans are the same and so is the
> > > > logical reads to execute it. Here is an example:
>
> > > > WITH x as (SELECT titleid, authorid FROM books)
> > > > SELECT * from x where authorid=99
>
> > > > SELECT titleid, authorid FROM books where authorid=99
>
> > > > These have the same plan and I/O so obviously it isn't evaluating the
> > > > CTE and then applying the where clause
>
> > > > Sometimes, it seems like the entire CTE is executed before applying
> > > > any WHERE clause data in the actual select. Does anyone know why this
> > > > is and what triggers it? I have a very complicated query using 3
> > > > ctes, and based on the performance, I can tell it is evaluating 1 of
> > > > the CTEs before the WHERE clause is applied to it.
>
> > The where clause can't be applied there because it is dynamic. If I
> > take a specific instance though and move it in the CTE, it is much
> > faster (20 sec vs 230 sec).
>
> > The query is very complicated- 3 CTEs and a total of around 40 tables
> > (some repeated in diff ctes). Too make a very long story short, the
> > only reason I am using this CTE in issue is that the user can select
> > criteria dynamically. This consists of 14 different fields. I cannot
> > apply those directly to the query because you cannot use aliases in
> > the where clause. I am therefore wrapping it in a CTE and then
> > applying this dynamic where clause.
>
> > Here's an example: I get in something that could be like "WHERE foo
> > BETWEEN '01/01/2007' AND '02/01/2007'". My select looks like this
> > (obviously simplified):
>
> > SELECT CASE WHEN xxxxx THEN blah WHEN yyyyy THEN fdfdfd WHEN ....
> > END as foo
> > FROM tttt
>
> > I am wrapping this select in the CTE so I can just apply the where
> > clause as is.
>
> I could use a view or derived table, but don't see how that is going
> to performance.....they are all functionally the same thing.

Problem solved---not exactly elegant, but it works. I do a replace on
the WHERE clause passed in and replace it with whatever is in the
select list. Basically, something like REPLACE('WHERE
authorid=11','authorid',' CASE WHEN xxxx THEN yyyy WHEN fdsfd then
sss').