Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: ­Only 8 values IN Clause in generated SQL

  1. #1
    Join Date
    Mar 2009

    Default ­Only 8 values IN Clause in generated SQL

    Hello !
    Going from mondrian 3.3 to 3.5 brought a strange phenomenon.

    First, some query take 5X the time to execute. Looking at the mondian SQL log, I noticed that some query are unsing the IN clause with only 8 members at a time, repeting the SELECT to include all the 800+ value:

    SELECT ...
    AND DeptCode IN (1,2,3,4,5,6,7,8)
    SELECT ...
    AND DeptCode IN (9,10,11,12,13,14,15,16)

    My mondrian­.­rolap­.­maxConstraints parameter is set at 1000. I tried increasing it to 10000 with no changes.

    Is this parameter still used in 3.5 ? Why do I only get 8 values per IN clause ?

    Database: SQLServer 2008

    Thanks !

  2. #2
    Join Date
    Jan 2013


    This is likely the result of mondrian.rolap.cellBatchSize. cellBatchSize is a feature that was added as a safety valve to limit the number of cells that are requested at a time. This prevents large queries from consuming an unbounded amount of memory, but can result in less efficient sql. I'd recommend setting cellBatchSize to a very large number and see whether the query count is reduced. The trick with that property is to set it high enough the be effective for your expected workload, but not so high that you have queries exceeding the memory you've allocated to mondrian. It defaults to 100000 if unset, which is probably about right for the majority of use cases.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.