Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Эффективность использования CTE  [new]
Артем Макаров
Member

Откуда:
Сообщений: 5
Доброго времени суток!

У меня возникла такая проблема.
Существует 1 плоская таблица с большим набором столбцов и существует процедура по обработке строк в этой таблице.

В процедуре всё выглядит примерно так.

Update #Table
set column 1 = case when условие then арбуз else column1 end
where -- как может быть так и отсутствовать

Update #Table
set column 2 = case when условие then арбуз else column1 end
where -- как может быть так и отсутствовать

Update #Table
set column 3 = case when условие then арбуз else column1 end
where -- как может быть так и отсутствовать

Я решил это дело оптимизировать при помощи CTE.

;with up as (
select * from #table
where -- набор условий для тех строк, которые должны быть обработаны.
)
update
set
column 1 = case when условие then арбуз else column1 end
,column 2 = case when условие then арбуз else column1 end
,column 3 = case when условие then арбуз else column1 end
end

Таблица содержит 60 млн записей.

Для такого объема работает довольно шустро.
Как то удалось протестировать вместо 8 часов выполнения под нагрузкой, процедура с такой оптимизацией отработала за 4.5 часа.
Так как проход осуществляется по таблице за 1 раз нежели за три(в бою всё гораздо печальнее там таких апдейтов около 19)

Но существует аналогичная таблица, но около 38 млн строк.
Так вот для 1 эта оптимизация работает довольно шустро, а для 2 очень и очень медленно я не видел ещё конца завершения.


Тестирую на машине 4 GB RAM. CPU INTEL(R) Core(TM)2Quad CPU Q6600 @ 2.40GHZ 2.39GHz. HDD 1 TB Sata3.
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ).

Скрипты предназначаются на более мощный сервер (озвучить параметры не могу к сожалению за исключением RAM 64 GB).

На тесте WAIT_Type выдает PAGEIOLATCHE.

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

Ну и сами вопрос.
Верно ли направление оптимизации с помощью CTE при такой обработке?

P.S уровень владения MS SQL довольно сыроват около 1 года опыта.
На уровне администрирования только базовые понятия.
Прошу не кидаться тапками это мой 1 пост на форуме в жизни =))).
До этого мне хватало книг и справочной информации.
13 авг 15, 11:12    [18011895]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
iap
Member

Откуда: Москва
Сообщений: 47105
Артем Макаров,

а что мешает одним UPDATEом апдейтить все нужные поля #Table,
не пользуясь CTE? Чем CTE тут помочь-то может?
13 авг 15, 11:18    [18011937]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
хе-хе )
Guest
CTE разворачиватся при исполнении в обычный запрос, он не ускоряет и не тормоит аналогичный запрос
13 авг 15, 11:25    [18011973]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
Артем Макаров
Member

Откуда:
Сообщений: 5
Что мешает одним update'ом сделать?
Да в принципе ничего, просто CTE более модульный и удобный подход.

CTE разворачиватся при исполнении в обычный запрос:
Не понимаю в какой вы имеет ввиду обычный запрос, но судя по плану выполнения, для компилятора есть разница. CTE работает чуточку быстрее (могу ошибаться).
13 авг 15, 12:29    [18012410]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
хе-хе )
Guest
Артем Макаров, Потому-то в CTE и отдельно у Вас разные запросы
В CTE один, отделено - три. Что мешает сразу объединить все в один апдейт?
13 авг 15, 12:40    [18012501]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
;WITH cte AS (
	SELECT *
	FROM #table
)
UPDATE cte
SET
	  col1 = IIF(1=1, арбуз, col1)
	, col2 = IIF(2=2, арбуз, col2)
	, col3 = IIF(3=3, арбуз, col2)
WHERE 1=1 OR 2=2 OR 3=3


в Вашем случае выполняется условие или нет, но все поля обновляются на те значения что и были ранее... пробуйте перед обновление делать проверку что хоть какое-то из условие в рамках строки должно выполниться... я имею ввиду 1=1, 2=2...
13 авг 15, 12:42    [18012523]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
И еще.... обновляйте пакетами... лог же вырастет до неприличия на таком большом объеме данных...
13 авг 15, 12:44    [18012542]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
PAGEIOLATCHE - памяти точно не хватает.... обновление по 10.000 по 100.000 должно помочь... сам такое практикую... CTE тут не ничего не решает, смотрите в сторону оптимизации чтения, возможности оперировать меньшим числом строк (дробить на пакеты по н-обновлений).... ведь план запроса зависит от количества строк, которые Вы хотите обработать...
13 авг 15, 12:55    [18012632]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
Артем Макаров
Member

Откуда:
Сообщений: 5
Ну в данном случае where тут не причем, в том месте он вообще не учавствует.
Так как фильтр происходит в блоке with, а после уже происходит обработка выбранного

А вот на счет кол-во строк, забыл написать, там размер пляшет от 5000 строк до 250 000.
Пробовал ограничить в принципе работает, но приходится джобу по нескольку раз запускать =)))
Всем спасибо за ответы. Некоторые моменты в знаниях просветлились.

P.S Выборка работает верно.

AlanDenton
;WITH cte AS (
	SELECT *
	FROM #table
)
UPDATE cte
SET
	  col1 = IIF(1=1, арбуз, col1)
	, col2 = IIF(2=2, арбуз, col2)
	, col3 = IIF(3=3, арбуз, col2)
WHERE 1=1 OR 2=2 OR 3=3


в Вашем случае выполняется условие или нет, но все поля обновляются на те значения что и были ранее... пробуйте перед обновление делать проверку что хоть какое-то из условие в рамках строки должно выполниться... я имею ввиду 1=1, 2=2...
13 авг 15, 13:03    [18012684]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Да я не о фильтре говорил... Идея в том чтобы сократить количество строк которые нужно обновлять... т.е. если строка не подходит ни под одно из условий в IIF то "запишется" в нее прежнее значение... если все эти условии при обновлении перечислить через OR добавить OPTION(RECOMPILE) и подумать о пакетной обработке... все должно быть явно быстрее.
13 авг 15, 13:06    [18012720]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
Артем Макаров
Member

Откуда:
Сообщений: 5
Кстати, по смотрел счетчики производительности. PAGEIOLATCHE не с RAM связан а с I/O HDD.

AlanDenton
PAGEIOLATCHE - памяти точно не хватает.... обновление по 10.000 по 100.000 должно помочь... сам такое практикую... CTE тут не ничего не решает, смотрите в сторону оптимизации чтения, возможности оперировать меньшим числом строк (дробить на пакеты по н-обновлений).... ведь план запроса зависит от количества строк, которые Вы хотите обработать...
13 авг 15, 13:07    [18012724]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Сорри, моя вина... PAGEIOLATCHE спутал с RESOURCE_SEMAPHORE... то голова уже не варит
13 авг 15, 13:13    [18012765]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
С другой стороны если есть латчи это признак того что ресурсов не хватает, из буффер пула сиквел все сливает на диск... дробление на пакеты должно помочь... :)
13 авг 15, 13:14    [18012778]     Ответить | Цитировать Сообщить модератору
 Re: Эффективность использования CTE  [new]
Артем Макаров
Member

Откуда:
Сообщений: 5
Ok =)
AlanDenton,
13 авг 15, 15:21    [18013643]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить