 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008CTEs - sometimes much slower, sometimes not
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@a70g2000hsh.googlegroups.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 [quoted text clipped - 13 lines] > 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: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? [quoted text clipped - 20 lines] > > 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 On Jul 16, 3:40 pm, "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 [quoted text clipped - 8 lines] > > 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. 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)??  SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net 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? [quoted text clipped - 21 lines] > > 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. > 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 [quoted text clipped - 57 lines] > 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 help performance.....they are all functionally the same thing. > > 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 [quoted text clipped - 63 lines] > I could use a view or derived table, but don't see how that is going > to help 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'). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.