May 1, 2014 at 7:14 am
Hi
I have a C# solution with many parameterized sql queries.
I would like to be able to add optimizations to these in a centralized way.
For the sake of simplicity, let's say I want to add the option to OPTIMIZE FOR UNKNOWN for all the queries (in practice I might want to add different optimzations or none to selected queries)...
I've experimented with a query in SSMS and it seems that if you have two selects (so sp_executesql is executing sql that has two selects), then it matters where you insert "OPTION (OPTIMIZE FOR UNKNOWN)".
This is probably all fine and good, but I want to make sure that all statements uses the optimize for unknown option.
How can I (without too much work) make sure that all sql in my parameterized calls uses the option?
I would like to avoid having to edit every query, but instead have one (or a few) central places in my code where I can manipulate the queries.
I was thinking that a simple solution would be to add the option to the end of the commandtext, but some queries have multiple selects (eg. first an insert into a temporary table and then a select all from that temporary table, in which case I believe the option should have been inserted somewhere inside the commandtext to have an effect).
It seems like an inefficient method, if I have to analyze the commandtext and insert multiple option statements...
May 1, 2014 at 7:30 am
jdk77 (5/1/2014)
HiI have a C# solution with many parameterized sql queries.
I would like to be able to add optimizations to these in a centralized way.
For the sake of simplicity, let's say I want to add the option to OPTIMIZE FOR UNKNOWN for all the queries (in practice I might want to add different optimzations or none to selected queries)...
I've experimented with a query in SSMS and it seems that if you have two selects (so sp_executesql is executing sql that has two selects), then it matters where you insert "OPTION (OPTIMIZE FOR UNKNOWN)".
This is probably all fine and good, but I want to make sure that all statements uses the optimize for unknown option.
How can I (without too much work) make sure that all sql in my parameterized calls uses the option?
I would like to avoid having to edit every query, but instead have one (or a few) central places in my code where I can manipulate the queries.
I was thinking that a simple solution would be to add the option to the end of the commandtext, but some queries have multiple selects (eg. first an insert into a temporary table and then a select all from that temporary table, in which case I believe the option should have been inserted somewhere inside the commandtext to have an effect).
It seems like an inefficient method, if I have to analyze the commandtext and insert multiple option statements...
I would ask the question of why do you have to use dynamic sql so often in your application? There are times when dynamic sql is the best choice but it sounds like you are using it most of the time if not more. Why not convert this into stored procedures so you can gain the benefits of plan reuse and deal with parameter sniffing when it is an issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2014 at 9:06 am
OPTIMIZE FOR UNKOWN is a query hint and must be applied to each individual query. It has to be a part of the text, no options. There is no central mechanism for applying it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply