[EMAIL PROTECTED] wrote:
> Hello
>
> I got another problem few weeks ago. In quick: I need
> to store a variable
> for monitoring graph, collected at 5 minutes. I also don't
> want to use rrdtool,
> nor mrtg but to extract the data directly from a database, to
> create charts
> (600 values each) on 3 basic periods: day, month, year. I
> need to extract only
> precomputed averages for the month and year (~600 rows), for
> the speed, and I
> don't want to precompute them in the application. So here is
> the table, and the
> trigger to get all the work inside the sapdb, and my question
> is if exists other
> ways to do that (in order of speed or elegance).
>
You are using the set-function COUNT.
But why do you not use the set-function SUM as well?
Instead of
DECLARE c02h CURSOR FOR SELECT val FROM dbc.orx WHERE h >= :lasth and avgt = 0;
and doing a loop with
SET j = j + i ;
FETCH NEXT c02h INTO :i ;
perhaps a
DECLARE c02h CURSOR FOR SELECT sum(val) FROM dbc.orx WHERE h >= :lasth and avgt = 0;
would be fine.
But you want to have the AVG and a function AVG is available, too.
And if you will combine the insert and the select, something like
INSERT INTO dbc.orx (h, val, avgt) as
SELECT :new.h,AVG(val),1 FROM dbc.orx WHERE h >= :lasth and avgt = 0;
is normal SQL (see reference manual), much shorter than your loop with something
in the end and should improve the performance.
Elke
SAP Labs Berlin
> create table dbc.orx (
> id serial primary key,
> h fixed(10,0), -> unix time
> val FIXED(10,0), -> value
> avgt int) -> average type
>
> insert 3 averages :
>
> session.sql ('insert into dbc.orx (h ,val ,avgt) values (
> '1043324062','0',1 )')
> session.sql ('insert into dbc.orx (h ,val ,avgt) values (
> '1043324063','0',2 )')
> session.sql ('insert into dbc.orx (h ,val ,avgt) values (
> '1043324064','0',3 )')
>
>
> avg 1 is for 1800 seconds
> avg 2 is for 7200 seconds
> avg 3 is for 86400 seconds
>
>
>
> CREATE TRIGGER mkavg FOR dbc.orx AFTER INSERT EXECUTE (
> VAR
> lasth FIXED(10,0) ;
> i FIXED(10,0) ;
> q FIXED(10,0) ;
> j FIXED(10,0) ;
> x FIXED(10,0) ;
> difh FIXED(10,0) ;
>
> SET j = 0;
> SET x = 0;
> SET q = 0;
>
> DECLARE c01h CURSOR FOR SELECT h FROM dbc.orx WHERE avgt = 1 ;
> FETCH LAST c01h INTO :lasth;
> CLOSE c01h;
> SET difh = h - lasth ;
> IF ( difh >= 1800 ) THEN
> BEGIN
> DECLARE c02h CURSOR FOR SELECT val FROM dbc.orx WHERE h
> >= :lasth and avgt = 0;
> FETCH c02h INTO :i ;
> WHILE $RC = 0 DO
> BEGIN
> SET j = j + i ;
> FETCH NEXT c02h INTO :i ;
> END ;
> CLOSE c02h ;
> SELECT count(*) FROM dbc.orx WHERE h >= :lasth and avgt = 0;
> FETCH LAST INTO :x;
> SET q = j / x ;
> INSERT INTO dbc.orx (h, val, avgt) VALUES (:new.h, :q, 1) ;
> END ;
>
> DECLARE c11h CURSOR FOR SELECT h FROM dbc.orx WHERE avgt = 2 ;
> FETCH LAST c11h INTO :lasth;
> CLOSE c11h;
> SET difh = h - lasth ;
> IF ( difh >= 7200 ) THEN
> BEGIN
> DECLARE c12h CURSOR FOR SELECT val FROM dbc.orx WHERE h
> >= :lasth and avgt = 1;
> FETCH c12h INTO :i ;
> WHILE $RC = 0 DO
> BEGIN
> SET j = j + i ;
> FETCH NEXT c12h INTO :i ;
> END ;
> CLOSE c12h ;
> SELECT count(*) FROM dbc.orx WHERE h >= :lasth and avgt = 1;
> FETCH LAST INTO :x;
> SET q = j / x ;
> INSERT INTO dbc.orx (h, val, avgt) VALUES (:new.h, :q, 2) ;
> END ;
>
> DECLARE c21h CURSOR FOR SELECT h FROM dbc.orx WHERE avgt = 3 ;
> FETCH LAST c21h INTO :lasth;
> CLOSE c21h;
> SET difh = h - lasth ;
> IF ( difh >= 86400 ) THEN
> BEGIN
> DECLARE c22h CURSOR FOR SELECT val FROM dbc.orx WHERE h
> >= :lasth and avgt = 2;
> FETCH c22h INTO :i ;
> WHILE $RC = 0 DO
> BEGIN
> SET j = j + i ;
> FETCH NEXT c22h INTO :i ;
> END ;
> CLOSE c22h ;
> SELECT count(*) FROM dbc.orx WHERE h >= :lasth and avgt = 2;
> FETCH LAST INTO :x;
> SET q = j / x ;
> INSERT INTO dbc.orx (h, val, avgt) VALUES (:new.h, :q, 3) ;
>
> END ;
> IF $rc = 0 THEN STOP($rc) ;
> )
>
>
>
>
> Regards
>
> dim
>
> _______________________________________________
> sapdb.general mailing list
> [EMAIL PROTECTED]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
