SQL Server T-SQL Performance Tuning – 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 Views

Impacts:

• 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 best-nootropics-for-studying 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_ID

Poor 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
end

Healthy 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
end

Healthy 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
End

Create 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]
GO

In the next article of this series we will look at Index Tuning]]>