I've been struggling all day with a stored procedure. I originally wrote it
using views to get it working and it was taking about 30 min to run. I
converted it to table variables to speed it up, and it got slower???? (It's
now down to 1hr 25 min - but that includes some other functionality)

At the moment I'm just interested in anyone having a good website that will
go into the details of how views and table variables compare. I've found
heaps comparing table variables and temporary tables, but none considering
views.

If I'm having no luck tomorrow then I'll post some more details of the
stored procedure asking for help then.

Thanks all

Derek
Can you show us the stored procedure?
[quoted text, click to view]

Well I haven't been able to work it out. The code is at the bottom. The
bits which are slow are 10, 11, 12, 13.

Note that I can't split up the stored procedure into multiple stored
procedure as ultimately it will be used in a trigger (although only the
updates for the correct PersonID's will be affected).

The base tables that all this comes from is a third party application and
therefore I can't do anything about indexes and the like on those.

Any questions welcome.


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE [dbo].[UpdateAttemptsAndState]
AS
Begin
DECLARE @tblStartingActionsEndDates table
(PersonID int, ActionID int, EndDateCheck varchar(24))
DECLARE @tblStartingActions table
(ActionID int, PersonID int, SubType Varchar(6),
DateOfAction varchar(8), TimeOfAction Varchar(8),
Datestamped varchar(8), Timestamped varchar(8), RecClassID Varchar(6))
DECLARE @tblEarliestStateDateCheck table
(PersonID int, DateCheck varchar(24) Collate SQL_Latin1_General_Cp1_CI_AS)
DECLARE @tblGatewayPeople table
(PersonID int)

--Following is used for testing only
Declare @tblTimes table (Stage char(1), TheTime datetime)
Insert into @tblTimes (Stage, TheTime) values ('A', GetDate())


DELETE from tblAttempts
Insert into @tblTimes (Stage, TheTime) values ('B', GetDate())
DELETE from tblState
Insert into @tblTimes (Stage, TheTime) values ('C', GetDate())

--1
Insert into @tblEarliestStateDateCheck (PersonID, DateCheck)
SELECT dbo.[Action].PersonID,
MIN(dbo.[Action].DateOfAction + dbo.[Action].DateStamped +
dbo.[Action].TimeStamped) AS DateCheck
FROM dbo.[Action] INNER JOIN
dbo.Action_SubTypes ON dbo.[Action].SubType
= dbo.Action_SubTypes.ActionSubTypeID
WHERE (dbo.Action_SubTypes.EffectCandidateCurrentState <> '') and Status
= 'A'
GROUP BY dbo.[Action].PersonID

Insert into @tblTimes (Stage, TheTime) values ('D', GetDate())

--2
Insert into @tblStartingActions
(ActionID, PersonID, SubType, DateOfAction, TimeOfAction,
Datestamped, Timestamped, RecClassID)
SELECT A.ActionID, A.PersonID, A.SubType, A.DateOfAction, A.TimeOfAction,
A.DateStamped, A.TimeStamped, A.RecClassID
FROM dbo.[Action] A INNER JOIN
@tblEarliestStateDateCheck E ON A.PersonID = E.PersonID AND A.DateOfAction
+ A.DateStamped + A.TimeStamped = E.DateCheck
WHERE A.Status = 'A'
UNION
SELECT A.ActionID, A.PersonID, A.SubType, A.DateOfAction,
A.TimeOfAction, A.DateStamped, A.TimeStamped, A.RecClassID
FROM dbo.[Action] A INNER JOIN dbo.tblSpecialActions S ON A.Subtype
= S.Subtype
WHERE S.SACID = 1 and A.Status = 'A'

Insert into @tblTimes (Stage, TheTime) values ('E', GetDate())

--3
Insert into @tblStartingActionsEndDates (PersonID, ActionID, EndDateCheck)
SELECT [First].PersonID, [First].ActionID,
MIN([Second].DateOfAction + [Second].DateStamped + [Second].TimeStamped) AS
EndDateCheck
FROM @tblStartingActions [First] INNER JOIN
@tblStartingActions [Second]
ON [First].PersonID = [Second].PersonID AND
[First].DateOfAction + [First].DateStamped + [First].TimeStamped <
[Second].DateOfAction + [Second].DateStamped + [Second].TimeStamped
GROUP BY [First].PersonID, [First].ActionID

Insert into @tblTimes (Stage, TheTime) values ('F', GetDate())

--4
Insert into tblAttempts (PersonID, AttemptID, StartDate, EndDate,
JSContract, FirstStateActionID, FirstStateDatestamp)
SELECT SA.PersonID, SA.ActionID AS AttemptID,
dbo.GetDateTime(SA.DateOfAction, SA.TimeOfAction) AS StartDate,
CONVERT(datetime, LEFT(ED.EndDateCheck, 8), 112) AS EndDate,
SA.RecClassID AS JSContract,
SA.ActionID AS FirstStateActionID,
dbo.GetDateTime(SA.DateStamped, SA.TimeStamped) AS FirstStateDatestamp
FROM @tblStartingActions SA LEFT OUTER JOIN @tblStartingActionsEndDates ED
ON SA.ActionID = ED.ActionID AND
SA.PersonID = ED.PersonID

Insert into @tblTimes (Stage, TheTime) values ('G', GetDate())

--Insert other fields into tblAttempts - where possible

--5
--Attempt Count
Update tblAttempts SET tblAttempts.Attempt =
(Select Count(*) FROM tblAttempts A1
WHERE A1.PersonID = tblAttempts.PersonID and A1.StartDate <=
tblAttempts.StartDate)

Insert into @tblTimes (Stage, TheTime) values ('H', GetDate())

--Categories
--6
Insert into @tblGatewayPeople
Select Person.PersonID
FROM Person
WHERE Person.Status = 'A' and RecClassID = 'NEWDL' and PersonID not in
(SELECT PersonID
FROM [Action]
WHERE Subtype = 'SRTFT' and Status = 'A')
UNION Select PersonID from [Action] WHERE Status = 'A' and (Subtype =
'SRTOPT' OR SubType = 'SRTIAP')

Insert into @tblTimes (Stage, TheTime) values ('I', GetDate())

--7
Update A Set Category = Case P.RecClassID
WHEN 'NEWDL' THEN
Case When IsNull(G.PersonID, -1) = -1
Then 'FT Trans'
When IsNull(R.CategoryID, '') = 'A1'
Then 'GW Trans'
Else 'Flow'
End
ELSE RecClassID
END
FROM Person P LEFT OUTER JOIN Resource_Categories R
ON P.PersonID = R.PersonID
LEFT OUTER JOIN @tblGatewayPeople G
ON P.PersonID = G.PersonID
INNER JOIN tblAttempts A ON A.PersonID = P.PersonID

Insert into @tblTimes (Stage, TheTime) values ('J', GetDate())

--8
--Update Cohorts
Update A SET CohortDWPMonth = D.MonthLong, CohortDWPYear = D.[Year],
CohortJCMonth = J.MonthLong, CohortJCYear = J.[Year],
CohortCalendarMonth = dbo.MonthString(A.StartDate, 0), CohortCalendarYear
= CONVERT(Varchar(4), YEAR(A.StartDate))
FROM tblAttempts A INNER JOIN
dbo.tblDWPMonths D
ON A.StartDate >= D.StartDate AND A.StartDate <= D.EndDate
INNER JOIN dbo.tblJCMonths J
ON A.StartDate >= J.StartDate AND A.StartDate <= J.EndDate

Insert into @tblTimes (Stage, TheTime) values ('K', GetDate())

--Build tblState
--StateChangingActions
DECLARE @tblStateChangingActions table
(PersonID int, ActionID int, DateOfAction datetime, Datestamp datetime,
State varchar(10), Datecheck char(24))

--9
Insert into @tblStateChangingActions
(PersonID, ActionID, DateOfAction, Datestamp, State, DateCheck)
SELECT dbo.[Action].PersonID, dbo.[Action].ActionID,
CONVERT(datetime, dbo.[Action].DateOfAction + ' ' +
dbo.[Action].TimeOfAction) AS DateOfAction,
CONVERT(DateTime, dbo.[Action].DateStamped + ' ' +
dbo.[Action].TimeStamped) AS DateStamp,
dbo.Action_SubTypes.EffectCandidateCurrentState AS State,
dbo.[Action].DateOfAction + dbo.[Action].DateStamped +
dbo.[Action].TimeStamped AS DateCheck
FROM dbo.[Action] INNER JOIN
dbo.Action_SubTypes
ON dbo.[Action].SubType = dbo.Action_SubTypes.ActionSubTypeID
WHERE (dbo.Action_SubTypes.ActionSubTypeStatus = 'A') AND
(dbo.[Action].Status = 'A')
AND (IsNull(dbo.[Action].DateOfAction, '0000') > '1990')
AND (IsNull(dbo.[Action].DateStamped, '0000') > '1990')
AND (dbo.[Action].DateOfAction < '2999')

Whereas the following took 2 minutes and 2 seconds to run (about 2% of the
time that the table variables took).

(Sorry about the formatting, it's set up for a wider screen).

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE [dbo].[UpdateAttemptsAndStateTest04_Views]
AS
Begin
DECLARE @tblGatewayPeople table (PersonID int)

--Testing
Declare @tblTimes table (Stage char(1), TheTime datetime)
Insert into @tblTimes (Stage, TheTime) values ('A', GetDate())


DELETE from tblAttempts
Insert into @tblTimes (Stage, TheTime) values ('B', GetDate())
DELETE from tblState
Insert into @tblTimes (Stage, TheTime) values ('C', GetDate())
--1
--4
Insert into tblAttempts (PersonID, AttemptID, StartDate, EndDate,
JSContract, FirstStateActionID, FirstStateDatestamp)
SELECT SA.PersonID, SA.ActionID AS AttemptID,
dbo.GetDateTime(SA.DateOfAction, SA.TimeOfAction) AS StartDate,
CONVERT(datetime, LEFT(ED.EndDateCheck, 8), 112) AS EndDate,
SA.RecClassID AS JSContract,
SA.ActionID AS FirstStateActionID,
dbo.GetDateTime(SA.DateStamped, SA.TimeStamped) AS FirstStateDatestamp
FROM vwStartingActions SA LEFT OUTER JOIN vwStartingActionsEndDates ED
ON SA.ActionID = ED.ActionID AND
SA.PersonID = ED.PersonID
Insert into @tblTimes (Stage, TheTime) values ('G', GetDate())

--Insert other fields into tblAttempts - where possible

--5
--Attempt Count
Update tblAttempts SET tblAttempts.Attempt =
(Select Count(*) FROM tblAttempts A1 WHERE A1.PersonID =
tblAttempts.PersonID and A1.StartDate <= tblAttempts.StartDate)
Insert into @tblTimes (Stage, TheTime) values ('H', GetDate())

--Categories
--6
Insert into @tblGatewayPeople
Select Person.PersonID
FROM Person
WHERE Person.Status = 'A' and RecClassID = 'NEWDL' and PersonID not in
(SELECT PersonID
FROM [Action]
WHERE Subtype = 'SRTFT' and Status = 'A')
UNION Select PersonID from [Action] WHERE Status = 'A' and (Subtype =
'SRTOPT' OR SubType = 'SRTIAP')
Insert into @tblTimes (Stage, TheTime) values ('I', GetDate())

--7
Update A Set Category = Case P.RecClassID
WHEN 'NEWDL' THEN
Case When IsNull(G.PersonID, -1) = -1 Then 'FT Trans'
When IsNull(R.CategoryID, '') = 'A1' Then 'GW Trans'
Else 'Flow'
End
ELSE RecClassID
END
FROM Person P LEFT OUTER JOIN Resource_Categories R ON P.PersonID =
R.PersonID
LEFT OUTER JOIN @tblGatewayPeople G ON P.PersonID = G.PersonID
INNER JOIN tblAttempts A ON A.PersonID = P.PersonID
Insert into @tblTimes (Stage, TheTime) values ('J', GetDate())

--8
--Update Cohorts
Update A SET CohortDWPMonth = D.MonthLong, CohortDWPYear = D.[Year],
CohortJCMonth = J.MonthLong, CohortJCYear = J.[Year],
CohortCalendarMonth = dbo.MonthString(A.StartDate, 0), CohortCalendarYear
= CONVERT(Varchar(4), YEAR(A.StartDate))
FROM tblAttempts A INNER JOIN
dbo.tblDWPMonths D ON A.StartDate >= D.StartDate AND
A.StartDate <= D.EndDate INNER JOIN
dbo.tblJCMonths J ON A.StartDate >= J.StartDate AND
A.StartDate <= J.EndDate
Insert into @tblTimes (Stage, TheTime) values ('K', GetDate())

--Build tblState
--StateChangingActions

INSERT INTO tblState (PersonID, ActionID, State, StartDate, EndDate, Days,
NextActionID, NextState, JSContract)
SELECT TOP 100 PERCENT ThisStateAction.PersonID,
ThisStateAction.ActionID, ThisStateAction.State,
ThisStateAction.DateOfAction AS StartDate,
NextStateAction.DateOfAction AS EndDate, DATEDIFF(day,
ThisStateAction.DateOfAction,
ISNULL(NextStateAction.DateOfAction, GETDATE())) AS Days,
NextStateAction.ActionID AS NextActionID,
NextStateAction.State AS NextState, dbo.Person.RecClassID AS JSContract
FROM vwPreviousAction PreviousAction INNER JOIN
vwStateChangingActions ThisStateAction ON
PreviousAction.NextActionID = ThisStateAction.ActionID AND
(PreviousAction.State <> ThisStateAction.State OR
PreviousAction.State IS NULL) INNER JOIN
dbo.Person ON ThisStateAction.PersonID =
dbo.Person.PersonID LEFT OUTER JOIN
vwNextActionDateCheck vwNextActionDateCheck INNER JOIN
vwStateChangingActions NextStateAction ON
vwNextActionDateCheck.NewDateCheck = NextStateAction.DateCheck
AND vwNextActionDateCheck.PersonID = NextStateAction.PersonID ON
ThisStateAction.PersonID =
vwNextActionDateCheck.PersonID AND
vwNextActionDateCheck.ActionID =
ThisStateAction.ActionID
Insert into @tblTimes (Stage, TheTime) values ('P', GetDate())
--Insert PreviousActionID into tblState
Update tblState SET tblState.PreviousActionID = PreviousState.ActionID
FROM tblState LEFT OUTER JOIN tblState PreviousState ON tblState.ActionID
= PreviousState.NextActionID

Insert into @tblTimes (Stage, TheTime) values ('Q', GetDate())
--Insert AttemptID into tblState
Update S SET S.AttemptID = A.AttemptID
FROM tblState S INNER JOIN tblAttempts A ON S.PersonID = A.PersonID
AND S.StartDate >= A.StartDate
AND S.StartDate <= IsNull(A.EndDate, '29980101')

Insert into @tblTimes (Stage, TheTime) values ('R', GetDate())
--Insert Last ActionID, Last State information and correct the enddate in
tblAttempts
Update A SET A.LastStateActionID = FirstState.ActionID, A.LastState =
FirstState.State,
A.EndDate = Case When FinishingState is null Then Case When
SecondState.ActionID is null Then Null Else FirstState.EndDate End
Else FirstState.StartDate End
FROM tblAttempts A INNER JOIN tblState FirstState
ON A.AttemptID = FirstState.AttemptID
LEFT OUTER JOIN tblState SecondState
ON FirstState.NextActionID = SecondState.ActionID
LEFT OUTER JOIN tblFinishingStates Finishing ON FirstState.State =
Finishing.FinishingState
WHERE SecondState.ActionID is Null or SecondState.AttemptID <>
firstState.AttemptID

Insert into @tblTimes (Stage, TheTime) values ('P', GetDate())
sELECT * FROM @tblTimes

End


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO