参数化SQL-输入/不输入固定数量的参数，用于查询计划缓存优化? [英] Parameterized SQL - in / not in with fixed numbers of parameters, for query plan cache optimization?
问题描述
如果直接使用SQL或由NHibernate创建SQL，且条件可能很大(在[/1到100个参数之间]/不处于[[1至100个参数])，则将参数填充到一定的限制以限制数量是否有意义查询计划?
If SQL is used directly or created by NHibernate, with possibly big "where in / not in ([1 to 100 parameters])" conditions, does it make sense to fill up parameters to certain limits, to have a limited number of query plans?
参数是整数/数字，DBMS是MSSQL或Oracle.通过sp_executesql/executeimmediate调用查询以强制执行查询计划缓存.
Parameters are int/number, DBMS is MSSQL or Oracle. The queries are called via sp_executesql/executeimmediate to enforce query plan caching.
通常，这样的查询将为同一查询提供多达100个查询计划.几个这样的查询可能会迅速填满缓存，或者根本不使用缓存的查询计划，从而导致性能不佳.
Normally, such a query would have up to 100 query plans for the same query. Several such queries might quickly fill up the cache, or result in poor performance by not using cached query plans at all.
用户可以通过重复最后一个值来填充参数列表，直到达到一定数量的参数为止?
A user may fill up the parameter list by repeating the last value, until a certain number of parameters is reached?
据我所知，MSSQL和Oracle通过字符串相等性识别已知查询，从而为每个不同数量的参数产生了不同的查询计划.
As far as I know, MSSQL and Oracle identify known queries by string equality, resulting in a different query plan for each different number of parameters.
(值当然是参数，而不是连接的数字).
(values would of course be parameters and not concatenated numbers).
SELECT * FROM MyTable WHERE Id in (4001, 4002, 4003, ... , 4055, 4056)
具有56个参数，更改为:
with 56 parameters, change to:
SELECT * FROM MyTable WHERE Id in (4001, 4002, 4003, ... , 4055, 4056, 4056, 4056, 4056, 4056)
通过重复值4056具有60个参数，所有长"in"列表的长度分别为50、60、70、80、90、100.仅会保留少于10个参数.
having 60 parameters by repeating value 4056, with all long "in" lists having lengths of 50, 60, 70, 80, 90, 100. Only less than 10 params will be left.
对于具有最多100个参数的此类查询，将有10个查询计划用于10到100个参数，外加9个查询计划用于1到9个参数(无填充).
For such a query with up to 100 parameters, there would be 10 query plans for 10 to 100 parameters, plus 9 query plans for 1 to 9 parameters (no fill).
我发现NHibernate(3.1.0.4或更高版本)和SQL Server具有batch-size ="200"，实际上将参数列表拆分为多个具有固定长度参数列表的语句.例如，具有118个ID参数的选择和batch-size ="200"可以作为具有100、12和6个ID的三个选择发送，而不是具有118个ID的选择发送.这与我想要的类似，batch-size ="200"，没有200个不同的SQL字符串，因此查询计划会随着时间的推移而累积，但是数量较少，也许是16.似乎每个参数计数在1之间的情况下只有一个SQL和12，然后是带有25、50和100参数的语句.也许填充重复的值可能会更有效率，但这是确保查询计划重用的好方法.
I found that NHibernate (3.1.0.4 or later) and SQL Server, with batch-size="200" actually splits parameter lists into multiple statements, with fixed length param lists. For example, select with 118 ID parameters and batch-size="200" may be sent as three selects with 100, 12 and 6 IDs, instead of one with 118 IDs. This is similar to what I wanted, batch-size="200" not with 200 different SQL strings and thus query plans accumulating over time, but only a smaller number, perhaps 16. There seemed to be one SQL for each parameter count between 1 and 12, then statements with 25, 50 and 100 parameters. Maybe filling up with a repeated value -can- be more efficient, but this is a good way to ensure query plan reuse.
推荐答案
如果您有大量都表示相同值类型的查询参数，则它们应该是表中的列，而不是参数列表.
If you have a large number of query parameters that all represent the same value type, they should be a column in a table, not a parameter list.
如果它们足够静态，则将它们放在过滤器表中并执行以下操作:
If they are sufficiently static, put them in a filter table and do:
SELECT t.*
FROM MyTable t
INNER JOIN FilterTable f ON t.Id = f.Id
如果它们是完全动态的，则使用表值参数. 在SQL Server 2008中，我可以将表值参数从NHibernate传递到我的存储过程.如何在Oracle中实现相同的功能
If they are completely dynamic, then use a Table Valued Parameter. In SQL Server 2008 I am able to pass table-valued parameter to my stored procedure from NHibernate.How to achieve the same in Oracle
这篇关于参数化SQL-输入/不输入固定数量的参数，用于查询计划缓存优化?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
