none
SQL statements with parameters vs. parameter embedded RRS feed

  • Question

  • Hi everybody,

    Can you please give me pros and cons in using parameters when sending SQL statements from C# code vs. having them with parameters embedded into the code (there is no chance for SQL injection in that code, the parameters are all numeric).

    Thanks a lot in advance. [My preference to always go with parameters but my colleague prefers to use them embedded and he is in charge of the project - he even adjusted some of my code to not use parameters]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, November 12, 2019 5:15 PM
    Moderator

Answers

  • Hi Naomi,

    I'm not Erland, Tom or Dan - hopefully they will add insights. Just two cents here:

    Lousy plan re-use. Every query will have its own hash (unless your "parameters" are identical) so bunch and bunch of single-use plans in cache. There are ways to address this, but no *really* good ones.

    Potentially very very high turnaround of cached plans.

    Which in term limits monitorability to some degree.

    You have the responsibility to double up single quotes, make sure datetime literals work etc.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, November 13, 2019 7:29 PM
    Moderator
  • Hi Jorg,

    I glanced through the article. Do I understand you correctly that you're favoring "embedded" parameters in order to avoid the possibility of the parameter sniffing?

    Thanks.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Hi Naomi,

    Jörg referenced a blog article from me (thank you Jörg) :)

    I'm doubtful about the question of using parameters or literals.
    Tibor has already highlighted the problem cases. Basically, the advantage of parametrization is that cached plans can be reused. The use of parameters prevents the query hash from being changed and thus the problem of plan cache bloating does not occur.
    On the other hand, as Rachel has already pointed out, the use of parameters increases the risk of parameter sniffing. My blog article is supposed to show how to solve this problem.
    Even if you can exclude SQL injection as written by you, the solution with literals has pitfalls in addition to Plan cache bloating, which can be just as likely to occur with parameter sniffing-outdated statistics can lead to poor execution plans.
    Personally, I would ALWAYS work with parameters, as the benefits outweigh especially with low-memory systems. If it is apparent that there is an uneven distribution of data due to skewed data, I will only have OPTION (RECOMPILE) for these specific cases to prevent an execution plan from being used again.
    Other alternatives such as OPTION (OPTIMIZE FOR UNKNOWN), QUERY STORE, PLAN_GUIDES have generally not been effective.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Thursday, November 14, 2019 12:17 PM
  • I agree with Tibor and Dan.  The main issue is cached plan reusability.  

    Without parameters, this will not use the cached plan and cause the plan to be regenerated every call.  This could be good or bad, it depends on the situation.  This may also hurt the overall performance because generating a new plan uses server resource and will then be cached and as a result may push another good cached plan out of the cache, hurting another query.

    With parameters, it will use the cached plan (most of the time).  This could be good or bad, depending on the query.  Injection can and should be handled outside of this discussion.

    The main variable is how many times you call it.  If you only run this query a few times, the compile may not be significant.  But, if you call it 1 million times, and the recompile takes 1ms, that is 1ms * 1 million = 1 million ms = 16.6 seconds you are wasting on compile time and server resources and cache space which could be used elsewhere.

    Here is a good discussion on the subject:

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/performance-implications-of-parameterized-queries/


    Thursday, November 14, 2019 1:04 PM
    Moderator
  • Most of us don't enjoy arguing either. Ignoring security and other benefits, the cleaner and more maintainable code alone should justify the practice of using parameters, IMHO. 

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, November 14, 2019 12:50 PM

All replies

  • Hi Naomi N, 

    Thank you for you issue . 

    Sorry foe my poor understanding. I am not sure what is parameters embedded . In SQL Server , I could not  find anything.

    When you use parameters , please attention Parameter Sniffing. For more information, please refer to SQL Query Optimization Techniques in SQL Server: Parameter Sniffing

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 13, 2019 2:21 AM
  • Hi Rachel,

    By embedded I mean that the actual value is used, e.g. 

    select columns from myTable where Column = 123;

    vs.

    select columns from myTable where Column = @parameter;

    and parameter defined with correct type for that column.

    I know about parameter sniffing issue so this would be the cons against using the parameters in this situation. I'm interested in full discussion about pros and cons of both approaches. I personally think that we should be using parameters and not the first way, but I'd like to hear some arguments in support or against my thinking.

    Thanks.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Wednesday, November 13, 2019 3:22 AM
    Moderator
  • Hi Naomi N,

    Thank you for your detailed reply.

    I think we might use parameters on the basis that we have no problem with efficiency. This makes it much easier to reuse code to get the results we want without changing the actual code. 

    Also , I find an article which will show you disadvantage of parameters . Please check it . Using Parameters for SQL Server Queries and Stored Procedures

    So I think everything has two sides, it depends on how you use it and under what circumstances.

    Hope it will help you.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 13, 2019 6:42 AM
  • Thanks, Rachel. I was hoping for some input from Erland or Dan or Tom to validate my point of using parameters. 

    Thanks in advance.

     

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, November 13, 2019 7:10 PM
    Moderator
  • Hi Naomi,

    I'm not Erland, Tom or Dan - hopefully they will add insights. Just two cents here:

    Lousy plan re-use. Every query will have its own hash (unless your "parameters" are identical) so bunch and bunch of single-use plans in cache. There are ways to address this, but no *really* good ones.

    Potentially very very high turnaround of cached plans.

    Which in term limits monitorability to some degree.

    You have the responsibility to double up single quotes, make sure datetime literals work etc.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, November 13, 2019 7:29 PM
    Moderator
  • Hallo Naomi,

    Sie finden hier einen sehr guten Artikel:

    https://www.db-berater.de/2016/09/parameter-sniffing-loesungsansaetze/

    Grüße

    Jörg


    • Edited by Joerg_x Wednesday, November 13, 2019 10:05 PM
    Wednesday, November 13, 2019 10:03 PM
  • Hi Jorg,

    I glanced through the article. Do I understand you correctly that you're favoring "embedded" parameters in order to avoid the possibility of the parameter sniffing?

    Thanks.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, November 13, 2019 10:51 PM
    Moderator
  • Thank you for your reply.

    I still hope my advice will help you. I will wait with you for their replies.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 14, 2019 9:14 AM
  • Tibor is spot on with his comments, some of which I cover in more detail in Why Parameters are a Best Practice article.

    In cases where parameter sniffing is a problem (only relevant for non-trivial plans), query hints (e.g. OPTION RECOMPILE or OPTIMIZE FOR) are your friend. Even better is leveraging the Query Store in newer SQL Server versions to address problem queries.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, November 14, 2019 11:59 AM
  • Hi Jorg,

    I glanced through the article. Do I understand you correctly that you're favoring "embedded" parameters in order to avoid the possibility of the parameter sniffing?

    Thanks.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Hi Naomi,

    Jörg referenced a blog article from me (thank you Jörg) :)

    I'm doubtful about the question of using parameters or literals.
    Tibor has already highlighted the problem cases. Basically, the advantage of parametrization is that cached plans can be reused. The use of parameters prevents the query hash from being changed and thus the problem of plan cache bloating does not occur.
    On the other hand, as Rachel has already pointed out, the use of parameters increases the risk of parameter sniffing. My blog article is supposed to show how to solve this problem.
    Even if you can exclude SQL injection as written by you, the solution with literals has pitfalls in addition to Plan cache bloating, which can be just as likely to occur with parameter sniffing-outdated statistics can lead to poor execution plans.
    Personally, I would ALWAYS work with parameters, as the benefits outweigh especially with low-memory systems. If it is apparent that there is an uneven distribution of data due to skewed data, I will only have OPTION (RECOMPILE) for these specific cases to prevent an execution plan from being used again.
    Other alternatives such as OPTION (OPTIMIZE FOR UNKNOWN), QUERY STORE, PLAN_GUIDES have generally not been effective.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    Thursday, November 14, 2019 12:17 PM
  • Hi Uwe and Dan,

    The reason I asked this question is I personally always try to use parameters and even try to fix code where I see parameters embedded into the query. Unfortunately, I've been part of the project recently where this practice was thrown upon and my colleague preferred of using C# 5 new string extrapolation technique and inserted parameters directly into the sql strings (in our case there is no chance for SQL injection in most cases - the values were either decimal or int for parameters). I'm trying to come up with the convincing arguments to support my way of writing the code. 

    It is hard to argue even when I know I'm right :(

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, November 14, 2019 12:37 PM
    Moderator
  • Most of us don't enjoy arguing either. Ignoring security and other benefits, the cleaner and more maintainable code alone should justify the practice of using parameters, IMHO. 

    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Thursday, November 14, 2019 12:50 PM
  • I agree with Tibor and Dan.  The main issue is cached plan reusability.  

    Without parameters, this will not use the cached plan and cause the plan to be regenerated every call.  This could be good or bad, it depends on the situation.  This may also hurt the overall performance because generating a new plan uses server resource and will then be cached and as a result may push another good cached plan out of the cache, hurting another query.

    With parameters, it will use the cached plan (most of the time).  This could be good or bad, depending on the query.  Injection can and should be handled outside of this discussion.

    The main variable is how many times you call it.  If you only run this query a few times, the compile may not be significant.  But, if you call it 1 million times, and the recompile takes 1ms, that is 1ms * 1 million = 1 million ms = 16.6 seconds you are wasting on compile time and server resources and cache space which could be used elsewhere.

    Here is a good discussion on the subject:

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/performance-implications-of-parameterized-queries/


    Thursday, November 14, 2019 1:04 PM
    Moderator
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 19, 2019 8:42 AM