Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Indexes on a Query with a pivot function Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 1:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 2:35 AM
Points: 14, Visits: 111
Hi all,
Many thanks to the poster that helped me clear up a misconception that I was having with indexes a week ago but now I have a question as to whether indexes are possible to create on a pivoted view. Say we have two tables:
People:

PeopleId
FirstName
LastName
Age
Address
Country
Profession
.... some other fields .....

Employee:
EmployeeId
EmQualifier - this is the string name for the column field
EmData - this is the data value

and in order to access data from the Employee table we have a pivoted view, call it Employee_Pivot_View:
Select EmployeeId, LastName, FirstName, Department, Company, EmploymentStatus
From
(Select EmployeeId, EmQualifier, EmData
From Employee) as E
PIVOT
(MAX(EmData)
For EmQualifier IN (LastName, FirstName, Department, Company, EmploymentStatus)) As E_Pivot


If I were to write a query:
SELECT P.FirstName, P.LastName, P.Age, P.Address, EPV.Company, EPV.Department
FROM People P INNER JOIN Employee_Pivot_View EPV ON (P.LastName = EPV.LastName AND P.FirstName = EPV.FirstName)
Where EPV.EmploymentStatus = 'Employed'

I know that I can't use an indexed view on a PIVOT but is it possible to generate an index on the Employee table, in order to improve performance?
Please ignore the percentage of hits the WHERE condition can generate, let's say it's under 5 % so that an index seek can be used.
Post #1446324
Posted Thursday, April 25, 2013 4:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 14,788, Visits: 27,264
The best approach for tuning a query like this would be to start with the execution plan to understand how the optimizer is resolving it. That can help you apply indexes. Without that, I'm just guessing. But, you can see that you're doing JOIN on the firstname and lastname columns. Those look like candidates for an index. You also have a WHERE clause. That column is also a candidate for an index, but, based on the type of data that is likely there, it may not be a good index key all by itself, so you may need to experiment with adding it to other indexes, but without seeing the execution plan, you'll just be guessing at what might be useful.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1446408
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse