#sql #sql-server-2005 #tsql
#sql #sql-server-2005 #tsql
Вопрос:
Спецификация по сути представляет собой текстовую строку, представляющую предложение «where», созданное конечным пользователем.
У меня есть хранимые процедуры, которые копируют набор связанных таблиц и записей в другие места. Операция всегда одинакова, но зависит от некоторых сумасшедших требований пользователя, таких как «замороженные и синие продукты, поступающие в продажу во вторник».
Что, если мы передадим пользовательскую спецификацию (или строковый параметр) скалярной функции, которая вернет true / false, которая выполнит спецификацию как динамический SQL или просто exec (@variable). Это может сказать нам, существуют ли эти записи. Мы могли бы добавить результат функции в наше предложение copy products where.
Это удержало бы нас от перекомпиляции сценария копирования каждый раз, когда наши предложения where изменялись. Кроме того, это позволило бы изолировать выбор продукта в одной функции.
Кто-нибудь когда-нибудь делал что-нибудь подобное или у кого есть примеры? Что плохого может из этого получиться?
Редактировать:
Это спецификация, которую я просто добавил в конец каждого оператора insert / select:
and exists (
select null as nothing
from SameTableAsOutsideTable inside
where inside.ID = outside.id and -- Join operations to outside table
inside.page in (6, 7) and -- Criteria 1
inside.dept in (7, 6, 2, 4) -- Criteria 2
)
Было бы здорово передать параметр в функцию, которая создает записи на основе пользовательских критериев, поэтому все, что указано выше, может быть чем-то вроде:
and dbo.UserCriteria( @page="6,7", @dept="7,6,2,4")
Комментарии:
1. Похоже, вы пытаетесь создать конструктор запросов, чтобы определить, существуют ли данные, до выполнения фактической операции копирования?
2. Использовать Exists() для определения, какие записи копировать, на основе пользовательских критериев.
3. Худшим в предлагаемой функции, вероятно, является производительность — ваша функция будет вызываться один раз для каждой записи; если производительность не имеет значения (например, редкий процесс в таблице с небольшим количеством данных), то этот подход действительно может быть более чистым.
4. 1 для Tao. Мне было интересно, почему это было так медленно. Я изменил UDFS на временные таблицы, и это увеличило производительность примерно на пять секунд. Удивительные.
Ответ №1:
Динамические условия поиска в T-SQL
При оптимизации SQL важным моментом является оптимизация пути доступа к данным (т. Е. использование индекса). Это превосходит повторное использование кода, удобство обслуживания, хорошее форматирование и практически все другие преимущества разработки, о которых вы можете подумать. Это связано с тем, что неправильный путь доступа приведет к тому, что запрос будет выполняться в сотни раз медленнее, чем следовало бы. В статье, на которую дана ссылка, очень хорошо суммируются все имеющиеся у вас варианты, и ваша предполагаемая функция нигде не видна. Ваши варианты будут ориентированы на динамический SQL или очень сложные статические запросы. Боюсь, бесплатного обеда по этой теме не будет.
Комментарии:
1. Вы практически никогда не ошибетесь, следуя советам Эрланда по любому SQL
2. Хорошее чтение. Я попытался отправить список<string> в параметры с табличными значениями SQL 2005, но он их не поддерживает. Это было бы идеально в данной ситуации.
3. Я думаю, что он поддерживает List<DBDataRecord>
Ответ №2:
По-моему, это не очень хорошая идея. Даже предположив, что у вас было надлежащее защитное кодирование, позволяющее избежать атак с использованием SQL-инъекций, это ничего вам не даст. Код по-прежнему необходимо «компилировать» каждый раз.
Кроме того, почти всегда плохая идея позволять пользователям создавать предложения WHERE в произвольной форме. Пользователи довольно хорошо умеют находить новые и новаторские способы довести сервер до полной остановки.
Если вы, или ваши пользователи, или кто-то еще в бизнесе не можете сформулировать некоторые конкретные требования к поиску, то, скорее всего, кто-то недостаточно серьезно об этом думает и на самом деле не знает, чего хочет. Вы можете иметь довольно универсальные возможности поиска, не позволяя пользователям полностью отключиться от системы. В качестве альтернативы, посмотрите на некоторые из существующих инструментов BI и рассмотрите возможность создания витрины данных, где они могут выполнять такого рода поиск ad hoc.
Комментарии:
1. Это реклама. Они повторно используют содержимое предыдущих недель для незначительных изменений на этой неделе, копируя содержимое на основе постоянно меняющихся критериев. В шести-двенадцати таблицах имеются тысячи похожих записей. Копирование выполняется достаточно просто. Если бы это были C # и Linq, я бы просто создал функцию делегирования и отправил ее в службу данных.
Ответ №3:
Как насчет этого: вы создаете другую процедуру хранения (вместо функции) и передаете ей правильное условие. На основе этого условия он сбрасывает идентификаторы записей во временную таблицу.
Следующая процедура перемещения будет считывать идентификаторы из этой таблицы и выполнять необходимые действия?
Или вы могли бы создать пользовательскую функцию, которая возвращает таблицу, представляющую собой не что иное, как идентификаторы записей, соответствующие вашим критериям (динамические)
Если я полностью отключен, то, пожалуйста, разъясните мне.
Надеюсь, это поможет.
Комментарии:
1. Это то, о чем я думал. Теперь вопрос в том, как закодировать это с использованием TSQL? Если бы у нас была табличная функция, которая принимала критерии и создавала пустые записи, подобные приведенному в примере, она, безусловно, выбрала бы правильные записи.
2. Передайте свои критерии второму сохраненному процессу ИЛИ определяемой пользователем функции. Затем вы создаете динамический запрос на основе параметра. Выполните печать на динамическом sql. Затем попробуйте выполнить этот динамический sql, чтобы увидеть, что он возвращает правильный набор идентификаторов. Вам просто нужны соответствующие идентификаторы, выгружаемые store proc или func в некоторую временную таблицу. После того, как вы сбросили данные во временную таблицу, все, что вам нужно, это присоединиться к основной таблице, чтобы сделать то, что вам нужно. Надеюсь, это поможет.
3. Попробовал UDF, который выполнялся для каждой присоединенной записи (ой.) Затем попробовал временную таблицу без индекса (без перекомпиляции), и это было молниеносно. (где страница в (выберите значение из #somelist )). Ключевым моментом было бы не перекомпилировать SP каждый раз. Сейчас 2005 год, поэтому нет параметров таблицы, которые были бы идеальными. Однако это, вероятно, оптимизирует SP на основе значений параметров. Возможно, мне следует создать SP для каждого изменения параметра (что было бы быстро, но непрактично). Возможно, перекомпиляция SP при каждом выполнении — неплохая вещь, которая подтолкнула бы меня обратно к динамическому SQL.
Ответ №4:
Если вы вынуждены использовать динамические запросы и у вас нет каких-либо твердых и предопределенных требований к поиску, настоятельно рекомендуется использовать sp_executesql вместо EXEC . Он предоставляет параметризованные запросы для предотвращения атак SQL-инъекций (в некоторой степени) и использует планы выполнения для ускорения производительности. (Дополнительная информация)