SqlAlchemy+pymssql. Will raw parametrized queries

问题:

In my application I have parametrized queries like this:

res = db_connection.execute(text("""
        SELECT * FROM Luna_gestiune WHERE id_filiala = :id_filiala AND  anul=:anul AND luna = :luna
        """), 
     id_filiala=6, anul=2010, luna=7).fetchone()

Will such query use same query execution plan if I run it in loop with different parameter values?

回答1:

It seems unlikely. pymssql uses FreeTDS, and FreeTDS performs the parameter substitution before sending the query to the server, unlike some other mechanisms that send the query "template" and the parameters separately (e.g., pyodbc with Microsoft's ODBC drivers, as described in this answer).

That is, for the query you describe in your question, pymssql/FreeTDS will not send a query string like

SELECT * FROM Luna_gestiune WHERE id_filiala = @P1 AND anul = @P2 AND luna = @P3

along with separate values for @P1 = 6, @P2 = 2010, etc.. Instead it will build the literal query first, and then send

SELECT * FROM Luna_gestiune WHERE id_filiala = 6 AND anul = 2010 AND luna = 7

So for each parameteized query you send, the SQL command text will be different, and my understanding is that database engines will only re-use a cached execution plan if the current command text is identical to the cached version.

Edit: Subsequent testing confirms that pymssql apparently does not re-use cached execution plans. Details in this answer.