Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use an optimization for all operations in sp_executesql?


How to use an optimization for all operations in sp_executesql?

Author
Message
jdk77
jdk77
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
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...
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16568 Visits: 17016
jdk77 (5/1/2014)
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...


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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17586 Visits: 32265
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search