SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Debugging stacked CTEs efficently


Debugging stacked CTEs efficently

Author
Message
Banana-823045
Banana-823045
SSC Veteran
SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)

Group: General Forum Members
Points: 280 Visits: 556
I'm curious if anyone else has figured out a good system for performing analysis on a query that may have multiple CTEs stacked. While some problems can be resolved by considering the T-SQL and making logical evaluations, sometime it's nice to be able to see the intermediate data so you can see what is really going on under the hood.

When I have such query, I have the following steps:

1) add a /* and */ delimiter on the bottom part of the query to comment out all subsequent steps
2) comment out the line after the closing parenthesis of last CTE to make the next CTE a outer statement
3) run the query, analyze the data
4) uncomment the comment from #2, move the /* to the next CTE and comment out the next line after the new closing parenthesis. Repeat until we reach the final statement.

This works but I wondered if there was a more effective and a bit less time-consuming & error-prone approach that would permit for data analysis of individual steps.

Thanks in advance!
Vedran Kesegic
Vedran Kesegic
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2012 Visits: 1266
Intermediate results sometimes can be calculated separately and stored to temp tables. Post your cte if you want more specific answer. There is no debugging tool that i am aware of, that will show you how sql internally executes the command (cte is executed as a SINGLE command) and show you the intermediate data. Execution plan is closest to it.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40496 Visits: 14413
Banana-823045 (3/20/2013)
I'm curious if anyone else has figured out a good system for performing analysis on a query that may have multiple CTEs stacked. While some problems can be resolved by considering the T-SQL and making logical evaluations, sometime it's nice to be able to see the intermediate data so you can see what is really going on under the hood.

That would require that CTEs be a fundamentally different thing than what they are. The optimizer is handed a CTE by you, the query writer, but that is just a description of the data you want in the form of a question, it is not an actual set of native instructions the database engine can use to retrieve the data. The optimizer will take your query and change it into a native set of instructions. We can get a view into these instructions by looking at the execution plan. In SSMS highlight your query and press Ctrl+L to see one.

Here is an example. Consider this simple query to show all columns in a database:

SELECT  s.name AS [schema_name],
t.name AS table_name,
c.name AS column_name
FROM sys.schemas s
INNER JOIN sys.tables t ON s.[schema_id] = t.[schema_id]
INNER JOIN sys.columns c ON t.[object_id] = c.[object_id]
ORDER BY s.name,
t.name,
c.column_id;



Now consider a logically equivalent query that uses a series of three cascaded CTEs:

WITH    schema_cte
AS (
SELECT [schema_id],
name AS [schema_name]
FROM sys.schemas
),
table_cte
AS (
SELECT s.[schema_id],
s.schema_name,
t.[object_id],
t.name AS table_name
FROM schema_cte s
INNER JOIN sys.tables t ON s.[schema_id] = t.[schema_id]
),
column_cte
AS (
SELECT t.schema_name,
t.table_name,
c.name AS column_name,
c.column_id
FROM table_cte t
INNER JOIN sys.columns c ON t.[object_id] = c.[object_id]
)
SELECT [schema_name],
table_name,
column_name
FROM column_cte
ORDER BY [schema_name],
table_name,
column_id;



The queries look very different, however they will deliver the same results. Further, when we look at the execution plans of each we see that the optimizer actually decided to execute them in the exact same way.



So, as you can see, it's not as if the database engine processes your query in steps where it materializes the first CTE, then takes that intermediate result and uses it to materialize the second CTE, and so on. It reworks your query, sometimes many thousands of different ways, looking for an efficient way to execute your query.

Regarding debugging, what you have showed in terms of steps is how you would debug a query with cascading CTEs. If you want a debugging experience where you can see the intermediate results consider using #temp tables.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search