Hi everybody,
I need more brains to help. I had this app that i used to run without problem, but after upgrade Sense 3.0.2 to 3.0.5 my engine consume all memory in the server and crash. Any ideia why may this occur?
Follow the code and the part thats crash:
for a=0 to 2
let vCarga=Timestamp(Today()-$(a),'YYYYMMDD');
[CONCAT_TABLE_$(vPath)]:
LOAD
Distinct
*
FROM [lib://Dados/$(vPath)/Leituras/Individual_Read/RS_INDIVIDUAL_READ_$(vCarga).qvd](qvd) where DATE_READ>TODAY()-2;
next
TMP:
LOAD
OID_SYSTEM,
OID_GROUP,
NAME_SYSTEM,
SSN_REMOTE_SYSTEM,
STATUS,
ID_SYSTEM_TYPE
FROM [lib://Dados/$(vPath)\RS/RS_SYSTEM.qvd]
(qvd) WHERE STATUS<>'99' and OID_GROUP<>'28';
LEFT JOIN (TMP)
LOAD
OID_SYSTEM,
bairro as NEIGHB_SYSTEM,
municipio as CITY_SYSTEM
FROM [lib://Dados/$(vPath)\Enderecos/enderecocompleto.qvd]
(qvd);
INNER JOIN (TMP)
LOAD
OID_GROUP,
NAME_GROUP
FROM [lib://Dados/$(vPath)\RS/RS_GROUP.qvd]
(qvd);
INNER JOIN (TMP)
LOAD
OID_UC,
NAME_UC,
ADDRESS_BLOCK_UC,
OID_SYSTEM
FROM [lib://Dados/$(vPath)\RS/RS_UC.qvd]
(qvd) WHERE (UPPER(NAME_UC)<>'PISCINA') AND (UPPER(NAME_UC)<>'MEDIDOR PARALELO');
INNER JOIN (TMP)
LOAD
OID_METER,
OID_UC,
OID_TYPE_METER,
TYPE_CONSTANT
FROM [lib://Dados/$(vPath)\RS//RS_METER.qvd]
(qvd);
INNER JOIN (TMP)
LOAD
OID_TYPE_METER,
NAME_TYPE_METER
FROM [lib://Dados/$(vPath)\RS//RS_TYPE_METER.qvd]
(qvd);
INNER JOIN (TMP)
LOAD
OID_COUNTERS,
OID_METER,
CONCENTRATOR_COUNTERS,
PORT_COUNTERS
FROM [lib://Dados/$(vPath)\RS/RS_COUNTERS.qvd]
(qvd);
TMP2:
LOAD
NAME_SYSTEM,
NAME_GROUP,
NEIGHB_SYSTEM,
CITY_SYSTEM,
OID_METER,
NAME_TYPE_METER,
TYPE_CONSTANT,
OID_UC,
NAME_UC,
ADDRESS_BLOCK_UC,
OID_SYSTEM&'|'&CONCENTRATOR_COUNTERS&'|'&PORT_COUNTERS AS ID_LEITURA
RESIDENT TMP
WHERE NAME_SYSTEM <> 'MANSAO RAVELLO' AND NAME_TYPE_METER <> 'Agua quente-retorno';
left join (TMP2)
LOAD
VALUE_READ,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,
Date(floor(DATE_READ)) AS DATA,
Time(frac(DATE_READ)) AS TEMPO
RESIDENT [CONCAT_TABLE_$(vPath)] where DATE_READ>=TODAY()-2;
Output:
load
Date(floor(DATE_READ)) AS DATA,
Hour(DATE_READ) AS HORA,
Time(frac(DATE_READ)) AS TEMPO,
ID_SYSTEM&'|'&CONCENTRATOR&'|'&PORT AS ID_LEITURA,
VALUE_READ
resident [CONCAT_TABLE_$(vPath)]
where DATE_READ>=TODAY()-2;
inner join (Output)
load
DATA,
HORA,
ID_LEITURA,
time(min(TEMPO)) as TEMPO
resident Output group by DATA, HORA,ID_LEITURA;
NoConcatenate
VALOR:
LOAD
DATA,
HORA,
TEMPO,
ID_LEITURA,
Min(VALUE_READ) AS VALUE_READ
RESIDENT Output
Group By
DATA,
HORA,
TEMPO,
ID_LEITURA
;
Inner Join(TMP2)
Load
ID_LEITURA,
DATA,
VALUE_READ,
TEMPO
RESIDENT VALOR;
DROP TABLE Output;
DROP TABLE VALOR;
Corrige_Medidor:
LOAD
DISTINCT
OID_METER,
VALUE_READ AS VALOR,
DATA,
TEMPO
RESIDENT TMP2
ORDER BY OID_METER,DATA,TEMPO DESC ;
NoConcatenate
Exclude_Meter:
LOAD
DISTINCT
OID_METER,
VALOR,
If((PEEK(VALOR)- VALOR)=0,OID_METER) AS EXCLUIR,
DATA,
TEMPO
RESIDENT Corrige_Medidor
ORDER BY OID_METER,DATA,TEMPO DESC ;
drop table Corrige_Medidor;
Exclusao:
LOAD
OID_METER,
EXCLUIR
RESIDENT Exclude_Meter
WHERE NOT EXISTS(EXCLUIR,OID_METER)=-1
ORDER BY EXCLUIR;
DROP TABLE Exclude_Meter;
inner join (Exclusao)
ANALISE_TMP:
LOAD
ID_LEITURA,
NAME_SYSTEM,
NAME_GROUP,
NEIGHB_SYSTEM,
CITY_SYSTEM,
OID_METER,
NAME_TYPE_METER,
TYPE_CONSTANT,
NAME_UC,
ADDRESS_BLOCK_UC,
DATA AS DIA0,
TEMPO,
VALUE_READ AS Val0,
IF(OID_METER=PEEK(OID_METER),PEEK(Val0)*PEEK(TYPE_CONSTANT)-VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA0
RESIDENT TMP2
where FLOOR(DATA)=FLOOR(TODAY()) AND (HOUR(TEMPO)=2 OR HOUR(TEMPO)=5)
ORDER BY OID_METER,DATA,TEMPO DESC ;
LEFT JOIN (Exclusao)
LOAD
OID_METER,
TYPE_CONSTANT,
VALUE_READ AS Val1,
IF(OID_METER=PEEK(OID_METER),PEEK(Val1)*PEEK(TYPE_CONSTANT)-VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA1
RESIDENT TMP2
where FLOOR(DATA)=FLOOR(TODAY()-1) AND (HOUR(TEMPO)=2 OR HOUR(TEMPO)=5)
ORDER BY OID_METER,DATA,TEMPO DESC ;
LEFT JOIN (Exclusao)
LOAD
OID_METER,
TYPE_CONSTANT,
VALUE_READ AS Val2,
IF(OID_METER=PEEK(OID_METER),PEEK(Val2)*PEEK(TYPE_CONSTANT)-VALUE_READ*TYPE_CONSTANT) AS CONSUMODIA2
RESIDENT TMP2
where FLOOR(DATA)=FLOOR(TODAY()-2) AND (HOUR(TEMPO)=2 OR HOUR(TEMPO)=5)
ORDER BY OID_METER,DATA,TEMPO DESC ;
