[Mondrian] Native TopCount
patl at seewind.com
Mon Jan 9 14:14:24 EST 2012
Just curious what types of top count queries Mondrian will decided to run in native SQL when mondrian.native.topcount.enable is true. We have yet to be able to find any query that Mondrian will top count in native SQL, everything is done in Java every time. Unfortunately for this situation we need 5 top 5's off a set of millions (iterative top count in a generate), and it's taking Mondrian up to 296 seconds to compute the results and seemingly ordering them itself.
Also from what I've seen of the code, the native top count only uses order by and not limit. I realize in the SQL spec LIMIT is just a reserved word and not actually part of the language specification, but MySQL, PostgreSQL and even SQLite support it in syntactically identical methods on compound SELECT statements. Yes, Microsoft is Microsoft and they don't support LIMIT in T-SQL but the same effect can be achieved by using TOP or ROW_NUMBER() OVER ORDER BY WHERE row <= X (depending on which version of SQL Server they're using).
I'd be willing to take a stab at adding this to the native dialects, just curious if there was a reason that it wasn't being used already (and thus would be a fruitless effort).
SeeWind Design Corp
e: patl at seewind.com
p: 613.288.0326 x 99
More information about the Mondrian