Блог

Мне нужно написать запрос для получения большого списка идентификаторов.

Мы поддерживаем многие серверные части (MySQL, Firebird, SQLServer, Oracle, PostgreSQL …), поэтому мне нужно написать стандартный SQL.

Размер набора идентификаторов может быть большим, запрос будет сгенерирован программно. Итак, каков наилучший подход?

1) Написание запроса с использованием В

 SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)
  

Мой вопрос вот в чем. Что произойдет, если n будет очень большим? Кроме того, как насчет производительности?

2) Написание запроса с использованием ИЛИ

 SELECT * FROM TABLE WHERE ID = id1 OR ID = id2 OR ... OR ID = idn
  

Я думаю, что у этого подхода нет предела n, но как насчет производительности, если n очень велико?

3) Написание программного решения:

   foreach (var id in myIdList)
  {
      var item = GetItemByQuery("SELECT * FROM TABLE WHERE ID = "   id);
      myObjectList.Add(item);
  }
  

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

Каким было бы правильное решение этой проблемы?

Вариант 1 является единственным хорошим решением.

Почему?

• Вариант 2 делает то же самое, но вы повторяете имя столбца много раз; кроме того, механизм SQL не сразу узнает, что вы хотите проверить, является ли значение одним из значений в фиксированном списке. Однако хороший движок SQL мог бы оптимизировать его, чтобы иметь такую же производительность, как с IN . Однако все еще существует проблема с удобочитаемостью…

• Вариант 3 просто ужасен с точки зрения производительности. Он отправляет запрос в каждом цикле и загружает базу данных небольшими запросами. Это также предотвращает использование каких-либо оптимизаций для «значения является одним из значений в заданном списке»

То, что предложил Эд Гиннесс, действительно повышает производительность, у меня был запрос, подобный этому

 select * from table where id in (id1,id2.........long list)
  

что я сделал :

 DECLARE @temp table(
            ID  int
            )
insert into @temp 
select * from dbo.fnSplitter('#idlist#')
  

Затем inner объединил временную таблицу с основной таблицей :

 select * from table inner join temp on temp.id = table.id
  

И производительность значительно улучшилась.

Альтернативным подходом может быть использование другой таблицы для хранения значений id. Затем эта другая таблица может быть объединена внутри вашей ТАБЛИЦЫ, чтобы ограничить возвращаемые строки. Это будет иметь главное преимущество в том, что вам не понадобится динамический SQL (проблематичный в лучшие времена), и у вас не будет бесконечно длинного предложения IN .

Вы бы сократили эту другую таблицу, вставили большое количество строк, затем, возможно, создали индекс, чтобы повысить производительность объединения. Это также позволило бы вам отделить накопление этих строк от извлечения данных, возможно, предоставляя вам больше возможностей для настройки производительности.

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

Первый вариант, безусловно, лучший вариант.

 SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)
  

Однако, учитывая, что список идентификаторов очень огромен, скажем, миллионы, вам следует учитывать размеры блоков, как показано ниже:

• Разделите список идентификаторов на фрагменты с фиксированным числом, скажем, 100
• Размер блока должен определяться на основе объема памяти вашего сервера
• Предположим, у вас 10000 идентификаторов, у вас будет 10000/100 = 100 блоков
• Обрабатывать по одному фрагменту за раз, что приводит к 100 вызовам базы данных для select

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

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

Для меня это всегда работало как шарм. Надеюсь, это сработает и для моих коллег-разработчиков 🙂

Выполнение команды SELECT * FROM MyTable, где идентификатор в (), в таблице SQL Azure с 500 миллионами записей привело к времени ожидания > 7 минут!

Вместо этого это немедленно вернуло результаты:

 select b.id, a.* from MyTable a
join (values (250000), (2500001), (2600000)) as b(id)
ON a.id = b.id
  

Используйте соединение.

В большинстве систем баз данных IN (val1, val2, …) и ряд OR систем оптимизированы по одному и тому же плану.

Третьим способом было бы импортировать список значений во временную таблицу и объединить ее, что более эффективно в большинстве систем, если значений много.

Возможно, вы захотите прочитать эти статьи:

• Передача параметров в MySQL: В списке или во временной таблице

Я думаю, вы имеете в виду SQLServer, но в Oracle у вас есть жесткое ограничение на количество элементов IN, которые вы можете указать: 1000.

Пример 3 был бы худшим исполнителем из всех, потому что вы запускаете базу данных бесчисленное количество раз без видимой причины.

Загрузка данных во временную таблицу и последующее объединение в ней были бы, безусловно, самыми быстрыми. После этого IN должен работать немного быстрее, чем группа редакторов.

1. Для первого варианта
   Добавьте идентификаторы во временную таблицу и добавьте внутреннее соединение с основной таблицей.

 CREATE TABLE #temp (column int)
INSERT INTO #temp (column) 
SELECT t.column1 FROM (VALUES (1),(2),(3),...(10000)) AS t(column1)
  

Попробуйте это

 SELECT Position_ID , Position_Name
FROM 
position
WHERE Position_ID IN (6 ,7 ,8)
ORDER BY Position_Name