性能问题

银铠圣袍 发表于 2007-6-15 11:08:14

12.5.0的查询计划QUERY PLAN FOR STATEMENT 1 (at line 1).     STEP 1         The type of query is INSERT.         The update mode is direct.         Worktable2 created for REFORMATTING.         FROM TABLE             FeeType             ft         Nested iteration.         Using Clustered Index.         Index : PK_FEETYPE         Forward scan.         Positioning at start of table.         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         TO TABLE             Worktable2.     STEP 2         The type of query is INSERT.         The update mode is direct.         Worktable3 created for REFORMATTING.         FROM TABLE             VendorFee             vf         Nested iteration.         Using Clustered Index.         Index : PK_VENDORFEE         Forward scan.         Positioning at start of table.         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         TO TABLE             Worktable3.     STEP 3         The type of query is INSERT.         The update mode is direct.         Worktable1 created for ORDER BY.         FROM TABLE             OtherFee             o         Nested iteration.         Using Clustered Index.         Index : PK_OTHERFEE         Forward scan.         Positioning at start of table.         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Vendor             v         Nested iteration.         Using Clustered Index.         Index : PK_VENDOR         Forward scan.         Positioning by key.         Keys are:             VendorID  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Stores             s         Nested iteration.         Using Clustered Index.         Index : PK_STORES         Forward scan.         Positioning by key.         Keys are:             StoreID  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Worktable2.         Nested iteration.         Using Clustered Index.         Forward scan.         Positioning by key.         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Worktable3.         Nested iteration.         Using Clustered Index.         Forward scan.         Positioning by key.         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             UserBuyer             u         Nested iteration.         Using Clustered Index.         Index : PK_USERBUYER         Forward scan.         Positioning by key.         Keys are:             Buyer  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Buyer             b         Nested iteration.         Using Clustered Index.         Index : PK_BUYER         Forward scan.         Positioning by key.         Keys are:             Buyer  ASC         Run subquery 1 (at nesting level 1).         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         TO TABLE             Worktable1.     STEP 4         The type of query is SELECT.         This step involves sorting.         FROM TABLE             Worktable1.         Using GETSORTED         Table Scan.         Forward scan.         Positioning at start of table.         Using I/O Size 2 Kbytes for data pages.         With MRU Buffer Replacement Strategy for data pages. NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.   QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 10).     Correlated Subquery.     Subquery under an EXPRESSION predicate.     STEP 1         The type of query is SELECT.         Evaluate Ungrouped COUNT AGGREGATE.         FROM TABLE             ST_Vendor             a         Nested iteration.         Using Clustered Index.         Index : PK_ST_VENDOR         Forward scan.         Positioning by key.         Keys are:             VendorID  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Stores             b         Nested iteration.         Using Clustered Index.         Index : PK_STORES         Forward scan.         Positioning by key.         Keys are:             StoreID  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.   END OF QUERY PLAN FOR SUBQUERY 1. Server Message:  Number  1562, Severity  10 Server 'FYMIS02', Line 1: The sort for Worktable2 is done in Serial   Server Message:  Number  1562, Severity  10 Server 'FYMIS02', Line 1: The sort for Worktable3 is done in Serial   Server Message:  Number  1562, Severity  10 Server 'FYMIS02', Line 1: The sort for Worktable1 is done in Serial   Table: ST_Vendor scan count 1, logical reads: (regular=7 apf=0 total=7), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Stores scan count 24, logical reads: (regular=51 apf=0 total=51), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: OtherFee scan count 1, logical reads: (regular=73110 apf=0 total=73110), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Vendor scan count 20, logical reads: (regular=60 apf=0 total=60), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: UserBuyer scan count 20, logical reads: (regular=40 apf=0 total=40), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Buyer scan count 760, logical reads: (regular=1520 apf=0 total=1520), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Stores scan count 20, logical reads: (regular=42 apf=0 total=42), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: VendorFee scan count 1, logical reads: (regular=2011 apf=0 total=2011), physical reads: (regular=288 apf=0 total=288), apf IOs used=0 Table: FeeType scan count 1, logical reads: (regular=6 apf=0 total=6), physical reads: (regular=6 apf=0 total=6), apf IOs used=0 Table: Worktable1  scan count 0, logical reads: (regular=1050 apf=0 total=1050), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable2  scan count 20, logical reads: (regular=171 apf=0 total=171), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable3  scan count 0, logical reads: (regular=92068 apf=0 total=92068), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Total writes for this command: 2481 (760 rows affected)

银铠圣袍 发表于 2007-6-15 11:09:01

12.5.3的查询计划QUERY PLAN FOR STATEMENT 1 (at line 1).     STEP 1         The type of query is INSERT.         The update mode is direct.         Worktable2 created for REFORMATTING.         FROM TABLE             VendorFee             vf         Nested iteration.         Using Clustered Index.         Index : PK_VENDORFEE         Forward scan.         Positioning at start of table.         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         TO TABLE             Worktable2.     STEP 2         The type of query is INSERT.         The update mode is direct.         Worktable1 created, in allpages locking mode, for ORDER BY.         FROM TABLE             OtherFee             o         Nested iteration.         Using Clustered Index.         Index : PK_OTHERFEE         Forward scan.         Positioning at start of table.         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Vendor             v         Nested iteration.         Using Clustered Index.         Index : PK_VENDOR         Forward scan.         Positioning by key.         Keys are:             VendorID  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Stores             s         Nested iteration.         Using Clustered Index.         Index : PK_STORES         Forward scan.         Positioning by key.         Keys are:             StoreID  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             FeeType             ft         Nested iteration.         Using Clustered Index.         Index : PK_FEETYPE         Forward scan.         Positioning at start of table.         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Worktable2.         Nested iteration.         Using Clustered Index.         Forward scan.         Positioning by key.         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             UserBuyer             u         Nested iteration.         Using Clustered Index.         Index : PK_USERBUYER         Forward scan.         Positioning by key.         Keys are:             Buyer  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Buyer             b         Nested iteration.         Using Clustered Index.         Index : PK_BUYER         Forward scan.         Positioning by key.         Keys are:             Buyer  ASC         Run subquery 1 (at nesting level 1).         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         TO TABLE             Worktable1.     STEP 3         The type of query is SELECT.         This step involves sorting.         FROM TABLE             Worktable1.         Using GETSORTED         Table Scan.         Forward scan.         Positioning at start of table.         Using I/O Size 2 Kbytes for data pages.         With MRU Buffer Replacement Strategy for data pages.     STEP 1 NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.   QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 10).     Correlated Subquery.     Subquery under an EXPRESSION predicate.     STEP 1         The type of query is SELECT.         Evaluate Ungrouped COUNT AGGREGATE.         FROM TABLE             ST_Vendor             a         Nested iteration.         Using Clustered Index.         Index : PK_ST_VENDOR         Forward scan.         Positioning by key.         Keys are:             VendorID  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.         FROM TABLE             Stores             b         Nested iteration.         Using Clustered Index.         Index : PK_STORES         Forward scan.         Positioning by key.         Keys are:             StoreID  ASC         Using I/O Size 2 Kbytes for data pages.         With LRU Buffer Replacement Strategy for data pages.   END OF QUERY PLAN FOR SUBQUERY 1. Server Message:  Number  1562, Severity  10 Server 'FYMIS03', Line 1: The sort for Worktable2 is done in Serial   Server Message:  Number  1562, Severity  10 Server 'FYMIS03', Line 1: The sort for Worktable1 is done in Serial   Table: ST_Vendor scan count 1, logical reads: (regular=6 apf=0 total=6), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Stores scan count 24, logical reads: (regular=49 apf=0 total=49), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: OtherFee scan count 1, logical reads: (regular=48445 apf=0 total=48445), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Vendor scan count 20, logical reads: (regular=80 apf=0 total=80), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: UserBuyer scan count 20, logical reads: (regular=60 apf=0 total=60), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Buyer scan count 780, logical reads: (regular=1560 apf=0 total=1560), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Stores scan count 20, logical reads: (regular=41 apf=0 total=41), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: VendorFee scan count 1, logical reads: (regular=2003 apf=0 total=2003), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: FeeType scan count 20, logical reads: (regular=60 apf=0 total=60), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable1  scan count 0, logical reads: (regular=1079 apf=0 total=1079), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Table: Worktable2  scan count 0, logical reads: (regular=92833 apf=0 total=92833), physical reads: (regular=0 apf=0 total=0), apf IOs used=0 Total writes for this command: 2698 (780 rows affected)

flybean 发表于 2007-6-15 11:15:37

查询计划不一样哟

页: [1]

PowerBuilder(PB)BBS社区's Archiver

性能问题