ГлавнаяОптимизацияSql server оптимизация запросов

Sql server- оптимизация запросов со многими столбцами. Sql server оптимизация запросов


sql server - Оптимизация SQL запроса с подзапросами

Всем привет. Нужна помощь в оптимизации запроса. Есть таблица с товарами, которые приходят и уходят с склада. Нужно оптимизировать 4 поля которые делают расчёты с этими товарами. Получается такая ситуация, что при каждом запросе происходит "пролопачивание" всей базы по нескольку раз (т.к. поля с подзапросами). На вход этой функции приходит две даты и в этом промежутке делаются расчёты. В идеале хотелось бы сделать выборку данных, а после чего производить расчёт с полученными данными, как это сделать не пойму.

ALTER FUNCTION [dbo].[GetItemsForPeriodFunc] ( -- Add the parameters for the function here @startDate DATE, @endDate Date ) RETURNS TABLE AS RETURN ( SELECT DISTINCT item.Name, item.Code, --расчет пришедшего товара ISNULL((SELECT SUM(ItemCount) FROM InOutWarehouse AS IOW WHERE IOW.ItemID = InOutWarehouse.ItemID AND DateCreated >= @startDate AND DateCreated <= @endDate AND IOW.InOut = 1),0) AS CountIN, --расчет ушедшего товара ISNULL((SELECT SUM(ItemCount) FROM InOutWarehouse AS IOW WHERE IOW.ItemID = InOutWarehouse.ItemID AND DateCreated >= @startDate AND DateCreated <= @endDate AND IOW.InOut = 0),0) AS CountOut, --расчет остатка с предыдущего периода ISNULL((SELECT SUM(ItemCount) FROM InOutWarehouse AS IOW WHERE IOW.ItemID = InOutWarehouse.ItemID AND DateCreated >= DATEADD(month, -1, @startDate) AND DateCreated <= @startDate AND IOW.InOut = 1),0)- ISNULL((SELECT SUM(ItemCount) FROM InOutWarehouse AS IOW WHERE IOW.ItemID = InOutWarehouse.ItemID AND DateCreated >= DATEADD(month, -1, @startDate) AND DateCreated <= @startDate AND IOW.InOut = 0),0) AS LLP, --расчет наличия товара ISNULL((SELECT SUM(ItemCount) FROM InOutWarehouse AS IOW WHERE IOW.ItemID = InOutWarehouse.ItemID AND DateCreated >= DATEADD(month, -1, @startDate) AND DateCreated <= @startDate AND IOW.InOut = 1),0)-ISNULL((SELECT SUM(ItemCount) FROM InOutWarehouse AS IOW WHERE IOW.ItemID = InOutWarehouse.ItemID AND DateCreated >= DATEADD(month, -1, @startDate) AND DateCreated <= @startDate AND IOW.InOut = 0),0) + ISNULL((SELECT SUM(ItemCount) FROM InOutWarehouse AS IOW WHERE IOW.ItemID = InOutWarehouse.ItemID AND DateCreated >= @startDate AND DateCreated <= @endDate AND IOW.InOut = 1),0)-ISNULL((SELECT SUM(ItemCount) FROM InOutWarehouse AS IOW WHERE IOW.ItemID = InOutWarehouse.ItemID AND DateCreated >= @startDate AND DateCreated <= @endDate AND IOW.InOut = 0),0) AS NLP FROM InOutWarehouse INNER JOIN Item item ON InOutWarehouse.ItemID = item.ID WHERE DateCreated >= @startDate AND DateCreated <= @endDate )

ru.stackoverflow.com

Оптимизация запросов в Microsoft SQL Server

DESCRIPTION

Оптимизация запросов в Microsoft SQL Server. Дмитрий Костылев Начальник отдела разработки системного ПО ОАО « Нордеа Банк » SQL Server MVP 2009-2011. Содержание. Основные понятия Инструменты, поиск «плохих запросов» Анализ плана выполнения - PowerPoint PPT Presentation

Transcript

Конференция по разработке прикладных приложений www.msdevcon.ru 1 Оптимизация запросов в Microsoft SQL Server Дмитрий Костылев Начальник отдела разработки системного ПО ОАО «Нордеа Банк» SQL Server MVP 2009-2011 www.msdevcon.ru 2 Содержание Основные понятия Инструменты, поиск «плохих запросов» Анализ плана выполнения Причины снижения производительности Способы оптимизации запросов Техника написания "быстрых" запросов Табличные переменные и временные таблицы www.msdevcon.ru 3 Основные понятия Оптимизатор План выполнения www.msdevcon.ru Основные понятия: оптимизатор план выполнения Статистика Рекомпиляция Оптимизатор – компилирует запросы. В SQL Server запрос компилируется непосредственно перед первым выполнением. При создании процедуры компиляция не происходит. План выполнения – скомпилированный запрос, в отличии от «обычного» кода его достаточно легко «увидеть» и проанализировать Статистика – информация о распределении значений в полях таблицы Рекомпиляция – повторная компиляция запроса, уже находящегося в процедурном кэше 4 Пример плана выполнения select * from Client c cross apply ( select COUNT_BIG(*) as Cnt from Orders o where o.ClientID = c.ID and o.Status = 'A' ) cn where Status = 'D' www.msdevcon.ru Основные понятия Оптимизатор План выполнения «Процедурный кэш» Статистика Рекомпиляция Логические чтения www.msdevcon.ru Основные понятия: оптимизатор план выполнения Статистика Рекомпиляция Оптимизатор – компилирует запросы. В SQL Server запрос компилируется непосредственно перед первым выполнением. При создании процедуры компиляция не происходит. План выполнения – скомпилированный запрос, в отличии от «обычного» кода его достаточно легко «увидеть» и проанализировать Статистика – информация о распределении значений в полях таблицы Рекомпиляция – повторная компиляция запроса, уже находящегося в процедурном кэше 6 Логические чтения ID Name 1 Иванов 2 Петров 3 Сидоров Клиенты Заказы ID ClientID 1 1 2 3 3 2 4 1 5 2 6 3 www.msdevcon.ru Основные понятия Оптимизатор План выполнения «Процедурный кэш» Статистика Рекомпиляция Логические чтения Прослушивание параметров www.msdevcon.ru Основные понятия: оптимизатор план выполнения Статистика Рекомпиляция Оптимизатор – компилирует запросы. В SQL Server запрос компилируется непосредственно перед первым выполнением. При создании процедуры компиляция не происходит. План выполнения – скомпилированный запрос, в отличии от «обычного» кода его достаточно легко «увидеть» и проанализировать Статистика – информация о распределении значений в полях таблицы Рекомпиляция – повторная компиляция запроса, уже находящегося в процедурном кэше 8 Инструменты SQL Server Management Studio (SSMS) Profiler Динамические системные представления (DMV) www.msdevcon.ru Статистика 9 План выполнения запроса Читаем слева направо и сверху вниз Поток данных – справа налево и снизу вверх www.msdevcon.ru 10 Причины снижения производительности Изменились данные Устарела статистика Недостаточно ресурсов для поиска лучшего плана выполнения Процедура запущена с «плохими» параметрами www.msdevcon.ru 11 Способы оптимизации Изменение структур (создание и изменение индексов и статистик) Подсказки оптимизатору (hints): Уровня запроса Табличные Типы соединений Plan Guides Изменение логики запроса, использование промежуточных наборов Удаление хинтов  www.msdevcon.ru Просмотреть хинты запросов Изменение логики запроса – «техника написания быстрых запросов» 12 Техника написания быстрых запросов Все возможные вычисления делать предварительно Не изменять проиндексированные поля, если по ним желателен поиск Скажи нет неявным преобразованиям! Использовать INNER JOIN если только не нужен OUTER Порядок таблиц в запросе – сначала «меньшие потоки данных» Универсальные запросы работают всегда одинаково плохо Борьба с прослушивание параметров www.msdevcon.ru Табличные переменные и временные таблицы Разное использование статистики Для временных таблиц сохраняются все правила «обычных» По табличным переменным не строится статистика, следствия: Нет перекомпиляции запросов после изменения данных в таблице Предполагается, что будет выбираться одна строка за одно обращение к таблице Можно использовать подсказку recompile www.msdevcon.ru Итоги Быстродействие конкретных запросов зависит от выбранного оптимизатором плана выполнения Главным образом на выбор «правильного» плана выполнения влияет статистика Хорошая оптимизация запроса заключается в том, чтобы оптимизацией занимался сам сервер www.msdevcon.ru 15 Обратная связь Ваше мнение очень важно для нас. Пожалуйста, оцените доклад, заполните анкету и сдайте ее при выходе из зала Спасибо! www.msdevcon.ru 16 Вопросы DB804 Дмитрий Костылев [email protected] www.sql.ru/blogs/decolores начальник отдела разработки системного ПО Вы сможете задать вопросы докладчику в зоне «Спроси эксперта» в течение часа после завершения этого доклада www.msdevcon.ru 17 www.msdevcon.ru 18

documents.tips

sql - sql server- оптимизация запросов со многими столбцами

К сожалению, я не думаю, что в вашем выпуске есть чисто SQL-решение. Вот несколько альтернатив:

Еще один вариант, который я только что вспомнил о реализации в системе один раз. Создайте вертикальную таблицу, включающую все данные, которые вы ищете, и создайте для нее запрос. Это проще всего сделать с динамическим SQL, но может быть сделано с использованием параметров табличного значения или таблицы temp в крайнем случае.

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

В таблице должен быть уникальный индекс (ID профиля, Имя атрибута) (уникальный для правильной работы поиска, индекс сделает его хорошо).

В этой таблице у вас есть строки данных типа:

Тогда ваш SQL будет выглядеть примерно так:

SELECT * FROM Profile JOIN ( SELECT ProfileID FROM ProfileAttributes WHERE (AttributeName = 'city' AND AttributeValue = 'grand rapids') AND (AttributeName = 'state' AND AttributeValue = 'MI') GROUP BY ProfileID HAVING COUNT(*) = 2 ) SelectedProfiles ON Profile.ProfileID = SelectedProfiles.ProfileID ... -- Add your paging here

Как я уже сказал, вы можете использовать временную таблицу с именем/значениями атрибутов:

SELECT * FROM Profile JOIN ( SELECT ProfileID FROM ProfileAttributes JOIN PassedInAttributeTable ON ProfileAttributes.AttributeName = PassedInAttributeTable.AttributeName AND ProfileAttributes.AttributeValue = PassedInAttributeTable.AttributeValue GROUP BY ProfileID HAVING COUNT(*) = CountOfRowsInPassedInAttributeTable -- calculate or pass in ) SelectedProfiles ON Profile.ProfileID = SelectedProfiles.ProfileID ... -- Add your paging here

Как я помню, это закончилось очень хорошо, даже по довольно сложным запросам (хотя я думаю, что у нас было только 12 или около того).

qaru.site

sql - Оптимизация запросов SQL Server - Неожиданная медлительность в простом запросе

Возможное объяснение здесь в комментарии

В SQL Server 2014 Enterprise Edition (64-разрядная версия) - я пытаюсь читать из представления. Стандартный запрос содержит только предложение ORDER BY и OFFSET-FETCH, подобное этому.

Подход 1

SELECT * FROM Metadata ORDER BY AgeInHours ASC, RankingPoint DESC, PublishDate DESC OFFSET 150000 ROWS FETCH NEXT 40 ROWS ONLY

Однако этот довольно простой запрос выполняет почти 9 раз медленнее (примечание при пропуске большого количества строк, например 150k), чем следующий запрос, который возвращает тот же результат.

В этом случае я сначала считываю первичный ключ, а затем использую его как параметр для функции WHERE...IN

Подход 2

SELECT * FROM Metadata WHERE NewsId IN ( SELECT NewsId FROM Metadata ORDER BY AgeInHours ASC, RankingPoint DESC, PublishDate DESC OFFSET 150000 ROWS FETCH NEXT 40 ROWS ONLY ) ORDER BY AgeInHours ASC, RankingPoint DESC, PublishDate DESC

Наклейка этих двух показывает эту разницу

(40 row(s) affected) SQL Server Execution Times: CPU time = 14748 ms, elapsed time = 3329 ms. (40 row(s) affected) SQL Server Execution Times: CPU time = 3828 ms, elapsed time = 469 ms.

У меня есть индексы первичного ключа PubilshDate, и их фрагментация очень низкая. Я также пытался запускать похожие запросы в отношении таблицы базы данных, но в каждом случае второй подход дает большие выигрыши в производительности. Я также тестировал это на SQL Server 2012.

Может кто-нибудь объяснить, что происходит?

Схема

Подход 1: План выполнения

Подход 2: План выполнения (Левая часть)

Подход 2: План выполнения (правая часть)

qaru.site

Prostoy-Site | Все права защищены © 2018 | Карта сайта