Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nkpriya
Starting Member
34 Posts |
 Posted - 2012-04-03 : 17:58:31
|
| I have this below CTE and creating a view with this CTE SQL. I am using this view in another views. I see that there is huge performance issue when using this CTE view.Please share some ideas how to improve performance. I have seen in one of the forums that we can create a temp table/function using this CTE and then use that in other views. But I am not sure how to do that. Please let me know. ThanksWITH AllReports AS (SELECT 1 AS number, [Employee Name], [Employee Mgr Name], [Lvl3 Desc Curr], [Employee Prefixed ID], [Employee RC Num], [Lvl4 Desc Curr], [Mgr Title], [Dept Name], [Mgr Prefix] FROM dbo.HRDB1_Name UNION ALL SELECT tp.number + 1 AS ex, tc.[Employee Name], tp.[Employee Mgr Name], tc.[Lvl3 Desc Curr], tc.[Employee Prefixed ID], tc.[Employee RC Num], tc.[Lvl4 Desc Curr], tc.[Mgr Title], tc.[Dept Name], tc.[Mgr Prefix] FROM AllReports AS tp INNER JOIN dbo.HRDB1_Name AS tc ON tp.[Employee Name] = tc.[Employee Mgr Name] WHERE (tp.number < 5)) SELECT number, [Employee Name], [Employee Mgr Name], [Lvl3 Desc Curr], [Employee Prefixed ID], [Employee RC Num], [Lvl4 Desc Curr], [Mgr Title], [Dept Name], [Mgr Prefix] FROM AllReports AS AllReports_1 WHERE (number IN (5, 4, 3, 2, 1)) AND ([Employee Name] IS NOT NULL) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-04-04 : 03:25:44
|
Do you have a proper index over columns [Employee Name] and [Employee Mgr Name] ?
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
|
|
|
|
|
CTE Performance Issue