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

Откуда: Жатай->Подольск
Сообщений: 137
Есть таблица ExecutedOperation из которой производится удаление, допустим DELETE FROM ExecutedOperation WHERE id=значение
и Есть еще ~20 табличек в которых есть поле idExecutedOperation по которому они привязаны к ExecutedOperation FK, во всех табличках есть индексы по полю idExecutedOperation, однако несмотря на это получаю следующий план:


|--Sequence
|--Table Spool
| |--Assert(WHERE:(CASE WHEN NOT [Expr1102] IS NULL THEN (0) ELSE CASE WHEN NOT [Expr1103] IS NULL THEN (1) ELSE CASE WHEN NOT [Expr1104] IS NULL THEN (2) ELSE CASE WHEN NOT [Expr1105] IS NULL THEN (3) ELSE CASE WHEN NOT [Expr1106] IS NULL THEN (4) ELSE CASE WHEN NOT [Expr1107] IS NULL THEN (5) ELSE CASE WHEN NOT [Expr1108] IS NULL THEN (6) ELSE CASE WHEN NOT [Expr1109] IS NULL THEN (7) ELSE CASE WHEN NOT [Expr1110] IS NULL THEN (8) ELSE CASE WHEN NOT [Expr1111] IS NULL THEN (9) ELSE CASE WHEN NOT [Expr1112] IS NULL THEN (10) ELSE CASE WHEN NOT [Expr1113] IS NULL THEN (11) ELSE CASE WHEN NOT [Expr1114] IS NULL THEN (12) ELSE CASE WHEN NOT [Expr1115] IS NULL THEN (13) ELSE CASE WHEN NOT [Expr1116] IS NULL THEN (14) ELSE CASE WHEN NOT [Expr1117] IS NULL THEN (15) ELSE CASE WHEN NOT [Expr1118] IS NULL THEN (16) ELSE CASE WHEN NOT [Expr1119] IS NULL THEN (17) ELSE CASE WHEN NOT [Expr1120] IS NULL THEN (18) ELSE CASE WHEN NOT [Expr1121] IS NULL THEN (19) ELSE CASE WHEN NOT [Expr1122] IS NULL THEN (20) EL
| |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Sbor_data].[ref].[ExecutedOperations].[id]), DEFINE:([Expr1124] = [PROBE VALUE]))
| |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Sbor_data].[ref].[ExecutedOperations].[id]), DEFINE:([Expr1123] = [PROBE VALUE]))
| | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Sbor_data].[ref].[ExecutedOperations].[id]), DEFINE:([Expr1122] = [PROBE VALUE]))
| | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Sbor_data].[ref].[ExecutedOperations].[id]), DEFINE:([Expr1121] = [PROBE VALUE]))
| | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Sbor_data].[ref].[ExecutedOperations].[id]), DEFINE:([Expr1120] = [PROBE VALUE]))
| | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Sbor_data].[ref].[ExecutedOperations].[id]), DEFINE:([Expr1119] = [PROBE VALUE]))
| | | | | | |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Sbor_data].[ref].[ExecutedOperations].[id]), DEFINE:([Expr1118] = [PROBE VALUE]))
| | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegGoalIndicatorValues].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegGoalIndicatorValues].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[GoalAttribute].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[GoalAttribute].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegGoalIndicators].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegGoalIndicators].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegTemplatesApplyingHistory].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegTemplatesApplyingHistory].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegGoalCommunication].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegGoalCommunication].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegExternalSystemLink].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegExternalSystemLink].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegServiceVolumes].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegServiceVolumes].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegExpenditureCommitments].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegExpenditureCommitments].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegServices].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegServices].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegDistributionOfExpendituresByGoals].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegDistributionOfExpendituresByGoals].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegServiceIndicatorsValues].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegServiceIndicatorsValues].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegApprovedTasks].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegApprovedTasks].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegServiceIndicators].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegServiceIndicators].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[regAdministeredRevenues].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[regAdministeredRevenues].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegParameterValues].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegParameterValues].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | | | | | | | | | |--Merge Join(Left Semi Join, MERGE:([Sbor_data].[ref].[ExecutedOperations].[id])=([Sbor_data].[ref].[RegLimitVolumeAppropriations].[idExecutedOperation]), RESIDUAL:([Sbor_data].[ref].[RegLimitVolumeAppropriations].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]))
| | | | | | | | | | | | | | | | | | | | | | | |--Sort(ORDER BY:([Sbor_data].[ref].[ExecutedOperations].[id] ASC))
| | | | | | | | | | | | | | | | | | | | | | | | |--Clustered Index Delete(OBJECT:([Sbor_data].[ref].[ExecutedOperations].[ExecutedOperations_PK_844]), OBJECT:([Sbor_data].[ref].[ExecutedOperations].[ExecutedOperations_parent]), OBJECT:([Sbor_data].[ref].[ExecutedOperations].[ExecutedOperations_uq]) WITH UNORDERED PREFETCH)
| | | | | | | | | | | | | | | | | | | | | | | | |--Top(ROWCOUNT est 0)
| | | | | | | | | | | | | | | | | | | | | | | | |--Index Seek(OBJECT:([Sbor_data].[ref].[ExecutedOperations].[ExecutedOperations_uq]), SEEK:([Sbor_data].[ref].[ExecutedOperations].[idOwner]=newid()) ORDERED FORWARD)
| | | | | | | | | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegLimitVolumeAppropriations].[RegLimitVolumeAppropriations_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegParameterValues].[RegParameterValues_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[regAdministeredRevenues].[regAdministeredRevenues_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegServiceIndicators].[RegServiceIndicators_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegApprovedTasks].[RegApprovedTasks_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegServiceIndicatorsValues].[RegServiceIndicatorsValues_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegDistributionOfExpendituresByGoals].[RegDistributionOfExpendituresByGoals_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegServices].[RegServices_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegExpenditureCommitments].[RegExpenditureCommitments_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegServiceVolumes].[RegServiceVolumes_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegExternalSystemLink].[RegExternalSystemLink_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegGoalCommunication].[RegGoalCommunication_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegTemplatesApplyingHistory].[RegTemplatesApplyingHistory_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegGoalIndicators].[RegGoalIndicators_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[GoalAttribute].[GoalAttribute_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | | |--Index Scan(OBJECT:([Sbor_data].[ref].[RegGoalIndicatorValues].[RegGoalIndicatorValues_idExecutedOperation]), ORDERED FORWARD)
| | | | | | | |--Index Seek(OBJECT:([Sbor_data].[ref].[GoalHierarchy].[GoalHierarchy_idExecutedOperation]), SEEK:([Sbor_data].[ref].[GoalHierarchy].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]) ORDERED FORWARD)
| | | | | | |--Index Seek(OBJECT:([Sbor_data].[ref].[RegGoalProgramAttribute].[RegGoalProgramAttribute_idExecutedOperation]), SEEK:([Sbor_data].[ref].[RegGoalProgramAttribute].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]) ORDERED FORWARD)
| | | | | |--Index Seek(OBJECT:([Sbor_data].[ref].[RegVolumesIncome].[RegVolumesIncome_idExecutedOperation]), SEEK:([Sbor_data].[ref].[RegVolumesIncome].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]) ORDERED FORWARD)
| | | | |--Index Seek(OBJECT:([Sbor_data].[ref].[RegGoalPrograms].[RegGoalPrograms_idExecutedOperation]), SEEK:([Sbor_data].[ref].[RegGoalPrograms].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]) ORDERED FORWARD)
| | | |--Index Seek(OBJECT:([Sbor_data].[ref].[RegGoalTasksCommunication].[RegGoalTasksCommunication_idExecutedOperation]), SEEK:([Sbor_data].[ref].[RegGoalTasksCommunication].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]) ORDERED FORWARD)
| | |--Index Seek(OBJECT:([Sbor_data].[ref].[RegGroupLegalActs].[RegGroupLegalActs_idExecutedOperation]), SEEK:([Sbor_data].[ref].[RegGroupLegalActs].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]) ORDERED FORWARD)
| |--Index Seek(OBJECT:([Sbor_data].[ref].[RegIndirectCostsRatios].[RegIndirectCostsRatios_idExecutedOperation]), SEEK:([Sbor_data].[ref].[RegIndirectCostsRatios].[idExecutedOperation]=[Sbor_data].[ref].[ExecutedOperations].[id]) ORDERED FORWARD)
|--Assert(WHERE:(CASE WHEN NOT [Expr1125] IS NULL THEN (0) ELSE NULL END))
|--Nested Loops(Left Semi Join, OUTER REFERENCES:([Sbor_data].[ref].[ExecutedOperations].[id]), DEFINE:([Expr1125] = [PROBE VALUE]))
|--Table Spool
|--Index Seek(OBJECT:([Sbor_data].[ref].[ExecutedOperations].[ExecutedOperations_parent]), SEEK:([Sbor_data].[ref].[ExecutedOperations].[parent]=[Sbor_data].[ref].[ExecutedOperations].[id]) ORDERED FORWARD)

Собственно вопрос, за что он Index Scan вместо IndexSeek в основной части использует?
Есть конечно предположение, что некоторые таблички маленькие и оптимизатор считает что скан быстрее, но не столько-же...
MS SQL 10.0.5500

P.S. собственно ускорить удаление необходимо для того, чтобы во время некоторых операций исключить дедлоки между "insert into одна из 20 табличек" и "delete from ExecutedOperation"
16 янв 12, 09:39    [11908758]     Ответить | Цитировать Сообщить модератору
 Re: План запроса при удалении  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
У Вас там не только scan - еще и merge есть... Сам скрипт, который генерит сей план, покажите.
16 янв 12, 09:49    [11908802]     Ответить | Цитировать Сообщить модератору
 Re: План запроса при удалении  [new]
Matroskin
Member

Откуда: Жатай->Подольск
Сообщений: 137
SanyL,

DELETE FROM ExecutedOperation WHERE id=значение
16 янв 12, 09:50    [11908813]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить