SqlAlchemy+pymssql. Will raw parametrized queries use same execution plan?
<p>In my application I have parametrized queries like this:</p>

<pre><code>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()
</code></pre>

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

<p>I'm guessing that it's unlikely, because pymssql performs the parameter substitution <em>before</em> sending the query to the server, unlike some other mechanisms that send the query "template" and the parameters separately (e.g., pyodbc, as described in <a href="http://stackoverflow.com/a/40065211/2144390">this answer</a>).</p>

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



<pre class="lang-sql prettyprint-override"><code>SELECT * FROM Luna_gestiune WHERE id_filiala = @P1 AND anul = @P2 AND luna = @P3
</code></pre>

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

<pre class="lang-sql prettyprint-override"><code>SELECT * FROM Luna_gestiune WHERE id_filiala = 6 AND anul = 2010 AND luna = 7
</code></pre>

<p>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 <em>identical</em> to the cached version.</p>

<p><strong>Edit:</strong> Subsequent testing confirms that pymssql apparently <em>does not</em> re-use cached execution plans. Details in <a href="http://stackoverflow.com/a/40156617/2144390">this answer</a>.</p>

python sqlalchemy pymssql