Post reply

Indexes on a Query with a pivot function

  • April 25, 2013 at 1:32 am

    #271865

    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.

  • April 25, 2013 at 4:48 am

    #1609866

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply