Finding an unrelated value AT the maximum

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)

Finding an unrelated value AT the maximum

Author

Topic

Dev Null
Starting Member

39 Posts

Posted - 2006-02-15 : 22:38:25

I want to find the value of one column at the point where a second column is at its maximum. I can manage it, but it seems horribly inefficient; I'm hoping someone can help me make it less so. Its a problem I've run into in the past, but the current example looks a bit like:

I have 1-minute CPU load counts for a collection of machines. What I want to do is calculate the busiest total hour for each machine, what the CPU load was during that hour, and when that hour actually started.

My solution was to create a temporary table with each possible start time for each machine, and the average CPU load for the following hour. Then I select the max CPU load group by machine, and go back to the temp table to find the actual time this max occurred.


select cpu1.server_id, cpu1.time_bucket, avg( cpu2.cpu_total ) as avg_load
into #temp_load
from server_short cpu1 inner join server_short cpu2
on cpu1.server_id = cpu2.server_id
and cpu2.time_bucket >= cpu1.time_bucket
and cpu2.time_bucket < dateadd( hour, 1, cpu1.time_bucket )
group by cpu1.server_id, cpu1.time_bucket

select server_id, cpu, 
 (select min( time_bucket ) from #temp_load tl
  where tl.server_id = busiest.server_id
    and tl.avg_load = busiest.cpu) as busiest_hour
from (select server_id, max( avg_load ) 
  from #temp_load group by server_id)
 as busiest (server_id, cpu)

It seems to work, but the temporary table has the potential to grow quite large and the date lookup inefficient. Any suggestions?

mallier
Starting Member

24 Posts

Posted - 2006-02-16 : 06:09:43

OK,lets avoid temp table.create a view with ur first query. then will select server id,maximum cpu time, busiest hour from that.
Im not sure abt indexes of ur table.My suggestion is create a clusterindex on server_id,time_bucket.

--here is the view

create view dbo.vcpu
as
select top 100 percent cpu1.server_id,
cpu1.time_bucket,
avg( cpu2.cpu_total ) as avg_load
from server_short cpu1
inner join
server_short cpu2
on cpu1.server_id = cpu2.server_id
and cpu2.time_bucket >= cpu1.time_bucket
and cpu2.time_bucket < dateadd( hour, 1, cpu1.time_bucket )
group by
cpu1.server_id,
cpu1.time_bucket
order by
cpu1.server_id,cpu1.time_bucket

---select query

select tm.server_id,tm.cpu,min(time_bucket) as busiest_hour from vcpu c1 join
(select server_id,max(avg_load) as cpu from vcpu group by server_id ) as tm
on c1.server_id =tm.server_id and cl.avg_load = tm.cpu
group by tm.server_id,tm.cpu

--Note Im not tested with data.Post the outcome of my query

cheers,
http://mallier.blogspot.com

Dev Null
Starting Member

39 Posts

Posted - 2006-02-16 : 19:03:10

Bah, this always happens to me; you'd think I'd have learned my lesson by now. I try to simplify my problem down to its essence, so I don't bore you guys with all the details, and I end up leaving out something important...

Ok, so the problem with this idea is that what I actually need is the busiest hour from a given timeframe, so my query into the temp table is taking care of this restriction for me, and actually looks like


SELECT cpu1.server_id, 
    cpu1.time_bucket, 
    avg( cpu2.cpu_total / cpu2.cpu_count ) as avg_load
  INTO #temp_load
  FROM server_short cpu1 INNER JOIN server_short cpu2
    ON cpu1.server_id = cpu2.server_id
    AND cpu2.time_bucket >= cpu1.time_bucket
    AND cpu2.time_bucket < dateadd( hour, 1, cpu1.time_bucket )
  WHERE cpu1.server_id in ({0})  -- node selection subquery
    AND cpu1.time_bucket >= {1} -- requestDuration.GMTStartDate
    AND cpu1.time_bucket < {2}  -- requestDuration.GMTEndDate
  GROUP BY cpu1.server_id, cpu1.time_bucket

(No really - not lying this time. Sorry about that.) So while I think your view idea is a good one for the question I asked, I'm not sure I see how I can use it for my actual problem; the view would have to somehow take into account the start and end times, which change every time I run the query. (I also filter for only servers that I actually care about, but I don't see how that affects anything.)

mallier
Starting Member

24 Posts

Posted - 2006-02-17 : 04:19:26

create inline table-valued function instead of view,then u can pass the parameter like server_id,start time,end time.Hope this will help u.

cheers,
http://mallier.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources