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

2 comments:

  1. The Cleveland Public Library strives to offer entry to revolutionary know-how and selection of|quite a lot of|a big selection of} computer- and technology-related services. This coverage establishes guidelines for patrons’ use of the Cleveland Public Library’s 3D printing services. Choose from a spread of materials and print Compression Stockings settings to get prompt feedback on lead times and costs.

    ReplyDelete