WEB开发网
开发学院数据库DB2 DB2 Spatial Extender 性能调优(1) 阅读

DB2 Spatial Extender 性能调优(1)

 2007-06-01 16:21:39 来源:WEB开发网   
核心提示: 清单 4. 不同 inline length 设置的效果$ time db2se import_shape testdb -fileName /home/stolze/europe/roads-srsName WGS84_SRS_1003 -tableName roads -createT

清单 4. 不同 inline length 设置的效果

$ time db2se import_shape testdb -fileName /home/stolze/europe/roads
    -srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1
    -spatialColumn shape -typeName ST_LineString -inlineLength 292
    -idColumn id -commitScope 1500
GSE0000I The operation was completed successfully.
real  
          3m15.604s
        
user  0m0.050s
sys   0m0.026s
$ db2batch -d testdb -f test_inline_length.sql -i complete -s on
---------------------------------------------
Statement number: 1
SELECT SUM(inline) AS inline_storage,
    COUNT(*) - SUM(inline) AS lobified_storage
FROM  ( SELECT CASE
          WHEN 197 + LENGTH(shape..points) <=
              ( SELECT inline_length
               FROM  syscat.columns
               WHERE colname = 'SHAPE' AND
                  tabname = 'ROADS' )
          THEN 1
          ELSE 0
        END
     FROM  roads ) AS t(inline)
INLINE_STORAGE LOBIFIED_STORAGE 
-------------- ----------------
     89595      21384
---------------------------------------------
Statement number: 2
SELECT id
FROM  roads
WHERE db2gse.ST_Intersects(shape, db2gse.ST_LineString(
     'linestring(10 50, 20 40)', 1003)) = 1
Prepare Time is:      0.000   seconds 
Execute Time is:      0.854   seconds 
Fetch Time is:       0.000   seconds 
          Elapsed Time is:      0.855   seconds
        
Buffer pool data logical reads        = 16818
Buffer pool index logical reads       = 19731
Direct reads                 = 3088
          Direct read requests             = 1544
        
Direct read elapsed time (ms)        = 18
---------------------------------------------
$ db2 "DROP TABLE roads"
$ time db2se import_shape testdb -fileName /home/stolze/europe/roads
    -srsName WGS84_SRS_1003 -tableName roads -createTableFlag 1
    -spatialColumn shape -typeName ST_LineString -inlineLength 2000
    -idColumn id -commitScope 1500
GSE0000I The operation was completed successfully.
real  
          1m57.212s
        
user  0m0.049s
sys   0m0.027s
$ db2batch -d testdb -f test_inline_length.sql -i complete -s on
---------------------------------------------
Statement number: 1
SELECT SUM(inline) AS inline_storage,
    COUNT(*) - SUM(inline) AS lobified_storage
FROM  ( SELECT CASE
          WHEN 197 + LENGTH(shape..points) <=
              ( SELECT inline_length
               FROM  syscat.columns
               WHERE colname = 'SHAPE' AND
                  tabname = 'ROADS' )
          THEN 1
          ELSE 0
        END
     FROM  roads ) AS t(inline)
INLINE_STORAGE LOBIFIED_STORAGE 
-------------- ----------------
    110979        0 
---------------------------------------------
Statement number: 2
SELECT id
FROM  roads
WHERE db2gse.ST_Intersects(shape, db2gse.ST_LineString(
     'linestring(10 50, 20 40)', 1003)) = 1
Prepare Time is:      0.000   seconds
Execute Time is:      0.792   seconds
Fetch Time is:       0.000   seconds
          Elapsed Time is:      0.792   seconds
        
Buffer pool data logical reads        = 17337
Buffer pool index logical reads       = 19731
Buffer pool index physical reads       = 0
          Direct reads                 = 2
        
Direct read requests             = 1
Direct read elapsed time (ms)        = 0
---------------------------------------------

上一页  4 5 6 7 8 9 10  下一页

Tags:DB Spatial Extender

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接