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

Откуда: Хабаровск
Сообщений: 50
Здравствуйте.

Просьба не переносить в тему 1С, т.к. вопрос конкретно по MS SQL! И проблема именно в MS SQL !!!! Все вызовы делаются с MS SQL M Studio !!!! В ветке 1С знатоков MS SQL не найдется столько.


При построении плана данного запроса уходит от 1 до 2 мин на приличном сервере (96 Гб ОЗУ, куча процов и тп)
Сами таблицы практически пустые!

После построения плана - работает пулей.
Индексы все есть, перестраивал и тп.
Если сокращать кол-во джоинов (урезать запрос до 20 Join) - то делается быстро! Но таблица то одна и та же!

Что можете посоветовать?


+

exec sp_executesql N' SELECT
T1._Document457_IDRRef,
T1._Fld15335,
T1._LineNo15322,
T1._Fld15323RRef,
T1._Fld15324,
T1._Fld15325_TYPE,
T1._Fld15325_RTRef,
T1._Fld15325_RRRef,
T1._Fld15326,
T1._Fld15327,
0.0,
T1._Fld15330RRef,
T1._Fld15329,
T1._Fld15333RRef,
T1._Fld15332,
CAST(SUM(ISNULL(CAST(T2._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T3._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T4._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T20._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T21._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T22._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T23._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T24._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T5._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T6._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T7._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T8._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T9._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T10._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T11._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T12._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T13._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T14._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T15._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T16._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T17._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T18._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8)),
CAST(SUM(ISNULL(CAST(T19._Fld15348 AS NUMERIC(12, 2)),0.0)) AS NUMERIC(24, 8))
FROM dbo._Document457_VT15321 T1
LEFT OUTER JOIN dbo._Document457_VT15344 T2
ON ((((T1._Document457_IDRRef = T2._Document457_IDRRef) AND (T1._Fld15335 = T2._Fld15349)) AND (T2._Fld15346RRef = @P1))) AND (T2._Fld847 = @P2)
LEFT OUTER JOIN dbo._Document457_VT15344 T3
ON ((((T1._Document457_IDRRef = T3._Document457_IDRRef) AND (T1._Fld15335 = T3._Fld15349)) AND (T3._Fld15346RRef = @P3))) AND (T3._Fld847 = @P4)
LEFT OUTER JOIN dbo._Document457_VT15344 T4
ON ((((T1._Document457_IDRRef = T4._Document457_IDRRef) AND (T1._Fld15335 = T4._Fld15349)) AND (T4._Fld15346RRef = @P5))) AND (T4._Fld847 = @P6)
LEFT OUTER JOIN dbo._Document457_VT15344 T5
ON ((((T1._Document457_IDRRef = T5._Document457_IDRRef) AND (T1._Fld15335 = T5._Fld15349)) AND (T5._Fld15346RRef = @P7))) AND (T5._Fld847 = @P8)
LEFT OUTER JOIN dbo._Document457_VT15344 T6
ON ((((T1._Document457_IDRRef = T6._Document457_IDRRef) AND (T1._Fld15335 = T6._Fld15349)) AND (T6._Fld15346RRef = @P9))) AND (T6._Fld847 = @P10)
LEFT OUTER JOIN dbo._Document457_VT15344 T7
ON ((((T1._Document457_IDRRef = T7._Document457_IDRRef) AND (T1._Fld15335 = T7._Fld15349)) AND (T7._Fld15346RRef = @P11))) AND (T7._Fld847 = @P12)
LEFT OUTER JOIN dbo._Document457_VT15344 T8
ON ((((T1._Document457_IDRRef = T8._Document457_IDRRef) AND (T1._Fld15335 = T8._Fld15349)) AND (T8._Fld15346RRef = @P13))) AND (T8._Fld847 = @P14)
LEFT OUTER JOIN dbo._Document457_VT15344 T9
ON ((((T1._Document457_IDRRef = T9._Document457_IDRRef) AND (T1._Fld15335 = T9._Fld15349)) AND (T9._Fld15346RRef = @P15))) AND (T9._Fld847 = @P16)
LEFT OUTER JOIN dbo._Document457_VT15344 T10
ON ((((T1._Document457_IDRRef = T10._Document457_IDRRef) AND (T1._Fld15335 = T10._Fld15349)) AND (T10._Fld15346RRef = @P17))) AND (T10._Fld847 = @P18)
LEFT OUTER JOIN dbo._Document457_VT15344 T11
ON ((((T1._Document457_IDRRef = T11._Document457_IDRRef) AND (T1._Fld15335 = T11._Fld15349)) AND (T11._Fld15346RRef = @P19))) AND (T11._Fld847 = @P20)
LEFT OUTER JOIN dbo._Document457_VT15344 T12
ON ((((T1._Document457_IDRRef = T12._Document457_IDRRef) AND (T1._Fld15335 = T12._Fld15349)) AND (T12._Fld15346RRef = @P21))) AND (T12._Fld847 = @P22)
LEFT OUTER JOIN dbo._Document457_VT15344 T13
ON ((((T1._Document457_IDRRef = T13._Document457_IDRRef) AND (T1._Fld15335 = T13._Fld15349)) AND (T13._Fld15346RRef = @P23))) AND (T13._Fld847 = @P24)
LEFT OUTER JOIN dbo._Document457_VT15344 T14
ON ((((T1._Document457_IDRRef = T14._Document457_IDRRef) AND (T1._Fld15335 = T14._Fld15349)) AND (T14._Fld15346RRef = @P25))) AND (T14._Fld847 = @P26)
LEFT OUTER JOIN dbo._Document457_VT15344 T15
ON ((((T1._Document457_IDRRef = T15._Document457_IDRRef) AND (T1._Fld15335 = T15._Fld15349)) AND (T15._Fld15346RRef = @P27))) AND (T15._Fld847 = @P28)
LEFT OUTER JOIN dbo._Document457_VT15344 T16
ON ((((T1._Document457_IDRRef = T16._Document457_IDRRef) AND (T1._Fld15335 = T16._Fld15349)) AND (T16._Fld15346RRef = @P29))) AND (T16._Fld847 = @P30)
LEFT OUTER JOIN dbo._Document457_VT15344 T17
ON ((((T1._Document457_IDRRef = T17._Document457_IDRRef) AND (T1._Fld15335 = T17._Fld15349)) AND (T17._Fld15346RRef = @P31))) AND (T17._Fld847 = @P32)
LEFT OUTER JOIN dbo._Document457_VT15344 T18
ON ((((T1._Document457_IDRRef = T18._Document457_IDRRef) AND (T1._Fld15335 = T18._Fld15349)) AND (T18._Fld15346RRef = @P33))) AND (T18._Fld847 = @P34)
LEFT OUTER JOIN dbo._Document457_VT15344 T19
ON ((((T1._Document457_IDRRef = T19._Document457_IDRRef) AND (T1._Fld15335 = T19._Fld15349)) AND (T19._Fld15346RRef = @P35))) AND (T19._Fld847 = @P36)
LEFT OUTER JOIN dbo._Document457_VT15344 T20
ON ((((T1._Document457_IDRRef = T20._Document457_IDRRef) AND (T1._Fld15335 = T20._Fld15349)) AND (T20._Fld15346RRef = @P37))) AND (T20._Fld847 = @P38)
LEFT OUTER JOIN dbo._Document457_VT15344 T21
ON ((((T1._Document457_IDRRef = T21._Document457_IDRRef) AND (T1._Fld15335 = T21._Fld15349)) AND (T21._Fld15346RRef = @P39))) AND (T21._Fld847 = @P40)
LEFT OUTER JOIN dbo._Document457_VT15344 T22
ON ((((T1._Document457_IDRRef = T22._Document457_IDRRef) AND (T1._Fld15335 = T22._Fld15349)) AND (T22._Fld15346RRef = @P41))) AND (T22._Fld847 = @P42)
LEFT OUTER JOIN dbo._Document457_VT15344 T23
ON ((((T1._Document457_IDRRef = T23._Document457_IDRRef) AND (T1._Fld15335 = T23._Fld15349)) AND (T23._Fld15346RRef = @P43))) AND (T23._Fld847 = @P44)
LEFT OUTER JOIN dbo._Document457_VT15344 T24
ON ((((T1._Document457_IDRRef = T24._Document457_IDRRef) AND (T1._Fld15335 = T24._Fld15349)) AND (T24._Fld15346RRef = @P45))) AND (T24._Fld847 = @P46)
WHERE ((T1._Fld847 = @P47)) AND ((T1._Document457_IDRRef = @P48))
GROUP BY T1._Document457_IDRRef,
T1._LineNo15322,
T1._Fld15335,
T1._Fld15323RRef,
T1._Fld15324,
T1._Fld15325_TYPE,
T1._Fld15325_RTRef,
T1._Fld15325_RRRef,
T1._Fld15326,
T1._Fld15327,
T1._Fld15330RRef,
T1._Fld15329,
T1._Fld15333RRef,
T1._Fld15332',N'@P1 varbinary(16),@P2 numeric(10),@P3 varbinary(16),@P4 numeric(10),@P5 varbinary(16),@P6 numeric(10),@P7 varbinary(16),@P8 numeric(10),@P9 varbinary(16),@P10 numeric(10),@P11 varbinary(16),@P12 numeric(10),@P13 varbinary(16),@P14 numeric(10),@P15 varbinary(16),@P16 numeric(10),@P17 varbinary(16),@P18 numeric(10),@P19 varbinary(16),@P20 numeric(10),@P21 varbinary(16),@P22 numeric(10),@P23 varbinary(16),@P24 numeric(10),@P25 varbinary(16),@P26 numeric(10),@P27 varbinary(16),@P28 numeric(10),@P29 varbinary(16),@P30 numeric(10),@P31 varbinary(16),@P32 numeric(10),@P33 varbinary(16),@P34 numeric(10),@P35 varbinary(16),@P36 numeric(10),@P37 varbinary(16),@P38 numeric(10),@P39 varbinary(16),@P40 numeric(10),@P41 varbinary(16),@P42 numeric(10),@P43 varbinary(16),@P44 numeric(10),@P45 varbinary(16),@P46 numeric(10),@P47 numeric(10),@P48 varbinary(16)',0x8314FCAA140E89E111E62D353C693969,0,0x8314FCAA140E89E111E62D353C69396A,0,0x8314FCAA140E89E111E62D353C69396B,0,0x8314FCAA140E89E111E62D353C693942,0,0x8314FCAA140E89E111E62D353C693947,0,0x8314FCAA140E89E111E62D353C69394C,0,0x8314FCAA140E89E111E62D353C693943,0,0x8314FCAA140E89E111E62D353C693948,0,0x8314FCAA140E89E111E62D353C69394D,0,0x8314FCAA140E89E111E62D353C693944,0,0x8314FCAA140E89E111E62D353C693949,0,0x8314FCAA140E89E111E62D353C69394E,0,0x8314FCAA140E89E111E62D353C693945,0,0x8314FCAA140E89E111E62D353C69394A,0,0x8314FCAA140E89E111E62D353C69394F,0,0x8314FCAA140E89E111E62D353C693946,0,0x8314FCAA140E89E111E62D353C69394B,0,0x8314FCAA140E89E111E62D353C693950,0,0x8314FCAA140E89E111E62D353C69396C,0,0x8314FCAA140E89E111E62D353C69396D,0,0x8314FCAA140E89E111E62D353C69396E,0,0x8314FCAA140E89E111E62D353C69396F,0,0x8314FCAA140E89E111E62D353C693970,0,0,0x941B9457A558CA4B11E6C7D91D72BA0B


Сообщение было отредактировано: 23 дек 16, 03:42
23 дек 16, 03:40    [20039654]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Могу посоветовать не орать, а сократить кол-во джоинов до 20. Как сокращать кол-во джоинов, спрашивайте в форуме по 1с.
23 дек 16, 03:43    [20039655]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
Oleg Krivopusk
Member

Откуда: Хабаровск
Сообщений: 50
Гавриленко Сергей Алексеевич,

я Вам благодарен, а можно узнать про цифру 20 - это описанное ограничение SQL или из общей практики?
23 дек 16, 03:52    [20039658]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Oleg Krivopusk
Гавриленко Сергей Алексеевич,

я Вам благодарен, а можно узнать про цифру 20 - это описанное ограничение SQL или из общей практики?
Это намек на то, что ваш запрос кривой. И еще намек на то, что не надо спрашивать на форуме стиральных машин, как в них стирать кирпич. Лучше спрашивать на форуме повелителей кирпичей, как они до этого вообще додумались.

Сообщение было отредактировано: 23 дек 16, 04:01
23 дек 16, 03:55    [20039661]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
Oleg Krivopusk
Member

Откуда: Хабаровск
Сообщений: 50
Тут немного не соглашусь.
Запрос конечно не идеален, но он выполняется (после построения плана) мгновенно. Вопрос - почему план так долго делается, на казалось бы простом запросе? Ну 1-2, ну 5 секунд, но 2 минуты !!!!
Меня 1С как то не особо интересует в данном случае, я просто в профайлере обнаружил этот случай и очень заинтересовался. Повторюсь, именно с точки зрения MS SQL.
23 дек 16, 04:06    [20039664]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Oleg Krivopusk
Тут немного не соглашусь.
Запрос конечно не идеален, но он выполняется (после построения плана) мгновенно. Вопрос - почему план так долго делается, на казалось бы простом запросе? Ну 1-2, ну 5 секунд, но 2 минуты !!!!
Меня 1С как то не особо интересует в данном случае, я просто в профайлере обнаружил этот случай и очень заинтересовался. Повторюсь, именно с точки зрения MS SQL.
Потому что кирпич сервер считает, что выгоднее две минуты строить план. MSSQL неизвестной версии еще и не на такое способен.
23 дек 16, 04:10    [20039665]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
Oleg Krivopusk
Member

Откуда: Хабаровск
Сообщений: 50
Sorry,

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
23 дек 16, 04:14    [20039667]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
Понимаете, какой-нибудь банальный хинт (force order, loop join), или небанальный (для отключения тех или иных правил оптимизации), вполне могут сократить время компиляции до приемлемых величин, но вы же запрос не можете менять?

И да, почему вы решили, что время уходит именно на построение плана?

З.Ы. И сервер пробовали обновлять?

Сообщение было отредактировано: 23 дек 16, 04:23
23 дек 16, 04:21    [20039669]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
Oleg Krivopusk
Member

Откуда: Хабаровск
Сообщений: 50
[quot
И да, почему вы решили, что время уходит именно на построение плана?
?[/quot]

Первый раз только долго (если поставить пробел и тп - изменить, ну или при DBCC FREEPROCCACHE)

Второй и далее - мухой.

Сервер - попробуем обновить.
23 дек 16, 04:42    [20039675]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
hhgks
Guest
Забавный сайт в тему http://govnokod.ru/3032
А из-за "mysql ограничений в 64 join'a" не удалось в полной мере выразить мысль программиста...
23 дек 16, 05:02    [20039678]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
hhgks
Guest
https://blogs.msdn.microsoft.com/sqlqueryprocessing/2010/02/16/understanding-sql-server-memory-grant/

Compile (query optimization): Building and searching the most optimal plan out of hundreds candidates typically requires significant amount of memory. The lifetime of this usage is typically short because optimizer releases memory as soon as optimal plan is found. Lack of available memory would cause delays in compile, and potentially inefficient (slow) plans.
23 дек 16, 05:33    [20039683]     Ответить | Цитировать Сообщить модератору
 Re: Очень долго строится план exec sp_executesql  [new]
Oleg Krivopusk
Member

Откуда: Хабаровск
Сообщений: 50
Всем спасибо!
Код конечно ******, но все решилось апдейтом

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)
Jun 17 2016 19:14:09
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


Теперь план строится мгновенно. И так же мгновенно выполняется. Даже из *****ого и *****ого кода :)
23 дек 16, 08:19    [20039788]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить