SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


tuning the query which is having cursors


tuning the query which is having cursors

Author
Message
ekknaveen
ekknaveen
SSC Eights!
SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)SSC Eights! (931 reputation)

Group: General Forum Members
Points: 931 Visits: 343
There is one procedure which is written with cursors within cursors. The code is copied in this. Can anyone tell me how we can tune the procedure so that it runs faster.


ALTER PROCEDURE [dbo].[PerStat_Starts]

----------------------------------------------------------------------------------
AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

-- declare variables
DECLARE @EquipmentNumber_FK BIGINT
DECLARE @SNRule_FK INT
DECLARE @CEN_FK INT
DECLARE @Attribute VARCHAR(50)
DECLARE @LimitFaultsPerStarts DECIMAL (18, 4)
DECLARE @MessageType_FK INT
DECLARE @RemoteMonitoringType_FK VARCHAR(10)
DECLARE @EquipmentClass_FK VARCHAR(10)
DECLARE @AnalyzerType_FK VARCHAR(10)
DECLARE @StatisticsPeriodStartTime DATETIME
DECLARE @StatisticsPeriodEndTime DATETIME
DECLARE @StatisticsStartTime DATETIME
DECLARE @StatisticsEndTime DATETIME
DECLARE @TotalStarts INT
DECLARE @StartsInPeriod INT
DECLARE @StopLoop BIT
DECLARE @LatestPeriod BIT
DECLARE @FloorNbr INT
DECLARE @PeriodHours INT

DECLARE @CurrentDateTime DATETIME
SET @CurrentDateTime = GetDate()

DECLARE @LimitFixedStarts INT
SET @LimitFixedStarts = 10

-- declare and initialize a variable to hold @@ERROR.
DECLARE @ErrorSave INT
SET @ErrorSave = 0

-- declare and initialize a variable to hold @@ROWCOUNT.
DECLARE @RowsAffectedTotal INT
SET @RowsAffectedTotal = 0
DECLARE @RowsAffected INT

-- declare a variable to hold description of the current operation
DECLARE @Operation VARCHAR(500)

SELECT @ErrorSave = @@ERROR
SET @Operation = 'START'
EXEC InsertTaskLog_p 1,'SNRuleFaultsPerStat_Starts_FloorByFloor_p', @Operation, 0, @ErrorSave

-- get service need rules for equipments from table SNRuleFaultsPerStat:
-- selects only rows with LimitFaultsPerFixedStarts > 0
DECLARE SNRuleTable_cursor CURSOR FOR
SELECT DISTINCT
E.EquipmentNumber_FK,
E.Floor,
P.SNRule_PK,
P.AttributeStat,
P.CEN_FK,
P.LimitCoefficient,
P.MessageType_FK,
P.EquipmentClass_FK,
P.RemoteMonitoringType_FK,
P.AnalyzerType_FK
FROM
SNRuleFaultsPerStat P
INNER JOIN Event E ON P.CEN_FK = E.CEN AND P.RemoteMonitoringType_FK = E.RemoteMonitoringType_FK
INNER JOIN Equipment EQ ON P.EquipmentClass_FK = EQ.EquipmentClass_FK AND P.RemoteMonitoringType_FK = EQ.RemoteMonitoringType_FK AND P.AnalyzerType_FK = EQ.AnalyzerType_FK AND E.EquipmentNumber_FK = EQ.EquipmentNumber
WHERE
ISNULL(LimitCoefficient, 0) > 0 AND
AttributeStat = 'STARTS' AND
E.StartTimestamp BETWEEN DATEADD(month, -1, @CurrentDateTime) AND DATEADD(day, 1, @CurrentDateTime) AND
P.FloorByFloor = '1'

OPEN SNRuleTable_cursor

FETCH NEXT FROM SNRuleTable_cursor INTO
@EquipmentNumber_FK,
@FloorNbr,
@SNRule_FK,
@Attribute,
@CEN_FK,
@LimitFaultsPerStarts,
@MessageType_FK,
@EquipmentClass_FK,
@RemoteMonitoringType_FK,
@AnalyzerType_FK
WHILE @@FETCH_STATUS = 0
BEGIN

SET @TotalStarts = 0
SET @StopLoop = 0
SET @LatestPeriod = 1

-- get count of total starts for the equipment:
DECLARE TotalStats_cursor CURSOR FOR
SELECT
StatisticsStartTime,
StatisticsEndTime,
Starts
FROM
TotalStats_FloorByFloor_v
WHERE
EquipmentNumber_FK = @EquipmentNumber_FK AND
FloorNbr = @FloorNbr
ORDER BY
StatisticsEndTime DESC

OPEN TotalStats_cursor

FETCH NEXT FROM TotalStats_cursor INTO
@StatisticsPeriodStartTime,
@StatisticsPeriodEndTime,
@StartsInPeriod
WHILE @@FETCH_STATUS = 0 AND @StopLoop = 0
BEGIN

IF @LatestPeriod = 1
BEGIN
-- save the StatisticsEndTime for setting the timelimits when counting events
SET @LatestPeriod = 0
SET @StatisticsEndTime = @StatisticsPeriodEndTime
END

-- loop periods until the sum of total starts > Limit values (minimun period)
SET @TotalStarts = @TotalStarts + @StartsInPeriod
IF @TotalStarts > @LimitFixedStarts
SET @StopLoop = 1

IF @StopLoop = 1
BEGIN

-- now: total starts > Limit values

-- save the StatisticsStartTime for setting the timelimits when counting events
SET @StatisticsStartTime = @StatisticsPeriodStartTime
SET @PeriodHours = DATEDIFF(hour, @StatisticsStartTime, @StatisticsEndTime)

EXEC @RowsAffected = SN_EventCount_p
'SNRuleFaultsPerStat',
'SN_FPS_F',
@CurrentDateTime,
@EquipmentNumber_FK,
@SNRule_FK,
@CEN_FK,
@Attribute,
@MessageType_FK,
@RemoteMonitoringType_FK,
@EquipmentClass_FK,
@AnalyzerType_FK,
@StatisticsStartTime,
@StatisticsEndTime,
@LimitFixedStarts,
@LimitFaultsPerStarts,
NULL, -- @LimitFaultsPerPeriodHours
NULL, -- @LimitDurationPerPeriod
@PeriodHours,
NULL, -- @RequiredIntervalInMinutes
@TotalStarts,
1, -- @FloorByFloor
@FloorNbr

SET @RowsAffectedTotal = @RowsAffectedTotal + @RowsAffected

END

-- Get the row in TotalStats_cursor
FETCH NEXT FROM TotalStats_cursor INTO
@StatisticsPeriodStartTime,
@StatisticsPeriodEndTime,
@StartsInPeriod
END

CLOSE TotalStats_cursor
DEALLOCATE TotalStats_cursor

-- Get the row in SNRuleTable_cursor
FETCH NEXT FROM SNRuleTable_cursor INTO
@EquipmentNumber_FK,
@FloorNbr,
@SNRule_FK,
@Attribute,
@CEN_FK,
@LimitFaultsPerStarts,
@MessageType_FK,
@EquipmentClass_FK,
@RemoteMonitoringType_FK,
@AnalyzerType_FK
END

CLOSE SNRuleTable_cursor
DEALLOCATE SNRuleTable_cursor

SET @Operation = 'Insert new SNRuleFaultsPerStat_Starts_FloorByFloor_p service needs.'
EXEC InsertTaskLog_p 0,'SNRuleFaultsPerStat_Starts_FloorByFloor_p', @Operation, @RowsAffectedTotal, @ErrorSave

SET @Operation = 'END'
EXEC InsertTaskLog_p 1,'SNRuleFaultsPerStat_Starts_FloorByFloor_p', @Operation, 0, @ErrorSave

---
SET NOCOUNT OFF


Thanks in advance
NAveen
Gianluca Sartori
Gianluca Sartori
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43067 Visits: 13367
I don't think you will get any useful replies this way. Many things are missing.
You're not saying which tables are involved and what they look like, and, first of all, you're not saying what you're after.
Your code does lots of calls to other stored procedures, but you didn't include the code.

I suggest that you cut down this code into pieces and try to get rid of the cursors, using set-based code instead.

If you want advice on how to go down that road, don't simply post your code and wait for us to tune it for you. You'd better find the most problematic part of the procedure and ask for help on how to turn it into set based updates.

You may find useful taking a look at the article linked in my signature.

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
epriddy
epriddy
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 110
Without knowing your output, here's some things that jump out to me as typically costly syntax:
- Replace CURSORS with Set-based logic (as the previous post suggested), or, try a LOOP instead
- Do you need the "Select distinct" in your cursor? ... Distinct is a more expensive command
- For Where ISNULL(LimitCoefficient, 0) > 0 ... Could this instead read "where LimitCoefficient > 0" (preventing RBAR)
- Can parts of your where criteria be done on the tables prior to the joins by including them as Virtual tables? (see example below)
- Is a Order by necessary? .... For millions of rows this is VERY costly

Try running your execution plan to see if you can identify a particular task that is taking the bulk of the processing time

Here's a VT example:

declare @StartDate datetime
set @StartDate = DATEADD(month, -1, @CurrentDateTime)

declare @EndDate datetime
set @EndDate = DATEADD(day, 1, @CurrentDateTime)

SELECT DISTINCT
E.EquipmentNumber_FK,
E.Floor,
P.SNRule_PK,
P.AttributeStat,
P.CEN_FK,
P.LimitCoefficient,
P.MessageType_FK,
P.EquipmentClass_FK,
P.RemoteMonitoringType_FK,
P.AnalyzerType_FK
FROM SNRuleFaultsPerStat P
INNER JOIN
(Select EquipmentNumber_FK,
[Floor],
Cen,
RemoteMonitoringType_FK
from Event
where StartTimestamp BETWEEN @StartDate AND @EndDate
) E
ON P.CEN_FK = E.CEN
AND P.RemoteMonitoringType_FK = E.RemoteMonitoringType_FK
INNER JOIN Equipment EQ
ON P.EquipmentClass_FK = EQ.EquipmentClass_FK
AND P.RemoteMonitoringType_FK = EQ.RemoteMonitoringType_FK
AND P.AnalyzerType_FK = EQ.AnalyzerType_FK
AND E.EquipmentNumber_FK = EQ.EquipmentNumber
WHERE
ISNULL(LimitCoefficient, 0) > 0 AND
AttributeStat = 'STARTS' AND
--E.StartTimestamp BETWEEN DATEADD(month, -1, @CurrentDateTime) AND DATEADD(day, 1, @CurrentDateTime) AND
P.FloorByFloor = '1'
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57989 Visits: 8824
Forums are designed for short, quick, targeted advice since they are 'staffed' by volunteers. This isn't any of those. Smile It could take many hours of time to help you refactor this sproc, and I advise you to look at getting some professional help to do it.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search