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

  • 问题

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

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

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

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

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


    2013年1月31日 6:43

答案

  • 你是用参数的是吧,试一下直接在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!


    2013年1月31日 6:57
  • REBUILD 那个没看到具体的错误信息, 你直接用语句试试看是报什么错误

    ALTER INDEX ALL ON 表名 REBUILD
    • 已标记为答案 ahdung_AI 2013年2月5日 3:15
    2013年1月31日 9:46
  • 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.
    • 已标记为答案 ahdung_AI 2013年2月5日 3:16
    2013年2月1日 14:03
  • 重建索引需要自己手动的(不手动的话建个维护计划,但是重建索引会耗费大量系统资源,所以一般在系统不繁忙的时候做),

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

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

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


    给我写信: QQ我:点击这里给我发消息


    2013年2月4日 14:21

全部回复

  • 而且两次查询的执行计划也有明显不同,只是真心看不懂问题到底在哪?
    2013年1月31日 6:53
  • 你是用参数的是吧,试一下直接在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!


    2013年1月31日 6:57
  • 尝试过不连接其中一个比较大的表后,无论查A还是B都是秒查,所以我想重建对这个表的所有索引,但是重建时报错如下:

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

    2013年1月31日 7:00
  • 什么原因会导致同样的语句(仅仅是条件不同),产生截然不同的执行步骤呢?

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

    2013年1月31日 7:13
  • 回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)
    

    2013年1月31日 7:31
  • 然后这是快的执行计划:

    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

    2013年1月31日 7:33
  • 看来我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

    2013年1月31日 7:37
  • 再反映个情况,改用参数方式,A和B都很快,执行计划相同,但一旦把B写入where,立马变慢
    2013年1月31日 8:47
  • 以前也碰到同样的问题,查询TOP 1 半个小时出不来,如果查询多的数据反而很快,后来发现也是执行计划的问题,使用QUERY HINT之后问题解决了,但是原因不明。
    2013年1月31日 9:15
  • REBUILD 那个没看到具体的错误信息, 你直接用语句试试看是报什么错误

    ALTER INDEX ALL ON 表名 REBUILD
    • 已标记为答案 ahdung_AI 2013年2月5日 3:15
    2013年1月31日 9:46
  • 很幸运,就是索引问题,今早执行ALTER INDEX重建索引一切顺利,重建完了问题立马解决,感谢大家的帮助!

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

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

    2013年2月1日 1:53
  • 索引不是自动维护的, 你需要自己去建立维护计划

    2013年2月1日 3:33
  • You should rebuild index/update stats regularly.
    2013年2月1日 3:47
  • 你的意思索引从创建以后就不会随表内容变化而变化吗,直到重建?
    2013年2月1日 9:09
  • 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.
    • 已标记为答案 ahdung_AI 2013年2月5日 3:16
    2013年2月1日 14:03
  • 重建索引需要自己手动的(不手动的话建个维护计划,但是重建索引会耗费大量系统资源,所以一般在系统不繁忙的时候做),

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

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

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


    给我写信: QQ我:点击这里给我发消息


    2013年2月4日 14:21
  • 感谢各位的讲解,索引和统计信息这块是时候我该好好学习了
    2013年2月5日 3:17