Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

tuning the query which is having cursors Expand / Collapse
Author
Message
Posted Wednesday, March 31, 2010 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:09 AM
Points: 189, Visits: 333
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
Post #893751
Posted Wednesday, March 31, 2010 7:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 4,655, Visits: 11,126
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
Post #893768
Posted Thursday, April 8, 2010 10:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 22, 2012 4:46 PM
Points: 77, 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'
Post #899737
Posted Friday, April 9, 2010 9:53 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 4,472, Visits: 6,402
Forums are designed for short, quick, targeted advice since they are 'staffed' by volunteers. This isn't any of those. :) 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 at GMail
Post #900670
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse