[Mondrian] Adding hints to the SQL generated by Mondrian

Julian Hyde julianhyde at gmail.com
Tue Mar 22 20:22:37 EDT 2016


Dan,

It would be useful to have specifics. Would you want Mondrian to generate a hint each time it used a particular table, or a joined a particular pair of tables? Or added a /*hashjoin*/ hint only if the dimension had fewer than 1000 values after filtering? Most of these would require considerable intelligence from Mondrian while generating SQL.

On a related note, there was a proposal a while back to generate a “tag” into each a SQL query (in the form of a comment) that identified which client connection it came from. It would be useful when analyzing the SQL logs on the DB.

Julian



> On Mar 22, 2016, at 1:11 PM, Luc Boudreau <lucboudreau at gmail.com> wrote:
> 
> A long time ago, Table hints were implemented in the schema [1], as sub elements of Tables. It does not allow you to pin it to a specific dialect, and I'm not sure how well implemented it is. There might be places in the code where a table name is added to a query and the hints are not included. This has been one of the less used features, and we have not had many reports of success or failures from people using them. 
> 
> As for adding hints to the select clause, I'm almost positively sure that this is not supported in Mondrian 3. Using views is probably the best option. In Mondrian 4, I'd suggest adding the hints to your columns by changing them to calculated columns and specifying the SQL directly in the schema [2].
> 
> Let us know what your findings are.
> 
> [1] http://mondrian.pentaho.com/documentation/schema.php#Table_hints <http://mondrian.pentaho.com/documentation/schema.php#Table_hints>
> [2] https://github.com/pentaho/mondrian/blob/lagunitas/demo/FoodMart.mondrian.xml#L112-L127 <https://github.com/pentaho/mondrian/blob/lagunitas/demo/FoodMart.mondrian.xml#L112-L127>
> 
> 
> On Tue, Mar 22, 2016 at 3:34 PM, Justin Swanhart <greenlion at gmail.com <mailto:greenlion at gmail.com>> wrote:
> One solution might be using a view in the rdbms that is already hinted.
> 
> Sent from my iPhone
> 
> On Mar 22, 2016, at 3:24 AM, Dan <dan at dankeeley.co.uk <mailto:dan at dankeeley.co.uk>> wrote:
> 
>> Hi,
>> 
>> I've come across a possible situation where we'd like to add some hints to mondrians SQL.
>> 
>> Is this something that would come in the dialect? I see there is some reference to appending hints after the from clause, but in our case we want the hint after the select.  In that case it would be a core mondrian change would it?
>> 
>> (Well I guess you'd have to support post select hints via the dialect right?)
>> 
>> Thanks,
>> Dan
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
>> http://lists.pentaho.org/mailman/listinfo/mondrian <http://lists.pentaho.org/mailman/listinfo/mondrian>
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org <mailto:Mondrian at pentaho.org>
> http://lists.pentaho.org/mailman/listinfo/mondrian <http://lists.pentaho.org/mailman/listinfo/mondrian>
> 
> 
> _______________________________________________
> 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/20160322/4971cf77/attachment-0001.html 


More information about the Mondrian mailing list