Вход | Регистрация

1С:Предприятие :: 1С:Предприятие 8 общая

Длительная реструктуризация ИБ

Длительная реструктуризация ИБ
Я
   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>
 
 
   a2080016
 
1 - 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>
   mistеr
 
2 - 27.10.17 - 10:49
(0) >как оптимизировать/ускорить

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

Список тем форума
Рекламное место пустует  Рекламное место пустует
Пользователь не знает, чего он хочет, пока не увидит то, что он получил.
Э. Йодан
ВНИМАНИЕ! Если вы потеряли окно ввода сообщения, нажмите Ctrl-F5 или Ctrl-R или кнопку "Обновить" в браузере.
Тема не обновлялась длительное время, и была помечена как архивная. Добавление сообщений невозможно.
Но вы можете создать новую ветку и вам обязательно ответят!
Каждый час на Волшебном форуме бывает более 2000 человек.
Рекламное место пустует