3

I'm looking for some advice how to set up a set of queries which check for an aggregated value for sampling points within a time period. This should run at an IQ-server, so maybe not this many procedure calls would be cool ;)

I had a look into the windowing feature. I think it might only work for aggregate data for timeframe, not at a sampling point. So having this scenario:

I have a list of items with a start and an end date. I need to collect a sum of processes active at a current time. It's not about system processes, but more about something like how many craftsman were working at a given time.

Imagine tables like this (I've modified the example a little to leave out boring parts.... so might it not run perfectly)

create table items (
    id int ot null default autoincrement,
    "Type" integer,
    TimeStampStart datetime null, 
    TimeStampend datetime null
)

is currently used by this queryset:

create table #Processes(
  "Type" integer,
  "timestamp" "datetime" null,
  "Sum" integer null
  )

set @date = '20120303'
while @date <= '20130505'
  begin
    insert into #Processes
      select "Type",'timestamp'=@date,'Sum'="count"()
        from "items"
        and "TimeStampStart" between "dateadd"("day",-"abs"(100),@date) and @date
        and "TimeStampStart" <= @date
        and "isnull"("TimeStampEnd",@date) >= @date
        group by "Type"
    set @date = "dateadd"("ss",3600,@date)
  end
select * from #Processes;

This might not the best way of doing it. So I'm looking for a better approach ;)

  • It is you have a log and you need to know how many processes were running in every 60 min interval? Do you need it as SP/permanent query or a one off thing? – Stoleg Apr 11 '14 at 9:03
  • I need it on aregular base, but with adhoc data (= e.g. changing dates, changing intervall) – frlan Apr 11 '14 at 11:01
  • I would immediately go to windowing functions for this. Though I may still be foggy on what you need. Let me see if I understand: You have a table "Items" and you want to return a count of all records in "Items" for a given interval, grouping by type? – Mark Wilkinson Apr 11 '14 at 12:29
  • Yes. In additions, items are having a live time, so it should be only count items, that are allive at this very moment. – frlan Apr 11 '14 at 12:40
4
+100

I would take answer by Micheael Green a step further and suggest generating a Numbers table. It will help many other algorythms as well. Another handy table is Calendar table with every date for +/- 20 years. You can get numbers from ID column of such table as well.

Here is a query I came up with. You can easily wrap it into SP to TVF. It works for me. I tested on my dev db so had to prefix table names with tmp.

Play with >, >=, <, <= sings to count or not those processes that start/finish on boundary.

---- Create temp tables to test
create table tmp_numbers  (
id int null
)
insert into tmp_numbers values (0)
go
insert into tmp_numbers 
select MAX(ID)+1 from tmp_numbers 
go 1000
create clustered index Idx_ID on tmp_numbers (id)
go

create table tmp_items (
    "Type" integer,
    TimeStampStart datetime null, 
    TimeStampend datetime null
)
go

insert into tmp_items
select 1,'2012-03-03 02:13:01.000','2012-03-03 15:09:05.000'
UNION ALL
select 2,'2012-03-03 07:33:59.990','2012-03-03 14:59:10.000'
UNION ALL
select 3,'2012-03-03 22:13:01.000','2012-03-04 15:09:05.000'
UNION ALL
select 4,'2012-03-04 10:33:59.990','2012-03-04 14:59:10.000'
UNION ALL
select 5,'2012-03-04 23:20:00.000','2012-03-05 02:50:00.000'
UNION ALL
select 6,'2012-03-05 12:00:00.000','2012-03-05 23:01:00.000'

------ The query itself

declare @start datetime, @end datetime
set @start = '20120303'
set @end  = '20120305'

select 
ID,
DATEADD(hour, id, @start),
DATEADD(hour, id+1, @start),
(select COUNT (*) 
    from tmp_items 
    where TimeStampStart <= DATEADD(hour, tmp_numbers.id +1, @start) 
    and TimeStampEnd > DATEADD(hour, tmp_numbers.id, @start)

    -- To limit number of Log ("items") rows scanned:
    and TimeStampEnd >= @start

)
from tmp_numbers 
where id < DATEDIFF (hh,@start, @end)+24;

Another way to organise this query to overcome single CPU limitatation

declare @start datetime, @end datetime
    set @start = '20120303'
    set @end  = '20120305'

create table #Temp (
StartTime datetime null,
EndTime datetime null,
RowNum int null)

insert into #Temp
  select 
    DATEADD(hour, id, @start),
    DATEADD(hour, id+1, @start),
  from tmp_numbers 
    where id < DATEDIFF (hh,@start, @end)+24;

 update #Temp
 set RowNum =  (select COUNT (*) 
        from tmp_items 
        where TimeStampStart <= #Temp.EndTime 
        and TimeStampEnd > #Temp.StartTime 

        -- To limit number of Log ("items") rows scanned:
        and TimeStampEnd >= @start  
    )
| improve this answer | |
  • It seems to work but orignal approach seems to be little faster. Mainly because it apperas currently just using one core for this query. Still playing around with it. – frlan Apr 14 '14 at 11:20
  • Check the update. Hope it would allow to overcome single CPU issue. – Stoleg Apr 14 '14 at 12:33
  • Nice idea, but not solving this on IQ ;) – frlan Apr 14 '14 at 14:19
3

Frian,

I discussed this algorithm with a colleague last week. I haven't had a chance to test it yet, but we have confidence in it.

1) Convert the start and end times into a number of hours. This can be the HOURS() function or DATEDIFF. The baseline can be anything, but MIN(TimeStampStart) would be most convenient. Round TimeStampStart up and TimeStampEnd down. Let's call the resulting column HoursStart and HoursEnd.

2) Join to an Integers table where Integer.Number >= HoursStart and Integer.Number <= HoursEnd. Let's call Integer.Number "HourSampled" from now on. The integer table will be bigger or smaller depending on the baseline chosen for convertion to hours above. Create a new column called nnn (or anything else you choose) with a constant value 1.

3) Group by Type & HourSampled summing nnn into column Sum. HourSampled can be converted back to a DateTimeSampled if required.

Although I've laid this out in separate steps it can be coded in a single statement. This will be faster than your looping algorithm.

To change the sampling frequency change the function converting TimeStampStart to an integer and the corresponding content of your Integer table. For example, quarter hours would be MINUTES(TimeStampStart)/15.

I'm sorry but my Sybase knowledge is minimal. I've tried to reference the correct syntax where I can.

For added amusement you can select

select
    Type,
    REPLICATE(Sum)
from #Process
order by
    Type,
    HourSampled

To get a ready-made ASCII bar graph!

| improve this answer | |
  • Sounds like a nice approach. I was playing arround doing this with timestamp fileds using date-activated index and rounding of times. But needed joins were really slow. Using hours with integer really sounds good. Will give it a try. – frlan Apr 11 '14 at 13:00
  • HOURS() will drop the date so need another join / comparios for it. DATEDIFF() - what date to use as a starting point? What if someting is already running at the beginning? Numbers table is a good idea. – Stoleg Apr 11 '14 at 13:23
  • @Stoleg - using 0 as the start for DATEDIFF will work in all cases but requires a larger INTEGER table. Using MIN(TimeStampStart) needs a smaller INTEGER but would require a full table scan to determine. Testing will show which is better. – Michael Green Apr 11 '14 at 13:43
  • @MichaelGreen, 0 converts to date of '1900-01-01'. Given today's date and report range of 1-3 days it is not an option. Whole algorithm needs Hours+1 between Start and End date. One more scan will not improve it much. Having index on TimestampEnd or partitioning by it, will improve performance. – Stoleg Apr 11 '14 at 13:48
  • @Stoleg: if you can guarantee the data is all within a small range, say the last three days, then base DATEDIFF off four days ago. If you can guarantee it is all recent and historical base if off NOW() and use negative INTEGERS. The original question offered neither of these guarantees. – Michael Green Apr 11 '14 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.