51Testing软件测试论坛

标题: QTP+QC 非常缓慢的问题 [打印本页]

作者: unholyalliance 时间: 2012-3-5 18:06
标题: QTP+QC 非常缓慢的问题
服务器环境：
win2003 +qc 9.0+sqlserver 2000

在QC用例中关联QTP脚本，并存储在QC服务器上。经过一段时间的使用后，在客户端打开服务器上的QTP脚本性能异常慢。
经过监控服务器端SQL执行，分析发现引起慢的原因在于sql中的“ORDER BYRP_PARENT_PATH ”子句执行导致。
在屏蔽该语句时，系统整条语句执行1秒钟都不要，但是开启后执行，则需要花费高达15秒的时间。根据我的推断这应该是一个系统BUG来的。
下面贴出抓获的语句：

SELECT RP_ID,RP_PARENT_PATH,RP_NAME,RP_PATH_ID,RP_MODIFIED_TIME,DATALENGTH(RP_DATA) AS DATA_LENGTH
FROM (
SELECT * FROM (
SELECT * FROM td.REPOSITORY WHERE RP_PARENT_PATH='0/1/6/683/' AND RP_NAME='Default.xls' AND RP_PATH_ID=0
UNION ALL
SELECT td.REPOSITORY.* FROM (
SELECT td.REPOSITORY.*, RP_PARENT_PATH + convert(varchar,RP_PATH_ID)+'/' AS FILE_PPATH
FROM td.REPOSITORY
WHERE (RP_PARENT_PATH = '0/1/6/683/') AND RP_NAME ='Default.xls' AND RP_PATH_ID <> 0) FOLDER
INNER JOIN td.REPOSITORY ON td.REPOSITORY.RP_PARENT_PATH LIKE FOLDER.FILE_PPATH + '%') D
UNION ALL
SELECT * FROM (
SELECT * FROM td.REPOSITORY
WHERE RP_PARENT_PATH='0/1/6/683/' AND RP_NAME='Test.tsp' AND RP_PATH_ID=0
UNION ALL
SELECT td.REPOSITORY.*
FROM (
SELECT td.REPOSITORY.*, RP_PARENT_PATH + convert(varchar,RP_PATH_ID)+'/' AS FILE_PPATH
FROM td.REPOSITORY
WHERE (RP_PARENT_PATH = '0/1/6/683/') AND RP_NAME ='Test.tsp' AND RP_PATH_ID <> 0) FOLDER
INNER JOIN td.REPOSITORY ON td.REPOSITORY.RP_PARENT_PATH LIKE FOLDER.FILE_PPATH + '%') D
UNION ALL SELECT * FROM (
SELECT * FROM td.REPOSITORY
WHERE RP_PARENT_PATH='0/1/6/683/' AND RP_NAME='Parameters.mtr' AND RP_PATH_ID=0
UNION ALL
SELECT td.REPOSITORY.*
FROM (SELECT td.REPOSITORY.*, RP_PARENT_PATH + convert(varchar,RP_PATH_ID)+'/' AS FILE_PPATH
FROM td.REPOSITORY
WHERE (RP_PARENT_PATH = '0/1/6/683/') AND RP_NAME ='Parameters.mtr' AND RP_PATH_ID <> 0) FOLDER
INNER JOIN td.REPOSITORY ON td.REPOSITORY.RP_PARENT_PATH LIKE FOLDER.FILE_PPATH + '%') D
UNION ALL
SELECT * FROM (
SELECT * FROM td.REPOSITORY
WHERE RP_PARENT_PATH=(SELECT RP_PARENT_PATH + cast(RP_PATH_ID as varchar(255))+ '/'
FROM td.REPOSITORY
WHERE RP_NAME = 'Action0' AND RP_PARENT_PATH = '0/1/6/683/') AND RP_NAME='Resource.mtr' AND RP_PATH_ID=0
UNION ALL
SELECT td.REPOSITORY.*
FROM (SELECT td.REPOSITORY.*, RP_PARENT_PATH + convert(varchar,RP_PATH_ID)+'/' AS FILE_PPATH
FROM td.REPOSITORY
WHERE (RP_PARENT_PATH = (SELECT RP_PARENT_PATH + cast(RP_PATH_ID as varchar(255))+ '/'
FROM td.REPOSITORY
WHERE RP_NAME = 'Action0' AND RP_PARENT_PATH = '0/1/6/683/')) AND RP_NAME ='Resource.mtr' AND RP_PATH_ID <> 0) FOLDER
INNER JOIN td.REPOSITORY ON td.REPOSITORY.RP_PARENT_PATH LIKE FOLDER.FILE_PPATH + '%') D
UNION ALL SELECT * FROM (
SELECT * FROM td.REPOSITORY WHERE RP_PARENT_PATH=(
SELECT RP_PARENT_PATH + cast(RP_PATH_ID as varchar(255))+ '/'
FROM td.REPOSITORY WHERE RP_NAME = 'Action1' AND RP_PARENT_PATH = '0/1/6/683/') AND RP_NAME='Resource.mtr' AND RP_PATH_ID=0
UNION ALL
SELECT td.REPOSITORY.* FROM (
SELECT td.REPOSITORY.*, RP_PARENT_PATH + convert(varchar,RP_PATH_ID)+'/' AS FILE_PPATH
FROM td.REPOSITORY
WHERE (RP_PARENT_PATH = (
SELECT RP_PARENT_PATH + cast(RP_PATH_ID as varchar(255))+ '/'
FROM td.REPOSITORY
WHERE RP_NAME = 'Action1' AND RP_PARENT_PATH = '0/1/6/683/')) AND RP_NAME ='Resource.mtr' AND RP_PATH_ID <> 0) FOLDER INNER JOIN td.REPOSITORY ON td.REPOSITORY.RP_PARENT_PATH LIKE FOLDER.FILE_PPATH + '%') D
UNION ALL
SELECT * FROM (
SELECT * FROM td.REPOSITORY
WHERE RP_PARENT_PATH='0/1/6/683/' AND RP_NAME='default.cfg' AND RP_PATH_ID=0
UNION ALL
SELECT td.REPOSITORY.*
FROM (
SELECT td.REPOSITORY.*, RP_PARENT_PATH + convert(varchar,RP_PATH_ID)+'/' AS FILE_PPATH
FROM td.REPOSITORY
WHERE (RP_PARENT_PATH = '0/1/6/683/') AND RP_NAME ='default.cfg' AND RP_PATH_ID <> 0) FOLDER INNER JOIN td.REPOSITORY ON td.REPOSITORY.RP_PARENT_PATH LIKE FOLDER.FILE_PPATH + '%') D UNION ALL SELECT * FROM (SELECT * FROM td.REPOSITORY WHERE RP_PARENT_PATH='0/1/6/683/' AND RP_NAME='1221.usr' AND RP_PATH_ID=0 UNION ALL SELECT td.REPOSITORY.* FROM (SELECT td.REPOSITORY.*, RP_PARENT_PATH + convert(varchar,RP_PATH_ID)+'/' AS FILE_PPATH FROM td.REPOSITORY WHERE (RP_PARENT_PATH = '0/1/6/683/') AND RP_NAME ='1221.usr' AND RP_PATH_ID <> 0) FOLDER INNER JOIN td.REPOSITORY ON td.REPOSITORY.RP_PARENT_PATH LIKE FOLDER.FILE_PPATH + '%') D) D
ORDER BY RP_PARENT_PATH

求助高人，对于这样的情况如何解决？

作者: lyscser 时间: 2012-3-6 15:53
有些字段，根据自己的实际情况要自己建索引

作者: wangood 时间: 2012-3-7 09:29
在REPOSITORY 表建立欄位RP_PARENT_PATH的索引即可.

作者: caihongtiantang 时间: 2012-3-7 15:26
学习中哦

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)