SQL语句优化,SQL语句性能优化,改写求助

以下是一段sql语句,效率非常低, 请求帮忙优化一下

 SELECT *
FROM bas_Achieve a
    LEFT JOIN bas_PlayerAchieve b ON a.A_ID = b.PA_Achieve
WHERE PA_Player = @Player
    AND CAST(A_ID AS varchar(max)) + A_Value1 IN (SELECT CAST(A_ID AS varchar(max)) + MIN(A_Value1)
        FROM bas_Achieve a, bas_PlayerAchieve b
        WHERE a.A_ID = b.PA_Achieve
            AND PA_Player = @Player
            AND a.A_Value1 > b.PA_Value
        GROUP BY A_ID)
    OR CAST(A_ID AS varchar(max)) + A_Value1 IN (SELECT CAST(A_ID AS varchar(max)) + MAX(A_Value1)
        FROM bas_Achieve a, bas_PlayerAchieve b
        WHERE a.A_ID = b.PA_Achieve
            AND PA_Player = @Player
            AND a.A_Value1 < b.PA_Value
        GROUP BY A_ID)
ORDER BY A_ID

执行计划
耗时1
耗时2

不好意思..
以下是执行计划xml

 <?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.1600.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="8323.15" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="47.6064" StatementText="select  [A_ID],[A_Name],[A_Info],[A_Condition],[A_Value1],[A_Reward],[A_Value2],[A_Rewardinfo],[PA_Player]&#xD;&#xA;,[PA_Achieve],[PA_Value],[PA_Flag1],[PA_Flag2] from bas_Achieve a left join bas_PlayerAchieve b on a.A_ID = b.PA_Achieve where PA_Player ='o_EJvxL2OGpqFwvPI_0tkcN-UDZc' &#xD;&#xA;and cast(A_ID as varchar(max))+A_Value1 in (select cast(A_ID as varchar(10))+min(A_Value1) from bas_Achieve a,bas_PlayerAchieve b where a.A_ID = b.PA_Achieve and PA_Player ='o_EJvxL2OGpqFwvPI_0tkcN-UDZc' and a.A_Value1 &gt; b.PA_Value group by A_ID) &#xD;&#xA;  or cast(A_ID as varchar(max))+A_Value1 in (select cast(A_ID as varchar(10))+max(A_Value1) from bas_Achieve a,bas_PlayerAchieve b where a.A_ID = b.PA_Achieve and PA_Player ='o_EJvxL2OGpqFwvPI_0tkcN-UDZc' and a.A_Value1 &lt; b.PA_Value group by A_ID) &#xD;&#xA;order by A_ID&#xD;&#xA;" StatementType="SELECT" QueryHash="0xDC6E1845D70D13F6" QueryPlanHash="0xC931AC2CD61C4881">
          <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="80" CompileTime="20" CompileCPU="20" CompileMemory="1024">
            <RelOp AvgRowSize="918" EstimateCPU="0.0386564" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8323.15" LogicalOp="Left Semi Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="47.6064">
              <OutputList>
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Name" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Info" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Condition" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Reward" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value2" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Rewardinfo" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Player" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag1" />
                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag2" />
              </OutputList>
              <NestedLoops Optimized="false">
                <OuterReferences>
                  <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                  <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                  <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Player" />
                </OuterReferences>
                <RelOp AvgRowSize="918" EstimateCPU="0.0291642" EstimateIO="0.01878" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9247.95" LogicalOp="Right Outer Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.211871">
                  <OutputList>
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Name" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Info" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Condition" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Reward" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value2" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Rewardinfo" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Player" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag1" />
                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag2" />
                  </OutputList>
                  <Merge ManyToMany="true">
                    <InnerSideJoinColumns>
                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                    </InnerSideJoinColumns>
                    <OuterSideJoinColumns>
                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                    </OuterSideJoinColumns>
                    <Residual>
                      <ScalarOperator ScalarString="[VR_SG].[dbo].[bas_Achieve].[A_ID] as [a].[A_ID]=[VR_SG].[dbo].[bas_PlayerAchieve].[PA_Achieve] as [b].[PA_Achieve]">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                            </Identifier>
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Residual>
                    <RelOp AvgRowSize="83" EstimateCPU="0.100307" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5100" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.15972">
                      <OutputList>
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Player" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag1" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag2" />
                      </OutputList>
                      <MemoryFractions Input="1" Output="1" />
                      <Sort Distinct="false">
                        <OrderBy>
                          <OrderByColumn Ascending="true">
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                          </OrderByColumn>
                        </OrderBy>
                        <RelOp AvgRowSize="83" EstimateCPU="0.005767" EstimateIO="0.0423843" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5100" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0481513" TableCardinality="5100">
                          <OutputList>
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Player" />
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag1" />
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag2" />
                          </OutputList>
                          <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Player" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag1" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Flag2" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Index="[PK_BAS_PLAYERACHIEVE]" Alias="[b]" TableReferenceId="1" IndexKind="Clustered" />
                          </IndexScan>
                        </RelOp>
                      </Sort>
                    </RelOp>
                    <RelOp AvgRowSize="843" EstimateCPU="0.0003385" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="165" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00420424" TableCardinality="165">
                      <OutputList>
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Name" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Info" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Condition" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Reward" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value2" />
                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Rewardinfo" />
                      </OutputList>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Name" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Info" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Condition" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Reward" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value2" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Rewardinfo" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Index="[PK_BAS_ACHIEVE]" Alias="[a]" TableReferenceId="1" IndexKind="Clustered" />
                      </IndexScan>
                    </RelOp>
                  </Merge>
                </RelOp>
                <RelOp AvgRowSize="9" EstimateCPU="5.22675E-07" EstimateIO="0" EstimateRebinds="9246.65" EstimateRewinds="0.296296" EstimateRows="1" LogicalOp="Concatenation" NodeId="6" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="47.3558">
                  <OutputList />
                  <Concat>
                    <DefinedValues />
                    <RelOp AvgRowSize="9" EstimateCPU="2.14083E-05" EstimateIO="0" EstimateRebinds="9246.65" EstimateRewinds="0.296296" EstimateRows="1" LogicalOp="Filter" NodeId="7" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="47.3549">
                      <OutputList />
                      <Filter StartupExpression="false">
                        <RelOp AvgRowSize="73" EstimateCPU="1.98225E-06" EstimateIO="0" EstimateRebinds="9246.65" EstimateRewinds="0.296296" EstimateRows="10" LogicalOp="Compute Scalar" NodeId="8" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="47.255">
                          <OutputList>
                            <ColumnReference Column="Expr1008" />
                            <ColumnReference Column="Expr1016" />
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1016" />
                                <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(10),CONVERT(varchar(10),[VR_SG].[dbo].[bas_Achieve].[A_ID] as [a].[A_ID],0),0)">
                                  <Convert DataType="nvarchar" Length="20" Style="0" Implicit="true">
                                    <ScalarOperator>
                                      <Convert DataType="varchar" Length="10" Style="0" Implicit="false">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                          </Identifier>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </Convert>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp AvgRowSize="65" EstimateCPU="2.18048E-05" EstimateIO="0" EstimateRebinds="9246.65" EstimateRewinds="0.296296" EstimateRows="10" LogicalOp="Aggregate" NodeId="9" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="47.2458">
                              <OutputList>
                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                <ColumnReference Column="Expr1008" />
                              </OutputList>
                              <StreamAggregate>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1008" />
                                    <ScalarOperator ScalarString="MIN([VR_SG].[dbo].[bas_Achieve].[A_Value1] as [a].[A_Value1])">
                                      <Aggregate AggType="MIN" Distinct="false">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                          </Identifier>
                                        </ScalarOperator>
                                      </Aggregate>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <GroupBy>
                                  <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                </GroupBy>
                                <RelOp AvgRowSize="21" EstimateCPU="0.000276194" EstimateIO="0" EstimateRebinds="9246.65" EstimateRewinds="0.296296" EstimateRows="10" LogicalOp="Inner Join" NodeId="11" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="16.6965">
                                  <OutputList>
                                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                  </OutputList>
                                  <NestedLoops Optimized="false">
                                    <OuterReferences>
                                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                                    </OuterReferences>
                                    <RelOp AvgRowSize="15" EstimateCPU="0.000223" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="9246.95" EstimateRows="9.08059" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1.54743" TableCardinality="5100">
                                      <OutputList>
                                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                                      </OutputList>
                                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Index="[PK_BAS_PLAYERACHIEVE]" Alias="[b]" TableReferenceId="2" IndexKind="Clustered" />
                                        <SeekPredicates>
                                          <SeekPredicateNew>
                                            <SeekKeys>
                                              <Prefix ScanType="EQ">
                                                <RangeColumns>
                                                  <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Player" />
                                                </RangeColumns>
                                                <RangeExpressions>
                                                  <ScalarOperator ScalarString="N'o_EJvxL2OGpqFwvPI_0tkcN-UDZc'">
                                                    <Const ConstValue="N'o_EJvxL2OGpqFwvPI_0tkcN-UDZc'" />
                                                  </ScalarOperator>
                                                </RangeExpressions>
                                              </Prefix>
                                            </SeekKeys>
                                          </SeekPredicateNew>
                                        </SeekPredicates>
                                      </IndexScan>
                                    </RelOp>
                                    <RelOp AvgRowSize="21" EstimateCPU="1.76645E-06" EstimateIO="0" EstimateRebinds="158956" EstimateRewinds="0" EstimateRows="1.10125" LogicalOp="Filter" NodeId="13" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="25.5562">
                                      <OutputList>
                                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                      </OutputList>
                                      <Filter StartupExpression="true">
                                        <RelOp AvgRowSize="21" EstimateCPU="0.000158983" EstimateIO="0.003125" EstimateRebinds="158956" EstimateRewinds="0" EstimateRows="1.10125" LogicalOp="Clustered Index Seek" NodeId="14" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="25.2754" TableCardinality="165">
                                          <OutputList>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                          </OutputList>
                                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                              </DefinedValue>
                                              <DefinedValue>
                                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                              </DefinedValue>
                                            </DefinedValues>
                                            <Object Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Index="[PK_BAS_ACHIEVE]" Alias="[a]" TableReferenceId="2" IndexKind="Clustered" />
                                            <SeekPredicates>
                                              <SeekPredicateNew>
                                                <SeekKeys>
                                                  <Prefix ScanType="EQ">
                                                    <RangeColumns>
                                                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                                    </RangeColumns>
                                                    <RangeExpressions>
                                                      <ScalarOperator ScalarString="[VR_SG].[dbo].[bas_PlayerAchieve].[PA_Achieve] as [b].[PA_Achieve]">
                                                        <Identifier>
                                                          <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </RangeExpressions>
                                                  </Prefix>
                                                </SeekKeys>
                                              </SeekPredicateNew>
                                            </SeekPredicates>
                                            <Predicate>
                                              <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[VR_SG].[dbo].[bas_Achieve].[A_Value1] as [a].[A_Value1],0)&gt;[VR_SG].[dbo].[bas_PlayerAchieve].[PA_Value] as [b].[PA_Value]">
                                                <Compare CompareOp="GT">
                                                  <ScalarOperator>
                                                    <Convert DataType="int" Style="0" Implicit="true">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                                        </Identifier>
                                                      </ScalarOperator>
                                                    </Convert>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                                                    </Identifier>
                                                  </ScalarOperator>
                                                </Compare>
                                              </ScalarOperator>
                                            </Predicate>
                                          </IndexScan>
                                        </RelOp>
                                        <Predicate>
                                          <ScalarOperator ScalarString="[VR_SG].[dbo].[bas_PlayerAchieve].[PA_Player] as [b].[PA_Player]=N'o_EJvxL2OGpqFwvPI_0tkcN-UDZc'">
                                            <Compare CompareOp="EQ">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Player" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Const ConstValue="N'o_EJvxL2OGpqFwvPI_0tkcN-UDZc'" />
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </Predicate>
                                      </Filter>
                                    </RelOp>
                                  </NestedLoops>
                                </RelOp>
                              </StreamAggregate>
                            </RelOp>
                          </ComputeScalar>
                        </RelOp>
                        <Predicate>
                          <ScalarOperator ScalarString="(CONVERT_IMPLICIT(nvarchar(max),CONVERT(varchar(max),[VR_SG].[dbo].[bas_Achieve].[A_ID] as [a].[A_ID],0),0)+[VR_SG].[dbo].[bas_Achieve].[A_Value1] as [a].[A_Value1])=([Expr1016]+[Expr1008])">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Arithmetic Operation="ADD">
                                  <ScalarOperator>
                                    <Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="true">
                                      <ScalarOperator>
                                        <Convert DataType="varchar(max)" Length="2147483647" Style="0" Implicit="false">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Convert>
                                      </ScalarOperator>
                                    </Convert>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Arithmetic>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Arithmetic Operation="ADD">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1016" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1008" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Arithmetic>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </Filter>
                    </RelOp>
                    <RelOp AvgRowSize="9" EstimateCPU="3.50406E-05" EstimateIO="0" EstimateRebinds="9246.65" EstimateRewinds="0.296296" EstimateRows="1" LogicalOp="Filter" NodeId="24" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="47.6064">
                      <OutputList />
                      <Filter StartupExpression="false">
                        <RelOp AvgRowSize="73" EstimateCPU="3.2445E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="9246.95" EstimateRows="10" LogicalOp="Compute Scalar" NodeId="25" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="47.6064">
                          <OutputList>
                            <ColumnReference Column="Expr1014" />
                            <ColumnReference Column="Expr1018" />
                          </OutputList>
                          <ComputeScalar>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1018" />
                                <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(10),CONVERT(varchar(10),[VR_SG].[dbo].[bas_Achieve].[A_ID] as [a].[A_ID],0),0)">
                                  <Convert DataType="nvarchar" Length="20" Style="0" Implicit="true">
                                    <ScalarOperator>
                                      <Convert DataType="varchar" Length="10" Style="0" Implicit="false">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                          </Identifier>
                                        </ScalarOperator>
                                      </Convert>
                                    </ScalarOperator>
                                  </Convert>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <RelOp AvgRowSize="65" EstimateCPU="3.56895E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="9246.95" EstimateRows="10" LogicalOp="Aggregate" NodeId="26" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="47.6064">
                              <OutputList>
                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                <ColumnReference Column="Expr1014" />
                              </OutputList>
                              <StreamAggregate>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1014" />
                                    <ScalarOperator ScalarString="MAX([VR_SG].[dbo].[bas_Achieve].[A_Value1] as [a].[A_Value1])">
                                      <Aggregate AggType="MAX" Distinct="false">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                          </Identifier>
                                        </ScalarOperator>
                                      </Aggregate>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <GroupBy>
                                  <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                </GroupBy>
                                <RelOp AvgRowSize="21" EstimateCPU="0.0002508" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="9246.95" EstimateRows="10" LogicalOp="Inner Join" NodeId="27" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="47.6064">
                                  <OutputList>
                                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                  </OutputList>
                                  <NestedLoops Optimized="false">
                                    <OuterReferences>
                                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                                    </OuterReferences>
                                    <RelOp AvgRowSize="15" EstimateCPU="0.000223" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="9246.95" EstimateRows="18.4928" LogicalOp="Clustered Index Seek" NodeId="28" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1.64318" TableCardinality="5100">
                                      <OutputList>
                                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                                      </OutputList>
                                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                        <DefinedValues>
                                          <DefinedValue>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                                          </DefinedValue>
                                          <DefinedValue>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                                          </DefinedValue>
                                        </DefinedValues>
                                        <Object Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Index="[PK_BAS_PLAYERACHIEVE]" Alias="[b]" TableReferenceId="3" IndexKind="Clustered" />
                                        <SeekPredicates>
                                          <SeekPredicateNew>
                                            <SeekKeys>
                                              <Prefix ScanType="EQ">
                                                <RangeColumns>
                                                  <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Player" />
                                                </RangeColumns>
                                                <RangeExpressions>
                                                  <ScalarOperator ScalarString="N'o_EJvxL2OGpqFwvPI_0tkcN-UDZc'">
                                                    <Const ConstValue="N'o_EJvxL2OGpqFwvPI_0tkcN-UDZc'" />
                                                  </ScalarOperator>
                                                </RangeExpressions>
                                              </Prefix>
                                            </SeekKeys>
                                          </SeekPredicateNew>
                                        </SeekPredicates>
                                      </IndexScan>
                                    </RelOp>
                                    <RelOp AvgRowSize="21" EstimateCPU="0.0004231" EstimateIO="0.013125" EstimateRebinds="260640" EstimateRewinds="0" EstimateRows="1" LogicalOp="Eager Spool" NodeId="29" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="47.6064">
                                      <OutputList>
                                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                      </OutputList>
                                      <Spool>
                                        <SeekPredicateNew>
                                          <SeekKeys>
                                            <Prefix ScanType="EQ">
                                              <RangeColumns>
                                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                              </RangeColumns>
                                              <RangeExpressions>
                                                <ScalarOperator ScalarString="[VR_SG].[dbo].[bas_PlayerAchieve].[PA_Achieve] as [b].[PA_Achieve]">
                                                  <Identifier>
                                                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Achieve" />
                                                  </Identifier>
                                                </ScalarOperator>
                                              </RangeExpressions>
                                            </Prefix>
                                            <EndRange ScanType="LT">
                                              <RangeColumns>
                                                <ColumnReference Column="Expr1019" />
                                              </RangeColumns>
                                              <RangeExpressions>
                                                <ScalarOperator ScalarString="[VR_SG].[dbo].[bas_PlayerAchieve].[PA_Value] as [b].[PA_Value]">
                                                  <Identifier>
                                                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_PlayerAchieve]" Alias="[b]" Column="PA_Value" />
                                                  </Identifier>
                                                </ScalarOperator>
                                              </RangeExpressions>
                                            </EndRange>
                                          </SeekKeys>
                                        </SeekPredicateNew>
                                        <RelOp AvgRowSize="25" EstimateCPU="1.65E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="165" LogicalOp="Compute Scalar" NodeId="30" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00422074">
                                          <OutputList>
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                            <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                            <ColumnReference Column="Expr1019" />
                                          </OutputList>
                                          <ComputeScalar>
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Column="Expr1019" />
                                                <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[VR_SG].[dbo].[bas_Achieve].[A_Value1] as [a].[A_Value1],0)">
                                                  <Convert DataType="int" Style="0" Implicit="true">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                  </Convert>
                                                </ScalarOperator>
                                              </DefinedValue>
                                            </DefinedValues>
                                            <RelOp AvgRowSize="21" EstimateCPU="0.0003385" EstimateIO="0.00386574" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="165" LogicalOp="Clustered Index Scan" NodeId="31" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00420424" TableCardinality="165">
                                              <OutputList>
                                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                                <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                              </OutputList>
                                              <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
                                                <DefinedValues>
                                                  <DefinedValue>
                                                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                                  </DefinedValue>
                                                  <DefinedValue>
                                                    <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                                  </DefinedValue>
                                                </DefinedValues>
                                                <Object Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Index="[PK_BAS_ACHIEVE]" Alias="[a]" TableReferenceId="3" IndexKind="Clustered" />
                                              </IndexScan>
                                            </RelOp>
                                          </ComputeScalar>
                                        </RelOp>
                                      </Spool>
                                    </RelOp>
                                  </NestedLoops>
                                </RelOp>
                              </StreamAggregate>
                            </RelOp>
                          </ComputeScalar>
                        </RelOp>
                        <Predicate>
                          <ScalarOperator ScalarString="(CONVERT_IMPLICIT(nvarchar(max),CONVERT(varchar(max),[VR_SG].[dbo].[bas_Achieve].[A_ID] as [a].[A_ID],0),0)+[VR_SG].[dbo].[bas_Achieve].[A_Value1] as [a].[A_Value1])=([Expr1018]+[Expr1014])">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Arithmetic Operation="ADD">
                                  <ScalarOperator>
                                    <Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="true">
                                      <ScalarOperator>
                                        <Convert DataType="varchar(max)" Length="2147483647" Style="0" Implicit="false">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_ID" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Convert>
                                      </ScalarOperator>
                                    </Convert>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[VR_SG]" Schema="[dbo]" Table="[bas_Achieve]" Alias="[a]" Column="A_Value1" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Arithmetic>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Arithmetic Operation="ADD">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1018" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1014" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Arithmetic>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </Filter>
                    </RelOp>
                  </Concat>
                </RelOp>
              </NestedLoops>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

3个回答

先看看执行计划,然后再看主要耗时操作是什么,然后再对应的优化

Aet_yujun
Aet_Leng 回复刘望天: 全改了感觉还是那样... 觉得主要问题不在前面 是后面那两个and的条件耗时太高了.. 然而我并没有办法优化它
大约 2 年之前 回复
weixin_37447225
weixin_37447225 select 后面不能用*吧,这是最耗时的写法了
大约 2 年之前 回复
Aet_yujun
Aet_Leng ...要不要我贴出执行计划? 我看不懂这个....
大约 2 年之前 回复

贴下执行计划呀 老哥 不然咋优化

Aet_yujun
Aet_Leng 大爷,您要的执行计划是截图那个么 还是 xml那个... 因为确实不太懂这个 我都贴出来了
大约 2 年之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!