Execution plan of a CTE

Question

Post reply

Execution plan of a CTE

VastSQL

SSC-Dedicated

Points: 36097
More actions
July 4, 2014 at 7:08 am

#296275

Hi Experts,
We have got a query for fine tunning and it is using lot of CTE ,how can i check the execution plan of that?
CREATE VIEW Mercy
AS
with ADR
as
(
SELECT urpx.RoleID ,
urx.UserID
FROM [DBA].dbo.URPX WITH ( NOLOCK )
INNER JOIN [DBA].dbo.URX WITH ( NOLOCK ) ON urpx.RoleID = urx.RoleID
WHERE PermissionID = '1'
),
SDR
as
(
-- Collect the roles that a configured with Sales Team Create permission
-- This will include Sales Director , Suite Admin,
SELECT urpx.RoleID
FROM [DBA].dbo.URPX WITH ( NOLOCK )
INNER JOIN [DBA].dbo.URX WITH ( NOLOCK ) ON urpx.RoleID = urx.RoleID
LEFT OUTER JOIN ADR ON ADR.UserID = urx.UserID
WHERE ADR.RoleID IS NULL
AND PermissionID='2'
)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Write a typical select from the view, run it with Include Actual Execution Plan enabled.

There's nothing special about a CTE with it comes to exec plan. It's just a named subquery.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Be careful with all of those WITH(NOLOCK) though. Unless you like dirty data.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

What you've shown of your view definition equates to this:

SELECT

urpx.RoleID

FROM [DBA].dbo.URPX

INNER JOIN [DBA].dbo.URX

ON urpx.RoleID = urx.RoleID

LEFT OUTER JOIN (

SELECT

urx.RoleID,

urx.UserID

FROM [DBA].dbo.URX

INNER JOIN [DBA].dbo.URPX

ON urpx.RoleID = urx.RoleID

WHERE PermissionID = '1'

) ADR ON ADR.UserID = urx.UserID

WHERE ADR.RoleID IS NULL

AND PermissionID = '2'

Since both tables are read twice, this can almost certainly be improved. Which table contains column [PermissionID]?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

VastSQL

SSC-Dedicated

Points: 36097

July 4, 2014 at 7:50 am

#1727084

Thanks Gail & Koen

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

ChrisM@Work (7/4/2014)
What you've shown of your view definition equates to this:
SELECT
urpx.RoleID
FROM [DBA].dbo.URPX
INNER JOIN [DBA].dbo.URX
ON urpx.RoleID = urx.RoleID
LEFT OUTER JOIN (
SELECT
urx.RoleID,
urx.UserID
FROM [DBA].dbo.URX
INNER JOIN [DBA].dbo.URPX
ON urpx.RoleID = urx.RoleID
WHERE PermissionID = '1'
) ADR ON ADR.UserID = urx.UserID
WHERE ADR.RoleID IS NULL
AND PermissionID = '2'
Since both tables are read twice, this can almost certainly be improved. Which table contains column [PermissionID]?

Thanks a lot Chris. The column permission is in URPX table. The view actually calling 6 CTE all are select, i am a real noob in this and trying to learn.. Thanks a lot again for your help.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Can you post the whole view definition?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden