SELECT distinct * FROM
(SELECT dbo.FGetParentsProj(PROJECT_ID) AS Parents
FROM dbo.TPROJECT
WHERE (PROJECT_BUDGET = 'Y') AND (PROJECT_ACTIVE = 'Y')) AS A
inner JOIN
dbo.TPROJECT ON A.Parents LIKE '%▓' + dbo.TPROJECT.PROJECT_ID + '▓%'
涉及到的函数FGetParentsProj内容:
CREATE FUNCTION [dbo].[FGetParentsProj] (@id nvarchar(50))
RETURNS nvarchar(2000) AS
BEGIN
declare @result nvarchar(2000),@parentid nvarchar(50)
select @result=''
if(@id != null)
begin
select @parentid = PROJECT_PARENT_PRJ from TPROJECT
where PROJECT_ID=@id
if(@parentid != '')
set @result = '▓' + dbo.FGetParentsProj(@parentid)+'▓' + @id + '▓'
else
set @result = '▓' + @id + '▓'
end
return @result
END
看了下表TPROJECT,数据量也不大33条,但是执行时间却较多:
set statistics time on
go
分析:
这个视图还算简单,没有出现那种像文章一样的复杂视图和函数,理了下思绪,这个视图有以下几点可以优化:
1.使用case代替自己写的函数FGetParentsProj,这个函数就是一个判断值是否为空的作用
2.去掉inner join,或者使用left join,测试发现去掉inner join更快一点(这个是查询慢的罪魁祸首)
3.去掉inner join 后面的查询条件
4.去掉distinct,这点是基于表字段PROJECT_ID是主键字段
最后优化后的查询语句:
select case PROJECT_PARENT_PRJ
when '' then '▓' + PROJECT_ID + '▓'
else '▓' + PROJECT_PARENT_PRJ+'▓' + PROJECT_ID + '▓'
end as parents,* from dbo.TPROJECT where PROJECT_BUDGET = 'Y' AND PROJECT_ACTIVE = 'Y'
优化后的执行时间: