Need help replacing poor performing cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need help replacing poor performing cursor

Author

Topic

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-03-04 : 08:18:51

I am always reading nr’s signature line that says: “Cursors are useful if you don't know sql.” I guess I don’t know sql because I can’t think of a way to get rid of a poor performing cursor. The cursor’s select statement finds all outages that have impacted certain locations. Each ticket can have multiple locations impacted and different start and end times for each location. I am trying to find the duration per hour a ticket/location was impacted, for example: Outage 1 impacted location 1 between 1/1/2003 5:25am and 1/1/2003 6:59am, location 2 was impacted between
between 1/1/2003 5:45am and 1/1/2003 6:15am. Outage 2 impacted location 5 between 1/1/2003 11:00am and 1/1/2003 11:47am. Thanks in advance for any help!

ticket_id, location_id, day, hour, outage_minutes
1, 1, 1/1/2003, 5, 35,
1, 1, 1/1/2003, 6, 60,
1, 2, 1/1/2003, 5, 15,
1, 2, 1/1/2003, 6, 30,
2, 5, 1/1/2003, 11, 47,

DECLARE CRS_HOURLY_IMPACT CURSOR
FOR
SELECT TICKET_ID,LOCATION_ID, OUTAGE_START, OUTAGE_END
FROM PROBLEMS

OPEN CRS_HOURLY_IMPACT

DECLARE @Ticket_id VARCHAR(50)
DECLARE @Location_id VARCHAR(50)
DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
DECLARE @TotalOutageHours INT
DECLARE @Counter INT
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

FETCH NEXT FROM CRS_HOURLY_IMPACT INTO @Ticket_id, @Location_id, @StartTime, @EndTime

WHILE @@fetch_status = 0 BEGIN

SET @TotalOutageHours = datediff(hh, @StartTime, @EndTime)
SET @Counter = 0
SET @StartDate = @StartTime
SET @EndDate = @EndTime

WHILE @Counter <= @TotalOutageHours
BEGIN
SET NOCOUNT ON
INSERT INTO HOURLY_IMPACT
(TICKET_ID, LOCATION_ID, [DAY], [HOUR], OUTAGE_MINUTES)
VALUES
(@Ticket_id, @Location_id, CAST(FLOOR(CAST(@StartDate AS FLOAT))AS DATETIME), DATEPART(HH, @StartDate),
CASE WHEN @Counter = 0 AND @TotalOutageHours < 1 THEN datepart(n, @EndDate) - datepart(n, @StartDate)
WHEN @Counter = 0 AND @TotalOutageHours >= 1 THEN 60 - datepart(n, @StartDate)
WHEN @Counter > 0 AND @Counter < @TotalOutageHours THEN 60
WHEN @Counter = @TotalOutageHours THEN 60 - (60 - datepart(n, @EndDate))
END
)
SET @Counter = @Counter + 1
SET @StartDate = DATEADD(HH, 1, @StartDate)

END
FETCH NEXT FROM CRS_HOURLY_IMPACT INTO @Ticket_id, @Location_id, @StartTime, @EndTime

END

CLOSE CRS_HOURLY_IMPACT
DEALLOCATE CRS_HOURLY_IMPACT

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-04 : 08:32:50

Please provide some CREATE TABLE statements, INSERT statements for sample data and desired output. Help can come faster if you do a little of the leg work.

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-03-04 : 09:18:31

CREATE TABLE [PROBLEMS] (
[TICKET_ID] [varchar] (50),
[LOCATION_ID] [varchar] (3),
[OUTAGE_START] [datetime],
[OUTAGE_END] [datetime]
)
GO

INSERT PROBLEMS
(TICKET_ID, LOCATION_ID, OUTAGE_START, OUTAGE_END)
VALUES
('P1', 'FL', '1/1/2004 5:25:00 AM', '1/1/2004 6:59:59 AM')
INSERT PROBLEMS
(TICKET_ID, LOCATION_ID, OUTAGE_START, OUTAGE_END)
VALUES
('P1', 'WA', '1/1/2004 5:45 AM', '1/1/2004 6:15:00 AM')
INSERT PROBLEMS
(TICKET_ID, LOCATION_ID, OUTAGE_START, OUTAGE_END)
VALUES
('P2', 'CA', '1/1/2004 11:00:00 AM', '1/1/2004 11:47:00 AM')
INSERT PROBLEMS
(TICKET_ID, LOCATION_ID, OUTAGE_START, OUTAGE_END)
VALUES
('P3', 'TX', '1/1/2004 23:15:15 PM', '1/2/2004 6:15:15 AM' )

GO

CREATE TABLE [HOURLY_IMPACT] (
[TICKET_ID] [varchar] (50),
[LOCATION_ID] [varchar] (3),
[DAY] [datetime],
[HOUR] [int],
[OUTAGE_MINUTES] [int]
)
GO

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-04 : 09:39:51

Does the following help?

SELECT 
	TICKET_ID, 
	LOCATION_ID,
	CONVERT(DATETIME,CAST(OUTAGE_START AS VARCHAR(12)) ) [DAY],
	DATEPART(hh,OUTAGE_START) [HOUR],
	DATEDIFF(mi,OUTAGE_START,OUTAGE_END) [OUTAGE_MINUTES]
FROM PROBLEMS

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-03-04 : 09:47:39

Ehorn,

I need the outage minutes for every hour between the start and end times, not the total outage minutes between the start and end times.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-04 : 10:06:38

I am no understanding your request based on your sample data and example. Can you elaborate on your request?

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-03-04 : 10:38:45

ehorn,

If you run the create table and insert statements, then run the cursor statement it will populate data in the hourly_impact table just how I need it. The problem I have is that the cursor performs very poorly, it works but it is not efficient. The data I am creating in the hourly_impact table shows outage minutes per hour for every ticket/location/day that a problem was recorded in the problems table. The logic is that it takes the start and end times of a problem for a location and breaks it out hourly. Does this help or did I make it even more confusing?

HendersonToo
Starting Member

20 Posts

Posted - 2004-03-04 : 11:01:52

So to summarize, and please correct me if I'm wrong, because this sounds like a classic runs and streaks of time problem:

Results should be grouped by ticket, and then by location, and then by hour. Result should include only periods of outages. Outages spanning the start or end of an hour at a location should be grouped into unique records.

Is this correct?

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-03-04 : 11:12:30

HendersonToo,

I think you pretty much have it with one excepiton. "Results should be grouped by ticket, and then by location, DAY, and then by hour."

Thanks again for everyone assisting me with this!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-04 : 17:28:45

[code]--create tally table used for below query
create table numbers ( n INT)
declare @n int; set @n = 0
while @n <= 100
begin
insert into numbers
select @n
set @n = @n + 1
end
GO

select
ticket_id,
location_id,
[day],
[hour],
datediff(mi,outage_st,outage_ed) outage_minutes
from
(
select *,
case when nn=0 then OUTAGE_START else period end OUTAGE_ST,
case when nn<>dn AND nn=0 then period when nn<>dn AND nn>0 then dateadd(hh,1,period) else OUTAGE_END end OUTAGE_ED
from
(
select top 100 percent
n.n nn,
d.n dn,
outage_start,
outage_end,
ticket_id,
location_id,
convert(datetime,cast(dateadd(hh,n.n,d.outage_start) as varchar(12))) [DAY],
datepart(hh,dateadd(hh,n.n,d.outage_start)-n.n) [HOUR],
case
when n.n=0
then dateadd(hh,datepart(hh,dateadd(hh,n.n,d.outage_start)-n.n)+1,convert(datetime,cast(dateadd(hh,n.n,d.outage_start) as varchar(12))))
else dateadd(hh,datepart(hh,dateadd(hh,n.n,d.outage_start)-n.n),convert(datetime,cast(dateadd(hh,n.n,d.outage_start) as varchar(12))))
end period
from
(
select
ticket_id,
location_id,
outage_start,
outage_end,
datediff(hh,outage_start,outage_end) n
from problems p
) d
, numbers n
where n.n <= d.n
order by ticket_id,location_id,day,hour
) d
) d
order by ticket_id,location_id,day,hour

drop table numbers[/code]

schuhtl
Posting Yak Master

102 Posts

Posted - 2004-03-05 : 09:58:24

Ehorn,

You are awsome! I haven't studied it enough to figure out exactly how it works but the most important thing is that it does work much better than my cursor was working.

Thank you for your quick responses!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 10:10:40

Glad that worked for you schuhtl.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-05 : 11:34:26

I figured there had to be a set-based solution, that's why DDL and DML was important I can't wait to try it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources