In this installament of the TSQL Performance Tuning series we turn our attention to Views
Views Used Within Joins or Where Conditions
As a generic concept, an inner join with an object which has insufficient indexing will result in index scanning or perhaps a table scan which also applies for ViewsImpacts: • Much CPU Consumption particularly if it is used within huge data entity or it is used under stress of end users ending up with lots of CXPacket waits • Dramatic performance degradation for the entire of DB Server due to CPU overload impacting negatively on other transactions • Unbelievable performance behavior for a small scope of criteria coz views will also turn into selecting all of data first then filter the desired output according to the set of Criteria . So what alternatives can be here …? 1. We could pass directly those select statements of views as Sub queries inside SP, then we could put the needed where conditions inside these Sub Queries 2. Also , you could use CTEs (Common Table Expressions) with Appling the same where conditions to them or join with them also. 3. Indexed Views but this option has some restrictive limitations to enjoy with indexing feature such as : • You have to create this view on single table to enjoy with indexing feature • No use for with with (nolock) hint or such similar hints which might results of heavy locks for long running Queries such those of DWH solutions • You have to create them WITH SCHEMABINDING option to prevent any relevant tables from any DDL change till view is dropped first which does mean you will need for some down time for sure. • View should have unique clustered index which does mean enough that results of views should have some unique column which can be inapplicable some times. • View shouldn’t use any aggregative functions , Top or distinct commands.. Examples: Create Tables
CREATE TABLE [dbo].[Students]( [Student_ID] [int] NOT NULL, [Student_Name] [nvarchar](150) NULL, [Telephone] [nvarchar](10) NULL, CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ( [Student_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[Certificates]( [Student_ID] [int] NOT NULL, [Certificate] [nvarchar](100) NULL, [Certificates_Date] [date] NULL, [Place] [nvarchar](50) NULL ) ON [PRIMARY] GO CREATE VIEW [dbo].[Certificates_count] AS SELECT COUNT (Certificate)AS count, Student_ID FROM dbo.Certificates GROUP BY Student_IDPoor SP using views
create procedure [dbo].[Students_Statistics_Old] ( @student_id int) as begin select T.* from Students T INNER JOIN Certificates_count S ON T.student_id=S.student_id where S.count >1 and T.Student_id =@student_id return endHealthy SP using Sub Queries
ALTER procedure [dbo].[Students_Statistics_New1] ( @student_id int) as begin select T.* from Students T INNER JOIN (select COUNT (Certificate)AS count, Student_ID FROM dbo.Certificates GROUP BY Student_ID )S ON T.student_id=S.student_id where S.count >1 and (T.Student_id =@student_id OR @student_id IS NULL) RETURN endHealthy SP using CTE (Common table expression)
ALTER procedure [dbo].[Students_Statistics_New2] ( @student_id int) as begin ; with Temptable as (select COUNT (Certificate)AS count, Student_ID FROM dbo.Certificates GROUP BY Student_ID ) select T.* from Students T INNER JOIN Temptable S ON T.student_id=S.student_id where S.count >1 and T.Student_id =@student_id RETURN EndCreate Indexed views Kindly note that our case here use aggregative function which is one of the restrictions mentioned above to create indexes on views , but it is good know how to create indexed views for further usage within other cases.
create VIEW [dbo].[Certificates_Indexed] WITH SCHEMABINDING AS SELECT Certificate FROM dbo.Certificates CREATE UNIQUE CLUSTERED INDEX [Certificates_IX] ON [dbo].[Certificates_Indexed] ( [Certificate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GOIn the next article of this series we will look at Index Tuning]]>