1с в поступившем запросе слишком много параметров сервер поддерживает не более 2100 параметров
Обновлено: 07.07.2024
У меня есть список, содержащий идентификаторы, которые я хочу вставить в временную таблицу с помощью Dapper, чтобы избежать ограничения SQL по параметрам в предложении IN.
В настоящее время мой код выглядит следующим образом:
Я пробовал различные комбинации синтаксиса, но не получил нигде.
Вот где я сейчас:
Хорошо, здесь вам нужна версия. Я добавляю это как отдельный ответ, так как мой первый ответ с использованием SP/TVP использует другую концепцию.
Вам просто нужно внести изменения в свой оператор select, какой он был изначально. Поскольку у меня нет всех ваших таблиц в моей среде, я только что выбрал из созданной таблицы temp, чтобы доказать, что она работает так, как должна.
Ловушки, см. комментарии:
- Откройте соединение в начале, в противном случае таблица temp будет
уйти после того, как dapper автоматически закрывает соединение сразу после
создание таблицы. - Этот особый аромат INSERT INTO ограничен
до 1000 значений за раз, поэтому переданные идентификаторы должны быть разделены на
куски соответственно. - Не передавать повторяющиеся ключи, так как первичный ключ в таблице temp не позволит этого.
Edit
Кажется, Dapper поддерживает операцию на основе набора, которая также сделает эту работу:
Я не знаю, насколько хорошо это будет выполняться по сравнению с предыдущей версией (то есть 2500 одиночных вставок вместо трех вставок с 1000, 1000, 500 значений каждый). Но документ предполагает, что он работает лучше, если использовать его вместе с асинхронными, MARS и конвейерами.
Существует способ сделать это, не используя временную таблицу, используя хранимую процедуру с параметром значения таблицы.
Это создаст тип таблицы, называемый пользователем udtKeys , который содержит только один столбец int с именем i и хранимую процедуру, которая ожидает параметр этого типа. Против ничего не делает, кроме как выбрать идентификаторы, которые вы передали, но вы можете, конечно, присоединиться к другим таблицам. Для подсказки относительно синтаксиса см. Здесь.
Параметр внутри процедуры заполняется передачей DataTable и структура DataTable должна соответствовать типу созданного вами типа таблицы.
Если вам действительно нужно передать более 2100 значений, вы можете захотеть проиндексировать свой тип таблицы для повышения производительности. Фактически вы можете дать ему первичный ключ, если вы не передадите дублирующие ключи, например:
Вам также может потребоваться присвоить разрешения запуска для этого типа пользователю базы данных, с которым вы выполняете это, например:
У меня есть этот, казалось бы, простой запрос linq-to-sql, который ищет данные в нескольких столбцах; что-то вроде этого:
Я получаю такую ошибку:
Дополнительная информация: входящий запрос имеет слишком много параметров. Сервер поддерживает до 2100 параметров. Уменьшите количество параметров и повторно отправьте запрос.
Какой лучший способ решить это?
Я осмотрелся, и решение, которое я нашел, выглядит так:
Это компилируется и не генерирует никаких исключений, но, кажется, игнорирует предложения Where с SomeString.Contains
Какое решение заставить этот запрос работать?
Просто - пока TheTAbleID содержит менее 2100 идентификаторов - это незаконно.
Разрежьте таблицу на блоки по 2000, а затем запросите каждый блок отдельно, возможно, в несколько потоков.
SQL не поддерживает более 2100 значений в операторе in , но вы можете использовать in с таблицей с более чем 2100 строками, чтобы вы могли вставить свои данные в таблицу и изменить свой запрос, чтобы проверить in с выбором из этой таблицы
Столбец guid предназначен для предотвращения смешивания разных пользовательских данных
Также, если вы можете получить идентификаторы из базы данных, вы можете написать функцию табличного значения в sql, чтобы возвращать идентификаторы и моделировать эту функцию в своем коде, скажем, ее имя - fnGetIds . Затем используйте его в своем коде, как показано ниже
Для эффективности вы можете реорганизовать код, чтобы он делал это только в том случае, если список содержит более 2000:
Я получал это и не мог использовать 2100 параметров! Было что-то еще.
При ближайшем рассмотрении я обнаружил, что добавляю предположительно 5 параметров в цикл, но исходный объект не очищался, поэтому список объектов для вставки становился все больше и больше.
Мне пришлось предварительно затемнить новый объект wwo, и он отсортировал его
Проблема здесь в использовании Contains , который должен быть в отдельном запросе:
Часто при внедрении программ «1С: Предприятие 8» возникают ситуации, в которых простые запросы работают достаточно медленно.
Покажем варианты оптимизации таких запросов.
Для примера рассмотрим запрос из реального проекта (в базе клиента он выполнялся более 6 секунд)
МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Месяц,
ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Год,
СУММА(ДенежныеСредстваКПоступлениюБезналичныеОбороты.СуммаПриход) КАК СуммаПриход
РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Документ.Контрагент.Партнер = &Партнер)
C первого взгляда все хорошо, но опытный программист увидит неоптимальный код в запросе.
Источником проблем выступает параметр виртуальной таблицы, а точнее – обращение через «две точки» в фильтре .
В общем случае такой подход допустим, но есть одна проблема: поле «Документ» имеет составной тип, и при получении реквизитов данного поля выполняется соединение с каждой таблицей, входящей в составной тип этого поля .
Самым первым вариантом решения в голову приходит использовать конструкцию языка запросов «ВЫРАЗИТЬ», чтобы привести поле «Документ» к некоторому определенному типу. Это позволит избежать соединений с лишними таблицами. Но по ряду ограничений данный вариант не подходит:
- Нам нужны все документы, содержащиеся в составном типе. Таковы условия постановки задачи. Получается, что необходимо фильтровать все типы документов, входящие в составной тип.
- Даже если бы не было предыдущего ограничения, то обращение через «две точки» никуда не делось.
- Если бы можно было использовать «ВЫРАЗИТЬ», то это не спасало бы ситуацию: в параметрах виртуальной таблицы «ВЫРАЗИТЬ» не дает прироста производительности.
Оптимизация
Исходя из вышесказанного, прежде всего необходимо избавиться от обращения через «две точки» и при этом не испортить саму логику нашего запроса.
Из нескольких способов решения задачи предлагаем два следующих варианта:
Вариант 1
В регистр «ДенежныеСредстваКПоступлениюБезналичные» добавить новое измерение «Партнер», заполняя его при записи движений документов. Ввиду использования условия по данному измерению его необходимо проиндексировать.
После внесенных нами изменений у нас достаточно легко получится наложение фильтра на новое измерение в параметрах виртуальной таблицы:
РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Партнер = &Партнер) КАК
Что мы видим? Этот запрос начинает работать моментально. И это, к сожалению, единственный положительный момент, минусов наблюдается существенно больше. Главный минус – изменение структуры конфигурации, возникают проблемы при последующих обновлениях, использовании типовых обменов и т.д. К тому же у нас хранится дублируемая информация, что приводит к увеличению размера таблицы, а установка признака индексирования повышает скорость чтения, но при этом замедляет запись в регистр. Поэтому рассмотрим второй вариант.
Вариант 2
Можно попробовать изменить запрос так, чтобы фильтр по полю «Документ» накладывался примерно следующим образом:
РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(, , Месяц, Документ В (ВЫБРАТЬ Ссылка ИЗ
ВТ_ДокументыСПартнером)) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты
Что необходимо сделать, чтобы наш запрос пришел к подобному виду? Вначале соберем все документы, входящие в составной тип поля «Документы». Для них должно соблюдаться условие:
В нашем составном типе определены 5 документов, причем искомый реквизит «Контрагент» присутствует только в документах:
- ПоступлениеБезналичныхДенежныхСредств
- СписаниеБезналичныхДенежныхСредств
- РасходныйКассовыйОрдер
- ОперацияПоПлатежнойКарте
Далее сформируем временную таблицу для фильтрации. В ней будут документы, у которых реквизит «Партнер» равен нужному значению. Применим полученный фильтр по документам в нашем запросе:
ИЗ Документ.ОперацияПоПлатежнойКарте КАК ОперацияПоПлатежнойКарте
ГДЕ ОперацияПоПлатежнойКарте.Контрагент.Партнер = &Партнер
ИЗ Документ.ПоступлениеБезналичныхДенежныхСредств КАК ПоступлениеБезналичныхДенежныхСредств
ГДЕ ПоступлениеБезналичныхДенежныхСредств.Контрагент.Партнер = &Партнер
ИЗ Документ.РасходныйКассовыйОрдер КАК РасходныйКассовыйОрдер
ГДЕ РасходныйКассовыйОрдер.Контрагент.Партнер = &Партнер
ИЗ Документ.СписаниеБезналичныхДенежныхСредств КАК СписаниеБезналичныхДенежныхСредств
ГДЕ СписаниеБезналичныхДенежныхСредств.Контрагент.Партнер = &Партнер
МЕСЯЦ(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Месяц,
ГОД(ДенежныеСредстваКПоступлениюБезналичныеОбороты.Период) КАК Год,
ДенежныеСредстваКПоступлениюБезналичныеОбороты.СуммаПриход КАК СуммаПриход
РегистрНакопления.ДенежныеСредстваКПоступлениюБезналичные.Обороты(,, Месяц, Документ В
ВТ_ДокументыСПартнером)) КАК ДенежныеСредстваКПоступлениюБезналичныеОбороты
С другой стороны, можно сначала получить контрагентов с данным партнером и затем искать документы с фильтром по контрагенту, но особой разницы в скорости не наблюдается.
После проведенной оптимизации запрос стал выполняться менее одной секунды! Да, при этом он стал сложнее, но нет необходимости в изменении структуры метаданных, как в первом варианте.
Резюме
Вам представлен вариант решения оптимизации достаточно простого запроса, при котором не возникло необходимости в перестройке метаданных, создании дополнительных индексов.
Рекомендуем оптимизировать запросы посредством изменения текста самого запроса.
Анализ плана выполнения запроса с помощью консоли запросов
В этом видео показан наиболее простой способ получения плана выполнения запроса на СУБД – для этого используется официальная консоль запросов от фирмы «1С».
С помощью этого инструмента можно быстро оценить эффективность выполнения запроса и необходимость его оптимизации.
Читайте также: