Hey guys I can't get this view to be faster. It is causing me serious problems. I am trying to get all this data gathered to return to the client but I can't make it any faster. My Low return time is 24 seconds and my High return time is 77 Seconds. But the it will take 24 Seconds even if no data is returned.
What can i do?
I have indexed every foreign key.
i have pictures of my execution path that you can look at at the following address
http://salessimplicity.dnsalias.com/ssnet/needhelp/needhelp.htm
If you need anything to help me let me know I am more than willing to help to get this thing going faster..
The Main view is below
Code Snippet
create VIEW dbo.vuFollowUpTriggerAll AS
SELECT
Client.ID,
SubdivFU.ID AS FollowUpSubdivID,
FollowUpDone.ID AS FollowUpID,
Case
When SubdivFU.DeptID is Not Null then Agent1.ID
Else Agent2.ID
END As SalesAgentID,
-- Agent2.ID AS SalesAgentID,
Subdiv.ID AS SubdivisionID,
Subdiv.MarketingName AS SubdivisionName,
Buyer.FirstName,
Buyer.LastName,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As SalesAgentFirst,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As SalesAgentLast,
-- Agent2.FirstName AS SalesAgentFirst,
-- Agent2.LastName AS SalesAgentLast,
FollowUpDone.DateItemEntered,
FollowUpDone.DateItemComplete AS DateComplete,
FollowUpDone.Custom,
--Removed ClientStatus.Status - didn't see it used in code.
SubdivFU.ContactDesc AS TaskDesc,
FollowUpDone.Removed,
--For Contingencies and Deposit Reminders, we subtract the days rather then add them.
DATEADD(Day, TE.Multiplier * ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate) AS DueDate,
ClientDate.DepositID,
SubdivFU.ContactType AS TaskType,
SubdivFU.MasterReportID,
SubdivFU.EmailLetterID,
Buyer.Email,
Buyer.DoNotSendEmail,
Buyer.DoNotSendLetter,
SubdivFU.OverrideOptOut,
FollowUpDone.UserID,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As AssignedToFirstName,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As AssignedToLastName
FROM
dbo.tbSubdivision Subdiv
INNER JOIN dbo.tbSubdivisionFollowUp SubdivFU ON SubdivFU.SubdivisionID = Subdiv.ID
-- tbTrigger Event is the table that helps us put this whole spaghetti together
--it joins the Event type to the Date Type to the Multiplier.
INNER JOIN dbo.tbTriggerEvent TE ON TE.ID = SubdivFU.TriggerEvent
--tbDateType is what connects vuClient Dates the appropriate date field.
-- INNER JOIN dbo.tbDateType DT ON DT.ID = TE.DateTypeID
INNER JOIN dbo.tbClient Client ON Subdiv.ID = Client.SubdivisionID
AND ((SubdivFU.ClientRankID = Client.ClientRankID AND TE.UseRank = 1)
OR (TE.UseRank = 0))
--vuCLientDates replaces what we were going to use as tbClientDates but it is almost as fast to generate this on the fly
--as it is to generate it on the fly.
INNER JOIN dbo.vuClientDates ClientDate ON ClientDate.DateTypeID = TE.DateTypeID and Client.ID = ClientDate.ClientID
INNER JOIN dbo.tbMember Member ON Member.ClientID = Client.ID
AND Member.MemberTypeID = 1 --Buyer Type
INNER JOIN dbo.tbIndividual Buyer ON Member.IndividualID = Buyer.ID
--vuFollowUpTriggerGetAgent is a view we use to connect Clients to their Agents in each Department.
Left Outer Join vuFollowUpTriggerGetAgent GetAgent on GetAgent.DeptID = SubdivFU.DeptID and Client.ID = GetAgent.ClientID and GetAgent.SubdivisionID = Subdiv.ID
Left Outer JOIN dbo.tbUser Agent1 ON Agent1.ID = GetAgent.UserID
Inner JOIN dbo.tbUser Agent2 ON Agent2.ID = Client.UserID
LEFT OUTER JOIN dbo.tbFollowUp FollowUpDone ON FollowUpDone.SubdivFollowUpID = SubdivFU.ID
AND FollowUpDone.ClientID = Client.ID
AND isNull(FollowUpDone.DepositID, 0) = isNull(ClientDate.DepositID, 0)
WHERE (FollowUpDone.DateItemComplete Is Null)
AND (isnull(FollowUpDone.Removed,0) = 0)
AND (SubdivFU.Active = 1)
AND ((SubdivFU.TriggerEvent = 'R' AND DATEDIFF(DAY, ClientDate.EventDate, GETDATE()) < 0 )
OR (SubdivFU.TriggerEvent = 'O' AND (FLOOR(CAST(GETDATE() AS float)) >= DATEADD(Day, ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate)) )
OR (SubdivFU.TriggerEvent <> 'R' and SubdivFU.TriggerEvent <> 'O')
)
AND (SubdivFU.OverrideOptOut = 1
OR ((SubdivFU.ContactType = 'O')
OR (SubdivFU.ContactType = 'L' AND Buyer.DoNotSendLetter = 0)
OR (SubdivFU.ContactType = 'E' AND Buyer.DoNotSendEmail = 0)
OR (SubdivFU.ContactType = 'C' AND Buyer.DoNotCall = 0)))
AND (((SubdivFU.DeptID IS Not Null) and (Agent1.ID is Not Null)) or (SubdivFU.DeptID is Null) )
It joins to a few other views defined below
Code Snippet
ALTER View dbo.vuFollowUpTriggerGetAgent AS
-- 5/14/07 DebP Added link to tbDept in order to exclude inactive departments.
SELECT SFU.DeptID, tbClient.ID AS ClientID,
ISNULL(tbClientDept.UserID, ISNULL(tbSubdivisionDept.UserID, Null)) AS UserID,
tbSubdivision.ID as SubdivisionID
FROM tbSubdivision
INNER JOIN dbo.tbClient ON tbClient.SubdivisionID = tbSubdivision.ID
INNER Join
(SELECT SubdivisionID, DeptID
FROM dbo.tbSubdivisionFollowUp
INNER JOIN dbo.tbDept ON dbo.tbSubdivisionFollowUp.DeptID = dbo.tbDept.ID
AND dbo.tbDept.Active <> 0
GROUP BY SubdivisionID, DeptID)
SFU ON SFU.SubdivisionID = tbSubdivision.ID
-- The following is no longer needed since we're doing an inner join to tbDept.
--AND SFU.DeptID is Not Null
LEFT OUTER JOIN dbo.tbSubdivisionDept ON tbSubdivisionDept.DeptID = SFU.DeptID
AND tbSubdivisionDept.SubdivisionID = tbSubdivision.ID
AND tbSubdivisionDept.UserID is not Null
LEFT OUTER JOIN dbo.tbClientDept ON tbClientDept.DeptID = SFU.DeptID
AND tbClient.ID = tbClientDept.ClientID
AND tbClientDept.UserID is not Null
and
Code Snippet
ALTER view dbo.vuClientDates as
SELECT tbClient.ID as ClientID,
Case
When tbClient.ClientStatusID = 4 then 1
When tbClient.ClientStatusID = 1 then 2
When tbClient.ClientStatusID = 2 then 4
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then 11
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then 5
End as DateTypeID,
Case
When tbClient.ClientStatusID = 4 then VisitDate
When tbClient.ClientStatusID = 1 then ContractDate
When tbClient.ClientStatusID = 2 then ActualCloseDate
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then VisitDate
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then CancellationDate
End
as EventDate,
Null as DepositID, Null as StageID, Null as EscrowStageID From tbClient
UNION ALL
SELECT tbClient.ID as ClientID, 3 as DateTypeID, ExpirationDate as EventDate, Null as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
where (tbClient.Contingency = 1)
AND (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in ( 1, 2, 4)
)
UNION ALL
SELECT tbClient.ID as ClientID, 6 as DateTypeID, DateDue as EventDate, Deposits.ID as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
INNER JOIN dbo.tbDeposits Deposits ON Deposits.ClientID = tbClient.ID
AND Deposits.DateDue IS NOT NULL
AND Deposits.DatePaid IS NULL
Where (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in( 1,2,4)
)
These are the indexes i have added for this
Code Snippet
CREATE INDEX [IX_tbIndividual] on [dbo].[tbIndividual] ([BuilderID]) WITH FILLFACTOR = 95CREATE INDEX [IX_tbClient] on [dbo].[tbClient] ([ClientStatusID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_1] on [dbo].[tbClient] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_2] on [dbo].[tbClient] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember] on [dbo].[tbMember] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_1] on [dbo].[tbMember] ([IndividualID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_2] on [dbo].[tbMember] ([MemberTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbDeposits] on [dbo].[tbDeposits] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_2] on [dbo].[tbSubdivisionFollowUp] ([TriggerEvent]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp] on [dbo].[tbSubdivisionFollowUp] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_1] on [dbo].[tbSubdivisionFollowUp] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_3] on [dbo].[tbSubdivisionFollowUp] ([ContactType]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_2] on [dbo].[tbFollowUp] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp] on [dbo].[tbFollowUp] ([SubdivFollowUpID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_1] on [dbo].[tbFollowUp] ([MasterReportID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_3] on [dbo].[tbFollowUp] ([DepositID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionDept] on [dbo].[tbSubdivisionDept] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision] on [dbo].[tbUserSubdivision] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision_1] on [dbo].[tbUserSubdivision] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClientDept_1] on [dbo].[tbClientDept] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent] on [dbo].[tbTriggerEvent] ([DateTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent_1] on [dbo].[tbTriggerEvent] ([TriggerEventDesc]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([AvailablePlanOptionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([RoomPlanID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([Username]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([BuilderID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_LoginID] on [dbo].[tbUser] ([LoginID]) WITH FILLFACTOR = 95
|||
You have a lot of joins in your query. Have you tried using temporary tables instead to store intermediate results and then join them ?
|||Well, two of them are on the tbClient with CancelationDate and ClientStatusID, and one of those also looks at Contingency.
(Those are tables 2 and 3 with the 25% costs on the far right of the plan).
You're only indexing on ClientStatusID
You're also using a lot of functions in the filtering which will pop the plan back to table scans.
You may want to develop and "grow" this incrementally. Just do the first two tables and get them honed in, then add in the next table.
Get a good clean manageable baseline to grow from.
|||Also, to go along with Dale, you'll want to create indexes based on the composite key of the join. Don't just create an index for each column individually. Do as he says and "grow" the view. Each time you add something new, review the execution plan again to ensure nothing has snuck up on you.|||These are all great ideas...
Thanks
