Below is demo code that averages values into time periods from multiple
tables and combines them into a single table. The first approach uses
three SQL Selects and takes about 3/4 second while the second one does the
same job with a single select but takes about 32 seconds. Is there a
better way to do it in one? Thanks in Advance - Joe
m.TSref = DATETIME()
CREATE CURSOR joe (Stamp T, Value n(4))
FOR nn = 1 TO 100000
INSERT INTO joe VALUES (m.TSref + nn, 1000*RAND())
NEXT
CREATE CURSOR Moe (Stamp T, Value n(4))
FOR nn = 1 TO 100000
INSERT INTO moe VALUES (m.TSref + nn, 1000*RAND())
NEXT
m.strt = SECONDS()
SELECT Period(Joe.Stamp) as Period, Joe.Stamp, AVG(Joe.value) as Joe;
FROM joe;
GROUP BY 1;
INTO CURSOR Tmp1
SELECT Period(Moe.Stamp) as Period, Moe.Stamp, AVG(Moe.value) as Moe;
FROM Moe;
GROUP BY 1;
INTO CURSOR Tmp2
SELECT Tmp1.Period, Tmp1.stamp, Joe, Moe;
FROM Tmp1, Tmp2;
WHERE Tmp1.Period = Tmp2.period;
into cursor Out1
WAIT WINDOW SECONDS() - m.Strt
m.strt = SECONDS()
SELECT Period(Joe.Stamp) as Period, Joe.Stamp, AVG(Joe.value) as Joe,
AVG(Moe.value) as Moe;
FROM joe, Moe;
WHERE Period(Joe.Stamp) = Period(Moe.Stamp);
GROUP BY 1;
INTO CURSOR Out2
WAIT WINDOW SECONDS() - m.Strt
FUNCTION PERIOD
PARAMETERS m.ts
RETURN INT((m.Ts - m.TSref) / 60)
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/CABQeDnWkisyndzGJNvwdVwiAc4=bjxts3+qjzzxvke5av9o...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
