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
---------------------------------------------
- ››db2 对float类型取char后显示科学计数法
- ››DB2中出现SQL1032N错误现象时的解决办法
- ››DB2 锁升级示例
- ››db2诊断系列之---定位锁等待问题
- ››db2 命令选项解释
- ››DB2 最佳实践: 使用 DB2 pureXML 管理 XML 数据的...
- ››DB2 9.5 SQL Procedure Developer 认证考试 735 准...
- ››DB2 9.5 SQL Procedure Developer 认证考试 735 准...
- ››DB2 9.5 SQL Procedure Developer 认证考试 735 准...
- ››DB2 基础: 表空间和缓冲池
- ››DB2 XML 编程,第 1 部分: 理解 XML 数据模型
- ››DB2 pureScale 实战
更多精彩
赞助商链接