【SQL】查询性能奇怪问题求教

Question

一个普通的查询语句，连接了几张表，查询条件是单据编号。整个就是SELECT X FROM X WHERE X的句式。

现在的问题是不同的单据查询时间相差巨大，例如查A单据瞬间，B单据就要接近3分钟，排除单据明细数据量原因，因为A单据有明细有10行，而B只有1行。

请看两次查询的跟踪情况：

上面是B的查询情况，下方是A的，可见查询时长相差之大，同时Reads和CPU消耗差异也是巨大的，而两次查询语句唯一的不同就是单据编号。我目前在SQL性能优化这块还很菜，望大侠帮忙分析。

需要更多信息请提出，我跟帖说明。

Answer 1

而且两次查询的执行计划也有明显不同，只是真心看不懂问题到底在哪？

Answer 2

你是用参数的是吧，试一下直接在SSMS里运行一下，然后把下面的信息详细发上来，

把SQL 语句也发上来，还有把下面的结果信息发上来，不过很少有人能发全信息，不知你是否可以。 

把下面的语句运行一下，然后把Rows Executes StmtText 。。。。等为列头的结果表（不要漏了表头，直接拷贝好了（包含表头），格式乱没有关系）跟消息那个TAB下来的内容发上来。
 1,set statistics profile on
   go
   set statistics io on
   go
set statistics time on
   go

     --  insert into  快的时候的T-SQL

  set statistics time off
   go
set statistics io off
   go
set statistics profile off
go

-----------------------------------------------

 2,set statistics profile on
   go
   set statistics io on
   go
set statistics time on
   go

     --      --  insert into  慢的时候的T-SQL

  set statistics time off
   go
set statistics io off
   go
set statistics profile off
go

---

Please click the Mark as Answer button if a post solves your problem!

Answer 3

尝试过不连接其中一个比较大的表后，无论查A还是B都是秒查，所以我想重建对这个表的所有索引，但是重建时报错如下：

说明这张表现在一定存在什么问题，必须解决，不能无视它，而仅仅去从查询语句上动手脚

Answer 4

什么原因会导致同样的语句（仅仅是条件不同），产生截然不同的执行步骤呢？

PS:如果需要AB的执行计划，我可以提供

Answer 5

回Jackie，不是参数，单号是直接写在where中的。需要说明，这查询语句是硬编码到ERP软件中的，我无法修改，所以只能从语句以外的地方着手优化。

首先这是查询语句：

SELECT  TBusPurchase.MasterId ,
           TBusPurchase.BillDate ,
           TBusPurchase.Store ,
           TBusPurchase.PurchaseType ,
           TBusPurchase.Opr ,
           TBusPurchase.OpDate ,
           TBusPurchase.Checker ,
           TBusPurchase.CheckDate ,
           TBusPurchase.Accepter ,
           TBusPurchase.AcceptDate ,
           TBusPurchase.Charger ,
           TBusPurchase.ChargeDate ,
           TBusPurchase.Checked ,
           TBusPurchase.Accepted ,
           TBusPurchase.Charged ,
           TBusPurchase.Abolished ,
           TBusPurchase.Remark ,
           TBusPurchase.dateout ,
           CASE TBusPurchase.Accepted WHEN 1 THEN TBusPurchase.datein ELSE '' END AS datein,
           TBusPurchaseDt.Sku ,
           TDefSku.Style ,
           '['+TDefSku.Clr+']'+tdefclr.clrname as clr,
 			  '['+TDefSku.Size+']'+tdefsize.sizename as size,
           TBusPurchaseDt.Qty ,
           TDefStyle.StyleName ,
           TBusPurchase.ProvideId ,
           TBusPurchaseDt.PreQty ,
           TBusPurchaseDt.FPrice ,
           TBusPurchaseDt.PreFAmount ,
           TBusPurchaseDt.FAmount,
           taccstyleprice.dprice    
        FROM TBusPurchase ,
           TBusPurchaseDt ,
           TDefSku ,
           TDefStyle,
           taccstyleprice,
            tdefstore ,
				tdefsize,
				tdefclr     
        WHERE ( TBusPurchaseDt.MasterId = TBusPurchase.MasterId ) and          ( TBusPurchaseDt.Sku = TDefSku.Sku ) and          ( TDefStyle.Style = TDefSku.Style ) and          ( ( TBusPurchase.MasterId = 'BYNKM02000007366' ) )  and (taccstyleprice.style=tdefstyle.style)
             and  (tdefstore.store=TBusPurchase.Store) and (tdefstore.buyerid=taccstyleprice.buyerid)

Answer 6

然后这是快的执行计划：

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
10 1 SELECT  TBusPurchase.MasterId ,
           TBusPurchase.BillDate ,
           TBusPurchase.Store ,
           TBusPurchase.PurchaseType ,
           TBusPurchase.Opr ,
           TBusPurchase.OpDate ,
           TBusPurchase.Checker ,
           TBusPurchase.CheckDate ,
           TBusPurchase.Accepter ,
           TBusPurchase.AcceptDate ,
           TBusPurchase.Charger ,
           TBusPurchase.ChargeDate ,
           TBusPurchase.Checked ,
           TBusPurchase.Accepted ,
           TBusPurchase.Charged ,
           TBusPurchase.Abolished ,
           TBusPurchase.Remark ,
           TBusPurchase.dateout ,
           CASE TBusPurchase.Accepted WHEN 1 THEN TBusPurchase.datein ELSE '' END AS datein,
           TBusPurchaseDt.Sku ,
           TDefSku.Style ,
           '['+TDefSku.Clr+']'+tdefclr.clrname as clr,
   '['+TDefSku.Size+']'+tdefsize.sizename as size,
           TBusPurchaseDt.Qty ,
           TDefStyle.StyleName ,
           TBusPurchase.ProvideId ,
           TBusPurchaseDt.PreQty ,
           TBusPurchaseDt.FPrice ,
           TBusPurchaseDt.PreFAmount ,
           TBusPurchaseDt.FAmount,
           taccstyleprice.dprice    
        FROM TBusPurchase ,
           TBusPurchaseDt ,
           TDefSku ,
           TDefStyle,
           taccstyleprice,
            tdefstore ,
tdefsize,
tdefclr     
        WHERE ( TBusPurchaseDt.MasterId = TBusPurchase.MasterId ) and          ( TBusPurchaseDt.Sku = TDefSku.Sku ) and          ( TDefStyle.Style = TDefSku.Style ) and          ( ( TBusPurchase.MasterId = 'BYNKM02000007366' ) )  and (taccstyleprice.style=tdefstyle.style)
             and  (tdefstore.store=TBusPurchase.Store) and (tdefstore.buyerid=taccstyleprice.buyerid)
and tdefsize.size = tdefsku.size and tdefclr.clr = tdefsku.clr
--- 1 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 1.910099 NULL NULL SELECT 0 NULL
0 0  |--Compute Scalar(DEFINE:([Expr1025]=[Expr1031]+[newemax].[dbo].[TDefClr].[ClrName], [Expr1026]=[Expr1032]+[newemax].[dbo].[TDefSize].[SizeName])) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1025]=[Expr1031]+[newemax].[dbo].[TDefClr].[ClrName], [Expr1026]=[Expr1032]+[newemax].[dbo].[TDefSize].[SizeName]) [Expr1025]=[Expr1031]+[newemax].[dbo].[TDefClr].[ClrName], [Expr1026]=[Expr1032]+[newemax].[dbo].[TDefSize].[SizeName] 1 0 1E-07 781 1.910099 [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[Charger], [newemax].[dbo].[TBusPurchase].[Charged], [newemax].[dbo].[TBusPurchase].[Chargedate], [newemax].[dbo].[TBusPurchase].[DateOut], [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [Expr1024], [Expr1025], [Expr1026] NULL PLAN_ROW 0 1
10 1       |--Nested Loops(Inner Join, WHERE:([newemax].[dbo].[TDefStore].[Store]=[newemax].[dbo].[TBusPurchase].[Store])) 1 3 2 Nested Loops Inner Join WHERE:([newemax].[dbo].[TDefStore].[Store]=[newemax].[dbo].[TBusPurchase].[Store]) NULL 1 0 4.18E-06 786 1.910099 [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[Charger], [newemax].[dbo].[TBusPurchase].[Charged], [newemax].[dbo].[TBusPurchase].[Chargedate], [newemax].[dbo].[TBusPurchase].[DateOut], [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefSize].[SizeName], [newemax].[dbo].[TDefClr].[ClrName], [Expr1024], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
0 0            |--Compute Scalar(DEFINE:([newemax].[dbo].[TBusPurchase].[Charger]=[newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[Charged]=[newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Chargedate]=[newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[DateOut]=[newemax].[dbo].[TBusPurchase].[DateIn], [Expr1024]=CASE WHEN [newemax].[dbo].[TBusPurchase].[Accepted]=(1) THEN [newemax].[dbo].[TBusPurchase].[DateIn] ELSE '          ' END)) 1 4 3 Compute Scalar Compute Scalar DEFINE:([newemax].[dbo].[TBusPurchase].[Charger]=[newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[Charged]=[newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Chargedate]=[newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[DateOut]=[newemax].[dbo].[TBusPurchase].[DateIn], [Expr1024]=CASE WHEN [newemax].[dbo].[TBusPurchase].[Accepted]=(1) THEN [newemax].[dbo].[TBusPurchase].[DateIn] ELSE '          ' END) [newemax].[dbo].[TBusPurchase].[Charger]=[newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[Charged]=[newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Chargedate]=[newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[DateOut]=[newemax].[dbo].[TBusPurchase].[DateIn], [Expr1024]=CASE WHEN [newemax].[dbo].[TBusPurchase].[Accepted]=(1) THEN [newemax].[dbo].[TBusPurchase].[DateIn] ELSE '          ' END 1 0 1E-07 296 0.0032832 [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[Charger], [newemax].[dbo].[TBusPurchase].[Charged], [newemax].[dbo].[TBusPurchase].[Chargedate], [newemax].[dbo].[TBusPurchase].[DateOut], [Expr1024] NULL PLAN_ROW 0 1
1 1            |    |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TBusPurchase].[PK_TBUSPURCHASE]), SEEK:([newemax].[dbo].[TBusPurchase].[MasterId]='BYNKM02000007366') ORDERED FORWARD) 1 5 4 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TBusPurchase].[PK_TBUSPURCHASE]), SEEK:([newemax].[dbo].[TBusPurchase].[MasterId]='BYNKM02000007366') ORDERED FORWARD [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[DateIn] 1 0.003125 0.0001581 264 0.0032831 [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[DateIn] NULL PLAN_ROW 0 1
1350 1            |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Size])) 1 17 3 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Size]) NULL 1 0 4.18E-06 504 1.906811 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [newemax].[dbo].[TDefSize].[SizeName], [newemax].[dbo].[TDefClr].[ClrName], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
1350 1                 |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Clr])) 1 18 17 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Clr]) NULL 1 0 4.18E-06 379 1.903524 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [newemax].[dbo].[TDefClr].[ClrName], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
1350 1                 |    |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[Style])) 1 19 18 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[Style]) NULL 1 0 4.18E-06 255 1.900236 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
1350 1                 |    |    |--Hash Match(Inner Join, HASH:([newemax].[dbo].[TDefStore].[BuyerId])=([newemax].[dbo].[TAccStylePrice].[BuyerId]), RESIDUAL:([newemax].[dbo].[TDefStore].[BuyerId]=[newemax].[dbo].[TAccStylePrice].[BuyerId])) 1 20 19 Hash Match Inner Join HASH:([newemax].[dbo].[TDefStore].[BuyerId])=([newemax].[dbo].[TAccStylePrice].[BuyerId]), RESIDUAL:([newemax].[dbo].[TDefStore].[BuyerId]=[newemax].[dbo].[TAccStylePrice].[BuyerId]) NULL 1 0 0.02498998 135 1.896949 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TAccStylePrice].[Style], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
135 1                 |    |    |    |--Index Scan(OBJECT:([newemax].[dbo].[TDefStore].[IdxBuyer])) 1 21 20 Index Scan Index Scan OBJECT:([newemax].[dbo].[TDefStore].[IdxBuyer]) [newemax].[dbo].[TDefStore].[Store], [newemax].[dbo].[TDefStore].[BuyerId] 135 0.003125 0.0003055 19 0.0034305 [newemax].[dbo].[TDefStore].[Store], [newemax].[dbo].[TDefStore].[BuyerId] NULL PLAN_ROW 0 1
550 1                 |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[FlowNo], [Expr1036]) OPTIMIZED WITH UNORDERED PREFETCH) 1 22 20 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[FlowNo], [Expr1036]) OPTIMIZED WITH UNORDERED PREFETCH NULL 542.5072 0 0.00226768 136 1.868526 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TAccStylePrice].[Style], [newemax].[dbo].[TAccStylePrice].[BuyerId], [newemax].[dbo].[TAccStylePrice].[DPrice], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
550 1                 |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Style]) OPTIMIZED) 1 25 22 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Style]) OPTIMIZED NULL 542.5072 0 0.00226768 129 0.0994013 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TAccStylePrice].[FlowNo], [newemax].[dbo].[TAccStylePrice].[Style], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
0 0                 |    |    |         |    |--Compute Scalar(DEFINE:([Expr1031]=('['+[newemax].[dbo].[TDefSku].[Clr])+']', [Expr1032]=('['+[newemax].[dbo].[TDefSku].[Size])+']')) 1 27 25 Compute Scalar Compute Scalar DEFINE:([Expr1031]=('['+[newemax].[dbo].[TDefSku].[Clr])+']', [Expr1032]=('['+[newemax].[dbo].[TDefSku].[Size])+']') [Expr1031]=('['+[newemax].[dbo].[TDefSku].[Clr])+']', [Expr1032]=('['+[newemax].[dbo].[TDefSku].[Size])+']' 10.43005 0 1.043005E-06 111 0.06546407 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
10 1                 |    |    |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TBusPurchaseDt].[Sku])) 1 28 27 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TBusPurchaseDt].[Sku]) NULL 10.43005 0 4.359762E-05 85 0.06546303 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size] NULL PLAN_ROW 0 1
0 0                 |    |    |         |    |         |--Compute Scalar(DEFINE:([newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FAmount])) 1 29 28 Compute Scalar Compute Scalar DEFINE:([newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FAmount]) [newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FAmount] 10.43005 0 1.043005E-06 66 0.0343867 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount] NULL PLAN_ROW 0 1
10 1                 |    |    |         |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TBusPurchaseDt].[DetailId])) 1 30 29 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TBusPurchaseDt].[DetailId]) NULL 10.43005 0 4.359762E-05 66 0.03438566 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount] NULL PLAN_ROW 0 1
10 1                 |    |    |         |    |         |         |--Index Seek(OBJECT:([newemax].[dbo].[TBusPurchaseDt].[IdxMasterIdSku]), SEEK:([newemax].[dbo].[TBusPurchaseDt].[MasterId]='BYNKM02000007366') ORDERED FORWARD) 1 31 30 Index Seek Index Seek OBJECT:([newemax].[dbo].[TBusPurchaseDt].[IdxMasterIdSku]), SEEK:([newemax].[dbo].[TBusPurchaseDt].[MasterId]='BYNKM02000007366') ORDERED FORWARD [newemax].[dbo].[TBusPurchaseDt].[DetailId], [newemax].[dbo].[TBusPurchaseDt].[Sku] 10.43005 0.003125 0.0001684731 32 0.003293473 [newemax].[dbo].[TBusPurchaseDt].[DetailId], [newemax].[dbo].[TBusPurchaseDt].[Sku] NULL PLAN_ROW 0 1
0 0                 |    |    |         |    |         |         |--Compute Scalar(DEFINE:([newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[PreQty],0), [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[Qty],0))) 1 33 30 Compute Scalar Compute Scalar DEFINE:([newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[PreQty],0), [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[Qty],0)) [newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[PreQty],0), [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[Qty],0) 1 0 1E-07 50 0.03104859 [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice] NULL PLAN_ROW 0 10.43005
10 10                 |    |    |         |    |         |              |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TBusPurchaseDt].[PK_TBUSPURCHASEDT]), SEEK:([newemax].[dbo].[TBusPurchaseDt].[DetailId]=[newemax].[dbo].[TBusPurchaseDt].[DetailId]) LOOKUP ORDERED FORWARD) 1 34 33 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TBusPurchaseDt].[PK_TBUSPURCHASEDT]), SEEK:([newemax].[dbo].[TBusPurchaseDt].[DetailId]=[newemax].[dbo].[TBusPurchaseDt].[DetailId]) LOOKUP ORDERED FORWARD [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice] 1 0.003125 0.0001581 24 0.03104755 [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice] NULL PLAN_ROW 0 10.43005
10 10                 |    |    |         |    |         |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TDefSku].[PK_TDEFSKU]), SEEK:([newemax].[dbo].[TDefSku].[Sku]=[newemax].[dbo].[TBusPurchaseDt].[Sku]) ORDERED FORWARD) 1 52 28 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TDefSku].[PK_TDEFSKU]), SEEK:([newemax].[dbo].[TDefSku].[Sku]=[newemax].[dbo].[TBusPurchaseDt].[Sku]) ORDERED FORWARD [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size] 1 0.003125 0.0001581 27 0.03103273 [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size] NULL PLAN_ROW 0 10.43005
550 10                 |    |    |         |    |--Index Seek(OBJECT:([newemax].[dbo].[TAccStylePrice].[IdxStyle]), SEEK:([newemax].[dbo].[TAccStylePrice].[Style]=[newemax].[dbo].[TDefSku].[Style]) ORDERED FORWARD) 1 58 25 Index Seek Index Seek OBJECT:([newemax].[dbo].[TAccStylePrice].[IdxStyle]), SEEK:([newemax].[dbo].[TAccStylePrice].[Style]=[newemax].[dbo].[TDefSku].[Style]) ORDERED FORWARD [newemax].[dbo].[TAccStylePrice].[FlowNo], [newemax].[dbo].[TAccStylePrice].[Style] 52.01385 0.003125 0.0002142152 27 0.03166955 [newemax].[dbo].[TAccStylePrice].[FlowNo], [newemax].[dbo].[TAccStylePrice].[Style] NULL PLAN_ROW 0 10.43005
550 550                 |    |    |         |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TAccStylePrice].[PK_TACCSTYLEPRICE]), SEEK:([newemax].[dbo].[TAccStylePrice].[FlowNo]=[newemax].[dbo].[TAccStylePrice].[FlowNo]) LOOKUP ORDERED FORWARD) 1 60 22 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TAccStylePrice].[PK_TACCSTYLEPRICE]), SEEK:([newemax].[dbo].[TAccStylePrice].[FlowNo]=[newemax].[dbo].[TAccStylePrice].[FlowNo]) LOOKUP ORDERED FORWARD [newemax].[dbo].[TAccStylePrice].[BuyerId], [newemax].[dbo].[TAccStylePrice].[DPrice] 1 0.003125 0.0001581 30 1.766857 [newemax].[dbo].[TAccStylePrice].[BuyerId], [newemax].[dbo].[TAccStylePrice].[DPrice] NULL PLAN_ROW 0 542.5072
1350 1350                 |    |    |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TDefStyle].[PK_TDEFSTYLE]), SEEK:([newemax].[dbo].[TDefStyle].[Style]=[newemax].[dbo].[TAccStylePrice].[Style]) ORDERED FORWARD) 1 67 19 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TDefStyle].[PK_TDEFSTYLE]), SEEK:([newemax].[dbo].[TDefStyle].[Style]=[newemax].[dbo].[TAccStylePrice].[Style]) ORDERED FORWARD [newemax].[dbo].[TDefStyle].[StyleName] 1 0.003125 0.0001581 138 0.0032831 [newemax].[dbo].[TDefStyle].[StyleName] NULL PLAN_ROW 0 1
1350 1350                 |    |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TDefClr].[PK_TDEFCLR]), SEEK:([newemax].[dbo].[TDefClr].[Clr]=[newemax].[dbo].[TDefSku].[Clr]) ORDERED FORWARD) 1 68 18 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TDefClr].[PK_TDEFCLR]), SEEK:([newemax].[dbo].[TDefClr].[Clr]=[newemax].[dbo].[TDefSku].[Clr]) ORDERED FORWARD [newemax].[dbo].[TDefClr].[ClrName] 1 0.003125 0.0001581 138 0.0032831 [newemax].[dbo].[TDefClr].[ClrName] NULL PLAN_ROW 0 1
1350 1350                 |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TDefSize].[PK_TDEFSIZE]), SEEK:([newemax].[dbo].[TDefSize].[Size]=[newemax].[dbo].[TDefSku].[Size]) ORDERED FORWARD) 1 69 17 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TDefSize].[PK_TDEFSIZE]), SEEK:([newemax].[dbo].[TDefSize].[Size]=[newemax].[dbo].[TDefSku].[Size]) ORDERED FORWARD [newemax].[dbo].[TDefSize].[SizeName] 1 0.003125 0.0001581 138 0.0032831 [newemax].[dbo].[TDefSize].[SizeName] NULL PLAN_ROW 0 1

Answer 7

看来我RP不错，只是应该是制表位的变成了空格，可能会给你还原结构造成麻烦。下面是慢的执行计划：

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
1 1 SELECT  TBusPurchase.MasterId ,
           TBusPurchase.BillDate ,
           TBusPurchase.Store ,
           TBusPurchase.PurchaseType ,
           TBusPurchase.Opr ,
           TBusPurchase.OpDate ,
           TBusPurchase.Checker ,
           TBusPurchase.CheckDate ,
           TBusPurchase.Accepter ,
           TBusPurchase.AcceptDate ,
           TBusPurchase.Charger ,
           TBusPurchase.ChargeDate ,
           TBusPurchase.Checked ,
           TBusPurchase.Accepted ,
           TBusPurchase.Charged ,
           TBusPurchase.Abolished ,
           TBusPurchase.Remark ,
           TBusPurchase.dateout ,
           CASE TBusPurchase.Accepted WHEN 1 THEN TBusPurchase.datein ELSE '' END AS datein,
           TBusPurchaseDt.Sku ,
           TDefSku.Style ,
           '['+TDefSku.Clr+']'+tdefclr.clrname as clr,
   '['+TDefSku.Size+']'+tdefsize.sizename as size,
           TBusPurchaseDt.Qty ,
           TDefStyle.StyleName ,
           TBusPurchase.ProvideId ,
           TBusPurchaseDt.PreQty ,
           TBusPurchaseDt.FPrice ,
           TBusPurchaseDt.PreFAmount ,
           TBusPurchaseDt.FAmount,
           taccstyleprice.dprice    
        FROM TBusPurchase ,
           TBusPurchaseDt ,
           TDefSku ,
           TDefStyle,
           taccstyleprice,
            tdefstore ,
tdefsize,
tdefclr     
        WHERE ( TBusPurchaseDt.MasterId = TBusPurchase.MasterId ) and          ( TBusPurchaseDt.Sku = TDefSku.Sku ) and          ( TDefStyle.Style = TDefSku.Style ) and          ( ( TBusPurchase.MasterId = 'BYNKM02000007703' ) )  and (taccstyleprice.style=tdefstyle.style)
             and  (tdefstore.store=TBusPurchase.Store) and (tdefstore.buyerid=taccstyleprice.buyerid)
and tdefsize.size = tdefsku.size and tdefclr.clr = tdefsku.clr
--- 1 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.05478798 NULL NULL SELECT 0 NULL
0 0  |--Compute Scalar(DEFINE:([Expr1025]=[Expr1031]+[newemax].[dbo].[TDefClr].[ClrName], [Expr1026]=[Expr1032]+[newemax].[dbo].[TDefSize].[SizeName])) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1025]=[Expr1031]+[newemax].[dbo].[TDefClr].[ClrName], [Expr1026]=[Expr1032]+[newemax].[dbo].[TDefSize].[SizeName]) [Expr1025]=[Expr1031]+[newemax].[dbo].[TDefClr].[ClrName], [Expr1026]=[Expr1032]+[newemax].[dbo].[TDefSize].[SizeName] 1 0 1E-07 781 0.05478798 [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[Charger], [newemax].[dbo].[TBusPurchase].[Charged], [newemax].[dbo].[TBusPurchase].[Chargedate], [newemax].[dbo].[TBusPurchase].[DateOut], [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [Expr1024], [Expr1025], [Expr1026] NULL PLAN_ROW 0 1
1 1       |--Nested Loops(Inner Join, WHERE:([newemax].[dbo].[TDefStore].[Store]=[newemax].[dbo].[TBusPurchase].[Store])) 1 3 2 Nested Loops Inner Join WHERE:([newemax].[dbo].[TDefStore].[Store]=[newemax].[dbo].[TBusPurchase].[Store]) NULL 1 0 4.18E-06 786 0.05478788 [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[Charger], [newemax].[dbo].[TBusPurchase].[Charged], [newemax].[dbo].[TBusPurchase].[Chargedate], [newemax].[dbo].[TBusPurchase].[DateOut], [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefSize].[SizeName], [newemax].[dbo].[TDefClr].[ClrName], [Expr1024], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
0 0            |--Compute Scalar(DEFINE:([newemax].[dbo].[TBusPurchase].[Charger]=[newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[Charged]=[newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Chargedate]=[newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[DateOut]=[newemax].[dbo].[TBusPurchase].[DateIn], [Expr1024]=CASE WHEN [newemax].[dbo].[TBusPurchase].[Accepted]=(1) THEN [newemax].[dbo].[TBusPurchase].[DateIn] ELSE '          ' END)) 1 4 3 Compute Scalar Compute Scalar DEFINE:([newemax].[dbo].[TBusPurchase].[Charger]=[newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[Charged]=[newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Chargedate]=[newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[DateOut]=[newemax].[dbo].[TBusPurchase].[DateIn], [Expr1024]=CASE WHEN [newemax].[dbo].[TBusPurchase].[Accepted]=(1) THEN [newemax].[dbo].[TBusPurchase].[DateIn] ELSE '          ' END) [newemax].[dbo].[TBusPurchase].[Charger]=[newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[Charged]=[newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Chargedate]=[newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[DateOut]=[newemax].[dbo].[TBusPurchase].[DateIn], [Expr1024]=CASE WHEN [newemax].[dbo].[TBusPurchase].[Accepted]=(1) THEN [newemax].[dbo].[TBusPurchase].[DateIn] ELSE '          ' END 1 0 1E-07 296 0.0032832 [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[Charger], [newemax].[dbo].[TBusPurchase].[Charged], [newemax].[dbo].[TBusPurchase].[Chargedate], [newemax].[dbo].[TBusPurchase].[DateOut], [Expr1024] NULL PLAN_ROW 0 1
1 1            |    |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TBusPurchase].[PK_TBUSPURCHASE]), SEEK:([newemax].[dbo].[TBusPurchase].[MasterId]='BYNKM02000007703') ORDERED FORWARD) 1 5 4 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TBusPurchase].[PK_TBUSPURCHASE]), SEEK:([newemax].[dbo].[TBusPurchase].[MasterId]='BYNKM02000007703') ORDERED FORWARD [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[DateIn] 1 0.003125 0.0001581 264 0.0032831 [newemax].[dbo].[TBusPurchase].[MasterId], [newemax].[dbo].[TBusPurchase].[BillDate], [newemax].[dbo].[TBusPurchase].[PurchaseType], [newemax].[dbo].[TBusPurchase].[Store], [newemax].[dbo].[TBusPurchase].[ProvideId], [newemax].[dbo].[TBusPurchase].[Opr], [newemax].[dbo].[TBusPurchase].[OpDate], [newemax].[dbo].[TBusPurchase].[Checker], [newemax].[dbo].[TBusPurchase].[CheckDate], [newemax].[dbo].[TBusPurchase].[Accepter], [newemax].[dbo].[TBusPurchase].[AcceptDate], [newemax].[dbo].[TBusPurchase].[Checked], [newemax].[dbo].[TBusPurchase].[Accepted], [newemax].[dbo].[TBusPurchase].[Abolished], [newemax].[dbo].[TBusPurchase].[Remark], [newemax].[dbo].[TBusPurchase].[DateIn] NULL PLAN_ROW 0 1
135 1            |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Size])) 1 17 3 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Size]) NULL 1 0 4.18E-06 504 0.0515002 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [newemax].[dbo].[TDefSize].[SizeName], [newemax].[dbo].[TDefClr].[ClrName], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
135 1                 |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Clr])) 1 18 17 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Clr]) NULL 1 0 4.18E-06 379 0.04821292 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [newemax].[dbo].[TDefClr].[ClrName], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
135 1                 |    |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[Style])) 1 19 18 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[Style]) NULL 1 0 4.18E-06 255 0.04492564 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TDefStyle].[StyleName], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
0 0                 |    |    |--Compute Scalar(DEFINE:([newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FAmount])) 1 20 19 Compute Scalar Compute Scalar DEFINE:([newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FAmount]) [newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FAmount] 1 0 1E-07 135 0.04163836 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TAccStylePrice].[Style], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
135 1                 |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TBusPurchaseDt].[DetailId])) 1 21 20 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TBusPurchaseDt].[DetailId]) NULL 1 0 4.18E-06 135 0.04163826 [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice], [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TAccStylePrice].[Style], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
135 1                 |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Sku])) 1 22 21 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TDefSku].[Sku]) NULL 1 0 4.18E-06 101 0.03835088 [newemax].[dbo].[TBusPurchaseDt].[DetailId], [newemax].[dbo].[TBusPurchaseDt].[Sku], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TAccStylePrice].[Style], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
0 0                 |    |    |         |    |--Compute Scalar(DEFINE:([Expr1031]=('['+[newemax].[dbo].[TDefSku].[Clr])+']', [Expr1032]=('['+[newemax].[dbo].[TDefSku].[Size])+']')) 1 23 22 Compute Scalar Compute Scalar DEFINE:([Expr1031]=('['+[newemax].[dbo].[TDefSku].[Clr])+']', [Expr1032]=('['+[newemax].[dbo].[TDefSku].[Size])+']') [Expr1031]=('['+[newemax].[dbo].[TDefSku].[Clr])+']', [Expr1032]=('['+[newemax].[dbo].[TDefSku].[Size])+']' 1 0 6.803581E-07 92 0.0350636 [newemax].[dbo].[TDefSku].[Sku], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TAccStylePrice].[Style], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store], [Expr1031], [Expr1032] NULL PLAN_ROW 0 1
10790550 1                 |    |    |         |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[Style])) 1 24 23 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[Style]) NULL 6.803581 0 2.401792E-05 65 0.03506292 [newemax].[dbo].[TDefSku].[Sku], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size], [newemax].[dbo].[TAccStylePrice].[Style], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store] NULL PLAN_ROW 0 1
1892295 1                 |    |    |         |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[FlowNo]) OPTIMIZED) 1 25 24 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TAccStylePrice].[FlowNo]) OPTIMIZED NULL 1 0 4.18E-06 33 0.03175058 [newemax].[dbo].[TAccStylePrice].[Style], [newemax].[dbo].[TAccStylePrice].[DPrice], [newemax].[dbo].[TDefStore].[Store] NULL PLAN_ROW 0 1
1892295 1                 |    |    |         |    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([newemax].[dbo].[TDefStore].[BuyerId], [Expr1036]) WITH UNORDERED PREFETCH) 1 27 25 Nested Loops Inner Join OUTER REFERENCES:([newemax].[dbo].[TDefStore].[BuyerId], [Expr1036]) WITH UNORDERED PREFETCH NULL 1 0 0.0005643 33 0.0284633 [newemax].[dbo].[TAccStylePrice].[FlowNo], [newemax].[dbo].[TAccStylePrice].[Style], [newemax].[dbo].[TDefStore].[Store] NULL PLAN_ROW 0 1
135 1                 |    |    |         |    |         |    |    |--Index Scan(OBJECT:([newemax].[dbo].[TDefStore].[IdxBuyer])) 1 29 27 Index Scan Index Scan OBJECT:([newemax].[dbo].[TDefStore].[IdxBuyer]) [newemax].[dbo].[TDefStore].[Store], [newemax].[dbo].[TDefStore].[BuyerId] 135 0.003125 0.0003055 19 0.0034305 [newemax].[dbo].[TDefStore].[Store], [newemax].[dbo].[TDefStore].[BuyerId] NULL PLAN_ROW 0 1
1892295 135                 |    |    |         |    |         |    |    |--Index Seek(OBJECT:([newemax].[dbo].[TAccStylePrice].[IdxBuyerIdStyle]), SEEK:([newemax].[dbo].[TAccStylePrice].[BuyerId]=[newemax].[dbo].[TDefStore].[BuyerId]) ORDERED FORWARD) 1 30 27 Index Seek Index Seek OBJECT:([newemax].[dbo].[TAccStylePrice].[IdxBuyerIdStyle]), SEEK:([newemax].[dbo].[TAccStylePrice].[BuyerId]=[newemax].[dbo].[TDefStore].[BuyerId]) ORDERED FORWARD [newemax].[dbo].[TAccStylePrice].[FlowNo], [newemax].[dbo].[TAccStylePrice].[Style] 1 0.003125 0.0001581 27 0.0244685 [newemax].[dbo].[TAccStylePrice].[FlowNo], [newemax].[dbo].[TAccStylePrice].[Style] NULL PLAN_ROW 0 135
1892295 1892295                 |    |    |         |    |         |    |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TAccStylePrice].[PK_TACCSTYLEPRICE]), SEEK:([newemax].[dbo].[TAccStylePrice].[FlowNo]=[newemax].[dbo].[TAccStylePrice].[FlowNo]) LOOKUP ORDERED FORWARD) 1 32 25 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TAccStylePrice].[PK_TACCSTYLEPRICE]), SEEK:([newemax].[dbo].[TAccStylePrice].[FlowNo]=[newemax].[dbo].[TAccStylePrice].[FlowNo]) LOOKUP ORDERED FORWARD [newemax].[dbo].[TAccStylePrice].[DPrice] 1 0.003125 0.0001581 16 0.0032831 [newemax].[dbo].[TAccStylePrice].[DPrice] NULL PLAN_ROW 0 1
10790550 1892295                 |    |    |         |    |         |--Index Seek(OBJECT:([newemax].[dbo].[TDefSku].[IdxMatrix]), SEEK:([newemax].[dbo].[TDefSku].[Style]=[newemax].[dbo].[TAccStylePrice].[Style]) ORDERED FORWARD) 1 36 24 Index Seek Index Seek OBJECT:([newemax].[dbo].[TDefSku].[IdxMatrix]), SEEK:([newemax].[dbo].[TDefSku].[Style]=[newemax].[dbo].[TAccStylePrice].[Style]) ORDERED FORWARD [newemax].[dbo].[TDefSku].[Sku], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size] 5.745914 0.003125 0.0001633205 41 0.00328832 [newemax].[dbo].[TDefSku].[Sku], [newemax].[dbo].[TDefSku].[Style], [newemax].[dbo].[TDefSku].[Clr], [newemax].[dbo].[TDefSku].[Size] NULL PLAN_ROW 0 1
135 10790550                 |    |    |         |    |--Index Seek(OBJECT:([newemax].[dbo].[TBusPurchaseDt].[IdxMasterIdSku]), SEEK:([newemax].[dbo].[TBusPurchaseDt].[MasterId]='BYNKM02000007703' AND [newemax].[dbo].[TBusPurchaseDt].[Sku]=[newemax].[dbo].[TDefSku].[Sku]) ORDERED FORWARD) 1 42 22 Index Seek Index Seek OBJECT:([newemax].[dbo].[TBusPurchaseDt].[IdxMasterIdSku]), SEEK:([newemax].[dbo].[TBusPurchaseDt].[MasterId]='BYNKM02000007703' AND [newemax].[dbo].[TBusPurchaseDt].[Sku]=[newemax].[dbo].[TDefSku].[Sku]) ORDERED FORWARD [newemax].[dbo].[TBusPurchaseDt].[DetailId], [newemax].[dbo].[TBusPurchaseDt].[Sku] 1 0.003125 0.0001581 32 0.0032831 [newemax].[dbo].[TBusPurchaseDt].[DetailId], [newemax].[dbo].[TBusPurchaseDt].[Sku] NULL PLAN_ROW 0 1
0 0                 |    |    |         |--Compute Scalar(DEFINE:([newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[PreQty],0), [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[Qty],0))) 1 44 21 Compute Scalar Compute Scalar DEFINE:([newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[PreQty],0), [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[Qty],0)) [newemax].[dbo].[TBusPurchaseDt].[PreFAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[PreQty],0), [newemax].[dbo].[TBusPurchaseDt].[FAmount]=[newemax].[dbo].[TBusPurchaseDt].[FPrice]*CONVERT_IMPLICIT(numeric(10,0),[newemax].[dbo].[TBusPurchaseDt].[Qty],0) 1 0 1E-07 50 0.0032832 [newemax].[dbo].[TBusPurchaseDt].[PreFAmount], [newemax].[dbo].[TBusPurchaseDt].[FAmount], [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice] NULL PLAN_ROW 0 1
135 135                 |    |    |              |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TBusPurchaseDt].[PK_TBUSPURCHASEDT]), SEEK:([newemax].[dbo].[TBusPurchaseDt].[DetailId]=[newemax].[dbo].[TBusPurchaseDt].[DetailId]) LOOKUP ORDERED FORWARD) 1 45 44 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TBusPurchaseDt].[PK_TBUSPURCHASEDT]), SEEK:([newemax].[dbo].[TBusPurchaseDt].[DetailId]=[newemax].[dbo].[TBusPurchaseDt].[DetailId]) LOOKUP ORDERED FORWARD [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice] 1 0.003125 0.0001581 24 0.0032831 [newemax].[dbo].[TBusPurchaseDt].[PreQty], [newemax].[dbo].[TBusPurchaseDt].[Qty], [newemax].[dbo].[TBusPurchaseDt].[FPrice] NULL PLAN_ROW 0 1
135 135                 |    |    |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TDefStyle].[PK_TDEFSTYLE]), SEEK:([newemax].[dbo].[TDefStyle].[Style]=[newemax].[dbo].[TAccStylePrice].[Style]) ORDERED FORWARD) 1 63 19 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TDefStyle].[PK_TDEFSTYLE]), SEEK:([newemax].[dbo].[TDefStyle].[Style]=[newemax].[dbo].[TAccStylePrice].[Style]) ORDERED FORWARD [newemax].[dbo].[TDefStyle].[StyleName] 1 0.003125 0.0001581 138 0.0032831 [newemax].[dbo].[TDefStyle].[StyleName] NULL PLAN_ROW 0 1
135 135                 |    |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TDefClr].[PK_TDEFCLR]), SEEK:([newemax].[dbo].[TDefClr].[Clr]=[newemax].[dbo].[TDefSku].[Clr]) ORDERED FORWARD) 1 64 18 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TDefClr].[PK_TDEFCLR]), SEEK:([newemax].[dbo].[TDefClr].[Clr]=[newemax].[dbo].[TDefSku].[Clr]) ORDERED FORWARD [newemax].[dbo].[TDefClr].[ClrName] 1 0.003125 0.0001581 138 0.0032831 [newemax].[dbo].[TDefClr].[ClrName] NULL PLAN_ROW 0 1
135 135                 |--Clustered Index Seek(OBJECT:([newemax].[dbo].[TDefSize].[PK_TDEFSIZE]), SEEK:([newemax].[dbo].[TDefSize].[Size]=[newemax].[dbo].[TDefSku].[Size]) ORDERED FORWARD) 1 65 17 Clustered Index Seek Clustered Index Seek OBJECT:([newemax].[dbo].[TDefSize].[PK_TDEFSIZE]), SEEK:([newemax].[dbo].[TDefSize].[Size]=[newemax].[dbo].[TDefSku].[Size]) ORDERED FORWARD [newemax].[dbo].[TDefSize].[SizeName] 1 0.003125 0.0001581 138 0.0032831 [newemax].[dbo].[TDefSize].[SizeName] NULL PLAN_ROW 0 1

Answer 8

再反映个情况，改用参数方式，A和B都很快，执行计划相同，但一旦把B写入where，立马变慢

Answer 9

以前也碰到同样的问题，查询TOP 1 半个小时出不来，如果查询多的数据反而很快，后来发现也是执行计划的问题，使用QUERY HINT之后问题解决了，但是原因不明。

Answer 10

REBUILD 那个没看到具体的错误信息, 你直接用语句试试看是报什么错误

ALTER INDEX ALL ON 表名 REBUILD

Answer 11

很幸运，就是索引问题，今早执行ALTER INDEX重建索引一切顺利，重建完了问题立马解决，感谢大家的帮助！

另外请问索引不是由SQL自动维护么，为什么需要手动重建？如果手动重建是必须的，那么一般多长时间重建一次较为合适。

对于SQL性能优化这块，必须熟悉哪些知识？索引、执行计划、文件组……

Answer 12

索引不是自动维护的, 你需要自己去建立维护计划

Answer 13

You should rebuild index/update stats regularly.

Answer 14

你的意思索引从创建以后就不会随表内容变化而变化吗，直到重建？

Answer 15

That's correct. If table has many data changes, stats will be out dated and optimizer may pick wrong plan based on incorrect stats. Reindex/update stats will keep stats upto date.

Answer 16

重建索引需要自己手动的（不手动的话建个维护计划，但是重建索引会耗费大量系统资源，所以一般在系统不繁忙的时候做），

重建了索引之后除了解决碎片问题，还有一个就是更新了统计信息，有些查询速度慢的问题因为统计信息没有及时更新，而

重建索引的同时SQL还会更新统计信息，所以也能够解决这类因为没有及时更新统计信息的问题

根据LZ给出的统计信息，真的看不出什么，看了几片眼睛开始花了，最好LZ能截图，快的执行计划，慢的执行计划

---

给我写信： QQ我：

Answer 17

感谢各位的讲解，索引和统计信息这块是时候我该好好学习了