How to set default SQL options for all SQL queries in OpenLink Virtuoso

Question

Currently I actively exploit a very attractive ability of Virtuoso to define SQL views using SPARQL. When I use these views in a composite SQL query without any Virtuoso-specific options, the execution time is enormously large. So, additional options should be included at the end of each query, like

SELECT COUNT(*) from V1 JOIN V2 ON V1.s = V2.d ..... option (loop, order)

Is there a way to configure Virtuoso by a such manner, that the options clause will be added automatically and the SQL query itself will not include it ?

Answer 1

The query options like loop and order (as well as index) are fine-tuning hints for Virtuoso's built-in SQL optimizer; as such, they cannot be set globally (because they should not be).

Some queries may indeed work better with these set, but this certainly will not apply for every SQL query; in fact, in most cases, their use would produce worse execution plans. If you compose a SQL query and find Virtuoso does not make an optimal plan, this is when such on-demand fine tuning can be appropriate.

That said, it would be very helpful if any SQL queries that are producing worse execution plans were reported to us (OpenLink Software produces Virtuoso, and employs me) via the OpenLink Community Forum for any edition of Virtuoso and/or a GitHub issue for the Open Source Edition (a/k/a VOS), so we can look into them and possibly improve or fix the query optimizer.