CODEWITHSUNDEEP
sqlsql-serverdatabasejoinleft-join
Siddharth Bharadwaj
Siddharth Bharadwaj

Reputation: 81

Applying cluster indexing to a view in SQL Server 2012

I have a view which queries around 1+ million rows and takes around 10-15 minutes to finish its execution,I want to provide cluster indexing to it so that it exists in physical schema and takes less time to load, but there are a number of constraints in order to provide cluster indexing i.e. only INNER JOIN are allowed and No subqueries should be present in views defination how do I replace the LEFT JOIN present in this view with INNER JOIN and how do I eliminate subqueries from this views defination so that cluster indexing can be applied to it. 

CREATE view [dbo].[FTM_ProfileDetailsView] with SCHEMABINDING as

    select FTM.Id
    , FTM.EmployeeId
    , FTM.CustomerId
    , FTM.AbsenceFirstDate
    , FTM.BackgroundHistory
    , FTM.BackgroundHistoryComments
    , FTM.IsEmployeeAbsent,FTM.ServiceId
    , Case When isnull(FTM.IsSelfManagement,'')='' THEN cast(0 as bit) ELSE FTM.IsSelfManagement END as IsSelfManagement
    , PR.ServiceLineId,FTM.ProfileId,PR.StatusId,Status.Status as StatusName
    , PR.ReasonID
    , PR.ModifiedDate
    , PR.WithdrawnReason
    , PR.CreatedBy
    , PR.CreatedDate
    , PR.IsActive 
    , mgrs.usernames as LineManagers
    , cust.CustomerName
    , ltrim(rtrim( emp.EmployeeTitle+' '+ emp.FirstName+' '+ emp.Surname)) as EmployeeFullName
    , FTM.ProfileManagerId
    , FTM.IsProfileManagement
    , AM.MonitoringChecks
    , AM.Frequency
    , AM.ProfileManagerNotes
    , AM.TaskDateAndTime
    , FTM.ProfileManagementCriteriaId
    ,cast(case when PR.StatusId = 13 then 1 else 0 end as bit) as IsActiveMonitoring
    , CustServ.CustomerServiceName
    , BU.Name as BusinessUnit
    , emp.DASID
    , emp.DateOfBirth as EmployeeDOB
    , addr.PostCode
    , coninfo.Email
    , (select top 1
      StatusId from dbo.PR_Profileintervention ProfileInt
      where ProfileInt.ProfileId=FTM.Profileid
      order by ProfileInt.Id desc) as LatestInterventionStatusId
    , (select name from dbo.FTM_Intervention Intr
      where Intr.Id=(select top 1 InterventionId from dbo.PR_Profileintervention ProfileInt
      where ProfileInt.ProfileId=FTM.Profileid 
      order by ProfileInt.Id desc))
      as LatestInterventionName  from FTM_Profile FTM

    LEFT JOIN   dbo.ProfileManagersView mgrs ON mgrs.ProfileID = FTM.ProfileID
    INNER JOIN dbo.Customer cust on cust.Id= FTM.CustomerId
    INNER JOIN dbo.Employee emp on emp.Id = FTM.EmployeeId
    INNER JOIN dbo.PR_Profile PR on PR.Profileid=FTM.ProfileId
    LEFT JOIN   dbo.BusinessUnit BU on BU.Id=PR.BUId
    LEFT JOIN   dbo.PR_dv_Status [Status] on [Status].Id = PR.StatusId
    LEFT JOIN   dbo.CM_ActiveMonitoringDetails AM on AM.ProfileId = PR.Profileid
    LEFT JOIN   dbo.FTM_CustomerServiceMapping CustServ on CustServ.ServiceId = FTM.ServiceId and CustServ.CustomerId = FTM.CustomerId
    LEFT JOIN   dbo.contact con on con.Id = emp.ContactID
    LEFT JOIN   dbo.address addr on addr.Id = con.HomeAddressId
    LEFT JOIN   dbo.contactinfo coninfo on coninfo.Id = con.ContactInfoId

Upvotes: 2

Views: 67

Answers (2)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Ok, you didn't give me an answer on my question, but the subqueries should be changed. Try to use this instead the two subqueries:

/*
...
, emp.DateOfBirth as EmployeeDOB
, addr.PostCode
, coninfo.Email
*/
, p.StatusId as LatestInterventionStatusId
, p.name     as LatestInterventionName  

from FTM_Profile FTM

OUTER APPLY (
    select TOP 1 Intr.name, ProfileInt.StatusId
    from dbo.PR_Profileintervention ProfileInt
        LEFT JOIN dbo.FTM_Intervention Intr ON Intr.Id = ProfileInt.InterventionId
    where ProfileInt.ProfileId = FTM.Profileid 
    order by ProfileInt.Id desc
) p
/*
LEFT JOIN   dbo.ProfileManagersView mgrs ON mgrs.ProfileID = FTM.ProfileID
INNER JOIN dbo.Customer cust on cust.Id= FTM.CustomerId
INNER JOIN dbo.Employee emp on emp.Id = FTM.EmployeeId
...
*/

Upvotes: 0

VDK
VDK

Reputation: 583

I have a suggestion. Can you try and change your query so the sub -queries in the SELECT are placed in CROSS APPLYs?

So something along the lines of this in your WHERE clause:

CROSS APPLY (  
                select top 1 StatusId AS LatestInterventionStatusId
                from dbo.PR_Profileintervention ProfileInt
                where ProfileInt.ProfileId=FTM.Profileid
                order by ProfileInt.Id desc

            ) LatestInterventionStatusId

CROSS APPLY (
                select name AS LatestInterventionName
                from dbo.FTM_Intervention Intr
                where Intr.Id=(select top 1 InterventionId 
                               from dbo.PR_Profileintervention ProfileInt
                               where ProfileInt.ProfileId=FTM.Profileid 
                               order by ProfileInt.Id desc)
             )LatestInterventionName

And then of course change the column names in the SELECT to something like this:

, LatestInterventionStatusId.LatestInterventionStatusId
, LatestInterventionName.LatestInterventionName

Give this a go and let me know if it makes a different.

Upvotes: 1

Related Questions