Database Simplified Headline Animator

Database Simplified

Sunday, 10 June 2012

Bad Performance Of View In SQL Server

In this post I am going to describe the problem I encountered in a view, which may affect your view performance badly. Create tables from following script

If(OBJECT_ID('PerfTest1') is Not null)
Drop Table PerfTest1 Create Table PerfTest1(Id int Identity(1,1),Code Int,CodeName Char(30)) Insert Into PerfTest1 Values(1,'Archo'),(2,'Halie'),(3,'Raiye'),(4,'Amity')
Go 300 If(OBJECT_ID('PerfTest2') is Not null)
Drop Table PerfTest2 Create Table PerfTest2(Id int Identity(1,1),Code Int,CodeName Char(40)) Insert Into PerfTest2 Values(120,'LIVE'),(99,'ACTION'),(100,'SLOW'),(200,'PROCESS'),(200,'OUTDOOR')
Go 300

Create Following View

Create View Vw_PerfUnion
As
Select CodeName From PerfTest1
Union
Select CodeName From PerfTest2
Go

Run Following Query and Check the Execution Plan

Select * from Vw_PerfUnion Where CodeName='Live'

image

Its obvious that table scan will take since no index has been create on tables. Now lets create index on these tables.

 

Create NonClustered Index IX_PerfTest1 On PerfTest1(CodeName)
Go Create NonClustered Index IX_PerfTest2 On PerfTest2(CodeName)
Go

Now Run Query Again and Check Execution Plan

Select * from Vw_PerfUnion Where CodeName='Live'

image

As you can see in above screen shot even after creating index on table PerfTest1 Table Scan is taking Place. This is due to the Datatype mismatch between Two Query in the view. Check the datatype of PerfTest1(CodeName) and PerfTest1(CodeName) Fields they are Char(30) and Char(40) respectively. Now Lets alter the Datatype of PerfTest1 Table. To alter field we need to drop index we have created on that field.

Drop Index PerfTest1.IX_PerfTest1
Go
Alter Table PerfTest1 Alter Column CodeName Varchar(40)
Go
Create NonClustered Index IX_PerfTest1 On PerfTest1(CodeName)
Go

Now run the Query and Check Execution Plan

image

No comments:

Post a Comment