Параметризованный SQL - в/не с фиксированным количеством параметров, для оптимизации кеша плана запроса?

Если SQL используется напрямую или создается NHibernate, с возможными большими условиями "где в / не в ([от 1 до 100 параметров])", имеет ли смысл заполнять параметры до определенных пределов, чтобы иметь ограниченное количество планов запросов ?

Параметры — int/number, СУБД — MSSQL или Oracle. Запросы вызываются через sp_executesql/executeimmediate для обеспечения кэширования плана запроса.

Обычно такой запрос будет иметь до 100 планов запроса для одного и того же запроса. Несколько таких запросов могут быстро заполнить кеш или привести к снижению производительности из-за того, что кешированные планы запросов вообще не будут использоваться.

Пользователь может заполнить список параметров, повторяя последнее значение, пока не будет достигнуто определенное количество параметров?

Насколько мне известно, MSSQL и Oracle идентифицируют известные запросы по равенству строк, что приводит к разным планам запросов для каждого разного количества параметров.

(значения, конечно, будут параметрами, а не конкатенированными числами).

SELECT * FROM MyTable WHERE Id in (4001, 4002, 4003, ... , 4055, 4056)

с 56 параметрами, изменить на:

SELECT * FROM MyTable WHERE Id in (4001, 4002, 4003, ... , 4055, 4056, 4056, 4056, 4056, 4056)

иметь 60 параметров, повторяя значение 4056, при этом все длинные списки «входов» имеют длину 50, 60, 70, 80, 90, 100. Останется менее 10 параметров.

Для такого запроса до 100 параметров будет 10 планов запроса для 10–100 параметров плюс 9 планов запросов для 1–9 параметров (без заполнения).

EDIT: я обнаружил, что NHibernate (3.1.0.4 или более поздняя версия) и SQL Server с пакетным размером = "200" фактически разбивают списки параметров на несколько операторов со списками параметров фиксированной длины. Например, select со 118 ID-параметрами и batch-size="200" могут быть отправлены как три select со 100, 12 и 6 ID вместо одного со 118 ID. Это похоже на то, что я хотел, batch-size = "200" не с 200 различными строками SQL и, следовательно, планами запросов, накапливающимися с течением времени, а только с меньшим числом, возможно, 16. Казалось, что был один SQL для каждого параметра, подсчитываемого между 1 и 12, затем операторы с 25, 50 и 100 параметрами. Возможно, заполнение повторяющимся значением может быть более эффективным, но это хороший способ обеспечить повторное использование плана запроса.


sql parameters caching sql-execution-plan nhibernate
person Erik Hart    schedule 12.07.2013    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Если у вас есть большое количество параметров запроса, представляющих один и тот же тип значения, они должны быть столбцом в таблице, а не списком параметров.

Если они достаточно статичны, поместите их в таблицу фильтров и выполните:

SELECT t.*
FROM MyTable t
INNER JOIN FilterTable f ON t.Id = f.Id

Если они полностью динамические, используйте параметр с табличным значением. В SQL Server 2008 я могу передать табличный параметр в свою хранимую процедуру из NHibernate. Как добиться того же в Oracle

person Anon    schedule 12.07.2013
comment
К сожалению, NHibernate пока не поддерживает табличные параметры. Связанный пример не является реальной реализацией NHibernate (моделью данных), а просто простым вызовом SQL через NHibernate в классическом стиле ADO.NET. Все значения Id выбираются логикой клиента, здесь нет смысла в таблице фильтров. В любом случае, было бы здорово, если бы NHibernate поддерживал параметры таблицы через свою модель данных! - person Erik Hart; 31.07.2013