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

Откуда: Москва
Сообщений: 1139
День добрый,
есть вьюха, которая меньше чем за секунду выдает 21 000 записей. Предположим:
SELECT * FROM [vw test]

Однако, если я делаю SELECT и добавляю условие WHERE. То это дело выполняется 30 секунд (результат = 5 000 записей).
SELECT * FROM [vw test] 
WHERE ([Customer name] = N'CustomerName')



Не подскажете, почему так происходит и как с этим бороться?
26 мар 12, 14:48    [12314526]     Ответить | Цитировать Сообщить модератору
 Re: Вьюха + where = жуткие тормоза. Почему?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Gena928, планы глядеть не пробовали? Попробуйте.
26 мар 12, 14:50    [12314545]     Ответить | Цитировать Сообщить модератору
 Re: Вьюха + where = жуткие тормоза. Почему?  [new]
Ken@t
Member

Откуда: 大地
Сообщений: 3265
Gena928,

Потому , что WHERE
Потому, что нет индекса
Потому, что планы не смотрите.
Потому, что не только кнопки жать надо, а ещё и литературу читать.
26 мар 12, 14:51    [12314554]     Ответить | Цитировать Сообщить модератору
 Re: Вьюха + where = жуткие тормоза. Почему?  [new]
Gena928
Member

Откуда: Москва
Сообщений: 1139
kDnZP
Gena928, планы глядеть не пробовали? Попробуйте.


А можно поподробней?
в приложении план выполнения. Это как-то можно ускорить?

К сообщению приложен файл. Размер - 106Kb
26 мар 12, 15:16    [12314757]     Ответить | Цитировать Сообщить модератору
 Re: Вьюха + where = жуткие тормоза. Почему?  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Gena928, не умею лечить по фотографии))). Дайте в текстовом виде чтоль. И для обоих случаев. И план действительный.
26 мар 12, 15:21    [12314793]     Ответить | Цитировать Сообщить модератору
 Re: Вьюха + where = жуткие тормоза. Почему?  [new]
Gena928
Member

Откуда: Москва
Сообщений: 1139
kDnZP
Gena928, не умею лечить по фотографии))). Дайте в текстовом виде чтоль. И для обоих случаев. И план действительный.


+ Без запроса WHERE
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="8617.75" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="3.99982" StatementText="SELECT [vw auto Posted Vehicle Doc Line].*
FROM  [vw auto Posted Vehicle Doc Line]
" StatementType="SELECT" QueryHash="0x29EC1F139EE07337" QueryPlanHash="0x0825302A31D1B9BA">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="144" CompileTime="230" CompileCPU="230" CompileMemory="2160">
            <RelOp AvgRowSize="117" EstimateCPU="0.0982502" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8617.75" LogicalOp="Right Anti Semi Join" NodeId="0" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3.99982">
              <OutputList>
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
              </OutputList>
              <MemoryFractions Input="0" Output="0" />
              <Hash>
                <DefinedValues />
                <HashKeysBuild>
                  <ColumnReference Column="Union1016" />
                </HashKeysBuild>
                <HashKeysProbe>
                  <ColumnReference Column="Expr1017" />
                </HashKeysProbe>
                <ProbeResidual>
                  <ScalarOperator ScalarString="[Expr1017]=[Union1016]">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Column="Expr1017" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Column="Union1016" />
                        </Identifier>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </ProbeResidual>
                <RelOp AvgRowSize="31" EstimateCPU="0.13406" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="131.243" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.65331">
                  <OutputList>
                    <ColumnReference Column="Union1016" />
                  </OutputList>
                  <MemoryFractions Input="0" Output="0" />
                  <Hash>
                    <DefinedValues />
                    <HashKeysBuild>
                      <ColumnReference Column="Union1016" />
                    </HashKeysBuild>
                    <BuildResidual>
                      <ScalarOperator ScalarString="[Union1016] = [Union1016]">
                        <Compare CompareOp="IS">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Column="Union1016" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Column="Union1016" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </BuildResidual>
                    <RelOp AvgRowSize="31" EstimateCPU="0.00172248" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="17224.8" LogicalOp="Concatenation" NodeId="2" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="2.51925">
                      <OutputList>
                        <ColumnReference Column="Union1016" />
                      </OutputList>
                      <Concat>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Union1016" />
                            <ColumnReference Column="Expr1010" />
                            <ColumnReference Column="Expr1015" />
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp AvgRowSize="31" EstimateCPU="0.00086124" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8612.4" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.26162">
                          <OutputList>
                            <ColumnReference Column="Expr1010" />
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1010" />
                                <ScalarOperator ScalarString="[Expr1018]+[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[Appl_ To Post_ Transf_ Act No_] as [BACK_MOVEMENT_HEADER].[Appl_ To Post_ Transf_ Act No_]">
                                  <Arithmetic Operation="ADD">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Column="Expr1018" />
                                      </Identifier>
                                    </ScalarOperator>
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                      </Identifier>
                                    </ScalarOperator>
                                  </Arithmetic>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp AvgRowSize="33" EstimateCPU="0.13286" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8612.4" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.26076">
                              <OutputList>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                <ColumnReference Column="Expr1018" />
                              </OutputList>
                              <MemoryFractions Input="0" Output="0" />
                              <Hash>
                                <DefinedValues />
                                <HashKeysBuild>
                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                </HashKeysBuild>
                                <HashKeysProbe>
                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                </HashKeysProbe>
                                <ProbeResidual>
                                  <ScalarOperator ScalarString="[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_] as [BACK_MOVEMENT_LINE].[Document No_]=[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[No_] as [BACK_MOVEMENT_HEADER].[No_]">
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </ProbeResidual>
                                <RelOp AvgRowSize="31" EstimateCPU="0.0009765" EstimateIO="0.0157176" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="602.401" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0166941" TableCardinality="745">
                                  <OutputList>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                  </OutputList>
                                  <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Index="[TEST Database$Posted Vehicle Doc_ Header$0]" Alias="[BACK_MOVEMENT_HEADER]" TableReferenceId="1" IndexKind="Clustered" />
                                    <Predicate>
                                      <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[Appl_ To Post_ Transf_ Act No_] as [BACK_MOVEMENT_HEADER].[Appl_ To Post_ Transf_ Act No_],0)<>N''">
                                        <Compare CompareOp="NE">
                                          <ScalarOperator>
                                            <Convert DataType="nvarchar" Length="40" Style="0" Implicit="true">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Convert>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="N''" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Predicate>
                                  </IndexScan>
                                </RelOp>
                                <RelOp AvgRowSize="32" EstimateCPU="0.0013649" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13649" LogicalOp="Compute Scalar" NodeId="8" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.11077">
                                  <OutputList>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                    <ColumnReference Column="Expr1018" />
                                  </OutputList>
                                  <ComputeScalar>
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Column="Expr1018" />
                                        <ScalarOperator ScalarString="[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[VIN] as [BACK_MOVEMENT_LINE].[VIN]+' '">
                                          <Arithmetic Operation="ADD">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="' '" />
                                            </ScalarOperator>
                                          </Arithmetic>
                                        </ScalarOperator>
                                      </DefinedValue>
                                    </DefinedValues>
                                    <RelOp AvgRowSize="39" EstimateCPU="0.0151709" EstimateIO="1.09424" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13649" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.10941" TableCardinality="13649">
                                      <OutputList>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                      </OutputList>
                                      <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Index="[TEST Database$Posted Vehicle Doc_ Line$0]" Alias="[BACK_MOVEMENT_LINE]" TableReferenceId="1" IndexKind="Clustered" />
                                      </IndexScan>
                                    </RelOp>
                                  </ComputeScalar>
                                </RelOp>
                              </Hash>
                            </RelOp>
                          </ComputeScalar>
                        </RelOp>
                        <RelOp AvgRowSize="31" EstimateCPU="0.128007" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8612.4" LogicalOp="Inner Join" NodeId="17" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.25591">
                          <OutputList>
                            <ColumnReference Column="Expr1015" />
                          </OutputList>
                          <MemoryFractions Input="0" Output="0" />
                          <Hash>
                            <DefinedValues />
                            <HashKeysBuild>
                              <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                            </HashKeysBuild>
                            <HashKeysProbe>
                              <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                            </HashKeysProbe>
                            <ProbeResidual>
                              <ScalarOperator ScalarString="[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_] as [BACK_MOVEMENT_LINE].[Document No_]=[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[No_] as [BACK_MOVEMENT_HEADER].[No_]">
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </ProbeResidual>
                            <RelOp AvgRowSize="31" EstimateCPU="0.0009765" EstimateIO="0.0157176" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="602.401" LogicalOp="Clustered Index Scan" NodeId="19" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0166941" TableCardinality="745">
                              <OutputList>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                              </OutputList>
                              <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Index="[TEST Database$Posted Vehicle Doc_ Header$0]" Alias="[BACK_MOVEMENT_HEADER]" TableReferenceId="2" IndexKind="Clustered" />
                                <Predicate>
                                  <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[Appl_ To Post_ Transf_ Act No_] as [BACK_MOVEMENT_HEADER].[Appl_ To Post_ Transf_ Act No_],0)<>N''">
                                    <Compare CompareOp="NE">
                                      <ScalarOperator>
                                        <Convert DataType="nvarchar" Length="40" Style="0" Implicit="true">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Convert>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="N''" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Predicate>
                              </IndexScan>
                            </RelOp>
                            <RelOp AvgRowSize="42" EstimateCPU="0.0013649" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13649" LogicalOp="Compute Scalar" NodeId="21" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.11077">
                              <OutputList>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                <ColumnReference Column="Expr1015" />
                              </OutputList>
                              <ComputeScalar>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1015" />
                                    <ScalarOperator ScalarString="([SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[VIN] as [BACK_MOVEMENT_LINE].[VIN]+' ')+[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_] as [BACK_MOVEMENT_LINE].[Document No_]">
                                      <Arithmetic Operation="ADD">
                                        <ScalarOperator>
                                          <Arithmetic Operation="ADD">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="' '" />
                                            </ScalarOperator>
                                          </Arithmetic>
                                        </ScalarOperator>
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                          </Identifier>
                                        </ScalarOperator>
                                      </Arithmetic>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <RelOp AvgRowSize="39" EstimateCPU="0.0151709" EstimateIO="1.09424" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13649" LogicalOp="Clustered Index Scan" NodeId="22" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.10941" TableCardinality="13649">
                                  <OutputList>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                  </OutputList>
                                  <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Index="[TEST Database$Posted Vehicle Doc_ Line$0]" Alias="[BACK_MOVEMENT_LINE]" TableReferenceId="2" IndexKind="Clustered" />
                                  </IndexScan>
                                </RelOp>
                              </ComputeScalar>
                            </RelOp>
                          </Hash>
                        </RelOp>
                      </Concat>
                    </RelOp>
                  </Hash>
                </RelOp>
                <RelOp AvgRowSize="139" EstimateCPU="0.132633" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10324.7" LogicalOp="Inner Join" NodeId="28" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.24825">
                  <OutputList>
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
                    <ColumnReference Column="Expr1017" />
                  </OutputList>
                  <MemoryFractions Input="0" Output="0" />
                  <Hash>
                    <DefinedValues />
                    <HashKeysBuild>
                      <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Column="No_" />
                    </HashKeysBuild>
                    <HashKeysProbe>
                      <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                    </HashKeysProbe>
                    <ProbeResidual>
                      <ScalarOperator ScalarString="[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_]=[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[No_]">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Column="No_" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </ProbeResidual>
                    <RelOp AvgRowSize="19" EstimateCPU="0.0009765" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="745" LogicalOp="Index Scan" NodeId="29" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00484224" TableCardinality="745">
                      <OutputList>
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Column="No_" />
                      </OutputList>
                      <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Column="No_" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Index="[$2]" IndexKind="NonClustered" />
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="139" EstimateCPU="0.0013649" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13649" LogicalOp="Compute Scalar" NodeId="30" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.11077">
                      <OutputList>
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
                        <ColumnReference Column="Expr1017" />
                      </OutputList>
                      <ComputeScalar>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Column="Expr1017" />
                            <ScalarOperator ScalarString="([SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[VIN]+' ')+[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_]">
                              <Arithmetic Operation="ADD">
                                <ScalarOperator>
                                  <Arithmetic Operation="ADD">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                                      </Identifier>
                                    </ScalarOperator>
                                    <ScalarOperator>
                                      <Const ConstValue="' '" />
                                    </ScalarOperator>
                                  </Arithmetic>
                                </ScalarOperator>
                                <ScalarOperator>
                                  <Identifier>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                                  </Identifier>
                                </ScalarOperator>
                              </Arithmetic>
                            </ScalarOperator>
                          </DefinedValue>
                        </DefinedValues>
                        <RelOp AvgRowSize="117" EstimateCPU="0.0151709" EstimateIO="1.09424" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13649" LogicalOp="Clustered Index Scan" NodeId="31" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.10941" TableCardinality="13649">
                          <OutputList>
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
                          </OutputList>
                          <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Index="[TEST Database$Posted Vehicle Doc_ Line$0]" IndexKind="Clustered" />
                          </IndexScan>
                        </RelOp>
                      </ComputeScalar>
                    </RelOp>
                  </Hash>
                </RelOp>
              </Hash>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
26 мар 12, 15:51    [12315067]     Ответить | Цитировать Сообщить модератору
 Re: Вьюха + where = жуткие тормоза. Почему?  [new]
Gena928
Member

Откуда: Москва
Сообщений: 1139
kDnZP
Gena928, не умею лечить по фотографии))). Дайте в текстовом виде чтоль. И для обоих случаев. И план действительный.



+ с запросом WHERE
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementId="1" StatementText="
SET SHOWPLAN_XML ON

" StatementType="SET ON/OFF" />
      </Statements>
      <Statements>
        <StmtSimple StatementCompId="2" StatementId="2" StatementText="
SET STATISTICS XML ON


" StatementType="SET STATS" />
      </Statements>
      <Statements>
        <StmtSimple StatementCompId="3" StatementEstRows="738.207" StatementId="3" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="2.32918" StatementText="
SELECT [vw auto Posted Vehicle Doc Line].*
FROM  [vw auto Posted Vehicle Doc Line]
WHERE ([New Location Code] = N'DEALER')" StatementType="SELECT" ParameterizedText="(@1 nvarchar(4000))SELECT [vw auto Posted Vehicle Doc Line].* FROM [vw auto Posted Vehicle Doc Line] WHERE [New Location Code]=@1" QueryHash="0xA86DB9117CECECB4" QueryPlanHash="0x28AC25C865167B22">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="104" CompileTime="219" CompileCPU="219" CompileMemory="2256">
            <MissingIndexes>
              <MissingIndexGroup Impact="45.2924">
                <MissingIndex Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]">
                  <ColumnGroup Usage="INEQUALITY">
                    <Column Name="[New Location Code]" ColumnId="40" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[Journal Batch Name]" ColumnId="3" />
                    <Column Name="[Line No_]" ColumnId="4" />
                    <Column Name="[Entry Type]" ColumnId="5" />
                    <Column Name="[Document No_]" ColumnId="6" />
                    <Column Name="[Posting Date]" ColumnId="7" />
                    <Column Name="[VIN]" ColumnId="8" />
                    <Column Name="[No_]" ColumnId="27" />
                    <Column Name="[Location Code]" ColumnId="30" />
                    <Column Name="[DI No_]" ColumnId="116" />
                    <Column Name="[PI No_]" ColumnId="117" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>
            <RelOp AvgRowSize="120" EstimateCPU="0.00338917" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="738.207" LogicalOp="Left Anti Semi Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="2.32918">
              <OutputList>
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
              </OutputList>
              <NestedLoops Optimized="false">
                <OuterReferences>
                  <ColumnReference Column="Expr1017" />
                </OuterReferences>
                <RelOp AvgRowSize="142" EstimateCPU="0.0123227" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="810.806" LogicalOp="Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.18513">
                  <OutputList>
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
                    <ColumnReference Column="Expr1017" />
                  </OutputList>
                  <MemoryFractions Input="1" Output="1" />
                  <Sort Distinct="false">
                    <OrderBy>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Column="Expr1017" />
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp AvgRowSize="142" EstimateCPU="0.039382" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="810.806" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.16155">
                      <OutputList>
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
                        <ColumnReference Column="Expr1017" />
                      </OutputList>
                      <MemoryFractions Input="0" Output="0" />
                      <Hash>
                        <DefinedValues />
                        <HashKeysBuild>
                          <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Column="No_" />
                        </HashKeysBuild>
                        <HashKeysProbe>
                          <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                        </HashKeysProbe>
                        <ProbeResidual>
                          <ScalarOperator ScalarString="[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_]=[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[No_]">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Column="No_" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </ProbeResidual>
                        <RelOp AvgRowSize="19" EstimateCPU="0.0009765" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="745" LogicalOp="Index Scan" NodeId="3" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00484224" TableCardinality="745">
                          <OutputList>
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Column="No_" />
                          </OutputList>
                          <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Column="No_" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Index="[$2]" IndexKind="NonClustered" />
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="152" EstimateCPU="0.0013649" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="974.071" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.11077">
                          <OutputList>
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
                            <ColumnReference Column="Expr1017" />
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1017" />
                                <ScalarOperator ScalarString="([SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[VIN]+' ')+[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_]">
                                  <Arithmetic Operation="ADD">
                                    <ScalarOperator>
                                      <Arithmetic Operation="ADD">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                                          </Identifier>
                                        </ScalarOperator>
                                        <ScalarOperator>
                                          <Const ConstValue="' '" />
                                        </ScalarOperator>
                                      </Arithmetic>
                                    </ScalarOperator>
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                                      </Identifier>
                                    </ScalarOperator>
                                  </Arithmetic>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp AvgRowSize="120" EstimateCPU="0.0151709" EstimateIO="1.09424" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="974.071" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.10941" TableCardinality="13649">
                              <OutputList>
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
                              </OutputList>
                              <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Journal Batch Name" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Line No_" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Entry Type" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Document No_" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Posting Date" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="VIN" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="No_" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="Location Code" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="DI No_" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="PI No_" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Index="[TEST Database$Posted Vehicle Doc_ Line$0]" IndexKind="Clustered" />
                                <Predicate>
                                  <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(10),[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[New Location Code],0)=N'DEALER'">
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Convert DataType="nvarchar" Length="20" Style="0" Implicit="true">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Column="New Location Code" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Convert>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Const ConstValue="N'DEALER'" />
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Predicate>
                              </IndexScan>
                            </RelOp>
                          </ComputeScalar>
                        </RelOp>
                      </Hash>
                    </RelOp>
                  </Sort>
                </RelOp>
                <RelOp AvgRowSize="9" EstimateCPU="0.0001001" EstimateIO="0" EstimateRebinds="27.4747" EstimateRewinds="782.332" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="10" Parallel="false" PhysicalOp="Row Count Spool" EstimatedTotalSubtreeCost="1.14065">
                  <OutputList />
                  <RowCountSpool>
                    <RelOp AvgRowSize="9" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="27.4747" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="12" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.05949">
                      <OutputList />
                      <Top RowCount="false" IsPercent="false" WithTies="false">
                        <TopExpression>
                          <ScalarOperator ScalarString="(1)">
                            <Const ConstValue="(1)" />
                          </ScalarOperator>
                        </TopExpression>
                        <RelOp AvgRowSize="9" EstimateCPU="0.000178802" EstimateIO="0" EstimateRebinds="27.4747" EstimateRewinds="0" EstimateRows="1" LogicalOp="Concatenation" NodeId="13" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="1.05948">
                          <OutputList />
                          <Concat>
                            <DefinedValues />
                            <RelOp AvgRowSize="9" EstimateCPU="3.17971" EstimateIO="0" EstimateRebinds="27.4747" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0502558">
                              <OutputList />
                              <NestedLoops Optimized="false">
                                <Predicate>
                                  <ScalarOperator ScalarString="[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_] as [BACK_MOVEMENT_LINE].[Document No_]=[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[No_] as [BACK_MOVEMENT_HEADER].[No_]">
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Predicate>
                                <RelOp AvgRowSize="39" EstimateCPU="0.0150924" EstimateIO="1.09431" EstimateRebinds="0" EstimateRewinds="27.4747" EstimateRows="1.41248" LogicalOp="Clustered Index Scan" NodeId="16" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00705757" TableCardinality="13649">
                                  <OutputList>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                  </OutputList>
                                  <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Index="[TEST Database$Posted Vehicle Doc_ Line$0]" Alias="[BACK_MOVEMENT_LINE]" TableReferenceId="2" IndexKind="Clustered" />
                                    <Predicate>
                                      <ScalarOperator ScalarString="[Expr1017]=(([SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[VIN] as [BACK_MOVEMENT_LINE].[VIN]+' ')+[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_] as [BACK_MOVEMENT_LINE].[Document No_])">
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="Expr1017" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Arithmetic Operation="ADD">
                                              <ScalarOperator>
                                                <Arithmetic Operation="ADD">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Const ConstValue="' '" />
                                                  </ScalarOperator>
                                                </Arithmetic>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Arithmetic>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Predicate>
                                  </IndexScan>
                                </RelOp>
                                <RelOp AvgRowSize="19" EstimateCPU="0.000208532" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="40" EstimateRows="602.401" LogicalOp="Lazy Spool" NodeId="18" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.035672">
                                  <OutputList>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                  </OutputList>
                                  <Spool>
                                    <RelOp AvgRowSize="31" EstimateCPU="0.0009765" EstimateIO="0.0157176" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="602.401" LogicalOp="Clustered Index Scan" NodeId="20" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0166941" TableCardinality="745">
                                      <OutputList>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                      </OutputList>
                                      <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Index="[TEST Database$Posted Vehicle Doc_ Header$0]" Alias="[BACK_MOVEMENT_HEADER]" TableReferenceId="2" IndexKind="Clustered" />
                                        <Predicate>
                                          <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[Appl_ To Post_ Transf_ Act No_] as [BACK_MOVEMENT_HEADER].[Appl_ To Post_ Transf_ Act No_],0)<>N''">
                                            <Compare CompareOp="NE">
                                              <ScalarOperator>
                                                <Convert DataType="nvarchar" Length="40" Style="0" Implicit="true">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Convert>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Const ConstValue="N''" />
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </Predicate>
                                      </IndexScan>
                                    </RelOp>
                                  </Spool>
                                </RelOp>
                              </NestedLoops>
                            </RelOp>
                            <RelOp AvgRowSize="9" EstimateCPU="0.123612" EstimateIO="0" EstimateRebinds="27.4747" EstimateRewinds="0" EstimateRows="1.18921" LogicalOp="Inner Join" NodeId="23" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.06431">
                              <OutputList />
                              <MemoryFractions Input="0" Output="0" />
                              <Hash>
                                <DefinedValues />
                                <HashKeysBuild>
                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                </HashKeysBuild>
                                <HashKeysProbe>
                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                </HashKeysProbe>
                                <ProbeResidual>
                                  <ScalarOperator ScalarString="[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[Document No_] as [BACK_MOVEMENT_LINE].[Document No_]=[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[No_] as [BACK_MOVEMENT_HEADER].[No_] AND [Expr1017]=([Expr1019]+[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[Appl_ To Post_ Transf_ Act No_] as [BACK_MOVEMENT_HEADER].[Appl_ To Post_ Transf_ Act No_])">
                                    <Logical Operation="AND">
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="Expr1017" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Arithmetic Operation="ADD">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Column="Expr1019" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Arithmetic>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                </ProbeResidual>
                                <RelOp AvgRowSize="31" EstimateCPU="0.000898" EstimateIO="0.0157961" EstimateRebinds="0" EstimateRewinds="27.4747" EstimateRows="602.401" LogicalOp="Clustered Index Scan" NodeId="25" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0413663" TableCardinality="745">
                                  <OutputList>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                  </OutputList>
                                  <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="No_" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Index="[TEST Database$Posted Vehicle Doc_ Header$0]" Alias="[BACK_MOVEMENT_HEADER]" TableReferenceId="1" IndexKind="Clustered" />
                                    <Predicate>
                                      <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[Appl_ To Post_ Transf_ Act No_] as [BACK_MOVEMENT_HEADER].[Appl_ To Post_ Transf_ Act No_],0)<>N''">
                                        <Compare CompareOp="NE">
                                          <ScalarOperator>
                                            <Convert DataType="nvarchar" Length="40" Style="0" Implicit="true">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Header]" Alias="[BACK_MOVEMENT_HEADER]" Column="Appl_ To Post_ Transf_ Act No_" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Convert>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Const ConstValue="N''" />
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Predicate>
                                  </IndexScan>
                                </RelOp>
                                <RelOp AvgRowSize="32" EstimateCPU="0.0013649" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="27.4747" EstimateRows="100.921" LogicalOp="Compute Scalar" NodeId="27" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0168755">
                                  <OutputList>
                                    <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                    <ColumnReference Column="Expr1019" />
                                  </OutputList>
                                  <ComputeScalar>
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Column="Expr1019" />
                                        <ScalarOperator ScalarString="[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[VIN] as [BACK_MOVEMENT_LINE].[VIN]+' '">
                                          <Arithmetic Operation="ADD">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="' '" />
                                            </ScalarOperator>
                                          </Arithmetic>
                                        </ScalarOperator>
                                      </DefinedValue>
                                    </DefinedValues>
                                    <RelOp AvgRowSize="39" EstimateCPU="0.0150924" EstimateIO="1.09431" EstimateRebinds="0" EstimateRewinds="27.4747" EstimateRows="100.921" LogicalOp="Clustered Index Scan" NodeId="28" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0165881" TableCardinality="13649">
                                      <OutputList>
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                        <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                      </OutputList>
                                      <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="Document No_" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Alias="[BACK_MOVEMENT_LINE]" Column="VIN" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[SMR_ERP_DEV_TEST]" Schema="[dbo]" Table="[TEST Database$Posted Vehicle Doc_ Line]" Index="[TEST Database$Posted Vehicle Doc_ Line$0]" Alias="[BACK_MOVEMENT_LINE]" TableReferenceId="1" IndexKind="Clustered" />
                                      </IndexScan>
                                    </RelOp>
                                  </ComputeScalar>
                                </RelOp>
                              </Hash>
                            </RelOp>
                          </Concat>
                        </RelOp>
                      </Top>
                    </RelOp>
                  </RowCountSpool>
                </RelOp>
              </NestedLoops>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterCompiledValue="N'DEALER'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
26 мар 12, 15:51    [12315074]     Ответить | Цитировать Сообщить модератору
 Re: Вьюха + where = жуткие тормоза. Почему?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31984
Gena928,

Сервер вам предлагает инедкс сделать:
USE [SMR_ERP_DEV_TEST]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TEST Database$Posted Vehicle Doc_ Line] ([New Location Code])
INCLUDE ([Journal Batch Name],[Line No_],[Entry Type],[Document No_],[Posting Date],[VIN],[No_],[Location Code],[DI No_],[PI No_])
26 мар 12, 16:30    [12315529]     Ответить | Цитировать Сообщить модератору
 Re: Вьюха + where = жуткие тормоза. Почему?  [new]
komrad
Member

Откуда:
Сообщений: 5759
alexeyvg
Gena928,

Сервер вам предлагает инедкс сделать:
USE [SMR_ERP_DEV_TEST]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TEST Database$Posted Vehicle Doc_ Line] ([New Location Code])
INCLUDE ([Journal Batch Name],[Line No_],[Entry Type],[Document No_],[Posting Date],[VIN],[No_],[Location Code],[DI No_],[PI No_])


или попробовать добавить в конец запроса
option (LOOP JOIN)
26 мар 12, 18:00    [12316371]     Ответить | Цитировать Сообщить модератору
 Re: Вьюха + where = жуткие тормоза. Почему?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Зачем вы сравниваете с NVARCHAR если в таблице у вас явно VARCHAR?
CONVERT_IMPLICIT(nvarchar(20),[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Header].[Appl_ To Post_ Transf_ Act No_] as [BACK_MOVEMENT_HEADER].[Appl_ To Post_ Transf_ Act No_],0)<>N''
CONVERT_IMPLICIT(nvarchar(10),[SMR_ERP_DEV_TEST].[dbo].[TEST Database$Posted Vehicle Doc_ Line].[New Location Code],0)=N'DEALER'


Еще попробуйте обновить статистику, хуже не будет.
И планы, если в XML, то надо выкладывать в виде файлов, тогда больше шансов что на них вообще посмотрят.
26 мар 12, 20:44    [12317260]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить