Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос с большим количеством logical I/O  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1121
Добрый день!

Есть запрос:

SELECT   DISTINCT e.Tbl1CUID
  FROM Sg.Tbl1 AS e WITH (NOLOCK) 
            JOIN   Sg.[Srv]                        s  
            ON s.SrvID = e.SrvID
         LEFT JOIN [dbo].[W_SrvCf]    c  
         ON c.SrvID = s.SrvID  
            JOIN Sg.Ftr                        f
            on f.SrvID = s.SrvID
            JOIN Sg.PgFtrMp              pakF 
            ON pakF.FtrID = f.FtrID
            JOIN Sg.RLCap         cap   (NOLOCK)
            ON cap.PkID = pakF.PkID
            JOIN dbo.W_UPrMp                      uMap  (NOLOCK)
            ON uMap.UGUID = cap.RLID
        JOIN dbo.CCMGr                               g     (NOLOCK)  
        on g.OwBGrID = uMap.AuthRLID
        AND uMap.IsPP  = 1
            JOIN dbo.W_NDtl                           det   (NOLOCK)  
            ON det.NID = g.GID    
        LEFT JOIN dbo.W_RLFtrMp         relF  (NOLOCK)
        ON relF.AmUID  = uMap.AUID 
        AND relF.FtrId = pakF.FtrID
        LEFT JOIN dbo.W_NWFtrMp              netF  (NOLOCK)
        ON netF.NID = det.NID 
        AND netF.FtrId = pakF.FtrID           
        JOIN dbo.CCUM                           mem   (NOLOCK)
        on mem.MBOfGID = det.NID
        
WHERE mem.AmUID = @AUID AND det.NID = @GID and
         ISNULL(netF.IsA,ISNULL(relF.IsA
                  ,CASE WHEN pakF.IIMTID IN (1,2) Then 1 -- ON, OFF
                        ELSE 0 
                   END ))=1 and 
       ISNULL(netF.IE
           ,ISNULL(relF.IE
                  ,CASE pakF.IIMTID WHEN 1 Then 1 -- ON
                        ELSE 0 
                   END ))=1



Самая проблемная часть здесь:
         ISNULL(netF.IsA,ISNULL(relF.IsA
                  ,CASE WHEN pakF.IIMTID IN (1,2) Then 1 -- ON, OFF
                        ELSE 0 
                   END ))=1 and 
       ISNULL(netF.IE
           ,ISNULL(relF.IE
                  ,CASE pakF.IIMTID WHEN 1 Then 1 -- ON
                        ELSE 0 
                   END ))=1


Здесь сначала проверяется есть ли значение в netF.IsA, если оно есть, то берется это значение, если NULL, то проверяется relF.IsA, если и оно NULL, то проверяется pakF.IIMTID. Тоже самое и во втором условии.


Можно ли эту часть как-то иначе переписать, чтобы было меньше logical I/O? Это запрос выполняется очень часто и создает нагрузку на сервер.

Спасибо.
20 сен 17, 04:51    [20807750]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
aleks222
Guest
Чудес, дарагой, не бывает.
Разве только покороче

coalesce(netF.IsA, relF.IsA, CASE WHEN pakF.IIMTID IN (1,2) Then 1 ELSE 0  END ) = 1 
20 сен 17, 05:48    [20807756]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
aleks222
Guest
А по-большому и колбасообразно - надо DISTINCT ликвидировать, ибо это группировка, а группировка - вселенское зло.

А у тя под вселенское зло запихнут join кучи таблиц. Хотя, по смыслу все это сводится к
SELECT   DISTINCT e.Tbl1CUID  FROM Sg.Tbl1 AS e 
  where exists ( select * from Sg.[Srv]  s  
         LEFT JOIN [dbo].[W_SrvCf]    c  
         ON c.SrvID = s.SrvID  
            JOIN Sg.Ftr                        f
            on f.SrvID = s.SrvID
            JOIN Sg.PgFtrMp              pakF 
            ON pakF.FtrID = f.FtrID
            JOIN Sg.RLCap         cap   (NOLOCK)
            ON cap.PkID = pakF.PkID
            JOIN dbo.W_UPrMp                      uMap  (NOLOCK)
            ON uMap.UGUID = cap.RLID
        JOIN dbo.CCMGr                               g     (NOLOCK)  
        on g.OwBGrID = uMap.AuthRLID
        AND uMap.IsPP  = 1
            JOIN dbo.W_NDtl                           det   (NOLOCK)  
            ON det.NID = g.GID    
        LEFT JOIN dbo.W_RLFtrMp         relF  (NOLOCK)
        ON relF.AmUID  = uMap.AUID 
        AND relF.FtrId = pakF.FtrID
        LEFT JOIN dbo.W_NWFtrMp              netF  (NOLOCK)
        ON netF.NID = det.NID 
        AND netF.FtrId = pakF.FtrID           
        JOIN dbo.CCUM                           mem   (NOLOCK)
        on mem.MBOfGID = det.NID
        
WHERE mem.AmUID = @AUID AND det.NID = @GID and
         ISNULL(netF.IsA,ISNULL(relF.IsA
                  ,CASE WHEN pakF.IIMTID IN (1,2) Then 1 -- ON, OFF
                        ELSE 0 
                   END ))=1 and 
       ISNULL(netF.IE
           ,ISNULL(relF.IE
                  ,CASE pakF.IIMTID WHEN 1 Then 1 -- ON
                        ELSE 0 
                   END ))=1

and  s.SrvID = e.SrvID
)


И, не исключено, что Tbl1CUID в Sg.Tbl1 уникально и distinct ваще не нужен.

Ну а дальше - только индексы... до индексированных представлений, включительно.
20 сен 17, 06:01    [20807757]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1121
Удаление DISTINCT не улучшило ситуацию.
20 сен 17, 08:37    [20807840]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
aleks222
Guest
Roust_m
Удаление DISTINCT не улучшило ситуацию.

Если все так незатейливо - удали запрос совсем.
Нет запроса - нет проблемы.

ЗЫ. Тупое удаление DISTINCT бесполезно.
20 сен 17, 08:47    [20807854]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1121
aleks222
Roust_m
Удаление DISTINCT не улучшило ситуацию.

Если все так незатейливо - удали запрос совсем.
Нет запроса - нет проблемы.

ЗЫ. Тупое удаление DISTINCT бесполезно.


Сталинские подходы, однако...
21 сен 17, 02:57    [20810740]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Massa52
Member

Откуда:
Сообщений: 379
aleks222
join кучи таблиц.

Проблема не во Where - а как было сказано - в кучах join - ов.
21 сен 17, 06:55    [20810764]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Я бы сделал выражения из WHERE запроса вычисляемыми полями и построил по ним индекс. Причём, добавил в него нужные столбцы и сделал фильтр, т.е. не нужно хранить индекс по условиям, которые в запросе будут отфильтрованы:

CREATE INDEX (...) INCLUDE(...) WHERE

Что-то типа материализованных представлений. Тогда запрос будет брать данные прямо из индекса. И только те, которые реально нужны. Это самый реальный способ уменьшить логические чтения.
21 сен 17, 10:11    [20811086]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1121
Massa52
aleks222
join кучи таблиц.

Проблема не во Where - а как было сказано - в кучах join - ов.


Проблема именно в WHERE. Если убрать кучу вложенных ISNULL и запросить одно поле, то логические I/O практически исчезают.

Вот только результат запроса становится неправильным.
22 сен 17, 04:43    [20814266]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
aleks222
Guest
Roust_m
Massa52
пропущено...

Проблема не во Where - а как было сказано - в кучах join - ов.


Проблема именно в WHERE. Если убрать кучу вложенных ISNULL и запросить одно поле, то логические I/O практически исчезают.

Вот только результат запроса становится неправильным.


Если убрать запрос - логические I/O исчезнут совсем => проблема, таки, в запросе.
22 сен 17, 05:46    [20814275]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1121
Dmitry V. Liseev
Я бы сделал выражения из WHERE запроса вычисляемыми полями и построил по ним индекс. Причём, добавил в него нужные столбцы и сделал фильтр, т.е. не нужно хранить индекс по условиям, которые в запросе будут отфильтрованы:

CREATE INDEX (...) INCLUDE(...) WHERE

Что-то типа материализованных представлений. Тогда запрос будет брать данные прямо из индекса. И только те, которые реально нужны. Это самый реальный способ уменьшить логические чтения.


Индексированное представление создать не получается, ибо в запросе есть "left join". :(
22 сен 17, 07:12    [20814297]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Ilnur26
Member

Откуда:
Сообщений: 15
Roust_m,

а если переписать c помощью union-ов?

конечно не так компактно будет выглядеть, но может помочь оптимизатору
22 сен 17, 09:49    [20814561]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
aleks222
Guest
Ilnur26
Roust_m,

а если переписать c помощью union-ов?

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


Орлы, тредстартер - классический балабол.

Ибо, если бы ему было "надо", он бы привел определения таблиц, сведения о числе записей в них и план запроса.

А он тока балаболит.
22 сен 17, 10:01    [20814604]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Roust_m
Dmitry V. Liseev
Я бы сделал выражения из WHERE запроса вычисляемыми полями и построил по ним индекс. Причём, добавил в него нужные столбцы и сделал фильтр, т.е. не нужно хранить индекс по условиям, которые в запросе будут отфильтрованы:

CREATE INDEX (...) INCLUDE(...) WHERE

Что-то типа материализованных представлений. Тогда запрос будет брать данные прямо из индекса. И только те, которые реально нужны. Это самый реальный способ уменьшить логические чтения.


Индексированное представление создать не получается, ибо в запросе есть "left join". :(
Видимо, я непонятно выразился.

Важно сделать запрос "саргабл" (тынц). Для этого нужно убрать вызовы функций и CASE из WHERE. То есть, вместо
ISNULL(A, B) = 1
лучше писать
A IS NULL AND B = 1 OR A = 1
Тогда оптимизатор сможет использовать индексы. Иначе никакие индексы не помогут и будет фулскан.

И следующий этап - все вычисления делать не на этапе селекта, а на этапе инсерта/апдейта. То есть, добавить в таблицу вычисляемое поле c пометкой PERSISTED. И построить по нему индекс (тынц). Вот эти вот CASE можно пихнуть в вычисляемое поле.

Никаких представлений здесь не нужно
22 сен 17, 10:08    [20814636]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Dmitry V. Liseev
лучше писать
A IS NULL AND B = 1 OR A = 1

Так лучше не писать.
Хотя бы так:
(A IS NULL AND B = 1) OR A = 1
22 сен 17, 10:15    [20814656]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Minamoto
Dmitry V. Liseev
лучше писать
A IS NULL AND B = 1 OR A = 1

Так лучше не писать.
Хотя бы так:
(A IS NULL AND B = 1) OR A = 1
Можно и (А * B) + 5, но, я помню приоритет операторов (тынц) и считаю, что все программисты его помнят. С тех пор, как Оккам научил меня бриться (entia non sunt multiplicanda praeter necessitatem), я не умножаю сущее без необходимости. Соответственно, скобки нужны только для явного изменения приоритета.

Впрочем, это вопрос стиля и соглашений, принятых в команде.
22 сен 17, 10:57    [20814868]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Dmitry V. Liseev
Minamoto
пропущено...
Так лучше не писать.
Хотя бы так:
(A IS NULL AND B = 1) OR A = 1
Можно и (А * B) + 5, но, я помню приоритет операторов (тынц) и считаю, что все программисты его помнят. С тех пор, как Оккам научил меня бриться (entia non sunt multiplicanda praeter necessitatem), я не умножаю сущее без необходимости. Соответственно, скобки нужны только для явного изменения приоритета.

Впрочем, это вопрос стиля и соглашений, принятых в команде.

полный бред. Праивльно писать со скобками, а не пускать слюни про приоритет операторов и разгребать чужие промахи в их иерархии
22 сен 17, 10:59    [20814887]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Dmitry V. Liseev,

и да бритва Оккама вас ничему не научила, ибо сущностей здесь не прибавляется
22 сен 17, 11:00    [20814896]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3708
Dmitry V. Liseev
Minamoto
пропущено...
Так лучше не писать.
Хотя бы так:
(A IS NULL AND B = 1) OR A = 1
Можно и (А * B) + 5, но, я помню приоритет операторов (тынц) и считаю, что все программисты его помнят. С тех пор, как Оккам научил меня бриться (entia non sunt multiplicanda praeter necessitatem), я не умножаю сущее без необходимости. Соответственно, скобки нужны только для явного изменения приоритета.

Впрочем, это вопрос стиля и соглашений, принятых в команде.


Оккам, научивший бриться(???)

"Мы не должны принимать какое-либо положение как не подлежащее обоснованию, если только это не логический вывод, или нечто, проверенное на опыте, или же благочестивое предписание, требующее от нас поступать именно так, а не иначе…"
22 сен 17, 11:57    [20815212]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
TaPaK
Dmitry V. Liseev,

и да бритва Оккама вас ничему не научила, ибо сущностей здесь не прибавляется
Здесь прибавляются скобки, которые не нужны.
22 сен 17, 15:47    [20816545]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1121
aleks222
Ilnur26
Roust_m,

а если переписать c помощью union-ов?

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


Орлы, тредстартер - классический балабол.

Ибо, если бы ему было "надо", он бы привел определения таблиц, сведения о числе записей в них и план запроса.

А он тока балаболит.


Я так просто не могу выкладывать определения таблиц, я даже имена таблиц зашифровал, ибо у нас организация серьезная.

Придется переименовать все столбцы и таблицы.
25 сен 17, 08:12    [20819647]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1121
Dmitry V. Liseev
Roust_m
пропущено...


Индексированное представление создать не получается, ибо в запросе есть "left join". :(
Видимо, я непонятно выразился.

Важно сделать запрос "саргабл" (тынц). Для этого нужно убрать вызовы функций и CASE из WHERE. То есть, вместо
ISNULL(A, B) = 1
лучше писать
A IS NULL AND B = 1 OR A = 1
Тогда оптимизатор сможет использовать индексы. Иначе никакие индексы не помогут и будет фулскан.

И следующий этап - все вычисления делать не на этапе селекта, а на этапе инсерта/апдейта. То есть, добавить в таблицу вычисляемое поле c пометкой PERSISTED. И построить по нему индекс (тынц). Вот эти вот CASE можно пихнуть в вычисляемое поле.

Никаких представлений здесь не нужно


Проблема в том, что поля из разных таблиц, как их добавить в одну таблицу я не совсем понимаю:
ISNULL(netF.IsA,ISNULL(relF.IsA
,CASE WHEN pakF.IIMTID IN (1,2) Then 1 -- ON, OFF
ELSE 0
END ))=1

90% времени исполнения запроса приходится на index seek всего одной таблицы relF.IsA, сканов там нет, но поскольку seek выполняется примерно 1200 раз, возникает много логических чтений. 99% логических чтений тоже приходятся на эту же таблицу.

Я попробовал написать что-то вроде:
(netF.IsA= 1 or (netF.IsA is NULL and relF.IsA = 1) or
(netF.IsAis NULL and relF.IsA is NULL and pakF.IIMTID IN (1,2)))


Это нисколько не поменяло количество логических чтений и план, похоже такой-же.
25 сен 17, 09:15    [20819706]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с большим количеством logical I/O  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5490
Roust_m
Я так просто не могу выкладывать определения таблиц, я даже имена таблиц зашифровал, ибо у нас организация серьезная.
Тогда нет смысла заморачиваться.
Roust_m
Проблема в том, что поля из разных таблиц, как их добавить в одну таблицу
Я не говорил, что в одну.
Roust_m
но поскольку seek выполняется примерно 1200 раз, возникает много логических чтений.
"Много" - это обычно от 10 миллионов. Когда какой-нибудь LOOP JOIN крутит в цикле достаточно тяжёлый подзапрос.
Roust_m
Это нисколько не поменяло количество логических чтений и план, похоже такой-же.
Тут сложно угадать, что происходит, если всё засекречено.
25 сен 17, 10:16    [20819847]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить