September 17, 2010 at 10:10 am
Experts..
I have one proc running poorly...when i went thru code, i saw the below function being called inside a cursor..
First of all , am going to advise them to remove cusror part. but when it comes to this function, i guess this is being called for every record. am i right? if yes, whats the better way to rewrite this function so that it called once for entore table.
CREATE FUNCTION [dbo].[CheckDayMatch] (
@ViewDate as DateTime,
@Freq as CodeType
)
RETURNS Bit AS
BEGIN
/*
This function is used for the nth "Whatever" day in a month clinic frequencies; to evaluate if the
the @ViewDate parameter is a day of the @Freq type parameter.
E.g. @ViewDate = 17 Mar 2004 , @Freq = 3WD Return = 1 (17 Mar 2004 is a 3rd Wednesday)
*/
DECLARE @blnReturn bit
SET @blnReturn = 0
--if it's not a recognised frequency then why continue?
IF @Freq LIKE '_MD' OR
@Freq LIKE '_TuD' OR
@Freq LIKE '_WD' OR
@Freq LIKE '_ThD' OR
@Freq LIKE '_FD' OR
@Freq LIKE '_SD'
BEGIN
DECLARE @DayName NVARCHAR(9)
DECLARE @DayOffset Int
/*
this bit gets the number relating the the required day of week plus the day offset
the day offset is the number that when subtracted from the day of month of the supplied
viewdate gives an answer between 1 and 7 if the viewdate is a match for the pattern.
Got all that? I thought not...
*/
SELECT
@DayName = CASE RIGHT(@Freq, LEN(@Freq)-1)
WHEN 'MD' THEN 'Monday'
WHEN 'TuD' THEN 'Tuesday'
WHEN 'WD' THEN 'Wednesday'
WHEN 'ThD' THEN 'Thursday'
WHEN 'FD' THEN 'Friday'
WHEN 'SD' THEN 'Saturday'
END,
@DayOffset = (CAST(LEFT(@Freq, 1) AS int) - 1) * 7
--check it's the correct day and the correct occurence
IF DATENAME(dw, @ViewDate) = @DayName AND DAY(@ViewDate) - @DayOffset BETWEEN 1 AND 7
BEGIN
SET @blnReturn = 1
END
END
RETURN @blnReturn
END
Regards
September 17, 2010 at 10:16 am
Could you provide the code that is calling this function?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2010 at 10:21 am
Thanks for immediate resposne.. Its being called in side a huge procedure..being called inside a cursor as below.
IF dbo.CheckDayMatch(@ViewDate, @SessionFrequency) = 1
Below is the procedure ,from where the above function being called..
CREATE PROCEDURE [dbo].[sp_AGC]
@ClinicID CODETYPE,
@Stream INTEGER,
@ViewDate DATETIME
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
/*variables assigned by cursors*/
DECLARE @index Integer
DECLARE @StartDate DateTime
DECLARE @SessionStartDate DateTime
DECLARE @SessionFrequency VarChar(10)
DECLARE @SectionStream Integer
DECLARE @AppointmentStream Integer
DECLARE @FrozenStream Integer
DECLARE @SectionStartTime DateTime
DECLARE @AppointmentTime DateTime
DECLARE @FrozenStartTime DateTime
DECLARE @SectionEndTime DateTime
DECLARE @FrozenEndTime DateTime
DECLARE @SectionSlotLength Integer
DECLARE @FrozenReason VarChar(40)
DECLARE @SequenceID CODETYPE
DECLARE @Duration Integer
DECLARE @ClientID CLIENTIDTYPE
DECLARE @ClientFirstname VarChar(20)
DECLARE @ClientSurname VarChar(20)
DECLARE @ClientName VarChar(40)
DECLARE @HCPCode VarChar(10)
DECLARE @HCPFirstname VarChar(20)
DECLARE @HCPSurname VarChar(20)
DECLARE @HCPName VarChar(40)
DECLARE @Activity VarChar(40)
DECLARE @ArrivalTime DateTime
DECLARE @ActualTime DateTime
DECLARE @OutcomeCode VarChar(10)
DECLARE @Outcome VarChar(40)
DECLARE @DNAFlag BIT
DECLARE @Timestamps MONEY
DECLARE @ActualDuration Integer
DECLARE @HasImms BIT
DECLARE @DischargeIndicator Bit
DECLARE @ReferralNumber Int
DECLARE @DischargeReason CODETYPE
DECLARE @UBRN VARCHAR(37)
DECLARE @AmsClinicSectionID INT
DECLARE @Status CODETYPE
SET @AmsClinicSectionID = 2
Declare @AmsAppointment Table (
SequenceID int, GenHCPCode VarChar(10), ClinicID VarChar(10), AppointmentDate DateTime,
IntendedTime DateTime, Stream int, IntendedDuration Int, Location VarChar(10),
AppointmentType VarChar(10), NonClientAppointmentFlag Bit, UBRN varchar(37), AmsClinicSectionID INT, Status VarChar(10)
)
SET @Index = 0
/*important!! Add this when using a temp_table*/
SET nocount on
/* Build temp table to store empty slots and appointments*/
CREATE TABLE #Temp_AmsClinicList (SequenceID VarChar(10), StartTime DateTime,
Duration int, Stream Int, HCPCode VarChar(10), HCP VarChar(40),
ClientID VarChar(15), ClientName VarChar(40), Activity VarChar(40),
ArrivalTime DateTime, ActualTime DateTime, OutcomeCode VarChar(10),
Outcome VarChar(40), FrozenReason VarChar(40), Timestamps MONEY,
DischargeIndicator Bit, CanBeDischarge Bit, ReferralNumber Int,
OutcomeDNA Bit, ActualDuration int, UBRN varchar(37), HasImms bit, AmsClinicSectionID INT, Status VarChar(10)
)
/*Use cursor for retrieving template session*/
DECLARE SessionCurs CURSOR FAST_FORWARD FOR
Select StartDate, Frequency
FROM dbo.AmsClinicTemplateSession
WHERE ClinicID=@ClinicID AND StartDate <= @ViewDate
AND IsNull(EndDate, DateAdd(d, 1, @ViewDate)) >= @ViewDate
FOR READ ONLY
OPEN SessionCurs
Fetch SessionCurs into
@SessionStartDate, @SessionFrequency
/*Check correct template*/
While (@@fetch_status = 0)
BEGIN
If @SessionFrequency = 'D1'
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
If @SessionFrequency = 'D2'
BEGIN
If DateName(dw, @ViewDate) <> 'Saturday' AND DateName(dw, @ViewDate) <> 'Sunday'
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
If @SessionFrequency = 'W1'
BEGIN
If DateDiff(d, @SessionStartDate, @ViewDate) % 7 = 0
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
If @SessionFrequency = 'W2'
BEGIN
If DateDiff(d, @SessionStartDate, @ViewDate)% 14 = 0
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
If @SessionFrequency = 'W3'
BEGIN
If DateDiff(d, @SessionStartDate, @ViewDate) % 21 = 0
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
If @SessionFrequency = 'W4'
BEGIN
If DateDiff(d, @SessionStartDate, @ViewDate) % 28 = 0
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
If @SessionFrequency = 'W5'
BEGIN
If DateDiff(d, @SessionStartDate, @ViewDate) % 35 = 0
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
If @SessionFrequency = 'W6'
BEGIN
If DateDiff(d, @SessionStartDate, @ViewDate) % 42 = 0
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
If @SessionFrequency = 'W7'
BEGIN
If DateDiff(d, @SessionStartDate, @ViewDate) % 49 = 0
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
If @SessionFrequency = 'W8'
BEGIN
If DateDiff(d, @SessionStartDate, @ViewDate) % 56 = 0
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
If @SessionFrequency = 'M1'
BEGIN
IF DAY(@SessionStartDate) <= 7
BEGIN
IF DAY(@ViewDate) <= 7
BEGIN
IF DATENAME(dw, @SessionStartDate) = DATENAME(dw, @ViewDate)
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
END
IF DAY(@SessionStartDate) > 7 AND DAY(@SessionStartDate) <= 14
BEGIN
IF DAY(@ViewDate) > 7 AND DAY(@ViewDate) <= 14
BEGIN
IF DATENAME(dw, @SessionStartDate) = DATENAME(dw, @ViewDate)
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
END
IF DAY(@SessionStartDate) > 14 AND DAY(@SessionStartDate) <= 21
BEGIN
IF DAY(@ViewDate) > 14 AND DAY(@ViewDate) <= 21
BEGIN
IF DATENAME(dw, @SessionStartDate) = DATENAME(dw, @ViewDate)
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
END
IF DAY(@SessionStartDate) > 21 AND DAY(@SessionStartDate) <= 28
BEGIN
IF DAY(@ViewDate) > 21 AND DAY(@ViewDate) <= 28
BEGIN
IF DATENAME(dw, @SessionStartDate) = DATENAME(dw, @ViewDate)
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
END
END
If @SessionFrequency = '1MD' OR @SessionFrequency = '2MD' OR @SessionFrequency = '3MD' OR @SessionFrequency = '4MD' OR @SessionFrequency = '5MD'
OR @SessionFrequency = '1TuD' OR @SessionFrequency = '2TuD' OR @SessionFrequency = '3TuD' OR @SessionFrequency = '4TuD' OR @SessionFrequency = '5TuD'
OR @SessionFrequency = '1WD' OR @SessionFrequency = '2WD' OR @SessionFrequency = '3WD' OR @SessionFrequency = '4WD' OR @SessionFrequency = '5WD'
OR @SessionFrequency = '1ThD' OR @SessionFrequency = '2ThD' OR @SessionFrequency = '3ThD' OR @SessionFrequency = '4ThD' OR @SessionFrequency = '5ThD'
OR @SessionFrequency = '1FD' OR @SessionFrequency = '2FD' OR @SessionFrequency = '3FD' OR @SessionFrequency = '4FD' OR @SessionFrequency = '5FD'
OR @SessionFrequency = '1SD' OR @SessionFrequency = '2SD' OR @SessionFrequency = '3SD' OR @SessionFrequency = '4SD' OR @SessionFrequency = '5SD'
BEGIN
IF dbo.CheckDayMatch(@ViewDate, @SessionFrequency) = 1
BEGIN
Select @index = 1
Select @StartDate = @SessionStartDate
END
END
Fetch SessionCurs into
@SessionStartDate, @SessionFrequency
END
CLOSE SessionCurs
DEALLOCATE SessionCurs
If @index = 1
BEGIN
If @Stream <> 0
BEGIN
DECLARE SectionCurs CURSOR FAST_FORWARD FOR
SELECT Stream, StartTime, EndTime, SlotLength
FROM dbo.AmsClinicTemplateSection
WHERE ClinicID = @ClinicID and StartDate = @StartDate and Stream = @Stream
ORDER BY StartTime, EndTime
FOR READ ONLY
END
ELSE
BEGIN
DECLARE SectionCurs CURSOR FAST_FORWARD FOR
SELECT Stream, StartTime, EndTime, SlotLength
FROM dbo.AmsClinicTemplateSection
WHERE ClinicID = @ClinicID AND StartDate = @StartDate
ORDER BY Stream, StartTime, EndTime
FOR READ ONLY
END
OPEN SectionCurs
FETCH SectionCurs into
@SectionStream, @SectionStartTime, @SectionEndTime, @SectionSlotLength
Declare @Time DateTime
While (@@fetch_status = 0)
BEGIN
Select @Time = @SectionStartTime
While (@Time < @SectionEndTime)
BEGIN
INSERT #Temp_AmsClinicList (SequenceID, StartTime, Duration, Stream, HCPCode, HCP, ClientID, ClientName,
Activity, ArrivalTime, ActualTime, OutcomeCode, Outcome, FrozenReason, Timestamps, DischargeIndicator,
CanBeDischarge, ReferralNumber, OutcomeDNA, ActualDuration, UBRN)
VALUES (NULL, @Time, @SectionSlotLength, @SectionStream, NULL, '- None -', NULL, 'Vacant',
'- None -', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL)
Select @Time = DateAdd(n, @SectionSlotLength, @Time)
END
FETCH SectionCurs into
@SectionStream, @SectionStartTime, @SectionEndTime, @SectionSlotLength
END
CLOSE SectionCurs
DEALLOCATE SectionCurs
/*use cursor for appointments retrieved*/
BEGIN
INSERT INTO @AmsAppointment (
SequenceID, GenHCPCode , ClinicID , AppointmentDate ,
IntendedTime , Stream , IntendedDuration , Location ,
AppointmentType , NonClientAppointmentFlag , UBRN, AMSClinicSectionID, Status )
--take into account the intended duration can span over from the previous day
--select previous days first that span over, the start time will be midnight for the current day
--also calculated the duration that is left over for today as we want to show it upto its end point on the clinic list view
SELECT SequenceID, GenHCPCode, ClinicID, AppointmentDate, dbo.nicetime('00:00:00.000') AS IntendedTime, --intended time starts at midnight as its spanned across days
Stream,
--get the duration left over today
(datediff(n, @ViewDate, dateadd(minute,IntendedDuration,dateadd(minute, datepart(minute,intendedtime),(dateadd(hour,datepart(hour,intendedtime),appointmentdate)))))) as IntendedDuration,
Location, AppointmentType, NonClientAppointmentFlag, UBRN, AMSClinicSectionID, Status
From dbo.AmsAppointment
Where ClinicID = @ClinicID
--does not start on the view date
AND dbo.NiceDate(AppointmentDate)<> dbo.nicedate(@ViewDate)
--and does not start after view date
AND datediff(n, dbo.nicedate(AppointmentDate), dbo.nicedate(@ViewDate)) > 0
--but starts before the viewdate and spans across to the view date
AND datediff(n, dateadd(minute,IntendedDuration,dateadd(minute, datepart(minute,intendedtime),(dateadd(hour,datepart(hour,intendedtime),appointmentdate)))), @ViewDate) <0
AND CancellationDateTime IS NULL
AND Stream <> 0
--other appointments that start on the same date
UNION
SELECT SequenceID, GenHCPCode, ClinicID, AppointmentDate, IntendedTime,
Stream, IntendedDuration, Location, AppointmentType, NonClientAppointmentFlag, UBRN, AMSClinicSectionID, Status
FROM dbo.AmsAppointment
WHERE AmsAppointment.ClinicID = @ClinicID
AND AmsAppointment.AppointmentDate = @ViewDate
AND CancellationDateTime IS NULL
AND Stream <> 0
DECLARE AppointmentCurs CURSOR FAST_FORWARD FOR
SELECT DISTINCT aa.SequenceID, aa.Stream, aa.GenHCPCode,
GenPerson.Firstname, GenPerson.Surname, dbo.nicetime(aa.IntendedTime) as IntendedTime, aa.IntendedDuration,
AmsAppointmentContact.ClientID,ISNULL(ClientName.GivenName1,' '), ClientName.Surname,
AmsAppointmentContact.ArrivalTime, AmsAppointmentContact.ActualTime,
AmsAppointmentContact.Outcome, AmsOutcome.CodeDescription, Convert(MONEY, AmsAppointmentContact.Timestamps),
AmsOutcome.DischargeIndicator, AmsOutcome.DNA, AmsAppointmentContact.ActualDuration, aa.UBRN, --aa.AmsClinicSectionID,
CASE WHEN csa.SequenceID IS NULL THEN 0 ELSE 1 END AS HasImms, aa.AmsClinicSectionID, aa.Status
FROM @AmsAppointment AS aa
LEFT JOIN dbo.AmsAppointmentContact ON aa.SequenceID = AmsAppointmentContact.SequenceID
LEFT JOIN dbo.ClientName ON AmsAppointmentContact.ClientID = ClientName.ClientID
INNER JOIN dbo.ClientAliasType WITH (NOLOCK) ON ClientAliasType.Code = ClientName.AliasType AND ClientAliasType.SpineCode = 'L'
LEFT JOIN dbo.AmsOutcome WITH (NOLOCK) ON AmsAppointmentContact.Outcome = AmsOutcome.Code
LEFT JOIN dbo.GenPerson ON aa.GenHCPCode COLLATE DATABASE_DEFAULT = GenPerson.GenPersonID COLLATE DATABASE_DEFAULT
LEFT JOIN dbo.ChdSchedulerAppointment csa
JOIN dbo.ChdSchedulerAppointmentImmunisationLink csail ON csa.SequenceID = csail.SchedulerAppSeqID
JOIN dbo.ChdClientImmunisation csi ON csail.ImmsSeqID = csi.SequenceID AND ISNULL(csi.Deleted, 0) = 0
ON aa.SequenceID = csa.AmsSeqID
WHERE (aa.Stream = @Stream OR @Stream = 0)
AND AmsAppointmentContact.CancellationDateTime IS NULL
ORDER BY IntendedTime
FOR READ ONLY
END
OPEN AppointmentCurs
FETCH AppointmentCurs Into
@SequenceID, @AppointmentStream, @HCPCode, @HCPFirstname, @HCPSurname, @AppointmentTime,
@Duration, @ClientID, @ClientFirstname, @ClientSurname, @ArrivalTime, @ActualTime, @OutcomeCode,
@Outcome, @Timestamps, @DischargeIndicator, @DNAFlag, @ActualDuration, @UBRN, @HasImms, @AmsClinicSectionID, @Status
WHILE (@@Fetch_status = 0)
BEGIN
Select @Activity = NULL
IF (@ClientID IS NULL) AND (@UBRN IS NOT NULL)
SET @ClientName = 'CABReserved'
ELSE
SET @ClientName = @ClientFirstname + ' ' + @ClientSurname
Select @HCPName = @HCPFirstname + ' ' + @HCPSurname
Set @activity = (Select Top 1 GenActivity.CodeDescription
From dbo.GenActivity WITH (NOLOCK)
Where Code IN
(Select GenActivityCode
From dbo.AmsAppointmentContactActivity
Where SequenceID = @SequenceID and ClientID = @ClientID))
DELETE #Temp_AmsClinicList
WHERE SequenceID Is NULL And Stream = @AppointmentStream
AND ( dbo.nicetime(StartTime) BETWEEN dbo.nicetime(@AppointmentTime) AND dbo.nicetime(DateAdd(n, isnull(@ActualDuration, @Duration)-1, @AppointmentTime))
OR StartTime = @AppointmentTime)
SELECT @ReferralNumber = ReferralID FROM dbo.AmsAppointmentContact WHERE ClientID = @ClientID AND SequenceID = @SequenceID
IF @ReferralNumber = 0
INSERT #Temp_AmsClinicList(SequenceID, StartTime, Duration, Stream, HCPCode, HCP, ClientID, ClientName,
Activity, ArrivalTime, ActualTime, OutcomeCode, Outcome, FrozenReason, Timestamps, DischargeIndicator,
CanBeDischarge, ReferralNumber, OutcomeDNA, ActualDuration, UBRN, HasImms, AmsClinicSectionID, Status)
VALUES(@SequenceID, @AppointmentTime, @Duration, @AppointmentStream, @HCPCode, @HCPName, @ClientID, @ClientName,
IsNull(@Activity, '- None -'), @ArrivalTime, @ActualTime, @OutcomeCode, IsNull(@Outcome, '- Outcome -'), NULL,
@Timestamps, @DischargeIndicator, 0, @ReferralNumber, ISNULL(@DNAFlag, 0), @ActualDuration, @UBRN, @HasImms, @AmsClinicSectionID, @Status)
ELSE
BEGIN
SELECT @DischargeReason = DischargeReason FROM dbo.AmsReferral WHERE ClientID = @ClientID AND ReferralNumber = @ReferralNumber
IF @DischargeReason = '' OR @DischargeReason IS NULL
INSERT #Temp_AmsClinicList(SequenceID, StartTime, Duration, Stream, HCPCode, HCP, ClientID, ClientName,
Activity, ArrivalTime, ActualTime, OutcomeCode, Outcome, FrozenReason, Timestamps, DischargeIndicator,
CanBeDischarge, ReferralNumber, OutcomeDNA, ActualDuration, UBRN, HasImms, AmsClinicSectionID, Status)
VALUES(@SequenceID, @AppointmentTime, @Duration, @AppointmentStream, @HCPCode, @HCPName, @ClientID, @ClientName,
IsNull(@Activity, '- None -'), @ArrivalTime, @ActualTime, @OutcomeCode, IsNull(@Outcome, '- Outcome -'), NULL,
@Timestamps, @DischargeIndicator, 1, @ReferralNumber, ISNULL(@DNAFlag, 0), @ActualDuration, @UBRN, @HasImms, @AmsClinicSectionID, @Status)
ELSE
INSERT #Temp_AmsClinicList(SequenceID, StartTime, Duration, Stream, HCPCode, HCP, ClientID, ClientName,
Activity, ArrivalTime, ActualTime, OutcomeCode, Outcome, FrozenReason, Timestamps, DischargeIndicator,
CanBeDischarge, ReferralNumber, OutcomeDNA, ActualDuration, UBRN, HasImms, AmsClinicSectionID, Status)
VALUES(@SequenceID, @AppointmentTime, @Duration, @AppointmentStream, @HCPCode, @HCPName, @ClientID, @ClientName,
IsNull(@Activity, '- None -'), @ArrivalTime, @ActualTime, @OutcomeCode, IsNull(@Outcome, '- Outcome -'), NULL,
@Timestamps, @DischargeIndicator, 0, @ReferralNumber, ISNULL(@DNAFlag, 0), @ActualDuration, @UBRN, @HasImms, @AmsClinicSectionID, @Status)
/*end if*/
END/* end if */
Fetch AppointmentCurs Into
@SequenceID, @AppointmentStream, @HCPCode, @HCPFirstname, @HCPSurname, @AppointmentTime, @Duration,
@ClientID, @ClientFirstname, @ClientSurname, @ArrivalTime, @ActualTime, @OutcomeCode, @Outcome,
@Timestamps, @DischargeIndicator, @DNAFlag, @ActualDuration, @UBRN, @HasImms, @AmsClinicSectionID, @Status
END
CLOSE AppointmentCurs
DEALLOCATE AppointmentCurs
/*use cursor for frozen slots retrieved
Frozen slots are union of slots from template and clinicstreamfrozen table*/
BEGIN
If @Stream <> 0
BEGIN
DECLARE FrozenCurs CURSOR FAST_FORWARD FOR
SELECT AmsClinicTemplateStreamFrozen.Stream,
AmsClinicTemplateStreamFrozen.StartTime,
AmsClinicTemplateStreamFrozen.EndTime, AmsFrozenReason.CodeDescription
FROM dbo.AmsClinicTemplateStreamFrozen
LEFT JOIN dbo.AmsFrozenReason WITH (NOLOCK) ON AmsClinicTemplateStreamFrozen.AmsFrozenReasonCode = AmsFrozenReason.Code
WHERE AmsClinicTemplateStreamFrozen.ClinicID=@ClinicID
AND AmsClinicTemplateStreamFrozen.StartDate = @StartDate
AND AmsClinicTemplateStreamFrozen.Stream = @Stream
UNION
SELECT AmsClinicStreamFrozen.Stream, AmsClinicStreamFrozen.StartTime,
AmsClinicStreamFrozen.EndTime, AmsFrozenReason.CodeDescription
FROM dbo.AmsClinicStreamFrozen
LEFT JOIN AmsFrozenReason WITH (NOLOCK) ON AmsClinicStreamFrozen.AmsFrozenReasonCode = AmsFrozenReason.Code
WHERE AmsClinicStreamFrozen.ClinicID = @ClinicID
AND AmsClinicStreamFrozen.ClinicDate = @ViewDate
AND AmsClinicStreamFrozen.Stream = @Stream
FOR READ ONLY
END
ELSE
BEGIN
DECLARE FrozenCurs CURSOR FAST_FORWARD FOR
SELECT AmsClinicTemplateStreamFrozen.Stream,
AmsClinicTemplateStreamFrozen.StartTime,
AmsClinicTemplateStreamFrozen.EndTime, AmsFrozenReason.CodeDescription
FROM dbo.AmsClinicTemplateStreamFrozen
LEFT JOIN dbo.AmsFrozenReason WITH (NOLOCK) ON AmsClinicTemplateStreamFrozen.AmsFrozenReasonCode = AmsFrozenReason.Code
WHERE AmsClinicTemplateStreamFrozen.ClinicID=@ClinicID
AND AmsClinicTemplateStreamFrozen.StartDate = @StartDate
UNION
SELECT AmsClinicStreamFrozen.Stream, AmsClinicStreamFrozen.StartTime,
AmsClinicStreamFrozen.EndTime, AmsFrozenReason.CodeDescription
FROM dbo.AmsClinicStreamFrozen
LEFT JOIN dbo.AmsFrozenReason WITH (NOLOCK) ON AmsClinicStreamFrozen.AmsFrozenReasonCode = AmsFrozenReason.Code
WHERE AmsClinicStreamFrozen.ClinicID = @ClinicID
AND AmsClinicStreamFrozen.ClinicDate = @ViewDate
FOR READ ONLY
END
END
OPEN FrozenCurs
Fetch FrozenCurs INTO
@FrozenStream, @FrozenStartTime, @FrozenEndTime, @FrozenReason
WHILE(@@fetch_status = 0)
BEGIN
DELETE #Temp_AmsClinicList
WHERE
SequenceID IS NULL
AND Stream = @FrozenStream
AND dbo.TimeFromDateTime(StartTime) >= dbo.TimeFromDateTime(@FrozenStartTime)
AND dbo.TimeFromDateTime(StartTime) < dbo.TimeFromDateTime(@FrozenEndTime)
INSERT #Temp_AmsClinicList(SequenceID, StartTime, Duration, Stream, HCPCode, HCP, ClientID, ClientName,
Activity, ArrivalTime, ActualTime, OutcomeCode, Outcome, FrozenReason, Timestamps, DischargeIndicator,
CanBeDischarge, ReferralNumber, OutcomeDNA, ActualDuration, UBRN)
VALUES('FROZEN', @FrozenStartTime, DateDiff(n, @FrozenStartTime, @FrozenEndTime), @FrozenStream,
NULL, NULL, NULL, NULL, NULL, @FrozenEndTime, NULL, NULL, NULL, @FrozenReason, NULL,
NULL, NULL, NULL, 0, NULL, NULL)
Fetch FrozenCurs INTO
@FrozenStream, @FrozenStartTime, @FrozenEndTime, @FrozenReason
END
CLOSE FrozenCurs
DEALLOCATE FrozenCurs
END
/*Select results on the temp table*/
SELECT SequenceID, Stream, Convert(char(8), StartTime, 108) AS StartTime,
HCPCode, HCP, Duration, ClientID, isnull(ClientName, '(Unnamed Client)') as ClientName, Activity, ArrivalTime,
ActualTime, Outcome, FrozenReason, Timestamps, DischargeIndicator,
CanBeDischarge, ReferralNumber, OutcomeDNA, ActualDuration, UBRN, HasImms, AmsClinicSectionID, Status
FROM #Temp_AmsClinicList
ORDER BY StartTime, Stream, Duration ASC, SequenceID
Thanks a lot for your time..
September 17, 2010 at 1:44 pm
CirquedeSQLeil --- Got any clues on this?
have a great weekend...
Thanks
September 17, 2010 at 3:20 pm
I spotted 4 c.u.r.s.o.r.s being part of that sproc but you already mentioned you're going to work on it. I think it's the major part of the poor performance.
The function [dbo].[CheckDayMatch] itself does "just" a calculation based on the parameter provided, without performing any table access. So, it's not THAT bad. But I would still consider one of the following concepts:
Option 1:
Add a computed persisted column to get the day of week.
Reason: If you'd get the day of week based on the [Frequency] column using DATEDIFF(dd,0,Frequency)%7+1 instead of 'Monday' or 'Tuesday'
in combination with the computed column holding a numeric value based on
WHEN 'MD' THEN 1
WHEN 'TuD' THEN 2
instead of 'Monday' or 'Tuesday' you'd get a more robust sproc.
If you want to see the current sproc to fail simply change the language to something different than English (e.g. SET LANGUAGE German). All of a sudden the function DATENAME(dw, @ViewDate) will return 'Montag' instead of 'Monday'.The setting of @DayName won't match anymore... Ouch!
Option 2:
Once the c.u.r.s.o.r.s are replaced with a set based solution (still calling the function) I'd replace the function with a CROSS APPLY syntax.
The first option would allow to get rid of a function call for each row but you'd have to pay the price of addtl. disc space (if that's an issue). I wouldn't expect any benefit from indexing since the distribution most probably would lead to ignore the index (1 out of 7 = about 13% per value...)
The second option won't require another column but would still be a call for each row behind the scene.
I would test both solutions as well as the function and see which one fits best (including the overall scenario).
But the most important issue: get rid of those RBAR code!!!
September 17, 2010 at 3:22 pm
Thanks for stepping in Lutz
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2010 at 3:38 pm
CirquedeSQLeil (9/17/2010)
Thanks for stepping in Lutz
[cringingness ON]
I'm glad being recognized by an SSChampion to be of some sort of help. 😀
[cringingness OFF]
More serious: Congrats for being the 6th person climbing up to that level!!!!!!
September 17, 2010 at 3:44 pm
LutzM (9/17/2010)
CirquedeSQLeil (9/17/2010)
Thanks for stepping in Lutz[cringingness ON]
I'm glad being recognized by an SSChampion to be of some sort of help. 😀
[cringingness OFF]
More serious: Congrats for being the 6th person climbing up to that level!!!!!!
Thank you.
I find you to be highly helpful quite often.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 18, 2010 at 5:08 am
CirquedeSQLeil (9/17/2010)
LutzM (9/17/2010)
...
[...] I find you to be highly helpful quite often.
:blush:
September 20, 2010 at 6:33 am
September 21, 2010 at 3:35 am
Hello,
looking at your code there is something I don't understand, let's me explain:
You define your first cursor, SessionCurs,
DECLARE SessionCurs CURSOR FAST_FORWARD FOR
Select StartDate, Frequency
FROM dbo.AmsClinicTemplateSession
WHERE ClinicID=@ClinicID AND StartDate <= @ViewDate
AND IsNull(EndDate, DateAdd(d, 1, @ViewDate)) >= @ViewDate
FOR READ ONLY
and then you check if any of your rows meet some criteria; if so you code
Select @index = 1
Select @StartDate = @SessionStartDate
That is, you find some records to be processed and keep this information in the variable @Index. Then you keep the value of @StartDate to use it next.
But StartDate in your cursor has not a fixed value, you are keeping all the values for StartDate and use the last value found; what is the meaning of this?, you are taking a random value, right?. Why do you take a random value?. Why not exiting the loop after finding the first row matching the criteria?.
What I'm missing?
Regards,
Francesc
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply