Мне нужно показать общее количество с общим столбцом в моем приложении... поэтому я использовал следующие запросы для поиска текущей суммы... и я обнаружил, что обе работают в соответствии с моей потребностью. в одном я использовал левое соединение с группой, и в другом я использовал sub-запрос.Ответ 1
Отличным ресурсом для расчета текущих итогов в SQL Server является этот документ от Itzik Ben Gan, который был отправлен команде SQL Server в рамках его кампания, чтобы предложение OVER было расширено от первоначальной реализации SQL Server 2005. В нем он показывает, как как только вы попадаете в десятки тысяч строк, курсоры выполняют множество основанных решений. SQL Server 2012 действительно расширил предложение OVER, делая этот вид запросов намного проще.
SELECT col1,
SUM(col1) OVER (ORDER BY ind ROWS UNBOUNDED PRECEDING)
FROM @tmp
Как вы уже на SQL Server 2005, это не доступно вам.
Adam Machanic показывает здесь, как CLR можно использовать для улучшения производительности стандартных курсоров TSQL.
Для определения этой таблицы
CREATE TABLE RunningTotals
(
ind int identity(1,1) primary key,
col1 int
)
Я создаю таблицы с 2 000 и 10 000 строк в базе данных с ALLOW_SNAPSHOT_ISOLATION ON и один с этим отключением (причина в том, что мои первоначальные результаты были в БД с настройкой, которая привела к загадочному аспекту результаты).
У кластеризованных индексов для всех таблиц была только одна корневая страница. Ниже показано количество страниц листа для каждого.
+-------------------------------+-----------+------------+
| | 2,000 row | 10,000 row |
+-------------------------------+-----------+------------+
| ALLOW_SNAPSHOT_ISOLATION OFF | 5 | 22 |
| ALLOW_SNAPSHOT_ISOLATION ON | 8 | 39 |
+-------------------------------+-----------+------------+
Я проверил следующие случаи (ссылки показывают планы выполнения)
- Left Join и Group By
- Связанный подзапрос 2000 ряд строк, 10000 ряд строк
- CTE из ответа Mikael (обновлено)
- CTE ниже
Причина включения дополнительного параметра CTE заключалась в том, чтобы предоставить решение CTE, которое будет работать, если столбец ind не был гарантирован последовательным.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @col1 int, @sumcol1 bigint;
WITH RecursiveCTE
AS (
SELECT TOP 1 ind, col1, CAST(col1 AS BIGINT) AS Total
FROM RunningTotals
ORDER BY ind
UNION ALL
SELECT R.ind, R.col1, R.Total
FROM (
SELECT T.*,
T.col1 + Total AS Total,
rn = ROW_NUMBER() OVER (ORDER BY T.ind)
FROM RunningTotals T
JOIN RecursiveCTE R
ON R.ind < T.ind
) R
WHERE R.rn = 1
)
SELECT @col1 =col1, @sumcol1=Total
FROM RecursiveCTE
OPTION (MAXRECURSION 0);
Все запросы содержали CAST(col1 AS BIGINT), чтобы избежать ошибок переполнения во время выполнения. Кроме того, для всех из них я присваивал результаты переменным, как указано выше, чтобы исключить время, потраченное на отправку результатов с рассмотрения.
Результаты
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| | | | Base Table | Work Table | Time |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| | Snapshot | Rows | Scan count | logical reads | Scan count | logical reads | cpu | elapsed |
| Group By | On | 2,000 | 2001 | 12709 | | | 1469 | 1250 |
| | On | 10,000 | 10001 | 216678 | | | 30906 | 30963 |
| | Off | 2,000 | 2001 | 9251 | | | 1140 | 1160 |
| | Off | 10,000 | 10001 | 130089 | | | 29906 | 28306 |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| Sub Query | On | 2,000 | 2001 | 12709 | | | 844 | 823 |
| | On | 10,000 | 2 | 82 | 10000 | 165025 | 24672 | 24535 |
| | Off | 2,000 | 2001 | 9251 | | | 766 | 999 |
| | Off | 10,000 | 2 | 48 | 10000 | 165025 | 25188 | 23880 |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| CTE No Gaps | On | 2,000 | 0 | 4002 | 2 | 12001 | 78 | 101 |
| | On | 10,000 | 0 | 20002 | 2 | 60001 | 344 | 342 |
| | Off | 2,000 | 0 | 4002 | 2 | 12001 | 62 | 253 |
| | Off | 10,000 | 0 | 20002 | 2 | 60001 | 281 | 326 |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| CTE Alllows Gaps | On | 2,000 | 2001 | 4009 | 2 | 12001 | 47 | 75 |
| | On | 10,000 | 10001 | 20040 | 2 | 60001 | 312 | 413 |
| | Off | 2,000 | 2001 | 4006 | 2 | 12001 | 94 | 90 |
| | Off | 10,000 | 10001 | 20023 | 2 | 60001 | 313 | 349 |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
Оба коррелированного подзапроса и версия GROUP BY используют "треугольные" вложенные объединения циклов, управляемые кластеризованным сканированием индексов в таблице RunningTotals (T1), и для каждой строки, возвращаемой этим сканированием, таблица (T2), присоединяющаяся к T2.ind<=T1.ind.
Это означает, что одни и те же строки обрабатываются повторно. Когда обрабатывается строка T1.ind=1000, самосоединение извлекает и суммирует все строки с помощью ind <= 1000, а затем для следующей строки, где T1.ind=1001 снова извлекаются одни и те же 1000 строк и суммируются вместе с одной дополнительной строкой и т.д.
Общее количество таких операций для таблицы 2000 строк составляет 2 001 000, для строк 10 тыс. 50 000 000 000 или более (n² + n) / 2, которые явно растут экспоненциально.
В случае с 2 000 строк основное различие между версиями GROUP BY и подзапросов состоит в том, что первый имеет агрегат потока после объединения и, следовательно, имеет в нем три столбца (T1.ind, T2.col1, T2.col1) и GROUP BY свойства T1.ind, тогда как последний вычисляется как скалярный агрегат, с агрегатом потока до объединения, имеет только T2.col1, загружаемый в него и не имеющий никакого свойства GROUP BY. Считается, что эта более простая компоновка имеет измеримое преимущество в плане сокращения времени процессора.
В случае с номером в 10 000 строк есть дополнительная разница в плане подзапроса. Он добавляет энергичную катушку, которая копирует все значения ind,cast(col1 as bigint) в tempdb. В случае, когда изоляция моментальных снимков работает, это работает более компактно, чем кластерная структура индекса, а чистый эффект - уменьшить количество чтений примерно на 25% (поскольку базовая таблица сохраняет довольно много пустого пространства для информации о версии) когда эта опция выключена, она работает менее компактно (предположительно из-за разницы bigint vs int) и больше читает результат. Это уменьшает разрыв между вспомогательным запросом и группой по версиям, но дополнительный запрос все еще выигрывает.
Явным победителем, однако, был рекурсивный CTE. Для версии "без пробелов" логические считывания из базовой таблицы теперь 2 x (n + 1), отражающие индекс n, стремятся к индексу уровня 2, чтобы получить все строки плюс дополнительный в конце, который ничего не возвращает и завершает рекурсию, Это все равно означало 20 002 чтения для обработки 22-страничного стола, однако!
Таблицы логических рабочих таблиц для рекурсивной версии CTE очень высоки. Кажется, что он работает в 6 рабочих таблицах в строке источника. Они поступают из индексной катушки, которая хранит выходные данные предыдущей строки, а затем считывается с нее на следующей итерации (это хорошее объяснение Umachandar Jayachandran здесь). Несмотря на большое количество, это по-прежнему лучший исполнитель.