Имя: Пароль:
1C
1С v8
Длительная реструктуризация ИБ
0 a2080016
 
27.10.17
10:03
Коллеги добрый день!
Мы столкнулись с проблемой длительной реструктуризации информационной базы при обновлении 1C:Бухгалтерии(Бит:Финанс) 2.0 на 3.0.

1С:Предприятие 8.3.10
MS SQL Server 2008 R2
Размер базы 230 Гб
Количество записей в регистре бухгалтерии 10 000 000. Значения субконто 46 000 000.

Уже 3-е суток ms sql работает над запросом:

SELECT TOP 1
T1._RecorderTRef,
T1._RecorderRRef
FR OM dbo._AccRgED479NG T1 WITH(NOLOCK)
LEFT OUTER JOIN dbo._AccRg443NG T2 WITH(NOLOCK)
ON T1._RecorderTRef = T2._RecorderTRef AND T1._RecorderRRef = T2._RecorderRRef AND T1._LineNo = T2._LineNo
WHERE T2._RecorderTRef IS NULL AND T2._RecorderRRef IS NULL


видим соединение таблиц регистра бухгалтерии хозрасчетный со значениями субконто.

Прилагаю также план запроса. В плане наблюдается NESTED LOOPS.

Есть ли мысли как оптимизировать/ускорить процедуру реструктуризации?

<a href="https://fotki.yandex.ru/next/users/a2080016/album/176798/view/586786"; target="_blank"><img src="https://img-fotki.yandex.ru/get/892397/30372615.c/0_8f422_1253f274_orig.png"; width="2220" height="142" border="0" title="2017-10-27_9-46-12.png" alt="2017-10-27_9-46-12.png"/></a>
1 a2080016
 
27.10.17
10:04
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; Version="1.1" Build="10.50.2550.0">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="SELECT TOP 1&#xA;T1._RecorderTRef,&#xA;T1._RecorderRRef&#xA;FROM dbo._AccRgED479NG T1 WITH(NOLOCK)&#xA;LEFT OUTER JOIN dbo._AccRg443NG T2 WITH(NOLOCK)&#xA;ON T1._RecorderTRef = T2._RecorderTRef AND T1._RecorderRRef = T2._RecorderRRef AND T1._LineNo = T2._LineNo&#xA;WHERE T2._RecorderTRef IS NULL AND T2._RecorderRRef IS NULL" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="118.572" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0xB8871017804A601A" QueryPlanHash="0x8829BBFEA3238952">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
          <QueryPlan CachedPlanSize="24" CompileTime="1252" CompileCPU="1203" CompileMemory="720">
            <RelOp NodeId="0" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="27" EstimatedTotalSubtreeCost="118.572" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
              <OutputList>
                <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderTRef" />
                <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderRRef" />
              </OutputList>
              <Top RowCount="0" IsPercent="0" WithTies="0">
                <TopExpression>
                  <ScalarOperator ScalarString="(1)">
                    <Const ConstValue="(1)" />
                  </ScalarOperator>
                </TopExpression>
                <RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="40.4749" AvgRowSize="27" EstimatedTotalSubtreeCost="118.572" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderTRef" />
                    <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderRRef" />
                  </OutputList>
                  <Filter StartupExpression="0">
                    <RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1.94105" EstimateIO="0" EstimateCPU="1.96707e+009" AvgRowSize="47" EstimatedTotalSubtreeCost="118.572" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                      <OutputList>
                        <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderTRef" />
                        <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderRRef" />
                        <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderTRef" />
                        <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderRRef" />
                      </OutputList>
                      <NestedLoops Optimized="0">
                        <Predicate>
                          <ScalarOperator ScalarString="[ds_finance_up].[dbo].[_AccRgED479NG].[_RecorderTRef] as [T1].[_RecorderTRef]=[ds_finance_up].[dbo].[_AccRg443NG].[_RecorderTRef] as [T2].[_RecorderTRef] AND [ds_finance_up].[dbo].[_AccRgED479NG].[_RecorderRRef] as [T1].[_RecorderRRef]=[ds_finance_up].[dbo].[_AccRg443NG].[_RecorderRRef] as [T2].[_RecorderRRef] AND [ds_finance_up].[dbo].[_AccRgED479NG].[_LineNo] as [T1].[_LineNo]=[ds_finance_up].[dbo].[_AccRg443NG].[_LineNo] as [T2].[_LineNo]">
                            <Logical Operation="AND">
                              <ScalarOperator>
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderTRef" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderTRef" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderRRef" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderRRef" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_LineNo" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_LineNo" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Logical>
                          </ScalarOperator>
                        </Predicate>
                        <RelOp NodeId="3" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1.94105" EstimateIO="254.255" EstimateCPU="50.5937" AvgRowSize="32" EstimatedTotalSubtreeCost="0.00328934" TableCardinality="4.59942e+007" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                          <OutputList>
                            <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderTRef" />
                            <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderRRef" />
                            <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_LineNo" />
                          </OutputList>
                          <IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderTRef" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_RecorderRRef" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Alias="[T1]" Column="_LineNo" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRgED479NG]" Index="[_AccRgED479_ByRecorderNG]" Alias="[T1]" IndexKind="NonClustered" />
                          </IndexScan>
                        </RelOp>
                        <RelOp NodeId="4" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1.02315e+007" EstimateIO="36.6157" EstimateCPU="11.2549" AvgRowSize="32" EstimatedTotalSubtreeCost="69.7168" TableCardinality="1.02315e+007" Parallel="0" EstimateRebinds="0" EstimateRewinds="1.94105">
                          <OutputList>
                            <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderTRef" />
                            <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderRRef" />
                            <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_LineNo" />
                          </OutputList>
                          <IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderTRef" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderRRef" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_LineNo" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Index="[_AccRg443_ByRecorderNG]" Alias="[T2]" IndexKind="NonClustered" />
                          </IndexScan>
                        </RelOp>
                      </NestedLoops>
                    </RelOp>
                    <Predicate>
                      <ScalarOperator ScalarString="[ds_finance_up].[dbo].[_AccRg443NG].[_RecorderTRef] as [T2].[_RecorderTRef] IS NULL AND [ds_finance_up].[dbo].[_AccRg443NG].[_RecorderRRef] as [T2].[_RecorderRRef] IS NULL">
                        <Logical Operation="AND">
                          <ScalarOperator>
                            <Compare CompareOp="IS">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderTRef" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="NULL" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Compare CompareOp="IS">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[ds_finance_up]" Schema="[dbo]" Table="[_AccRg443NG]" Alias="[T2]" Column="_RecorderRRef" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="NULL" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Logical>
                      </ScalarOperator>
                    </Predicate>
                  </Filter>
                </RelOp>
              </Top>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
2 mistеr
 
27.10.17
10:49
(0) >как оптимизировать/ускорить

Обновить статистику?
3 a2080016
 
27.10.17
11:06
(2) КАК?) Это новые таблицы во время реструктуризации.
Судя по информации с других форумов - Ошибка в платформе.
4 timurhv
 
27.10.17
11:14
Это на боевом сервере в копии базы?
На время обновления развернуть на локальной машине с i7 + SSD. Поставить 8.3.11
5 ildary
 
27.10.17
11:25
(3) напишите пожалуйста поточнее версию 1С, чтобы знать где можно столкнуться с ошибкой.
6 Borteg
 
27.10.17
11:43
SELECT TOP 1 впринципе не  должен долго выполняться(только если есть сортировка). Да и значений не так много.
7 mistеr
 
27.10.17
11:52
(3) Как определил, что это новые таблицы?
8 arsik
 
гуру
27.10.17
12:13
(0) В 8.3.11 должно намного  быстрее такое пройти
9 a2080016
 
28.10.17
12:02
(7) *NG в наименовании
10 a2080016
 
28.10.17
12:03
(4) ДА, так и делаем. Но взяли 8.3.8
11 a2080016
 
28.10.17
12:04
(6) ТАМ соединение NESTED LOOPS
12 Spieluhr
 
01.11.17
11:17
Модель восстановления у базы какая? SIMPLE поставить обязательно!
13 timurhv
 
01.11.17
11:20
(12) Это похоже ошибка ранних версий платформы 8.3.10, тоже с таким столкнулись. Особо в дебри не вдавались.
14 Мыш
 
01.11.17
11:33
(0) Переименовать таблицы, создать новые такие же пустые, обновить базу, перелить в пустые из переименованных.