How to avoid function ‘UPPER’ for varchar fields?

[arunsinghal01]

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. Is there any alternate way to handle the same scenario?

[Gaby38]

 If I were you I would create a separate table with 2 columns and 12 rows with the number of the month and the "name" (as you wish) of the month and do a join on the 2 tables on date_month.

SGBD are very fast on join managing;

I write an example of such a query in Oracle database case :

select fd.date_month as c0 
from fact_details as fd 

inner join tab_month as  tm on tm.date_month=fd.date_month
where (`fd`.`date_year` = 2010) 
and tm.month_name='Jan' 
group by c0 
order by fd.date_month ASC;

[arunsinghal01]

Hi Gaby38,

Thanks for the reply. I have also tried it with a separate dimension table as shown in code block. I have 236 million records in my fact table and 2500 records in date dimension table. Overall i have tried 3 scenario given below.

 

1. All year, month, day column in fact table as integer type. In this case my query takes 1.5 minute.

2. But i need month name instead of month number so i made one more column in my fact table as month_name. In this case it is taking 3.5 minutes.

3. Then i tried separate date dimension table as u suggested. But this case is also taking 3.5 minutes.

As i am using Infobright i asked this query on Infobright forum and there Mr. David suggested me to use date dimension as well as month integer column in fact table to make filter on fact table. But i my problem is how can i define dimension table as well as filter in an OLAP schema. The code for OLAP schema is given in code block. Please help me how can i define both  dimension table and filter column in fact table in OLAP schema.

 

Thanks,

Arun

 

 

 

Code:

<Hierarchy name="period_h" hasAll="true" allMemberName="all_periods" allMemberCaption="All Periods" primaryKey="date_id">    <Table name="dim_period" alias="dp">    </Table>    <Level name="Year" column="year_name" type="Integer" uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never">    </Level>    <Level name="Month" column="month" nameColumn="month_name" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">    </Level>    <Level name="Day" column="day" nameColumn="day_name" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">    </Level></Hierarchy>  

[Gaby38]

 Arun,

I didn't noticed that this was in an OLAP schema. I am not familar with this kind of "technology". So I am not able to help you in this domain.

Sorry

Gaby

[jeffib]

Hi Arun,

 

Are you still seeing this issue? Feel free to re-post on the infobright forum or send me a shout. I know this post is five months old, so I'm not sure on the your current status.

 

Thanks,

 

Jeff

ICE Community Manager

jeff@infobright.com