近期发现MRTG汇总存储过程存在问题，执行汇总过程死活汇不出东西来。于是研究之……
先简化存储过程，变成以下样子：
select top 10 * from mrtg_flow
where orgtime_dt >= '2009-06-25 00:00:00' and orgtime_dt < '2009-06-25 01:00:00'
group by ip, ifindex
和
select top 10 * from mrtg_flow
where orgtime_dt >= @v_begin_time and orgtime_dt < @v_end_time
group by ip, ifindex
发现用变量就会卡死，写死就什么事情没有，很快返回结果。
通过sybase的set showplan on、set noexec on，再执行以上存储过程，看sybase生成的执行计划。
写死参数的执行计划如下：
2> exec spw_mrtg_flow_sum_new_h
3> go
QUERY PLAN FOR STATEMENT 1 (at line 2).
STEP 1
The type of query is EXECUTE.
QUERY PLAN FOR STATEMENT 1 (at line 62).
STEP 1
The type of query is SELECT (into Worktable1).
GROUP BY
Evaluate Grouped COUNT AGGREGATE.
Evaluate Grouped COUNT AGGREGATE.
Evaluate Grouped COUNT AGGREGATE.
Evaluate Grouped MAXIMUM AGGREGATE.
Evaluate Grouped MAXIMUM AGGREGATE.
Evaluate Grouped MAXIMUM AGGREGATE.
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
FROM TABLE
mrtg_flow
a
Nested iteration.
Index : idx_orgtime_dt
Forward scan.
Positioning by key.
Keys are:
orgtime_dt DESC
Using I/O Size 8 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 8 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
FROM TABLE
Worktable1.
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 8 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
ip ifindex &nbs p;
-------------------------- -------------------- ----------------------- ---------------------------------------------------- ---------------------------------------------------- ----------------------- ----------------------- ---------------------------------------------------- ------------------------
NULL 10.233.100.1 775 0.00000000000 0.00000000000 0 0 0.00000000000 0
NULL 10.233.100.1 903 0.00000000000 0.00000000000 0 0 0.00000000000 0
NULL 10.233.100.1 1030 217.83333333333 221.08333333333 225 270 438.91666666666 491
NULL 10.233.100.1 1287 0.00000000000 0.00000000000 0 0 0.00000000000 0
NULL 10.233.100.1 1415 0.00000000000 0.00000000000 0 0 0.00000000000 0
NULL 10.233.100.1 67109249 112.58333333333 109.50000000000 117 114 222.08333333333 231
NULL 10.233.100.1 67109377 113.66666666666 104.83333333333 121 112 218.50000000000 233
NULL 10.233.100.1 134218113 114.41666666666 115.33333333333 120 127 229.75000000000 244
NULL 10.233.100.1 134218369 0.00000000000 21.00000000000 0 21 21.00000000000 21
NULL 10.233.100.1 134218497 114076.50000000000 117878.41666666666 133558 138947 231954.91666666666 272505
(10 rows affected)
(return status = 0)
1>
带变量的执行计划如下：
2> exec spw_mrtg_flow_sum_new_h
3> go
QUERY PLAN FOR STATEMENT 1 (at line 2).
STEP 1
The type of query is EXECUTE.
QUERY PLAN FOR STATEMENT 1 (at line 38).
STEP 1
The type of query is SELECT.
QUERY PLAN FOR STATEMENT 2 (at line 61).
STEP 1
The type of query is SELECT (into Worktable1).
GROUP BY
Evaluate Grouped COUNT AGGREGATE.
Evaluate Grouped COUNT AGGREGATE.
Evaluate Grouped COUNT AGGREGATE.
Evaluate Grouped MAXIMUM AGGREGATE.
Evaluate Grouped MAXIMUM AGGREGATE.
Evaluate Grouped MAXIMUM AGGREGATE.
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
FROM TABLE
mrtg_flow
a
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 2
The type of query is SELECT.
FROM TABLE
Worktable1.
Nested iteration.
对比可见，写死的SQL在查询过程中，用到了索引idx_orgtime_dt，但是带变量的SQL就不使用索引，而采用全表扫描。这个 mrtg_flow有7千万的数据量，当然会卡死。
解决方法，查询时强制使用索引：
select top 10 * from mrtg_flow (index idx_orgtime_dt)
where orgtime_dt >= @v_begin_time and orgtime_dt < @v_end_time
group by ip, ifindex
同时进一步优化存储过程：
1、先建临时表，将数据从大表读取后，放到临时表；
2、在临时表中建立索引，方便查询；
3、基于临时表进行汇总运算；
4、存储过程结束时，删除临时表；
没有评论:
发表评论