Sql fetch в цикле

В этой статье я попробую показать как избежать одного из признаков «загнивающего» кода, а именно SQL запросы внутри циклов. Примеры будут на простом PHP без использования ООП. Это значительно облегчит понимание. Прочтение займет от 5 до 10 минут.

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

Все очень просто. Каждый запрос это потеря времени на «подготовительные» и «заключительные» операции по обращению к БД. Предположим к каждому товару есть отдельная таблица для картинок, скидок и каких-нибудь опций(например выбор цвета платья). То есть для получения информации о каждом товаре, вам нужно будет выполнить 3 запроса. Т.е. 300 запросов на вывод 100 товаров + запрос на получение списка товаров. Итого 301 запрос на 100 товаров. И как результат, существенное снижение производительности вашего приложения. Этого можно и стоит избегать.

Ниже кусочек кода из OpenCart 3:

public getProducts($data = array()) { //….. $query = $this->db->query($sql); foreach ($query->rows as $result) { // for never get one more with same product id if(!isset($product_data[$result[‘product_id’]])){ $product_data[$result[‘product_id’]] = $this->getProduct($result[‘product_id’]); } } return $product_data; } /* $this->getProduct($result[‘product_id’]) Делает выборку 1 товара из базы. Т.е. метод getProducts вытаскивает product_id-ы из базы, и потом в цикле “тащит” полные данные на каждого из товаров. Очень классное решение (сарказм). */

Задача для оптимизации

Рассмотрим задачу с двумя таблицами – Товары и Цены. У одного товара может быть несколько цен разного типа. Результат нужно получить в виде массива:

_r($products) /* Array ( [1] => Array ( [product_id] => 1 [name] => Товар 1 [prices] => Array ( [0] => Array ( [price_id] => 45 [product_id] => 1 [type] => 3 [price] => 95.00 ) [1] => Array ( [price_id] => 55 [product_id] => 1 [type] => 1 [price] => 90.00 ) [2] => Array ( [price_id] => 58 [product_id] => 1 [type] => 2 [price] => 90.00 ) ) ) ) */

Таблица товаров:

CREATE TABLE `product` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; INSERT INTO `product` (`product_id`, `name`) VALUES (1, ‘Товар 1’); INSERT INTO `product` (`product_id`, `name`) VALUES (2, ‘Товар 2’); INSERT INTO `product` (`product_id`, `name`) VALUES (3, ‘Товар 3’); INSERT INTO `product` (`product_id`, `name`) VALUES (4, ‘Товар 4’); INSERT INTO `product` (`product_id`, `name`) VALUES (5, ‘Товар 5’); INSERT INTO `product` (`product_id`, `name`) VALUES (6, ‘Товар 6’); INSERT INTO `product` (`product_id`, `name`) VALUES (7, ‘Товар 7’); INSERT INTO `product` (`product_id`, `name`) VALUES (8, ‘Товар 8’); INSERT INTO `product` (`product_id`, `name`) VALUES (9, ‘Товар 9’); INSERT INTO `product` (`product_id`, `name`) VALUES (10, ‘Товар 10’);

Таблица цен:

CREATE TABLE `product_price` ( `price_id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) NOT NULL, `type` enum(‘1′,’2′,’3’) NOT NULL, `price` decimal(10,2) NOT NULL, PRIMARY KEY (`price_id`) ) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8; INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (55, 1, ‘1’, 90.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (58, 1, ‘2’, 90.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (45, 1, ‘3’, 95.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (56, 2, ‘1’, 90.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (49, 2, ‘2’, 45.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (42, 2, ‘3’, 96.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (57, 3, ‘1’, 23.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (47, 3, ‘2’, 53.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (51, 3, ‘3’, 12.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (43, 4, ‘1’, 89.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (46, 4, ‘2’, 4.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (52, 4, ‘3’, 15.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (54, 5, ‘1’, 43.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (41, 5, ‘2’, 44.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (48, 5, ‘3’, 34.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (44, 6, ‘1’, 26.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (59, 6, ‘2’, 26.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (60, 6, ‘3’, 26.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (53, 7, ‘1’, 87.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (61, 7, ‘2’, 87.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (50, 7, ‘3’, 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (62, 8, ‘1’, 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (63, 8, ‘2’, 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (64, 8, ‘3’, 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (65, 9, ‘1’, 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (66, 9, ‘2’, 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (67, 9, ‘3’, 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (68, 10, ‘1’, 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (69, 10, ‘2’, 77.00); INSERT INTO `product_price` (`price_id`, `product_id`, `type`, `price`) VALUES (70, 10, ‘3’, 77.00);

Вариант с запросами внутри циклов:

<?php define(‘DB_HOST’ , ‘localhost’); define(‘DB_USER’ , ‘mysqluser’); define(‘DB_PASSWORD’ , ‘password’); define(‘DB_NAME’ , ‘habr’); $conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die(‘Can not connect to db’); mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn)); mysqli_query($conn , ‘SET NAMES utf8’); //получаем все товары – 7 шт. $sql = ‘select * from product’; $product_result = mysqli_query($conn , $sql); //Объявляем массив куда будем собирать все товары $products = array(); while ($product = mysqli_fetch_assoc($product_result)) { //Получаем список цен у текущего товара. $prices = array(); $sql = ‘select * from product_price WHERE product_id = ‘ . (int) $product[‘product_id’]; $price_result = mysqli_query($conn , $sql); while ($price = mysqli_fetch_assoc($price_result)) { $prices[] = $price; } //Полученный список цены помещаем в товар $product[‘prices’] = $prices; //Товар помещаем в массив товаров $products[] = $product; } _r($products); if ($conn) { mysqli_close($conn); }

Итак, чтобы вывести 7 товаров мы сделали 1+7 запросов. Как можно оптимизировать:

1. Выбираем товары из таблицы. Помещаем их в массив, но в качестве ключей будем использовать product_id;
2. Выбираем все цены для найденных товаров;
3. Найденные цены «распихиваем» по товарам.

Тоже самое, но на понятном языке PHP:

<?php define(‘DB_HOST’ , ‘localhost’); define(‘DB_USER’ , ‘mysqluser’); define(‘DB_PASSWORD’ , ‘password’); define(‘DB_NAME’ , ‘habr’); $conn = mysqli_connect(DB_HOST , DB_USER , DB_PASSWORD) or die(‘Can not connect to db’); mysqli_select_db($conn , DB_NAME) or die(mysqli_error($conn)); mysqli_query($conn , ‘SET NAMES utf8’); //получаем 3 товара $sql = ‘select * from product LIMIT 3’; $product_result = mysqli_query($conn , $sql); //Объявляем массив куда будем собирать все товары $products = array(); while ($product = mysqli_fetch_assoc($product_result)) { //Инициализируем цены $product[‘prices’] = array(); //Создаем новый продукт. Ключ уже указываем в качестве product_id $products[(int) $product[‘product_id’]] = $product; } //Проверяем что есть товары, для которых надо получить цены. if (count($products) > 0 ) { //Получаем список product_id-ов для которых теперь нам надо вытащить цены. $product_ids = array_keys($products); //составляем запрос на выборку $sql = ‘select * from product_price where product_id in (‘ . implode(‘,’ , $product_ids ). ‘)’; $prices_result = mysqli_query($conn , $sql) or die(mysqli_error($conn)); while ($price = mysqli_fetch_assoc($prices_result)) { $products[(int) $price[‘product_id’]][‘prices’][] = $price; } } _r($products); if ($conn) { mysqli_close($conn); }

Так теперь, для выборки любого количества товаров нужно будет выполнить всего 2 запроса. Работа такого скрипта будет иметь ощутимую разницу на выборках большого количества товаров. Данный подход можно обобщить. Например, избегайте в циклах обращение в внешним ресурсам(файловой системе, memcache, redis), если такое возможно. И помните о принципе разумности в принимаемых решениях.

Источник

Пересказ статьи Jeremy Kadlec. SQL Server Cursor Example

Проблема

В своем коде T-SQL я всегда использую теоретико-множественные операции. Мне говорили, что SQL Server ориентирован на обработку операций этого типа, и они будут выполняться быстрей, чем последовательная обработка. Я знаю, что существуют курсоры, но я не знаю, как их использовать. Можете дать мне несколько примеров курсора? Можете указать мне, когда использовать курсоры? Я предполагаю, что Microsoft включил их в SQL Server намеренно, поэтому они должны иметь свою нишу, где могут быть использованы эффективно.

Решение

В некоторых кругах курсоры никогда не используются. В других – они последнее средство. И есть группы, которые используют их регулярно. В каждом лагере имеются разные причины, на которых основывается их отношение к использованию курсоров. Так или иначе, они, вероятно, имеют место при определенных обстоятельствах, а при других – нет. Принятие решения о том, является ли обработка на базе курсора уместной, или же нет, сводится к вашему пониманию техники написания кода, а потом – к вашему пониманию существующей проблемы. Для начала давайте сделаем следующее:

• Рассмотрим пример курсора
• Разобьем курсор на компоненты
• Дадим дополнительные примеры курсора
• Проанализируем аргументы за и против использования курсоров

Давайте начнем с примера курсора в SQL Server, а затем ответим на все связанные с ним вопросы.

Пример курсора SQL Server

В качестве примера курсора возьмем простой скрипт для создания резервных копий всех баз данных SQL Server, в котором бэкапы создаются в последовательном стиле:

DECLARE @name VARCHAR(50) — имя базы данных
DECLARE @path VARCHAR(256) — путь для файлов бэкапа
DECLARE @fileName VARCHAR(256) — имя файла бэкапа
DECLARE @file VARCHAR(20) — используется для имени файла
SET @path = ‘C:Backup’
SELECT @file = CONVERT(VARCHAR(20),GET(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_US = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @file + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Что такое курсор в SQL Server

Курсор SQL Server – это набор логики T-SQL, повторяемой в цикле для заданного количества строк по одной за раз. В курсоре может может выполняться обновление одной строки за раз или административный процесс, подобный последовательному созданию резервных копий баз данных. Курсоры используются при разработке, в административных процессах и процессах ETL.

Как написать курсор в SQL Server

Создание курсора – процесс последовательный. Давайте проделаем эти шаги:

2. Объявите ваши переменные (для имен файлов, имен баз данных, номеров счетов и т.д.), которые вам нужны для реализации логики, и присвойте им начальные значения.

   Эта логика будет меняться в зависимости от задачи.

3. Объявите курсор с конкретным именем (как db_cursor в этом примере), которое вы будете использовать на протяжении всей логики вместе с бизнес-логикой (оператор SELECT) для наполнения курсора требуемыми записями. Имя курсора может быть осмысленным. Сразу после этого следует открытие курсора.

   Эта логика будет меняться в зависимости от задачи.

4. Извлеките запись из курсора, чтобы начать обработку.

   Замечание. Число переменных, объявленных для курсора, число столбцов в операторе SELECT и число переменных в операторе FETCH одинаково. В рассматриваемом примере имеется только одна переменная для извлечения данных из единственного столбца. Однако если должно быть пять элементов данных в курсоре, то необходимо также указать пять переменных в операторе FETCH.

5. Обработка данных уникальна для каждого набора логики. Это может быть вставка, обновление, удаление и т.д. для каждой извлекаемой строки данных. Это самый важный набор логики в данном процессе, который выполняется для каждой строки.

   Эта логика будет меняться в зависимости от задачи.

6. Извлечение следующей записи из курсора, как это делалось на шаге 3, а затем шаг 4 снова повторяется при обработке выбранных данных.
7. По завершению обработки всех данных курсор закрывается.
8. На последнем и важном шаге вам необходимо освободить курсор, т.е. освободить все удерживаемые внутренние ресурсы SQL Server.

Теперь рассмотрим с этой точки зрения приведенные ниже примеры, чтобы узнать, когда использовать курсоры SQL Server и как это делать.

— 1 – Объявление переменных
— * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД *
DECLARE @name VARCHAR(50) — имя базы данных
DECLARE @path VARCHAR(256) — путь в файлам резервных копий
DECLARE @fileName VARCHAR(256) — имя файла бэкапа
DECLARE @file VARCHAR(20) — используется для имени файла
— Инициализация переменных
— * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД *
SET @path = ‘C:Backup’
SELECT @file = CONVERT(VARCHAR(20),GET(),112)
— 2 – Объявление курсора
DECLARE db_cursor CURSOR FOR
— Наполнить курсор вашей логикой
— * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД *
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)
— Открыть курсор
OPEN db_cursor
— 3 – Извлечь следующую запись из курсора
FETCH NEXT FROM db_cursor INTO @name
— Проверить состояние курсора
WHILE @@FETCH_US = 0
BEGIN
— 4 – Начало настраиваемой бизнес-логики
— * ЗДЕСЬ ЗАМЕНИТЬ НА ВАШ КОД *
SET @fileName = @path + @name + ‘_’ + @file + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
— 5 – Извлечь следующую запись из курсора
FETCH NEXT FROM db_cursor INTO @name
END
— 6 – Закрыть курсор
CLOSE db_cursor
— 7 – Освободить ресурсы
DEALLOCATE db_cursor

Объяснение синтаксиса курсора

Курсор включает следующие составляющие (на примере выше):

• Операторы DECLARE – объявление переменных, используемых в блоке кода.
• Операторы SETSELECT – инициализация переменных конкретными значениями.
• Оператор DECLARE CURSOR – объявление курсора.

  Замечание: число переменных должно совпадать с числом столбцов в операторе SELECT для оператора DECLARE CURSOR FOR. Это может быть одна или больше переменных, связанных со столбцами в операторе SELECT.

• Оператор OPEN – открывает курсор и наполняет его данными, чтобы начать обработку.
• Операторы FETCH NEXT – Связывает конкретные значения из курсора с переменными в соответствии с оператором DECLARE CURSOR FOR и SELECT

  Замечание. Эта логика используется для начального наполнения до оператора WHILE, а затем повторяется на каждом шаге цикла оператора WHILE.

• Оператор WHILE – условие для начала и продолжения процесса обработки.
• Операторы BEGIN…END – начало и конец блока кода.

  При обработке данных может использоваться множество операторов BEGIN…END.

• Обработка данных – в нашем примере логика заключается в создании резервной копии базы данных по указанному пути и имени файла, но это могут быть любые операторы DML или административные задачи.
• Оператор CLOSE – Освобождает текущие данные и связанные с ними блокировки, но оставляет возможность повторно открыть курсор.
• Оператор DEALLOCATE – уничтожает курсор.

Зачем использовать курсоры в SQL Server

Хотя использование оператора INSERT, UP или DELETE для модификации всех данных, к которым он применяется, в одной транзакции обычно является лучшим способом работы с данными в SQL Server, курсор может понадобиться для:

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

Когда использовать курсоры

Ниже приведен анализ различных сценариев, в которых логика на базе курсора может быть предпочтительной и наоборот:

• OLTP (оперативная обработка транзакций) – в большинстве сред OLTP логика на основе множества строк (INSERT, UP или DELETE) имеет наибольшее предпочтение для коротких транзакций. Наша команда выполняла стороннее приложение, которое использовало курсоры для любой обработки, что вызывало проблемы, но это случалось нечасто. Обычно логики на основе множеств более чем достаточно, и курсоры редко могут понадобиться.
• Отчеты – для проектирования отчетов курсоры обычно не нужны. Однако наша команда столкнулась с требованиями к отчетности, когда не существовало ссылочной целостности в используемой базе данных, и было необходимо использовать курсор для корректного вычисления отчетных значений. Мы имели подобный опыт при необходимости агрегировать данные для последующих процессов. Подход на основе курсора было быстро разработать и выполнить в приемлемой манере.
• Последовательная обработка – если вам необходимо выполнить процесс в последовательной манере, курсоры являются работоспособным вариантом.
• Административные задачи – многие административные задачи, подобные резервированию баз данных или проверки согласованности баз данных, требуется выполнять в последовательной манере, которая хорошо вписывается в основанную на курсорах логику. Но существуют и другие системные объекты, которые удовлетворяют эту потребность. В некоторых из этих случаев курсоры используются для завершения процесса.
• Большие наборы данных – при больших наборах данных вы можете столкнуться с одним или несколькими из следующих случаев:
  • Логика на основе курсора может не масштабироваться в достаточной мере.
  • Операции на основе множеств с большими объемами данных на сервере с минимальным количеством памяти могут привести к тому, что данные будут выгружаться, отнимая много времени и потенциально вызвая конфликты и проблемы с памятью. В этом случае, решение на базе курсора может оказаться приемлемым.
  • Некоторые инструменты фактически кэшируют данные в файл, поэтому обработка данных в памяти может и не иметь места.
  • Если данные могут быть обработаны на автономном SQL Server, то влияние на производственную среду будет оказываться только на финальной стадии обработки. Все ресурсы автономного сервера могут использоваться для процессов ETL, после чего полученные данные импортируются.
  • SSIS поддерживает пакетную обработку наборов данных, которая может решить общую необходимость разбить большой набор данных на более мелкие и справиться с ними лучше, чем построчным методом на базе курсора.
  • В зависимости от того, как закодирована логика курсора или SSIS, может иметься возможность перезапуска с точки сбоя на основе контрольных точек, или обработки каждой строки при помощи курсора. Однако при подходе на основе множеств это может оказаться недоступным, пока не будет обработан весь набор данных. В таком случае найти строку, которая вызвала сбой, будет более сложным.

Как избежать курсоров в SQL Server

Ниже перечислены варианты написания кода T-SQL, позволяющие избежать использование курсоров:

• Логика на основе множеств
  • INSERT или SELECT INTO, или INSERT…SELECT для добавления записей в таблицу за одну транзакцию.
  • UP для модификации одной или многих строк в одной транзакции.
  • DELETE или TRUNCATE для удаления записей из таблицы.
  • Ветвящаяся логика MERGE для вставки, удаления или обновления данных на основе критериев.
• Рассмотрите возможность использования служб интеграции SQL Server (SSIS) для циклического перебора данных, в первую очередь, для извлечения, преобразования и загрузки данных из одной базы данных в другую.
• Команда WHILE для циклического обхода записей в последовательной манере.
• Команда COALESCE для обработки не-NULL значений.
• Системная хранимая процедура sp_MSforeachdb в SQL Server для перебора в цикле всех баз данных в экземпляре.
• Системная хранимая процедура sp_MSforeachtable в SQL Server для перебора в цикле всех таблиц в базе данных.
• Выражение CASE, которое может включать некоторую логику ветвления в обработку данных с помощью оператора SELECT.
• Повторение пакета с помощью команды GO.

Что дальше

• Когда вам нужно принять решение об обработке данных, определите, где вы можете столкнуться с использованием курсоров. Это может иметь место в вашем приложении или в операционных процессах. Существует много способов решить задачу. Использование курсора может оказаться разумной альтернативой в некоторых случаях. Решать вам.
• Если вы сталкиваетесь в проблемами при другом способе кодирования, и необходимо сделать что-то быстро, использование курсора может быть надежной альтернативой. Она может привести к более продолжительной обработке данных, но время написания кода может стать значительно быстрей. Если вам требуется одноразовый процесс или процесс, выполняемый в ночное время, это может помочь.
• Если в вашей среде избегают курсоров, выберите другое надежное решение. Просто убедитесь, что этот процесс не вызовет других проблем. Например, если используется курсор и обрабатываются миллионы строк, не приведет ли это к удалению всех данных из кеша и не спровоцирует ли дальнейшие конфликты? Или при большом наборе данных не будут ли данные сброшены на диск или записаны во временную директорию?
• Оценивая подход на основе курсора по сравнению с другими альтернативами, проведите честное сравнение методов с точки зрения времени, возможности конфликтов и необходимых ресурсов. Надеюсь, что эти факторы приведут вас к правильному способу.

Источник