[Mondrian] Multi-threading SQL execution

Matt Campbell mkambol at gmail.com
Fri Feb 9 11:14:21 EST 2007


Thanks for the responses, Michael and John.

We had been thinking about both 1 and 2.  Your right that maintaining
different SQL generation strategies could be tricky, but the enhancement
could have a huge impact.  Our DBA says 3-5 times performance boost when
using CUBE() over separate queries.

We may be willing to take on the work, but we'll need help identifying an
approach.

Michael, regarding the optimization you mentioned in the next release-- that
would not help with distinct count measures, is that correct?  What I've
been told is that at least some databases (Oracle, DB2) will correctly
compute count distincts when using CUBE and ROLLUP.

I also had a question about option (2):  You mentioned that running two
parallel queries won't work because trasaction context is mapped to the
thread.  If we're spawning separate threads for each query execution,
wouldn't that get past the limitation?



On 2/8/07, michael bienstein <mbienstein at yahoo.fr> wrote:
>
> Yes, there has been thought about it.  I'm sure Julian can fill you in.
>
> The main issues I see are:
> 1) Either you let the database do the parallelism, in which case you need
> to issue effectively multiple GROUP BYs in one SELECT (which is possible on
> some databases such as ORACLE and even MySQL with its ROLLUP option) and
> then disentangle the results,
> but this is not a standard in SQL.
> 2) Or you are really running two parallel queries in two separate
> connections (almost no JDBC driver will allow parallel queries.  The JTS/JTA
> maps transaction context to thread and JDBC drivers must obey this).
> 3) Or you have separate databases and have obviously different Connection
> objects open.
>
> In the options (2) and (3) you need to revisit the way JDBC connections
> are handled by Mondrian.  In option (1) you need to maintain different SQL
> generation strategies.  All nice ideas but a lot of work.  Yes I've thought
> about it.  But who can do it?  Frustrating.
>
> Michael
>
> ----- Message d'origine ----
> De : Matt Campbell <mkambol at gmail.com>
> À : Mondrian developer mailing list <mondrian at pentaho.org>
> Envoyé le : Jeudi, 8 Février 2007, 23h12mn 38s
> Objet : [Mondrian] Multi-threading SQL execution
>
>
> We've been looking into ways to improve performance when running against
> large fact tables.  One candidate we identified was to multi-thread SQL
> query execution.  As things stand today, in Mondrian, SQL queries are
> executed sequentially.  This means that if an MDX query involves 2 fact
> tables, and each fact table takes 5 seconds to query, that it takes Mondrian
> at least 10 seconds to get results.  Running the two queries simultaneously
> would likely produce much better speed (of course dependent on the RDBMS
> capacity).
>
> Has anyone else considered multi-threading SQL execution?
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
> ------------------------------
> Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions
> ! Profitez des connaissances, des opinions et des expériences des
> internautes sur Yahoo! Questions/Réponses<http://fr.rd.yahoo.com/evt=42054/*http://fr.answers.yahoo.com>
> .
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20070209/24b01886/attachment.html 


More information about the Mondrian mailing list