SQL Server Performance Forum – Threads Archive
T-SQL, Complex queries and variables
Often when I write complex queries with variables in T-SQL I find that replacing the variables with hard-coded values improves performance considerabley. Is this a well-known phenomenon? Is there a way of keeping the vaiables and achieve the same performance as the "hard coded" scenario? Thanks /Olle Olle, LondonPlease readhttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=523 as it may be relevant to your query
I remember reading about execution plan improving much when values are asigned to parameters in the stored procedure than as default parameter values. Not sure it is your case… Bambola.
Well this depends on if the values that you are passing to the storered procedure are typical values in the table on which query is executed. If the values are not typical, the quey analyzer will use a bad (precompiled for some other typical value) plan for this query and the performace willl be hit. If this is the case then it is a good idea to create stored procedure WITH RECOMPILE option. This way every time SP is executed, a new plan wikk be generated. Gaurav
]]>