i am using infobright as my database and Mondrian as OLAP tool for my Data warehouse .

many queries generated by Mondrian are not supported by infobright optimiser. those queries are executed by MySQL optimiser only.
before infobright i have been using MySQL MYISAM database. i have used many indexes on myisam.

below is the sample query

select
`DIM_LOCATION`.`country` as `c0`,
sum((case when demo_fact.optout =1 then 1 else 0 end)) as `m0`,
sum((case when demo_fact.touch =1 then 1 else 0 end)) as `m1`,
count( `demo_fact`.`profileid`) as `m2`
from
`DIM_LOCATION` as `DIM_LOCATION`,
`demo_fact` as `demo_fact`
where
`demo_fact`.`open_location` = `DIM_LOCATION`.`emailip`
and
`DIM_LOCATION`.`country` = ‘United States’
group by
`DIM_LOCATION`.`country`

in MySQL it took 2 minutes.
but in infobright it took 15 hours.

below are the explain plans

INFOBRIGHT :

explain select `DIM_LOCATION`.`country` as `c0`, sum((case when demo_fact.optout =1 then 1 else 0 end)) as `m0`, sum((case when demo_fact.touch =1 then 1 else 0 end)) as `m1`, count(distinct `demo_fact`.`profileid`) as `m2` from `DIM_LOCATION` as `DIM_LOCATION`, `demo_fact` as `demo_fact` where `demo_fact`.`open_location` = `DIM_LOCATION`.`emailip` and `DIM_LOCATION`.`country` = ‘United States’ group by `DIM_LOCATION`.`country` ;
+——+——————-+———————+———+———————-+———+————-+———+————-+—————————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+———————+———+———————-+———+————-+———+————-+—————————————————-+
| 1 | SIMPLE | demo_fact | ALL | NULL | NULL | NULL | NULL | 36250 | |
| 1 | SIMPLE | DIM_LOCATION | ALL | NULL | NULL | NULL | NULL | 3359911 | Using where with pushed condition |
+——+——————-+———————+———+———————-+———+————-+———+————-+————————————————-

in MySQL myisam :

explain select `DIM_LOCATION`.`country` as `c0`, sum((case when demo_fact.optout =1 then 1 else 0 end)) as `m0`, sum((case when demo_fact.touch =1 then 1 else 0 end)) as `m1`, count(distinct `demo_fact`.`profileid`) as `m2` from `DIM_LOCATION` as `DIM_LOCATION`, `demo_fact` as `demo_fact` where `demo_fact`.`open_location` = `DIM_LOCATION`.`emailip` and `DIM_LOCATION`.`country` = ‘United States’ group by `DIM_LOCATION`.`country` ;
+——+——————-+———————+————+———————-+————-+————-+—————————————————————+———-+——————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+——+——————-+———————+————+———————-+————-+————-+—————————————————————+———-+——————-+
| 1 | SIMPLE | demo_fact | ALL | open_location | NULL | NULL | NULL | 36250 | |
| 1 | SIMPLE | DIM_LOCATION | eq_ref | PRIMARY | PRIMARY | 18 | TIMEINC_DATAMART.demo_fact.open_location | 1 | Using where |
+——+——————-+———————+————+———————-+————-+————-+—————————————————————+———-+——————-+

i am expecting that INFOBRIGHT database this query is not supported by infobright optimiser and then it was executed by MySQL optimiser.
but in infobriht we don’t have any indexes on tables. that’s why it may take more time. 
do u have any ideas regarding my problem?

how can i solve this problem? please provide me solution.

regards
Raja K

Hi !

Your query includes syntax not supported by IB - e.g. SUM( some expression). Therefore the query is essentially executed by mysql as you suspect.

A usual way to deal with it is to use a subquery to produce the expression results -
SELECT SUM(a) FROM (SELECT case when demo_fact.optout =1 then 1 else 0 end AS a ...)

There are many threads in this forum dealing with this problem.

Also, soon a new revision of ICE/IEE will be released. The new release will handle such queries.

hi Janusz Borkowski

Thank u very much for quick reply. i would like to go with INFOBRIGHT ENTERPRISE EDTION. when will i expect release (solutions for this type quries).
This will more usefull for me. Based on this i wil start initial preparation for my project.

regards,

Raja K

hi all,
i got fix for my problem in infobright-3.2rc1.

The above same query executed in 2 seconds. which taken 15 hours priviously.

i got solutions for many problematic queries in infobright-3.2rc1

thanks,

RAJA K

RAJA - 31 July 2009 01:45 AM

i got fix for my problem in infobright-3.2rc1.

The above same query executed in 2 seconds. which taken 15 hours priviously.

Raja,

That is such an incredible story.  Congratulations!

For the benefit of the rest of the community, and for us here at Infobright, would you mind sharing some of the details?  It would be great to understand under what circumstances such a dramatic difference in performance can occur.  It’s such a big difference that some might find it hard to believe so supporting details would be a big help.

If possible, can you share the SQL statement, the nature of the table(s) - record counts and even DDL, if possible - the server spec ... anything you think might be helpful and are comfortable sharing.

Thanks!

HI DAVID,

Now i am working with sample database which in few million. i mainly concentrate knowledge grid support for my queries.

location: 4 mill
fact:8 mill

DIM_LOCATION;
+————-+——————-+———+——-+————-+———-+
| Field | Type | Null | Key | Default | Extra |
+————-+——————-+———+——-+————-+———-+
| id | int(11) | NO | | NULL | |
| country | va(30) | NO | | NULL | |
| region | char(2) | YES | | NULL | |
| city | va(30) | YES | | NULL | |
| emailip | va(16) | NO | PRI | | |
| cc | char(2) | YES | | NULL | |
+————-+——————-+———+——-+————-+———-+

FACT:
my fact table has 37 mill columns.

my server details:

model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHz
CPU MHz : 2260.998
cache size : 8192 KB
RAM: 8GB

infobright-3.2rc1 gives solution for many of my queries. i didn’t find Enterprise trail version for this.
my major problem is i need to support internationalisation in my product.

Raja,

Thanks for sharing your details.

Regarding the IEE Trial Version, as the Infobright version 3.2 is currently in Release Candidate status, it is not available for general distribution, yet.  This is currently (03Aug09) scheduled for later this month, between the 21st and the 28th of August.

We completely understand your, and many other people’s, needs for internationalization support in your applications and it is in Product Management’s and Engineering’s roadmap and backlog, but at the moment I can’t provide an expected date for release.  I hope you can be patient with us on character set support for UTF-8.

Regards,

Thanks for your quick reply.

We Will wait for internationalisation char-set support.

thanks
RAJA

peropi101,

Can you share the issue you are having in more detail?  Perhaps even start a new thread.  (I assume you are referring to the issue at the beginning of this thread and not the UTF-8 support at the end, right?)

Hi ,

1.In our product i have to collect the data from following data-sources. will infobright support all these?

database charsets:

UTF8
latin9
latin1
big5
ISO 8859-1
shiftjis
gb2312

2. infobright remote LOAD : ( http://www.infobright.org/Forums/viewthread/1147/#5039 )

if we have taken the infobright enterprise edition , can we get these 2 things soon? we already had plan to use IEE.
Can you please give more information Q1? what will be the month?

Thanks,
Raja