sql语句优化

Question

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
declare @SQLStr as nvarchar(max);with t as
(
select a.cate_node_name as 岗位名称,a.cate_node_id as 岗位id,a.table_id as 人员id,display_name as 人员,(select top 1 nodelevel from dbo.gettree(10064) where cate_node_id=a.cate_node_id) as [岗位level]  from v_cate_set a join PERSONS b on a.table_id=b.user_id where table_name='PERSONS'
 and
EXISTS (SELECT 1
   FROM dbo.gettree(10064)
   WHERE 
   cate_node_id= a.cate_node_id)
),
t1 as
(
select a.cate_node_name as 行业名称,a.cate_node_id as 行业id,a.table_id as 人员id,display_name as 人员  from v_cate_set a join PERSONS b on a.table_id=b.user_id where table_name='PERSONS'  and
EXISTS (SELECT 1
   FROM dbo.gettree(160)
   WHERE 
   cate_node_id= a.cate_node_id)
),
t2 as 
(
select display_name as 人员,isnull(cate_node_Name,(select top 1 cate_node_name from v_cate_set where table_id=a.default_cust_id and table_name='CUST' 
 and
EXISTS (SELECT 1
   FROM dbo.gettree(154)
   WHERE 
   cate_node_id= v_cate_set.cate_node_id)
)) as 区域,isnull(cate_node_id,(select top 1 cate_node_id from v_cate_set where table_id=a.default_cust_id and table_name='CUST' 
and 
EXISTS (SELECT 1
   FROM dbo.gettree(154)
   WHERE 
   cate_node_id= v_cate_set.cate_node_id)
)) as 区域id,[user_id] as 人员id from persons a left join v_cate_set b on a.[user_id]=b.table_id and root_id=154 and table_name='PERSONS' where a.state<>'INVALID'
),
t3 as
(
select *,(select top 1 parent_id from cate_tree where t2.区域id=cate_node_id) as 区域上级id from t2 where 区域 is not null
),
t4 as
(
select a.cate_node_name as 产品名称,a.cate_node_id as 产品id,a.table_id as 人员id,display_name as 人员  from v_cate_set a join PERSONS b on a.table_id=b.user_id where table_name='PERSONS' and
EXISTS (SELECT 1
   FROM dbo.gettree(1)
   WHERE 
   cate_node_id= a.cate_node_id)
)
select t.*,t1.行业名称,t1.行业id,t3.区域,t3.区域id,t3.区域上级id,t4.产品名称,t4.产品id from t left  join t1 on t1.人员id=t.人员id left join t3 on t3.人员id=t.人员id left join t4 on t4.人员id=t.人员id这个sql语句执行起来需要5.6秒钟，太慢了，而且每个表里面其实数据并不多，最多的也就1000多，我每个都试过，发现最慢的还是最后一句：select t.*,t1.行业名称,t1.行业id,t3.区域,t3.区域id,t3.区域上级id,t4.产品名称,t4.产品id from t left  join t1 on t1.人员id=t.人员id left join t3 on t3.人员id=t.人员id left join t4 on t4.人员id=t.人员id，请高手帮忙看看优化下，其中dbo。gettree是过的子节点的函数：set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER FUNCTION [dbo].[getTree] (@parentID int)RETURNS @t TABLE(CATE_NODE_ID int,CATE_CODE nvarchar(50),CATE_NODE_NAME nvarchar(50),nodeTreeName nvarchar(max),CATE_DESC nvarchar(4000),PARENT_ID INT,ROOT_ID INT,ORDERID INT,nodeLevel int) ASBEGIN
declare @i int  
    set @i = 1   
    
IF @parentID>0 
BEGIN
insert into @t select CATE_NODE_ID,CATE_CODE,CATE_NODE_NAME,CATE_NODE_NAME,CATE_DESC,PARENT_ID,ROOT_ID,ORDERID,@i from CATE_TREE WHERE (CATE_NODE_ID=@parentID OR CATE_NODE_ID=@parentID) AND STATE<>'invalid'
END
ELSE
BEGIN
insert into @t select CATE_NODE_ID,CATE_CODE,CATE_NODE_NAME,CATE_NODE_NAME,CATE_DESC,PARENT_ID,ROOT_ID,ORDERID,@i from CATE_TREE WHERE (PARENT_ID IS NULL) AND STATE<>'invalid'
END
  
    while @@rowcount<>0   
    begin  
        set @i = @i + 1   
        insert into @t   
        select  
            a.CATE_NODE_ID,a.CATE_CODE,a.CATE_NODE_NAME,b.nodeTreeName+'>'+a.CATE_NODE_NAME,a.CATE_DESC,A.PARENT_ID,A.ROOT_ID,A.ORDERID,@i   
        from  
            CATE_TREE a,@t b   
        where  
            a.PARENT_ID=b.CATE_NODE_ID AND a.STATE<>'invalid' and b.nodeLevel = @i-1   
    end  
    return 
END谢谢

Answer 1

优化建议:
1.结构优化,尽量使用集合的查询,减少查询的次数
2.函数[getTree],可以试试用CTE改写,支持递归,效率比较高

Answer 2

这么复杂，真是很难找到原因，对于最后那句
select t.*,t1.行业名称,t1.行业id,t3.区域,t3.区域id,t3.区域上级id,t4.产品名称,t4.产品id from t left  join t1 on t1.人员id=t.人员id left join t3 on t3.人员id=t.人员id left join t4 on t4.人员id=t.人员id
你每次只查一个表，或者两个，这样试试看

Answer 3

2.函数[getTree],可以试试用CTE改写,支持递归,效率比较高
请问这个怎么写呢？

Answer 4

从你的函数getTree里看，无论如何都至少有一条记录
那么这个条件and EXISTS (SELECT 1 FROM dbo.gettree(10064)   WHERE    cate_node_id= a.cate_node_id)
就没什么意思了