使用WITH AS 递归查询 ， 速度突然变慢

[wcywhole]

今天发现这个递归语句执行突然变慢了，以前执行五分钟，现在执行了半个多小时，主表大概20万条数据，但是递归的话每层也就几十条。服务器应该没问题select查询其他表时间还是正常的。请大侠们指点迷津

;
WITH T(zjh,lbjth,lbjmc)
AS
( SELECT zjh,lbjth,lbjmc FROM Uv_outerpdm_getbom WHERE zjh = 'QN1.233.088MX' AND sx1 = '整件'
UNION ALL
SELECT Uv_outerpdm_bom.zjh,Uv_outerpdm_bom.lbjth,Uv_outerpdm_bom.lbjmc FROM T
INNER JOIN Uv_outerpdm_getbom AS Uv_outerpdm_bom ON T.lbjth = Uv_outerpdm_bom.zjh AND Uv_outerpdm_bom.sx1 = '整件'
WHERE Uv_outerpdm_bom.zjh = T.lbjth
)

SELECT * FROM T

[wcywhole]

主表数据是通过链接服务器从另外一台服务器上提取的。

[wcywhole]

引用 3楼Tiger_Zhao 的回复:

说明访问链接服务器有较大的延迟，乘上递归的访问次数耗时就很可观了。

WITH T0 AS( -- 如果这个数据量不算很大，最好一次性取过来
    SELECT zjh,lbjth,lbjmc FROM Uv_outerpdm_getbom WHERE sx1 = '整件'
),
T(zjh,lbjth,lbjmc)AS(
    -- 用T0代替原来的Uv_outerpdm_getbom 
)
SELECT * FROM T

假如最终结果只是 sx1 = '整件' 中的极少一部分，那么你应该考虑在链接服务器上做个存储过程内部进行递归了，不要跨服务器递归。

试了你说的两种解决方法还是一样慢 主表有索引 但是表没有主键 这个有影响吗？

[Tiger_Zhao]

这什么烂设计，前面没看出来递归树居然用了两张表！

首先你到目标服务器的本机上执行一下存储过程，慢不慢？
一样慢，可能是索引的问题，在 Uv_outerpdm_bom 的 (sx1,zjh) 上建索引。
不慢，那么是网络环境的问题，和SQL没什么关系了，找网管吧。

[中国风]

引用 1 楼 wcy_da 的回复:

主表数据是通过链接服务器从另外一台服务器上提取的。

提取过程花销时间？ 你这样的情况，看一下条件列有没有建上索引？没有时建上

[吉普赛的歌]

楼主先贴出执行计划的截图吧。

[wcywhole]

把链接服务器上的视图取出来放临时表里 速度快多了

[吉普赛的歌]

以下几种方式可以改善性能： 1. 将递归改为循环； 2. 重建相关表的索引。

[Tiger_Zhao]

那么用我#4的前一种方法，把 Uv_outerpdm_bom 放在 T0 中一样的效果——前面以为只有一个表，结果把只取一次的 Uv_outerpdm_getbom 缓存了。

[xiaoxiangqing]

不用递归试下，看速度如何

[Tiger_Zhao]

说明访问链接服务器有较大的延迟，乘上递归的访问次数耗时就很可观了。

WITH T0 AS( -- 如果这个数据量不算很大，最好一次性取过来
    SELECT zjh,lbjth,lbjmc FROM Uv_outerpdm_getbom WHERE sx1 = '整件'
),
T(zjh,lbjth,lbjmc)AS(
    -- 用T0代替原来的Uv_outerpdm_getbom 
)
SELECT * FROM T

假如最终结果只是 sx1 = '整件' 中的极少一部分，那么你应该考虑在链接服务器上做个存储过程内部进行递归了，不要跨服务器递归。

[LongRui888]

另外，把涉及到的所有表，更新一下统计信息试试： update statistics 表

[LongRui888]

这个不太可能突然就慢了。 要么是网络故障，导致网络传输不畅