Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to use an optimization for all operations in sp_executesql? Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 7:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 1, 2014 9:15 AM
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...
Post #1566698
Posted Thursday, May 1, 2014 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:34 PM
Points: 13,481, Visits: 12,342
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)
Post #1566704
Posted Thursday, May 1, 2014 9:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 15,739, Visits: 28,147
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1566746
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse