[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

While Loop Question

Aussie Matt

3/23/2007 1:48:00 PM

Hello, I am hoping you can help me out, I am teaching myself SQL and I have a
problem that I don;t understand the solution for, Attached is the stored
procedure.
The actual stored procedure run's and actually gives me the results I am
after, but it seems to run a little slow, I am wondering if someone can tell
me if there is a smarter way to right it that might speed up the execution
time,

To give you some background on the hist_statusevents table it looks like this

shiftindex eqmt category starttime endtime .......
27170 RD50 1 0 10000
27170 RD50 2 10000 15000
27170 RD50 4 15000 28000
27170 RD50 1 28000 43200
27170 RD62 4 0 19500
27170 RD62 7 19500 22400
27170 RD62 1 22400 29000
27170 RD62 4 29000 43200

there are approx 50 different RD entires (these represent equipment) the
category represents the status of the equipment, Ready, Down, Delay etc...
The starttime and endtime is the timestamp of an event change, this time
representes in seconds from 6:30am in the morning the time the event occurs
into the day, (43200 is the number of seconds in a 12 hour period)

The intention of this Stored Procedure is to give a second by second look at
the status of the fleet showing the number of units in the Ready,Down, Delay
state to help give a picture of our performance.

The output of the Stored Procedure should look somthing like

ShiftIndex Counter ReadyTrucks AvailTrucks DownTrucks
27170 0 10 5 7
27170 1 10 5 7
27170 2 10 5 7
27170 3 10 5 7
.
.
27170 6000 9 5 8
27170 6000 10 5 8
27170 6000 10 5 8
.
.
.
.
27170 43200 5 10 8



CREATE PROCEDURE USP_FMSRX155 (@X int, @Y int) AS

declare @counter int
declare @ReadyTrucks int
declare @AvailTrucks int
declare @DownTrucks int
declare @value int

set @counter = 0
set @ReadyTrucks = 0
set @AvailTrucks = 0
set @DownTrucks = 0


delete from TIMECAT

while (@counter <= 43200)
begin
set @ReadyTrucks = isnull((select count(starttime) from hist_statusevents
where shiftindex between @X and @Y and category = 1 and @Counter between
starttime and endtime and eqmt like 'RD%'),0)
set @AvailTrucks = isnull((select count(starttime) from hist_statusevents
where shiftindex between @X and @Y and category in(3,2,7) and @Counter
between starttime and endtime and eqmt like 'RD%'),0)
set @DownTrucks = isnull((select count(starttime) from hist_statusevents
where shiftindex between @X and @Y and category in(4,5) and @Counter between
starttime and endtime and eqmt like 'RD%'),0)

insert into TIMECAT values (@X,
@counter,@ReadyTrucks,@AvailTrucks,@DownTrucks)

set @counter = @counter +1

end

select * from TIMECAT
GO

Appreciate any help

Cheers

Matt
5 Answers

Tom Moreau

3/23/2007 2:31:00 PM

0

Why not populate a table with all of the seconds and then do the following:

select
s.Counter
, sum (case when category = 1 then 1 else 0 end) as Ready
, sum (case when category = 2 then 1 else 0 end) as Down
, sum (case when category = 3 then 1 else 0 end) as Delay
, sum (case when category = 4 then 1 else 0 end) as Etc
from
Seconds s
left join
hist_statusevents e on e.starttime <= s.Counter and s.Counter< e.endtime
group by
s.Counter

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Aussie Matt" <AussieMatt@discussions.microsoft.com> wrote in message
news:FE685DC0-D881-4684-BE9C-0CD9E2C06962@microsoft.com...
Hello, I am hoping you can help me out, I am teaching myself SQL and I have
a
problem that I don;t understand the solution for, Attached is the stored
procedure.
The actual stored procedure run's and actually gives me the results I am
after, but it seems to run a little slow, I am wondering if someone can tell
me if there is a smarter way to right it that might speed up the execution
time,

To give you some background on the hist_statusevents table it looks like
this

shiftindex eqmt category starttime endtime .......
27170 RD50 1 0 10000
27170 RD50 2 10000 15000
27170 RD50 4 15000 28000
27170 RD50 1 28000 43200
27170 RD62 4 0 19500
27170 RD62 7 19500 22400
27170 RD62 1 22400 29000
27170 RD62 4 29000 43200

there are approx 50 different RD entires (these represent equipment) the
category represents the status of the equipment, Ready, Down, Delay etc...
The starttime and endtime is the timestamp of an event change, this time
representes in seconds from 6:30am in the morning the time the event occurs
into the day, (43200 is the number of seconds in a 12 hour period)

The intention of this Stored Procedure is to give a second by second look at
the status of the fleet showing the number of units in the Ready,Down, Delay
state to help give a picture of our performance.

The output of the Stored Procedure should look somthing like

ShiftIndex Counter ReadyTrucks AvailTrucks DownTrucks
27170 0 10 5 7
27170 1 10 5 7
27170 2 10 5 7
27170 3 10 5 7
.
.
27170 6000 9 5 8
27170 6000 10 5 8
27170 6000 10 5 8
.
.
.
.
27170 43200 5 10 8



CREATE PROCEDURE USP_FMSRX155 (@X int, @Y int) AS

declare @counter int
declare @ReadyTrucks int
declare @AvailTrucks int
declare @DownTrucks int
declare @value int

set @counter = 0
set @ReadyTrucks = 0
set @AvailTrucks = 0
set @DownTrucks = 0


delete from TIMECAT

while (@counter <= 43200)
begin
set @ReadyTrucks = isnull((select count(starttime) from hist_statusevents
where shiftindex between @X and @Y and category = 1 and @Counter between
starttime and endtime and eqmt like 'RD%'),0)
set @AvailTrucks = isnull((select count(starttime) from hist_statusevents
where shiftindex between @X and @Y and category in(3,2,7) and @Counter
between starttime and endtime and eqmt like 'RD%'),0)
set @DownTrucks = isnull((select count(starttime) from hist_statusevents
where shiftindex between @X and @Y and category in(4,5) and @Counter
between
starttime and endtime and eqmt like 'RD%'),0)

insert into TIMECAT values (@X,
@counter,@ReadyTrucks,@AvailTrucks,@DownTrucks)

set @counter = @counter +1

end

select * from TIMECAT
GO

Appreciate any help

Cheers

Matt

TheSQLGuru

3/23/2007 2:37:00 PM

0

SHEW!! I bet it IS poorly performing. By my count you are doing 43200 * 3
table scans on your table!! That is a lot of data chewing. :)

Do you ABSOLUTELY have to know each second's counts?? Most of the numbers
from one second to the next will be unchanging values and thus possibly not
of importance. If this is the case and you only need to have entries in the
output when one of the 3 numbers changes, this can probably be done with 3
single or double self joins of the base table. I won't bother working out
the details unless you say it is acceptable. Perhaps you could figure it
out on your own from here too. Check out some of Joe Celko's and Itzik
Ben-Gan's stuff. They are both wizzes when it comes to complex set based
stuff like this. The performance gains could be dramatic though!

Good luck with it.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Aussie Matt" <AussieMatt@discussions.microsoft.com> wrote in message
news:FE685DC0-D881-4684-BE9C-0CD9E2C06962@microsoft.com...
> Hello, I am hoping you can help me out, I am teaching myself SQL and I
> have a
> problem that I don;t understand the solution for, Attached is the stored
> procedure.
> The actual stored procedure run's and actually gives me the results I am
> after, but it seems to run a little slow, I am wondering if someone can
> tell
> me if there is a smarter way to right it that might speed up the execution
> time,
>
> To give you some background on the hist_statusevents table it looks like
> this
>
> shiftindex eqmt category starttime endtime .......
> 27170 RD50 1 0 10000
> 27170 RD50 2 10000 15000
> 27170 RD50 4 15000 28000
> 27170 RD50 1 28000 43200
> 27170 RD62 4 0 19500
> 27170 RD62 7 19500 22400
> 27170 RD62 1 22400 29000
> 27170 RD62 4 29000 43200
>
> there are approx 50 different RD entires (these represent equipment) the
> category represents the status of the equipment, Ready, Down, Delay etc...
> The starttime and endtime is the timestamp of an event change, this time
> representes in seconds from 6:30am in the morning the time the event
> occurs
> into the day, (43200 is the number of seconds in a 12 hour period)
>
> The intention of this Stored Procedure is to give a second by second look
> at
> the status of the fleet showing the number of units in the Ready,Down,
> Delay
> state to help give a picture of our performance.
>
> The output of the Stored Procedure should look somthing like
>
> ShiftIndex Counter ReadyTrucks AvailTrucks DownTrucks
> 27170 0 10 5 7
> 27170 1 10 5 7
> 27170 2 10 5 7
> 27170 3 10 5 7
> .
> .
> 27170 6000 9 5 8
> 27170 6000 10 5 8
> 27170 6000 10 5 8
> .
> .
> .
> .
> 27170 43200 5 10 8
>
>
>
> CREATE PROCEDURE USP_FMSRX155 (@X int, @Y int) AS
>
> declare @counter int
> declare @ReadyTrucks int
> declare @AvailTrucks int
> declare @DownTrucks int
> declare @value int
>
> set @counter = 0
> set @ReadyTrucks = 0
> set @AvailTrucks = 0
> set @DownTrucks = 0
>
>
> delete from TIMECAT
>
> while (@counter <= 43200)
> begin
> set @ReadyTrucks = isnull((select count(starttime) from hist_statusevents
> where shiftindex between @X and @Y and category = 1 and @Counter between
> starttime and endtime and eqmt like 'RD%'),0)
> set @AvailTrucks = isnull((select count(starttime) from hist_statusevents
> where shiftindex between @X and @Y and category in(3,2,7) and @Counter
> between starttime and endtime and eqmt like 'RD%'),0)
> set @DownTrucks = isnull((select count(starttime) from hist_statusevents
> where shiftindex between @X and @Y and category in(4,5) and @Counter
> between
> starttime and endtime and eqmt like 'RD%'),0)
>
> insert into TIMECAT values (@X,
> @counter,@ReadyTrucks,@AvailTrucks,@DownTrucks)
>
> set @counter = @counter +1
>
> end
>
> select * from TIMECAT
> GO
>
> Appreciate any help
>
> Cheers
>
> Matt


Alejandro Mesa

3/23/2007 2:54:00 PM

0

Aussie Matt,

This approach seems not to be full set-based oriented.

1 - Inside the loop, you are using three "select" stements thet differ only
on the expression "and category = ...". May be using a "case" expression can
help you there, using just one statement.

select
@ReadyTrucks = isnull(sum(case when category = 1 then starttime end), 0),
@AvailTrucks = isnull(sum(case when category in (3,2,7) then starttime
end), 0),
@DownTrucks = isnull(sum(case when category in (4, 5) then starttime end), 0)
from
dbo.hist_statusevents
where
shiftindex between @X and @Y
and @Counter between starttime and endtime
and eqmt like 'RD%'

2 - What woul happend if we insert all those counters into a temporary table
and we join the temporary table with table [dbo].[hist_statusevents] to do
the calculation in just one "select" statement.

while (@counter <= 43200)
begin
insert into #t values(@counter)
set @counter = @counter +1
end

insert into TIMECAT
select
b.c1,
isnull(sum(case when b.category = 1 then b.starttime end), 0),
isnull(sum(case when b.category in (3,2,7) then b.starttime end), 0),
isnull(sum(case when b.category in (4, 5) then b.starttime end), 0)
from
(
select
starttime, endtime, category
from
dbo.hist_statusevents
where
shiftindex between @X and @Y
and eqmt like 'RD%'
) as a
inner join
#t as b
on b.c1 between a.starttime and a.endtime
group by
b.c1


It will help also if you have an auxiliary table of numbers. If you are
using sql server 2005, there are some waays to generate a table of numbers on
the fly.

Why should I consider using an auxiliary numbers table?
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-...

I have not tested any code.

Hope this helps,

AMB


"Aussie Matt" wrote:

> Hello, I am hoping you can help me out, I am teaching myself SQL and I have a
> problem that I don;t understand the solution for, Attached is the stored
> procedure.
> The actual stored procedure run's and actually gives me the results I am
> after, but it seems to run a little slow, I am wondering if someone can tell
> me if there is a smarter way to right it that might speed up the execution
> time,
>
> To give you some background on the hist_statusevents table it looks like this
>
> shiftindex eqmt category starttime endtime .......
> 27170 RD50 1 0 10000
> 27170 RD50 2 10000 15000
> 27170 RD50 4 15000 28000
> 27170 RD50 1 28000 43200
> 27170 RD62 4 0 19500
> 27170 RD62 7 19500 22400
> 27170 RD62 1 22400 29000
> 27170 RD62 4 29000 43200
>
> there are approx 50 different RD entires (these represent equipment) the
> category represents the status of the equipment, Ready, Down, Delay etc...
> The starttime and endtime is the timestamp of an event change, this time
> representes in seconds from 6:30am in the morning the time the event occurs
> into the day, (43200 is the number of seconds in a 12 hour period)
>
> The intention of this Stored Procedure is to give a second by second look at
> the status of the fleet showing the number of units in the Ready,Down, Delay
> state to help give a picture of our performance.
>
> The output of the Stored Procedure should look somthing like
>
> ShiftIndex Counter ReadyTrucks AvailTrucks DownTrucks
> 27170 0 10 5 7
> 27170 1 10 5 7
> 27170 2 10 5 7
> 27170 3 10 5 7
> .
> .
> 27170 6000 9 5 8
> 27170 6000 10 5 8
> 27170 6000 10 5 8
> .
> .
> .
> .
> 27170 43200 5 10 8
>
>
>
> CREATE PROCEDURE USP_FMSRX155 (@X int, @Y int) AS
>
> declare @counter int
> declare @ReadyTrucks int
> declare @AvailTrucks int
> declare @DownTrucks int
> declare @value int
>
> set @counter = 0
> set @ReadyTrucks = 0
> set @AvailTrucks = 0
> set @DownTrucks = 0
>
>
> delete from TIMECAT
>
> while (@counter <= 43200)
> begin
> set @ReadyTrucks = isnull((select count(starttime) from hist_statusevents
> where shiftindex between @X and @Y and category = 1 and @Counter between
> starttime and endtime and eqmt like 'RD%'),0)
> set @AvailTrucks = isnull((select count(starttime) from hist_statusevents
> where shiftindex between @X and @Y and category in(3,2,7) and @Counter
> between starttime and endtime and eqmt like 'RD%'),0)
> set @DownTrucks = isnull((select count(starttime) from hist_statusevents
> where shiftindex between @X and @Y and category in(4,5) and @Counter between
> starttime and endtime and eqmt like 'RD%'),0)
>
> insert into TIMECAT values (@X,
> @counter,@ReadyTrucks,@AvailTrucks,@DownTrucks)
>
> set @counter = @counter +1
>
> end
>
> select * from TIMECAT
> GO
>
> Appreciate any help
>
> Cheers
>
> Matt

Alejandro Mesa

3/23/2007 2:58:00 PM

0

Correction,

After reading the post from Tom, I realized I made a big mistake putting the
column [starttime] inside the "sum".

select
@ReadyTrucks = isnull(sum(case when category = 1 then 1 else 0 end), 0),
@AvailTrucks = isnull(sum(case when category in (3,2,7) then 1 else 0 end),
0),
@DownTrucks = isnull(sum(case when category in (4, 5) then 1 else 0 end), 0)
from
dbo.hist_statusevents
where
shiftindex between @X and @Y
and @Counter between starttime and endtime
and eqmt like 'RD%'


AMB

"Alejandro Mesa" wrote:

> Aussie Matt,
>
> This approach seems not to be full set-based oriented.
>
> 1 - Inside the loop, you are using three "select" stements thet differ only
> on the expression "and category = ...". May be using a "case" expression can
> help you there, using just one statement.
>
> select
> @ReadyTrucks = isnull(sum(case when category = 1 then starttime end), 0),
> @AvailTrucks = isnull(sum(case when category in (3,2,7) then starttime
> end), 0),
> @DownTrucks = isnull(sum(case when category in (4, 5) then starttime end), 0)
> from
> dbo.hist_statusevents
> where
> shiftindex between @X and @Y
> and @Counter between starttime and endtime
> and eqmt like 'RD%'
>
> 2 - What woul happend if we insert all those counters into a temporary table
> and we join the temporary table with table [dbo].[hist_statusevents] to do
> the calculation in just one "select" statement.
>
> while (@counter <= 43200)
> begin
> insert into #t values(@counter)
> set @counter = @counter +1
> end
>
> insert into TIMECAT
> select
> b.c1,
> isnull(sum(case when b.category = 1 then b.starttime end), 0),
> isnull(sum(case when b.category in (3,2,7) then b.starttime end), 0),
> isnull(sum(case when b.category in (4, 5) then b.starttime end), 0)
> from
> (
> select
> starttime, endtime, category
> from
> dbo.hist_statusevents
> where
> shiftindex between @X and @Y
> and eqmt like 'RD%'
> ) as a
> inner join
> #t as b
> on b.c1 between a.starttime and a.endtime
> group by
> b.c1
>
>
> It will help also if you have an auxiliary table of numbers. If you are
> using sql server 2005, there are some waays to generate a table of numbers on
> the fly.
>
> Why should I consider using an auxiliary numbers table?
> http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-...
>
> I have not tested any code.
>
> Hope this helps,
>
> AMB
>
>
> "Aussie Matt" wrote:
>
> > Hello, I am hoping you can help me out, I am teaching myself SQL and I have a
> > problem that I don;t understand the solution for, Attached is the stored
> > procedure.
> > The actual stored procedure run's and actually gives me the results I am
> > after, but it seems to run a little slow, I am wondering if someone can tell
> > me if there is a smarter way to right it that might speed up the execution
> > time,
> >
> > To give you some background on the hist_statusevents table it looks like this
> >
> > shiftindex eqmt category starttime endtime .......
> > 27170 RD50 1 0 10000
> > 27170 RD50 2 10000 15000
> > 27170 RD50 4 15000 28000
> > 27170 RD50 1 28000 43200
> > 27170 RD62 4 0 19500
> > 27170 RD62 7 19500 22400
> > 27170 RD62 1 22400 29000
> > 27170 RD62 4 29000 43200
> >
> > there are approx 50 different RD entires (these represent equipment) the
> > category represents the status of the equipment, Ready, Down, Delay etc...
> > The starttime and endtime is the timestamp of an event change, this time
> > representes in seconds from 6:30am in the morning the time the event occurs
> > into the day, (43200 is the number of seconds in a 12 hour period)
> >
> > The intention of this Stored Procedure is to give a second by second look at
> > the status of the fleet showing the number of units in the Ready,Down, Delay
> > state to help give a picture of our performance.
> >
> > The output of the Stored Procedure should look somthing like
> >
> > ShiftIndex Counter ReadyTrucks AvailTrucks DownTrucks
> > 27170 0 10 5 7
> > 27170 1 10 5 7
> > 27170 2 10 5 7
> > 27170 3 10 5 7
> > .
> > .
> > 27170 6000 9 5 8
> > 27170 6000 10 5 8
> > 27170 6000 10 5 8
> > .
> > .
> > .
> > .
> > 27170 43200 5 10 8
> >
> >
> >
> > CREATE PROCEDURE USP_FMSRX155 (@X int, @Y int) AS
> >
> > declare @counter int
> > declare @ReadyTrucks int
> > declare @AvailTrucks int
> > declare @DownTrucks int
> > declare @value int
> >
> > set @counter = 0
> > set @ReadyTrucks = 0
> > set @AvailTrucks = 0
> > set @DownTrucks = 0
> >
> >
> > delete from TIMECAT
> >
> > while (@counter <= 43200)
> > begin
> > set @ReadyTrucks = isnull((select count(starttime) from hist_statusevents
> > where shiftindex between @X and @Y and category = 1 and @Counter between
> > starttime and endtime and eqmt like 'RD%'),0)
> > set @AvailTrucks = isnull((select count(starttime) from hist_statusevents
> > where shiftindex between @X and @Y and category in(3,2,7) and @Counter
> > between starttime and endtime and eqmt like 'RD%'),0)
> > set @DownTrucks = isnull((select count(starttime) from hist_statusevents
> > where shiftindex between @X and @Y and category in(4,5) and @Counter between
> > starttime and endtime and eqmt like 'RD%'),0)
> >
> > insert into TIMECAT values (@X,
> > @counter,@ReadyTrucks,@AvailTrucks,@DownTrucks)
> >
> > set @counter = @counter +1
> >
> > end
> >
> > select * from TIMECAT
> > GO
> >
> > Appreciate any help
> >
> > Cheers
> >
> > Matt

Aussie Matt

3/23/2007 11:42:00 PM

0


Excellent guys, Great replys,
I will rewrite it today and see how I go...

thanks !

Cheers

AussieMatt