US and Worldwide: +1 (866) 660-7555
Results 1 to 6 of 6

Thread: lot of readTuples query for single mdx query

  1. #1
    Join Date
    Nov 2013
    Posts
    5

    Default lot of readTuples query for single mdx query

    For a single MDX query, there seems to be lot of SqlTupleReader.readTuples queries generated.
    Lets say the MDX query is for dates 2013-12-01 and 2013-12-02, there are seperate readTuples queries for 2013-12-01 and 2013-12-02.
    This can be done in a single SQL query using IN clause. But mondrian doesn't do that.

    Is it is bug in mondrian or is there any mondrian configuration setting to control this ?

    BTW, i'm using mondrain version 3.5.

  2. #2
    Join Date
    Jan 2013
    Posts
    357

    Default

    Agreed, there are a lot of tuple queries. Mondrian evaluates the tuples on the axes and in some cases can be smart about the queries it needs to execute to load in members, but in other cases not so much. In general, though, queries against the dimension tables should be far less expensive than fact table queries, so making sure the fact table queries are optimal is given preference.

    There's not really any configuration settings to help with this.

    Are you hitting cases where the dimension queries are driving up the total MDX query time significantly?

  3. #3
    Join Date
    Nov 2013
    Posts
    5

    Default

    Yeah. We have lot of cases where dimension queries are major part of the workload.

    The log entries below for such queries.

    SqlStatement-SqlTupleReader.readTuples [[Hour].[Hour], [Time].[Date]] invoked 50 times for total of 10012ms. (Avg. 200ms/invocation)
    SqlStatement-Segment.load invoked 1 times for total of 458ms. (Avg. 458ms/invocation)


    Any workarounds for this ?

  4. #4
    Join Date
    Jan 2013
    Posts
    357

    Default

    Sorry, there's not a good way to reduce the number of tuple queries. The best I can offer is to make sure you have effective aggregate tables in place. SqlTupleReader queries like the one you have above should also take advantage of aggregate tables.

  5. #5
    Join Date
    Nov 2013
    Posts
    5

    Default

    @mcampbell,
    I modified the executeList function in RolapNativeSet.java in the mondrian version 3.5.0
    Added the below code in the beginning of the function. Basically i return the members as it is without firing readTuple SQL queries.
    Do you find anything wrong with this logic ?

    I'm facing an issue of returning wrong results for the queries. The results are from cache and the cache is wrongly populated. Also this issue is very rare, so it makes difficult for me to reproduce. Any help on this would be greatly appreciated.

    boolean descendantQuery = false; List<List<RolapMember>> TempResult = new ArrayList<List<RolapMember>>();

    for (CrossJoinArg arg : args) {
    if(arg instanceof DescendantsCrossJoinArg)
    descendantQuery = true;

    List<RolapMember> resultRow = new ArrayList<RolapMember>();
    List<List<RolapMember>> TempResult1 = new ArrayList<List<RolapMember>>();

    if(arg!=null && arg.getMembers()!=null && arg.getMembers().size() > 0)
    {
    for(int i=0;i<arg.getMembers().size();i++){
    if(TempResult.size() > 0){

    for(int j=0; j< TempResult.size();j++){
    resultRow = new ArrayList<RolapMember>(TempResult.get(j));
    resultRow.add(arg.getMembers().get(i));
    TempResult1.add(resultRow);
    }
    }else{
    resultRow = new ArrayList<RolapMember>();
    resultRow.add(arg.getMembers().get(i));
    TempResult1.add(resultRow);
    }
    }
    TempResult = new ArrayList<List<RolapMember>>(TempResult1);
    }
    }

    List<Member> members = new ArrayList<Member>();
    for(int i=0;i<TempResult.size(); i++){
    for(int j=0; j<TempResult.get(i).size();j++)
    members.add(TempResult.get(i).get(j));
    }

    if(members.size() > 0){
    TupleList resultModified = new ListTupleList(args.length, members);
    if(!descendantQuery && !MondrianProperties.instance().UseReadTupleSqlQueries.get() && resultModified!=null)
    {
    return resultModified;
    }
    }

  6. #6
    Join Date
    Jan 2013
    Posts
    357

    Default

    Hi arvindkumar. I'm not totally clear on what your change is intended to do. I will say that the so-called "Native" classes are somewhat brittle, and we've repeatedly seen cases where changes to fix or improve one case will break other cases. If you would like to pursue making improvements to this code to reduce unnecessary queries, though, I'd encourage you to write some unit tests that demonstrate the desired behavior, test it against your change, and then start up a conversation on the dev mailing list.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •