Hi,
I am trying to optimize month column in my fact table.
If month column is integer type then following query is generated by jasperserver which takes 8 sec and 313 milliseconds. This is fine.
select `fd`.`date_month` as `c0`
from `fact_details` as `fd`
where (`fd`.`date_year` = 2010)
and `fd`.`date_month` = 1
group by `c0`
order by `fd`.`date_month` ASC;
But i want to display String value for month field like ‘Jan’,‘Feb’ etc. Therefore i added one more column month_name varchar field in my fact table. Now jasperserver generates following query which takes 1 minute and 59 seconds.
select `fd`.`month_name` as `c0`
from `fact_details` as `fd`
where (`fd`.`date_year` = 2010)
and UPPER(`fd`.`month_name`) = UPPER(‘Jan’)
group by `c0`
order by `fd`.`month_name` ASC;
Now if i compare both the query then the difference is marked in red. This is due to UPPER function in the query.
Now if i run the second query without UPPER function as show below it takes 8 seconds and 547 milliseconds which is equivalent to first query. It means UPPER function is taking maximum time.
select `fd`.`month_name` as `c0`
from `fact_details` as `fd`
where (`fd`.`date_year` = 2010)
and (`fd`.`month_name`) = (‘Jan’)
group by `c0`
order by `fd`.`month_name` ASC;
Please suggest me how should i optimize this.
1. Can i avoid UPPER function so that jasperserver does not generate it for varchar fields?
2. Can i optimize UPPER function in infobright?
3. Is there any alternate way to handle the same scenario?
