sql - Ship in access plan 的成本非常高

Question

我得到了这个查询：

select 
    count(1) as total 
from 
    Document 
join 
    ClaimDocument on ClaimDocument.documentId = Document.documentId 
where 
    (Document.latestChangeLogonUserId = 1) and 
    (Document.documentStatusId = 1)

它执行以下访问计划：

Total Cost:         8.45269e+07
Query Degree:       1

           Rows 
          RETURN
          (   1)
           Cost 
            I/O 
            |
             1 
          GRPBY 
          (   2)
        8.45269e+07 
        2.16185e+07 
            |
          2090.31 
          MSJOIN
          (   3)
        8.45269e+07 
        2.16185e+07 
      /-----+------\
  25971.7         0.080484 
  TBSCAN           FILTER
  (   4)           (   7)
8.45092e+07        17717.3 
2.1589e+07         29952.7 
    |                |
  25971.7        5.39219e+06 
  SORT             IXSCAN
  (   5)           (   8)
8.45092e+07        17717.3 
2.1589e+07         29952.7 
    |                |
  25971.7        5.39219e+06 
  SHIP         INDEX: CD     
  (   6)     ClDoc_docuId_claiId
8.45092e+07          Q2
2.1589e+07 
    |
8.63559e+07 
NICKNM: CD     
 DOCUMENT
    Q1

注意这艘船的巨大​​成本。我在互联网上找不到任何关于如何提高船舶查询速度的信息。

Document 和 ClaimDocument 位于 2 个独立的数据库中。我当然有一些索引，但它们不会提高速度。在访问计划中，我得到了关于这艘船的以下信息：

6) SHIP  : (Ship)
    Cumulative Total Cost:      8.45092e+07
    Cumulative CPU Cost:        3.58355e+11
    Cumulative I/O Cost:        2.1589e+07
    Cumulative Re-Total Cost:   26574.5
    Cumulative Re-CPU Cost:     2.25043e+11
    Cumulative Re-I/O Cost:     0
    Cumulative First Row Cost:  3277.92
    Estimated Bufferpool Buffers:   2.1589e+07
    Remote Total Cost:  13426.5
    Remote Communication Cost:  13422.5

    Arguments:
    ---------
    CSERQY  : (Remote common subexpression)
        FALSE
    DSTSEVER: (Destination (ship to) server)
        - (NULL).
    RMTQTXT : (Remote statement)
        SELECT A0."DOCUMENTID" C0 FROM "CD"."DOCUMENT" A0 WHERE (A0."LATESTCHANGELOGONUSERID" = 24926) AND (A0."DOCUMENTSTATUSID" = -1) FOR READ ONLY
    SRCSEVER: (Source (ship from) server)
        CDDOC
    STREAM  : (Remote stream)
        FALSE

    Predicates:
    ----------
    3) Sargable Predicate, 
        Comparison Operator:        Equal (=)
        Subquery Input Required:    No
        Filter Factor:          0.2

        Predicate Text:
        --------------
        (Q1.DOCUMENTSTATUSID = 1)


    4) Sargable Predicate, 
        Comparison Operator:        Equal (=)
        Subquery Input Required:    No
        Filter Factor:          0.00150376

        Predicate Text:
        --------------
        (Q1.LATESTCHANGELOGONUSERID = 1)



    Input Streams:
    -------------
        1) From Object CD.DOCUMENT

            Estimated number of rows:   8.63559e+07
            Number of columns:      4
            Subquery predicate ID:      Not Applicable

            Column Names:
            ------------
            +Q1.$RID$+Q1.DOCUMENTSTATUSID
            +Q1.LATESTCHANGELOGONUSERID+Q1.DOCUMENTID


    Output Streams:
    --------------
        2) To Operator #5

            Estimated number of rows:   25971.7
            Number of columns:      1
            Subquery predicate ID:      Not Applicable

            Column Names:
            ------------
            +Q1.DOCUMENTID

有没有人有运输声明以及如何提高速度的经验？

标签： sqldb2sql-execution-plansqlperformance