on 04-08-2014 4:26 PM
I'm looking for some advicse, how to setup a set of queries, which are checking for a 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 hade a look onto the windowing feature, but I think it might only working for aggregate data for timeframe, not at a sampling point. So in case I'm wrong, I'd happy to get some advice.
So having this scenario:
I'm having a list of items with a start and an end date. I need to collect a sum of processes active at a current time.
Imagine tables like this (I've modified example a little to leave out boring parts.... so might not running 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;
Having this, might not the best way of doing it. So I'm looking for a better approach -- I hope it's not off topic here.
Let me try to make it more general what I want to achieve:
I've got a list with processes. Each of them is having a start and (given they have been ended up already) a end timestamp. Now I want to calculate the number of active processes at a given time.
this is quiet easy to achieve. But as you can see from my little sketch:
I'd like to do it for a given time period with a given interval. So e.g. doing a snapshot each hour or each day. So taking this example result would be something like:
P1: 2
P2: 2
P3: 4
P4: 4
P5: 4
P6: 3
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I will try to answer...
If you have to write own code then the accuracy of process/connection runtime would be depends on frequency of your snapshot. You will not have 100% accuracy!
Easy and accurate way would be get request level SQL logging enabled for that duration. Then load the file in satmp_request_time, query this table sum(millisecs) with group by conn_id, req_id. This is my opinion people may have different opinion.
Regards,
Abhi
Thanks for the good answer, but I'm afraid its tending to a wrong direction (hard to bring my thoughts to words w/o using my monther tongue). I don't want to monitor the database. More I like to do reporting over some data:
Processes are in my example an external dataset -- something like a craftsman is working at a special task for example. Also I canot say for sure, which intervalls and which time period I need a report for -- this is depending on user's wish.
Hi Frank,
No response.... looks like nobody understood what you are trying to acheive. I have gone through your post 3-4 times still have no clue what exactly you are looking for.
If you could write in simple words which would help.
Regards,
Abhijit
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.