October 20, 2015 at 10:51 pm
This view which is linked to a store procedure to bring data in a table. It runs and eats al the memory eventually getting terminated by the dba. He has advised me to simplify the view any helps please
CREATE VIEW [dbo].[VW_DTIRIS_Branck_Mngr_Hierarchy]
AS
SELECT DISTINCT
TOP (100) PERCENT
p.OrgUnitManager, p.OrgUnitID, p.OrgUnitName, p.ParentID AS CHILD1_ParentID, C.OrgUnitManager AS CHILD1_OrgUnitManager,
C.OrgUnitID AS CHILD1_OrgUnitID, C.OrgUnitName AS CHILD1_OrgUnitName, C2.OrgUnitManager AS CHILD2_OrgUnitManager, C2.OrgUnitID AS CHILD2_OrgUnitID,
C2.OrgUnitName AS CHILD2_OrgUnitName, C3.OrgUnitManager AS CHILD3_OrgUnitManager, C3.OrgUnitID AS CHILD3_OrgUnitID,
C3.OrgUnitName AS CHILD3_OrgUnitName, C4.OrgUnitID AS CHILD4_OrgUnitID, C4.OrgUnitName AS CHILD4_OrgUnitName,
C4.OrgUnitManager AS CHILD4_OrgUnitManager, C5.OrgUnitID AS CHILD5_OrgUnitID, C5.OrgUnitName AS CHILD5_OrgUnitName,
C5.OrgUnitManager AS CHILD5_OrgUnitManager, C6.OrgUnitName AS CHILD6_OrgUnitName, C6.OrgUnitManager AS CHILD6_OrgUnitManager,
C6.OrgUnitID AS CHILD6_OrgUnitID, C7.OrgUnitID AS CHILD7_OrgUnitID, C7.OrgUnitName AS CHILD7_OrgUnitName, C7.OrgUnitManager AS CHILD7_OrgUnitManager,
C8.OrgUnitID AS CHILD8_OrgUnitID, C8.OrgUnitName AS CHILD8_OrgUnitName, C8.OrgUnitManager AS CHILD8_OrgUnitManager, C9.OrgUnitID AS CHILD9_OrgUnitID,
C9.OrgUnitName AS CHILD9_OrgUnitName, C9.OrgUnitManager AS CHILD9_OrgUnitManager, C10.OrgUnitID AS CHILD10_OrgUnitID,
C10.OrgUnitName AS CHILD10_OrgUnitName, C10.OrgUnitManager AS CHILD10_OrgUnitManager, C11.OrgUnitID AS CHILD11_OrgUnitID,
C11.OrgUnitName AS CHILD11_OrgUnitName, C11.OrgUnitManager AS CHILD11_OrgUnitManager
FROM dbo.VW_DIM_Current_OrgStructure AS C6 LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C7 ON C6.OrgUnitID = C7.ParentID RIGHT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C5 ON C6.ParentID = C5.OrgUnitID RIGHT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C4 RIGHT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C3 ON C4.ParentID = C3.OrgUnitID RIGHT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS p LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C ON p.OrgUnitID = C.ParentID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C2 ON C.OrgUnitID = C2.ParentID ON C3.ParentID = C2.OrgUnitID ON C5.ParentID = C4.OrgUnitID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C8 ON C7.OrgUnitID = C8.ParentID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C9 ON C8.OrgUnitID = C9.ParentID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C10 ON C9.OrgUnitID = C10.ParentID LEFT OUTER JOIN
dbo.VW_DIM_Current_OrgStructure AS C11 ON C10.OrgUnitID = C11.ParentID
WHERE (p.OrgUnitManager IS NOT NULL)
ORDER BY p.OrgUnitManager, CHILD1_OrgUnitManager, CHILD2_OrgUnitManager, CHILD3_OrgUnitManager
GO
October 21, 2015 at 2:14 am
Here's that view formatted a little for readability:
CREATE VIEW [dbo].[VW_DTIRIS_Branck_Mngr_Hierarchy]
AS
SELECT --DISTINCT TOP (100) PERCENT
p.OrgUnitManager, p.OrgUnitID, p.OrgUnitName, p.ParentID AS CHILD1_ParentID,
C.OrgUnitManager AS CHILD1_OrgUnitManager, C.OrgUnitID AS CHILD1_OrgUnitID, C.OrgUnitName AS CHILD1_OrgUnitName,
C2.OrgUnitManager AS CHILD2_OrgUnitManager, C2.OrgUnitID AS CHILD2_OrgUnitID, C2.OrgUnitName AS CHILD2_OrgUnitName,
C3.OrgUnitManager AS CHILD3_OrgUnitManager, C3.OrgUnitID AS CHILD3_OrgUnitID, C3.OrgUnitName AS CHILD3_OrgUnitName,
C4.OrgUnitID AS CHILD4_OrgUnitID, C4.OrgUnitName AS CHILD4_OrgUnitName, C4.OrgUnitManager AS CHILD4_OrgUnitManager,
C5.OrgUnitID AS CHILD5_OrgUnitID, C5.OrgUnitName AS CHILD5_OrgUnitName, C5.OrgUnitManager AS CHILD5_OrgUnitManager,
C6.OrgUnitName AS CHILD6_OrgUnitName, C6.OrgUnitManager AS CHILD6_OrgUnitManager, C6.OrgUnitID AS CHILD6_OrgUnitID,
C7.OrgUnitID AS CHILD7_OrgUnitID, C7.OrgUnitName AS CHILD7_OrgUnitName, C7.OrgUnitManager AS CHILD7_OrgUnitManager,
C8.OrgUnitID AS CHILD8_OrgUnitID, C8.OrgUnitName AS CHILD8_OrgUnitName, C8.OrgUnitManager AS CHILD8_OrgUnitManager,
C9.OrgUnitID AS CHILD9_OrgUnitID, C9.OrgUnitName AS CHILD9_OrgUnitName, C9.OrgUnitManager AS CHILD9_OrgUnitManager,
C10.OrgUnitID AS CHILD10_OrgUnitID, C10.OrgUnitName AS CHILD10_OrgUnitName, C10.OrgUnitManager AS CHILD10_OrgUnitManager,
C11.OrgUnitID AS CHILD11_OrgUnitID, C11.OrgUnitName AS CHILD11_OrgUnitName, C11.OrgUnitManager AS CHILD11_OrgUnitManager
FROM dbo.VW_DIM_Current_OrgStructure AS C6
LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C7 ON C6.OrgUnitID = C7.ParentID
RIGHT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C5 ON C6.ParentID = C5.OrgUnitID
RIGHT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C4
RIGHT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C3 ON C4.ParentID = C3.OrgUnitID
RIGHT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS p
LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C ON p.OrgUnitID = C.ParentID
LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C2
ON C.OrgUnitID = C2.ParentID
ON C3.ParentID = C2.OrgUnitID
ON C5.ParentID = C4.OrgUnitID
LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C8 ON C7.OrgUnitID = C8.ParentID
LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C9 ON C8.OrgUnitID = C9.ParentID
LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C10 ON C9.OrgUnitID = C10.ParentID
LEFT OUTER JOIN dbo.VW_DIM_Current_OrgStructure AS C11 ON C10.OrgUnitID = C11.ParentID
WHERE (p.OrgUnitManager IS NOT NULL)
--ORDER BY p.OrgUnitManager, CHILD1_OrgUnitManager, CHILD2_OrgUnitManager, CHILD3_OrgUnitManager
This query appears to have been written using a query designer and trial and error. Rewrite it by hand *by design* and you will have a) manageable code and b) decent performance. When doing so, don't include TOP/ORDER BY - use of this silly trick is deprecated. Avoid DISTINCT also. Both ORDER BY and DISTINCT should be applied to the outermost select.
You're querying from another view. Use the base tables instead if you can. Views on views can result in huge query plans - which is probably what you've got here.
If you can post up some sample data and a set of business rules, folks could assist you in building a replacement query.
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
October 21, 2015 at 5:47 am
WOW! That is a lot of joins on the same table/view!
My experience has been, if you need to join on the same table but with multiple criteria, it might be better to "pivot" the data first into a temp table, then run the query. I *definitely* suggest posting some sample data and an idea as to what you are trying to accomplish. The multiple joins on the same table (or view) to generate all of these "child" fields has me very confused.
Thanks! 🙂
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
October 21, 2015 at 5:51 am
Nested views kill performance.
That view is querying other views, can you post their definitions please?
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
October 21, 2015 at 3:25 pm
CREATE VIEW [dbo].[VW_DIM_Current_OrgStructure]
AS
SELECT CurrentVersion, OrgUnitID, OrgUnitName, OrgUnitManager, CompanyFlag, BR1, BRCode, SegmentFlag, PCFlag, CCFlag, RLFlag, SalesOrgFlag, LevelsDeep, ParentID,
Delegation, NodesInLimb, NodeListing, CompanyCode, L1, L2, L3, L4, L5, L6, L7, L8, L9, L10, L11, L12, L13, L14, L1Name, L2Name, L3Name, L4Name, L5Name,
L6Name, L7Name, L8Name, L9Name, L10Name, L11Name, L12Name, SourceID, GETDATE() AS DateLoaded, GETDATE() AS VersionDate
FROM dbo.DIM_OrgStructure
WHERE (CurrentVersion = '1') AND (OrgUnitID IS NOT NULL)
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "DIM_OrgStructure"
Begin Extent =
Top = 6
Left = 38
Bottom = 254
Right = 206
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'VW_DIM_Current_OrgStructure'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'VW_DIM_Current_OrgStructure'
GO
First of all my apologies to chris for not posting the query in a readable format, but I am new to the online forum as well as SQL like a beginner new. Anyway so the above view is the one getting used by the primary view. now the data produced by the primary looks like the following pic below, It is creating a manager hierarchy
the link for sample data created by primary view is https://drive.google.com/file/d/0B6iaKvD8HegIeHNYWE8wYTFBUTA/view?usp=sharing
October 22, 2015 at 7:52 am
Another highly likely issue for performance is the lack of normalization. You have to join to the same table over and over. If this was properly normalized that wouldn't be required.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2015 at 7:56 am
Sean Lange (10/22/2015)
Another highly likely issue for performance is the lack of normalization. You have to join to the same table over and over. If this was properly normalized that wouldn't be required.
If OP posts something consumable (I can't access his source here) I'll take a shot with a rCTE to kick things off. There's not much in the way of business rules but it would be a start.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply