[SOLVED] How to append 'OPTION (MAXDOP 1)' to SQLAlchemy select statement?

How to append 'OPTION (MAXDOP 1)' to SQLAlchemy select statement?

I am translating queries from raw Transact-SQL to SQLAlchemy (v1.4 with future mode, happy to consider v2 as well).

Some of the queries limit the max degree of parallelism with OPTION (MAXDOP 1).

SELECT
[...]
OPTION (MAXDOP 1);

Is there a way to append this OPTION (MAXDOP 1) to a SQLAlchemy select without a compiler extension ?

Solution

This is possible using the with_statement_hint method, added to the outermost layer of your query object.

For instance, if you have a query object qry, adding the option would look like this:

qry = qry.with_statement_hint("OPTION (MAXDOP 1)")

This will append that text to the end of the query on SQL Server. Note that this is susceptible to SQL injection, so only leveraging constants would be wise.

Note that you can use any other valid query hint value, such as FORCE ORDER, RECOMPILE, etc. See the query hints documentation for others.

See the docs for more info, though they were not very helpful in my case. The documented behavior is just stated to be "specific to the backend database" with no samples which made it very challenging to find.