Databases
StackOverflow2013
Postsdbo
POHow to avoid slow down on Sql Server 2005 parameterized queries build from C#
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to avoid slow down on Sql Server 2005 parameterized queries build from C#
    primarykey
    data
    text
    <p>I'm building a complex query to show some statistics results in a web view. The view can have several different filters depending on the user's choice. Also, there is the possibility to use wildcards. </p> <p>I'm building this query programatically in c# using SqlParameters. So the query looks like this:</p> <pre><code>sc.CommandText = "SELECT * FROM table WHERE field1 = @filter1 AND field2 LIKE @filter2"; //...and more parameters sc.SqlParameters.Add( new SqlParameter("@filter1", SqlDbType.Int, 32) { Value = 1}); sc.SqlParameters.Add( new SqlParameter("@filter2", SqlDbType.VarChar, 446) { Value = "whatever%"}); </code></pre> <p>This is a very simplified version, but the query itself is not the point. Just keep in mind that it can have different optional parameters (which I think it is a pretty common situation).</p> <p>When I ran this query in Sql Manager I realized that there is a huge slow down when using parameters.So, the following two queries, that should be the same, they use a different execution plan that makes the parameterized one run a lot slower:</p> <pre><code>DECLARE @filter1 INT DECLARE @filter2 VARCHAR 446 SET @filter1 = 1 SET @filter2 = "whatever%" SELECT * FROM table WHERE field1 = @filter1 AND field2 LIKE @filter2 </code></pre> <p>The fast version: </p> <pre><code>SELECT * FROM table WHERE field1 = 1 AND field2 LIKE 'whatever%' </code></pre> <p>Here is another example of someone with the same issue:</p> <p><a href="https://stackoverflow.com/questions/510214/why-does-a-parameterized-query-produces-vastly-slower-query-plan-vs-non-parameter">Why does a parameterized query produces vastly slower query plan vs non-parameterized query</a></p> <p>Seems that there is something called <a href="https://groups.google.com/group/microsoft.public.sqlserver.programming/msg/1e4a2438bed08aca?hl=de" rel="nofollow noreferrer">parameter sniffing</a>, that might make a parameterized query run slower, but it does not apply in my case because this is not a stored procedure. </p> <p>One of the <a href="http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx" rel="nofollow noreferrer">solutions</a> proposed is to use OPTION(RECOMPILE) or OPTION(OPTIMIZE FOR). I can't do that because I have about 10 optional parameters, that may be in the filter or not, and this option is not working when using a <code>LIKE</code>.</p> <p>So, I feel I'm in a dead end and I'm thinking on get rid of the parameters and build dynamic literal queries on the code. But then Sql Injection comes in the game. </p> <p>So, do you have any other suggestions on how to solve this issue? Or do you know a safe way to escape the parameters?</p> <p><strong>EDIT</strong>: Here you can see the execution plans for a query with one parameter using <code>LIKE</code>:</p> <ul> <li><a href="https://i.stack.imgur.com/Yn3ip.jpg" rel="nofollow noreferrer">Execution Plan</a></li> </ul> <p><strong>EDIT</strong>: A more simplified representative query execution plan:</p> <ul> <li><a href="https://i.stack.imgur.com/zPcjB.jpg" rel="nofollow noreferrer">Simplified execution plan</a></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USDavid Espart
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to avoid slow down on Sql Server 2005 parameterized queries build from C#
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to avoid slow down on Sql Server 2005 parameterized queries build from C#
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to avoid slow down on Sql Server 2005 parameterized queries build from C#
      1. USJimmy Chandra
      1. VTFavorite
    1. COParameter sniffing does not just apply to stored procedures. It applies to any query where the parameterised execution plan is cached and reused later by other invocations of the query that pass different values for the parameters. When you use literal values the Query optimizer can make certain simplifications to the query that are valid for that particular case but would not be suitable for a plan which needs to be potentially applied to any parameter value (this is different to Parameter sniffing) Can you provide an example execution plan to illustrate the exact problems you are having?
      singulars
      1. POHow to avoid slow down on Sql Server 2005 parameterized queries build from C#
      1. USMartin Smith
    2. CO@Martin I've added the execution plan.
      singulars
      1. POHow to avoid slow down on Sql Server 2005 parameterized queries build from C#
      1. USDavid Espart
    3. CO@despart - Which is the top arrow going into the hash match in the first plan? (I can see in the bottom plan it comes from the `like` I'm just wondering if this is reversed in the good plan)
      singulars
      1. POHow to avoid slow down on Sql Server 2005 parameterized queries build from C#
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload