If a query is executed with 10 parameters in an IN clause, and then the same query is executed again but with 20 parameters, can the database server reuse the execution plan from the first run, or is a fresh execution plan having to be created each time?
Asked
Active
Viewed 109 times
0
-
Which RDBMS are you using? – Tom H Feb 26 '16 at 16:37
-
Our product supports both MSSQL server and Oracle. – riceball Feb 26 '16 at 16:39
1 Answers
0
I just did a quick check in SQL Server and it looks like each query did indeed receive its own query plan when I added/removed values from the IN clause.
This code will show you the query plans currently cached. You can adjust the check on text to narrow it down to your own specific queries and then see if new rows show up as you execute additional versions of the queries.
SELECT TOP 10
text,
*
FROM
sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) ST
WHERE
text LIKE 'select * from dbo.My_Table where%'
Tom H
- 46,766
- 14
- 87
- 128