I am using the latest version of ICE for some data analysis and I’ve found some odd behavior when using views. 

I created a view that returns an id column for a subset of rows in a brighthouse table.  I then join the view with the main table to access some of its other columns.  This is just a convinced to shorten long queries that restrict themselves to this portion of data.  I can write a query that does just that, join the base table with the view and returns some data.

Now, if I try to create a second view based on this query (the ones that joins the base table and the first view of it), and execute a query on this second view, infobright hangs and the mysql process consumes 100% CPU until I cancel the query.

Anyone seen this problem?

Also, the ICE manual claims that it supports SELECT ... INTO OUTFILE, but if I execute the command I get an error saying the syntax is not supported by the infobright optimizer.  I could turn on the mysql query path, but that means that if I try to export a subset of the table the query will not use the infobright optimizer and take too long.

Does ICE not support SELECT ... INFO OUTFILE?

Hi,

ICE supports INTO OUTFILE, so the problem might be either in unsupported SELECT, or unsupported syntax after INTO OUTFILE.

As about views: in ICE views are implemented as non-materialized, so they ate just a syntactical shortcut for a subquery. Views usually does not make anything faster, and the same query written as just a join of several instances of the same table (with different filtering conditions) is a better idea.

Please post the query causing problems, if they persist.

Regards,

My so far(may be too little) experience dares to suggest that, never go for views in Infobright at all, better to use the same query directly on table/s (on which you want to create the view) each time you want to query with filters applied.

. Please! Rectify, if my suggestion is wrong.

Hi,

Generally you’re right. The only situation when the view/subquery may have performance advantages I can imagine is when the result of subquery is much smaller (e.g. a few rows) comparing to the source tables. Also, using views to materialize aggregations before joining may be helpful in some cases.

Regards,

Jakub,

You’ve made a remark about ICE views. Does implementation of views in IEE differ from that in ICE?

Thanks,
Andriy

Jakub Wroblewski - 27 August 2010 02:04 AM

Hi,

ICE supports INTO OUTFILE, so the problem might be either in unsupported SELECT, or unsupported syntax after INTO OUTFILE.

As about views: in ICE views are implemented as non-materialized, so they ate just a syntactical shortcut for a subquery. Views usually does not make anything faster, and the same query written as just a join of several instances of the same table (with different filtering conditions) is a better idea.

Please post the query causing problems, if they persist.

Regards,

Hi,

andrijz - 25 December 2010 06:12 AM

You’ve made a remark about ICE views. Does implementation of views in IEE differ from that in ICE?

No, the query plan and implemented algorithms are identical in case of ICE and IEE. Only parallel execution of some operations makes a difference.

Regards,