英文:
From a parameter-usage viewpoint, is calling a table-valued functions from ADO.NET the same as a stored procedure or the same as a parametrised query?
问题
This article warns about the performance issues related to using parametrized queries instead of stored procedures. The core concern is that when using parametrized queries, the server must infer the data types of your parameters, which can negatively impact caching. The article mentions stored procedures as a solution, but it doesn't address user-defined table-valued functions, which are also strongly typed. ADO.NET primarily supports a special CommandType for stored procedures, so it's unclear how it recognizes user-defined table-valued functions versus raw SQL. The question is whether ADO.NET might treat a call to a user-defined table-valued function as a parametrized query and thus cause performance problems, or if it can intelligently handle user-defined table-valued functions with obvious parameters.
英文:
This article warns of the performance pitfalls of using parametrised queries rather than stored procedures. The basic idea is that the server has to guess the data type of your parameters and its guesses pollute the cache. My confusion comes from the article only mentioning stored procedures as a solution. As far as I know, user-defined table-valued function are just as strongly typed as stored procedures, so the server shouldn't have to guess when using them. However, I also know that ADO.NET only has a special ComamndType for stored procedures, so I don't know how it knows that it's talking to a user-defined table-valued function rather than passing around raw SQL.
My question is this: Given that I can't tell ADO.NET that what I'm telling it to call is a user-defined table-valued function rather than a stored procedure, am I at risk of it treating my call to a user-defined table-valued function as a parametrised query and therefore facing performance issues? Or is ADO.NET smart enough to know that user-defined table-valued functions have obvious parameters?
答案1
得分: 2
不，它们不一样。
虽然两者都将其参数强制转换为相应的类型，因此在支持的查询中没有问题，但针对 TVF 的临时批处理仍然会生成新的查询计划，因此您会面临重新编译的开销。
> 据我所知，用户定义的表值函数与存储过程一样具有强类型，因此服务器在使用它们时不应该猜测。
不，它们是不同的东西。存储过程已经编译了查询计划，而 TVF 只有在使用时才编译它们的计划，因此如果您使用了新的数据类型（或查询中的其他更改），那么将重新编译。
您可以通过检查生成的查询计划来看到这一点在此示例中。请注意，在存储过程情况下，query_hash 值是相同的，但在 TVF 情况下不是。
英文:
No, they are not the same.
While both get their parameters coerced into the respective types, therefore no issues with sargeable queries, an ad-hoc batch against a TVF still gets a new query plan, so you have the overhead of a recompilation.
> As far as I know, user-defined table-valued function are just as strongly typed as stored procedures, so the server shouldn't have to guess when using them.
No they are different things. Procedures have query plans already compiled, TVFs only get their plans compiled when they are used, so if you are using a new data type (or something else changed in the query) then you get a recompile.
You can see this by examining the query plans generated in this fiddle. Note how the query_hash values are the same in the procedure case, but not in the TVF cases.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论