Hi,
I have a question to a query:
table structure for the dimension:
dim_min with dim_start(timestamp), dim_end(timestamp), dim_year(int), dim_monthid(int), dim_month(int),...., dim_minid, dim_min
there is the full data for a few years, a record for 2010-07-23 looks like:
2010-07-23 00:00:00, 2010-07-23 00:00:59, 2010, 201007, 7, ... 201007230000, 0
the second table, the logtable, has the columns datanodeid, dim_min and val(decimal 15,5) where the datanodeid is an identifier, dim_min the reference to the dimension table and val the logged value.
my query looks like this:
select d.dim_dayid, coalesce(min(l.val),-1), coalesce(max(l.val),-1)
from dim_min as d left join logtable as l
ON d.dim_minid = l.dim_minid and l.datanodeid = 168
where d.dim_start >= '2009-04-26 00:00:00' and
d.dim_end <= '2009-06-17 23:59:59' group by d.dim_dayid order by d.dim_dayid asc;
the result is what I want, but the performance isn’t what I expected (7s on a logtable with ~40.000.000 records and dim_min with ~4.000.000 - on my notebook)
select d.dim_dayid, coalesce(min(l.val),-1), coalesce(max(l.val),-1)
from dim_min as d left join logtable as l
ON d.dim_minid = l.dim_minid
where l.datanodeid = 168 and d.dim_start >= '2009-04-26 00:00:00' and
d.dim_end <= '2009-06-17 23:59:59' group by d.dim_dayid order by d.dim_dayid asc;
this code works very performant (0,01sec), but I am missing the records, where there exists something in the dim_min, but not in the logtable…
I just tried a lot of combinations, but I could not find the solution, maybe someone can explain it to me?
Best Regards,
Wilfried
to submit a bug to 
