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

Откуда: Жатай->Подольск
Сообщений: 137
Имеем
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Запрос:
+

select * from (
SELECT 
                 [a].[id],
                 ROW_NUMBER() OVER ( ORDER BY [a].[id] ASC) AS [RowNumber],
                 COUNT(1) OVER () AS [_TotalRow]
          FROM   [reg].[LimitVolumeAppropriations] AS [a]
                 LEFT OUTER JOIN [reg].[ExecutedOperation] AS [b] ON [a].[idExecutedOperation] = [b].[id]
                 LEFT OUTER JOIN [ref].[EntityOperation] AS [c] ON [b].[idEntityOperation] = [c].[id]
                 LEFT OUTER JOIN [ref].[Operation] AS [d] ON [c].[idOperation] = [d].[id] and [d].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [ref].[PublicLegalFormation] AS [e] ON [a].[idPublicLegalFormation] = [e].[id] and [e].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [ref].[Version] AS [f] ON [a].[idVersion] = [f].[id] and [f].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [ref].[Budget] AS [g] ON [a].[idBudget] = [g].[id] and [g].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [reg].[EstimatedLine] AS [h] ON [a].[idEstimatedLine] = [h].[id] and [h].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [ref].[AuthorityOfExpenseObligation] AS [i] ON [a].[idAuthorityOfExpenseObligation] = [i].[id] and [i].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [reg].[TaskCollection] AS [j] ON [a].[idTaskCollection] = [j].[id]
                 LEFT OUTER JOIN [ref].[Activity] AS [k] ON [j].[idActivity] = [k].[id] and [k].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [ref].[HierarchyPeriod] AS [l] ON [a].[idHierarchyPeriod] = [l].[id] and [l].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [enm].[ValueType] AS [m] ON [a].[idValueType] = [m].[id] and [m].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [ref].[OKATO] AS [n] ON [a].[idOKATO] = [n].[id] and [n].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [ref].[Entity] AS [o] ON [a].[idRegistratorEntity] = [o].[id] and [o].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [ref].[Entity] AS [p] ON [a].[idApprovedEntity] = [p].[id] and [p].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [reg].[TaskCollection] AS [q] ON [a].[idTaskCollection] = [q].[id]
                 LEFT OUTER JOIN [ref].[Contingent] AS [r] ON [q].[idContingent] = [r].[id] and [r].[Caption] LIKE '%министерство%' ESCAPE '\'
                 LEFT OUTER JOIN [reg].[EstimatedLine] AS [u] ON [a].[idEstimatedLine] = [u].[id]
                 LEFT OUTER JOIN [ref].[SBP] AS [v] ON [u].[idSBP] = [v].[id] and [v].[Caption] LIKE '%министерство%' ESCAPE '\'
				 left outer join dbo.GetCaptionsByField(-1610612607, -1610611673) a1 on a1.Id=a.idRegistrator and a1.[IdEntity]=a.idRegistratorEntity and a1.Caption LIKE '%министерство%' ESCAPE '\'
				 left outer join dbo.GetCaptionsByField(-1610612607, -1610611670) a2 on a2.Id=a.idApproved and a2.[IdEntity]=a.idApprovedEntity and a2.Caption LIKE '%министерство%' ESCAPE '\'
          WHERE  (CAST ([a].[id] AS NVARCHAR (50)) LIKE '%министерство%' ESCAPE '\'
                  OR CAST ([a].[Value] AS NVARCHAR (50)) LIKE '%министерство%' ESCAPE '\'
                  OR [d].[id] is not null
                  OR [e].[id] is not null
                  OR [f].[id] is not null
                  OR [g].[id] is not null
                  OR [h].[id] is not null
                  OR [i].[id] is not null
                  OR [k].[id] is not null
                  OR [l].[id] is not null
                  OR [m].[id] is not null
                  OR [n].[id] is not null
                  OR a1.[id] is not null
                  OR [o].[id] is not null
				  OR a2.[id] is not null
                  OR [p].[id] is not null
                  OR [r].[id] is not null
                  OR [v].[id] is not null)
) a where a.RowNumber between 1 and 25




План внутреннего и всего запроса в приложенном архиве.

Внутренний запрос исполняется за 49 секунд

Весь я не смог дождаться, прерывал после 10 минут (критичное время)

Варианты с временными таблицами в табличными переменными использовать не могу из-за текущей реализации генератора запросов.

К сообщению приложен файл (plans.rar - 39Kb) cкачать
5 дек 13, 11:40    [15244774]     Ответить | Цитировать Сообщить модератору
 Re: Помогите, не понимаю почему так долго  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31959
Matroskin
Помогите, не понимаю почему так долго
Всё понятно, сплошные сканы из за сплошных [Caption] LIKE '%министерство%'

Схема базы плохая, и клиент такой же (... использовать не могу из-за текущей реализации генератора запросов).
5 дек 13, 21:56    [15249414]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить