Пять способов повысить производительность SQL
Эта статья взята из статьи MSDN «Пять способов улучшить производительность SQL» и предлагает способы повышения эффективности работы приложений на базе SQL Server. Настоятельно рекомендуется. Для некоторых прикладных систем с высоким трафиком вопрос о том, как улучшать и совершенствовать инструкции SQL, очень важен и является хорошей точкой прорыва.
*Статья в основном включает в себя следующее содержание (если вам интересно, посетите следующий URL-адрес, чтобы прочитать полный документ на китайском и английском языках):
1. Возврат IDENTITY из INSERT.
SELECT @@IDENTITY
2, встроенное представление и временная таблица
Временные таблицы. Временные таблицы в базе данных tempdb могут привести к тому, что запросы будут выполнять тяжелые операции ввода-вывода и доступ к диску, а временные таблицы могут потреблять много ресурсов.
Встроенные представления. Используйте встроенные представления вместо временных таблиц. Встроенное представление — это просто запрос, который можно объединить в предложении FROM. Если вам просто нужно объединить данные с другими запросами, вы можете попробовать использовать встроенные представления для экономии ресурсов.
3. Избегайте LEFT JOIN и NULL
LEFT JOIN очень ресурсоемки, поскольку содержат данные, соответствующие NULL (несуществующим) данным. В некоторых случаях это неизбежно, но цена может быть очень высокой. LEFT JOIN потребляет больше ресурсов, чем INNER JOIN, поэтому, если вы сможете переписать запрос так, чтобы он не использовал LEFT JOIN, вы получите очень хорошую награду.
Один из методов ускорения запросов, использующих LEFT JOIN, включает создание типа данных TABLE, вставку всех строк в первую таблицу (таблицу слева от LEFT JOIN), а затем обновление типа данных TABLE значениями из второй стол. Этот метод представляет собой двухэтапный процесс, но может сэкономить много времени по сравнению со стандартным LEFT JOIN. Хорошее правило — пробовать различные методы и записывать время, необходимое для каждого из них, пока не будет получен запрос, который лучше всего подходит для вашего приложения.
DECLARE @tblMonths TABLE (sMonth VARCHAR(7))
4, гибкое использование декартова произведения
Я подробно расскажу об этом методе и порекомендую в некоторых случаях использовать декартово произведение. По какой-то причине декартовы произведения (CROSS JOIN) вызывают много критики, и разработчиков часто предупреждают, чтобы они вообще не использовали их. Во многих случаях они потребляют слишком много ресурсов, чтобы их можно было использовать эффективно. Но, как и любой инструмент SQL, они могут быть полезными, если их правильно использовать.
Стоит следовать одному из примеров кода:
-- Декартово произведение возвращает всех клиентов за все месяцы. Декартово произведение по сути умножает первую таблицу на вторую, создавая набор строк, который содержит количество строк в первой таблице, умноженное на количество строк во второй таблице. Следовательно, декартово произведение возвращает 12 (все месяцы) * 81 (все клиенты) = 972 строки в таблицу @tblFinal. Последние шаги — обновить таблицу @tblFinal, указав ежемесячные итоги продаж для каждого клиента за этот диапазон дат, и выбрать окончательный набор строк.
ОБЪЯВИТЬ ТАБЛИЦУ @tblMonths (sMonth VARCHAR(7))
ОБЪЯВИТЕ ТАБЛИЦУ @tblCustomers (CustomerID CHAR(10),
Название компании VARCHAR(50),
Контактное имя VARCHAR(50))
ОБЪЯВИТЬ @tblFinal TABLE (sMonth VARCHAR(7),
Идентификатор клиента CHAR(10),
Название компании VARCHAR(50),
Контактное имя VARCHAR(50),
mSalesMONEY)
ОБЪЯВИТЬ @dtStartDate DATETIME,
@dtEndDate ДАТАВРЕМЯ,
@dtDate ДАТАВРЕМЯ,
@i INTEGER
SET @dtEndDate = '5/5/1997'
SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH(@dtEndDate) + 1) AS
VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + '23:59:59' AS DATETIME))
SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)
-- Получить все месяцы в первую таблицу
УСТАНОВИТЬ @i = 0
ПОКА (@i < 12)
НАЧИНАТЬ
SET @dtDate = DATEADD(мм, -1 * @i, @dtEndDate)
INSERT INTO @tblMonths SELECT CAST(YEAR(@dtDate) AS VARCHAR(4)) + '-' +
СЛУЧАЙ
КОГДА МЕСЯЦ(@dtDate) < 10
ТОГДА '0' + CAST(MONTH(@dtDate) AS VARCHAR(2))
ELSE CAST(MONTH(@dtDate) AS VARCHAR(2))
КОНЕЦ КАК МЕСЯЦ
УСТАНОВИТЬ @i = @i + 1
КОНЕЦ
– перенести всех клиентов, у которых были продажи за этот период, в таблицу "y".
ВСТАВИТЬ В @tblCustomers
ВЫБЕРИТЕ ОТЛИЧНЫЕ
c.Идентификатор клиента,
c.Название компании,
c.Имя контакта
ОТ клиентов c
Заказы INNER JOIN o ON c.CustomerID = o.CustomerID
ГДЕ o.OrderDate МЕЖДУ @dtStartDate И @dtEndDate
ВСТАВИТЬ В @tblFinal
ВЫБЕРИТЕ м.сМесяц,
c.Идентификатор клиента,
c.Название компании,
c.Имя контакта,
0
FROM @tblMonths m CROSS JOIN @tblCustomers c
UPDATE @tblFinal SET
mSales = mydata.mSales
ОТ @tblFinal f ВНУТРЕННЕЕ СОЕДИНЕНИЕ
(
ВЫБЕРИТЕ c.CustomerID,
CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +
СЛУЧАЙ, КОГДА MONTH(o.OrderDate) < 10
THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))
КОНЕЦ КАК МЕСЯЦ,
SUM(od.Quantity * od.UnitPrice) AS mSales
ОТ клиентов c
Заказы INNER JOIN o ON c.CustomerID = o.CustomerID
INNER JOIN [Сведения о заказе] od ON o.OrderID = od.OrderID
ГДЕ o.OrderDate МЕЖДУ @dtStartDate И @dtEndDate
ГРУППИРОВАТЬ ПО
c.Идентификатор клиента,
CAST(YEAR(o.OrderDate) AS VARCHAR(4)) + '-' +
СЛУЧАЙ, КОГДА MONTH(o.OrderDate) < 10
THEN '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
ELSE CAST(MONTH(o.OrderDate) AS VARCHAR(2))
КОНЕЦ
) мои данные на f.CustomerID = mydata.CustomerID И f.sMonth =
mydata.sMonth
ВЫБЕРИТЕ f.sMonth,
е.Идентификатор клиента,
f.Название компании,
f.Имя контакта,
f.mSales
ОТ @tblFinal f
ЗАКАЗАТЬ ПО
f.Название компании,
f.sMonth
5. Подберите недостающие детали и восполните недостающие детали.
Вот некоторые другие распространенные методы, которые могут помочь повысить эффективность SQL-запросов. Предположим, вы хотите сгруппировать всех продавцов по регионам и подсчитать их продажи, но хотите, чтобы только те продавцы были отмечены как активные в базе данных. Вы можете сгруппировать продавцов по регионам и исключить тех, кто неактивен, с помощью предложения HAVING или сделать это в предложении WHERE. Выполнение этого в предложении WHERE уменьшает количество строк, которые необходимо сгруппировать, поэтому это более эффективно, чем в предложении HAVING. Фильтрация на основе условий строки в предложении HAVING заставляет запрос группировать данные, которые были бы удалены в предложении WHERE.
Еще один совет по повышению эффективности — использовать ключевое слово DISTINCT для поиска отдельных отчетов по строкам данных вместо использования предложения GROUP BY. В этом случае SQL с ключевым словом DISTINCT более эффективен. Используйте GROUP BY только тогда, когда вам нужно вычислить агрегатные функции (SUM, COUNT, MAX и т. д.). Кроме того, если ваш запрос всегда возвращает уникальную строку, не используйте ключевое слово DISTINCT. В этом случае ключевое слово DISTINCT только добавляет системные издержки.
－－－－－－－－－－－－－－－－－－－
Китайский URL:
http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx
Английский URL-адрес:
http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/