优化SQL语句

Question

SELECT 
ri.[iID] AS ResumeID,
ri.[iUserID] AS UserID,
ri.[tiActive],  
ri.[nvcName] ResumeName,  
ri.[tiPublicLvl],  
ri.[tiActive],  
CASE   
WHEN ri.[tiPublicLvl] = 1 THEN '完全公开'   
WHEN ri.[tiPublicLvl] = 2 THEN '对51公开'   
ELSE '完全保密' 
END ResumeState,  
CASE   
WHEN ri.[tiFullLvl] = 0 THEN '未完整'   
WHEN ri.[tiFullLvl] = 1 THEN '基本完整'   
WHEN ri.[tiFullLvl] = 2 THEN '比较完整'   
ELSE '非常完整' 
END ResumeEntire,  
CASE   
WHEN ri.[tiActive] = 0 THEN '未激活'   
ELSE '已激活' 
END tiActiveName,  
CONVERT(NVARCHAR(10),ri.[dtUpdateTime],120) AS UpdateTime,   
CONVERT(NVARCHAR(10),ri.[dtCreateTime],120) AS CreateTime,  
ISNULL(rr.BrowseResumeCount,0) AS BrowseResumeCount,  
t_rin.[Progress],  
t_rin.[Qualified]  
FROM ps_s_ResumeInfo ri LEFT JOIN (
SELECT 
rr.[ResumeID],
COUNT(rr.[ResumeID]) AS BrowseResumeCount  
FROM ps_s_ResumeRead rr,ps_s_ResumeInfo ri  
WHERE rr.ResumeID = ri.[iID] AND ri.[iUserID] = 395007 
GROUP BY rr.[ResumeID]) rr ON ri.[iID] = rr.[ResumeID]   LEFT JOIN (  
SELECT   
t_ri.[iID],(
CASE 
WHEN (SELECT COUNT([iID]) FROM ps_s_ResumeInfo WHERE [iID] = t_ri.IID) > 0 THEN 48 
ELSE 0 
END +         
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeWill WHERE [iResumeID] = t_ri.IID) > 0 THEN 10 
ELSE 0 
END +       
CASE 
WHEN t_ri.ResumeType = 0 THEN 
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeWrok WHERE [iResumeID] = t_ri.IID) > 0 THEN 10 
ELSE 0 
END      
ELSE          
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeEducation WHERE [iResumeID] = t_ri.IID) > 0 THEN 10 
ELSE 0 
END      
END  ) AS [Qualified],(       
CASE 
WHEN t_ri.ResumeType = 0 THEN 
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeEducation WHERE [iResumeID] = t_ri.IID) > 0 THEN 10 
ELSE 0 
END      
ELSE          
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeWrok WHERE [iResumeID] = t_ri.IID) > 0 THEN 10 
ELSE 0 
END      
END +      
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeTrain WHERE [iResumeID] = t_ri.IID) > 0 THEN 5 
ELSE 0 
END +        
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeOther WHERE [iResumeID] = t_ri.IID) > 0 THEN 3 
ELSE 0 
END +        
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeProj WHERE [iResumeID] = t_ri.IID) > 0 THEN 6 
ELSE 0 
END +        
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeLang WHERE [iResumeID] = t_ri.IID) > 0 THEN 5 
ELSE 0 
END +         
CASE 
WHEN (SELECT COUNT([iResumeID]) FROM ps_d_ResumeCert WHERE [iResumeID] = t_ri.IID) > 0 THEN 3 
ELSE 0 
END   )  AS [Progress]    
FROM ps_s_ResumeInfo t_ri WHERE [iUserID] = 395007) t_rin  ON ri.[iID] = t_rin.[iID]  
WHERE ri.[iUserID] = 395007 ORDER BY ri.[dtUpdateTime] DESC执行要花13秒,速度太慢无法接受,能不能优化一下在5秒以内,请高手帮忙

Answer 1

count太多了
可以建一个表的对应各个IID 是否存在
存在就有数字，不存在就0这样算可能会快一些。

Answer 2

--那个查询里的count只要是大于0就赋一个值，个人觉得写count效率不好，用not exists
--所有的count改为
case when not exists(select 1 from tb where tb.id = t.id) then 0 else [你的值] end

Answer 3

这样那就用 exists吧！
case when exists(select 1 from tb where tb.id = t.id) then  [你的值] else 0 end

Answer 4

SELECT 
    ri.[iID] AS ResumeID,
    ri.[iUserID] AS UserID,
    ri.[tiActive],  
    ri.[nvcName] ResumeName,  
    ri.[tiPublicLvl],  
    ri.[tiActive],  
    CASE   
        WHEN ri.[tiPublicLvl] = 1 THEN '完全公开'   
        WHEN ri.[tiPublicLvl] = 2 THEN '对51公开'   
        ELSE '完全保密' 
    END ResumeState,  
    CASE   
        WHEN ri.[tiFullLvl] = 0 THEN '未完整'   
        WHEN ri.[tiFullLvl] = 1 THEN '基本完整'   
        WHEN ri.[tiFullLvl] = 2 THEN '比较完整'   
        ELSE '非常完整' 
    END ResumeEntire,  
    CASE   
        WHEN ri.[tiActive] = 0 THEN '未激活'   
        ELSE '已激活' 
    END tiActiveName,  
    CONVERT(NVARCHAR(10),ri.[dtUpdateTime],120) AS UpdateTime,   
    CONVERT(NVARCHAR(10),ri.[dtCreateTime],120) AS CreateTime,  
    ISNULL(rr.BrowseResumeCount,0) AS BrowseResumeCount,  
    t_rin.[Progress],  
    t_rin.[Qualified]  
    FROM ps_s_ResumeInfo ri 
    LEFT JOIN (
        SELECT   
            t_ri.[iID],(
            CASE 
                WHEN  ISNULL(sp_d1.conPs_s_ResumeInfo,0)> 0 THEN 48 --1
                ELSE 0 
            END +         
            CASE 
                WHEN sp_d9.conPs_d_ResumeWill > 0 THEN 10--9
                ELSE 0 
            END +       
            CASE 
                WHEN t_ri.ResumeType = 0 THEN 
                    CASE 
                        WHEN ISNULL(SP_D2.conPs_d_ResumeWrok,0) > 0 THEN 10 --2
                        ELSE 0 
                    END      
                ELSE          
                    CASE 
                        WHEN ISNULL(sp_d3.conPs_d_ResumeWrok,0) > 0 THEN 10 --3
                        ELSE 0 
                    END      
                END  ) AS [Qualified],(          
                CASE 
                    WHEN t_ri.ResumeType = 0 THEN 
                        CASE 
                            WHEN ISNULL(sp_d3.conPs_d_ResumeEducation,0) > 0 THEN 10 --3
                            ELSE 0 
                        END      
                    ELSE          
                        CASE 
                            WHEN ISNULL(sp_d2.conPs_d_ResumeWrok,0) > 0 THEN 10 --2
                            ELSE 0 
                        END      
                END +      
                CASE 
                    WHEN ISNULL(sp_d4.conPs_d_ResumeTrain,0) > 0 THEN 5 --4
                    ELSE 0 
                END +        
                CASE 
                    WHEN ISNULL(sp_d5.conPs_d_ResumeOther,0) > 0 THEN 3 --5
                    ELSE 0 
                END +        
                CASE 
                    WHEN ISNULL(sp_d6.conPs_d_ResumeProj,0) > 0 THEN 6 --6
                    ELSE 0 
                END +        
                CASE 
                    WHEN ISNULL(sp_d7.conPs_d_ResumeLang,0) > 0 THEN 5 --7
                    ELSE 0 
                END +         
                CASE 
                    WHEN ISNULL(sp_d8.conPs_d_ResumeCert,0) > 0 THEN 3 --8
                    ELSE 0 
                END   )  AS [Progress]    
            FROM ps_s_ResumeInfo t_ri 
LEFT JOIN (SELECT [iID] ,COUNT([iID]) AS conPs_s_ResumeInfo FROM ps_s_ResumeInfo GROUP BY [iID]) AS sp_d1 ON  sp_d1.[iID] = t_ri.IID
LEFT JOIN (SELECT [iResumeID],COUNT([iResumeID]) AS conPs_d_ResumeWrok FROM ps_d_ResumeWrok GROUP BY [iResumeID])AS sp_d2 ON  sp_d2.[iResumeID] = t_ri.IID
LEFT JOIN (SELECT [iResumeID],COUNT([iResumeID]) AS conPs_d_ResumeEducation FROM ps_d_ResumeEducation GROUP BY  [iResumeID])AS sp_d3 ON sp_d3.[iResumeID] = t_ri.IID
LEFT JOIN (SELECT [iResumeID],COUNT([iResumeID]) AS conPs_d_ResumeTrain FROM ps_d_ResumeTrain GROUP BY  [iResumeID])AS sp_d4 ON sp_d4.[iResumeID] = t_ri.IID
LEFT JOIN (SELECT [iResumeID],COUNT([iResumeID]) AS conPs_d_ResumeOther FROM ps_d_ResumeOther GROUP BY  [iResumeID]) AS sp_d5 ON sp_d5.[iResumeID] = t_ri.IID
LEFT JOIN (SELECT [iResumeID],COUNT([iResumeID]) AS conPs_d_ResumeProj FROM ps_d_ResumeProj GROUP BY [iResumeID] )AS sp_d6 ON [iResumeID] = t_ri.IID
LEFT JOIN (SELECT [iResumeID],COUNT([iResumeID]) AS conPs_d_ResumeLang FROM ps_d_ResumeLang GROUP BY  [iResumeID])as sp_d7 ON sp_d7.[iResumeID] = t_ri.IID
LEFT JOIN (SELECT [iResumeID],COUNT([iResumeID]) AS conPs_d_ResumeCert FROM ps_d_ResumeCert GROUP BY [iResumeID] ) AS sp_d8 ON sp_d8.[iResumeID] = t_ri.IID
LEFT JOIN (SELECT [iResumeID],COUNT([iResumeID]) AS conPs_d_ResumeWill FROM ps_d_ResumeWill GROUP BY [iResumeID])AS sp_d9 AS ON  sp_d9.[iResumeID] = t_ri.IID
            WHERE t_ri.[iUserID] = 395007
 ) t_rin  ON ri.[iID] = t_rin.[iID]  
WHERE ri.[iUserID] = 395007 ORDER BY ri.[dtUpdateTime] DESC

Answer 5

后面ri都带了条件，楼主有没有考虑把 left join 改成inner join

Answer 6

SELECT 
    ri.[iID] AS ResumeID,
    ri.[iUserID] AS UserID,
    ri.[tiActive],  
    ri.[nvcName] ResumeName,  
    ri.[tiPublicLvl],  
    ri.[tiActive],  
    CASE   
        WHEN ri.[tiPublicLvl] = 1 THEN '完全公开'   
        WHEN ri.[tiPublicLvl] = 2 THEN '对51公开'   
        ELSE '完全保密' 
    END ResumeState,  
    CASE   
        WHEN ri.[tiFullLvl] = 0 THEN '未完整'   
        WHEN ri.[tiFullLvl] = 1 THEN '基本完整'   
        WHEN ri.[tiFullLvl] = 2 THEN '比较完整'   
        ELSE '非常完整' 
    END ResumeEntire,  
    CASE   
        WHEN ri.[tiActive] = 0 THEN '未激活'   
        ELSE '已激活' 
    END tiActiveName,  
    CONVERT(NVARCHAR(10),ri.[dtUpdateTime],120) AS UpdateTime,   
    CONVERT(NVARCHAR(10),ri.[dtCreateTime],120) AS CreateTime,  
    ISNULL(rr.BrowseResumeCount,0) AS BrowseResumeCount,  
    t_rin.[Progress],  
    t_rin.[Qualified]  
    FROM ps_s_ResumeInfo ri LEFT JOIN (
        SELECT 
            rr.[ResumeID],
            COUNT(rr.[ResumeID]) AS BrowseResumeCount  
            FROM ps_s_ResumeRead rr,ps_s_ResumeInfo ri  
            WHERE rr.ResumeID = ri.[iID] AND ri.[iUserID] = 395007 
            GROUP BY rr.[ResumeID]) rr ON ri.[iID] = rr.[ResumeID]   LEFT JOIN (  
        SELECT   
            t_ri.[iID],(
            CASE 
                WHEN NOT EXISTS(SELECT 1 FROM ps_s_ResumeInfo WHERE [iID] = t_ri.IID) THEN 0 
                ELSE 48 
            END +         
            CASE 
                WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeWill WHERE [iResumeID] = t_ri.IID) THEN 0 
                ELSE 10 
            END +       
            CASE 
                WHEN t_ri.ResumeType = 0 THEN 
                    CASE 
                        WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeWrok WHERE [iResumeID] = t_ri.IID) THEN 0 
                        ELSE 10
                    END      
                ELSE          
                    CASE 
                        WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeEducation WHERE [iResumeID] = t_ri.IID) THEN 0 
                        ELSE 10 
                    END      
                END  ) AS [Qualified],(          
                CASE 
                    WHEN t_ri.ResumeType = 0 THEN 
                        CASE 
                            WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeEducation WHERE [iResumeID] = t_ri.IID) THEN 0 
                            ELSE 10 
                        END      
                    ELSE          
                        CASE 
                            WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeWrok WHERE [iResumeID] = t_ri.IID) THEN 0 
                            ELSE 10 
                        END      
                END +      
                CASE 
                    WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeTrain WHERE [iResumeID] = t_ri.IID) THEN 0 
                    ELSE 5 
                END +        
                CASE 
                    WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeOther WHERE [iResumeID] = t_ri.IID) THEN 0 
                    ELSE 3 
                END +        
                CASE 
                    WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeProj WHERE [iResumeID] = t_ri.IID) THEN 0 
                    ELSE 6 
                END +        
                CASE 
                    WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeLang WHERE [iResumeID] = t_ri.IID) THEN 0 
                    ELSE 5 
                END +         
                CASE 
                    WHEN NOT EXISTS(SELECT 1 FROM ps_d_ResumeCert WHERE [iResumeID] = t_ri.IID) THEN 0 
                    ELSE 3 
                END   )  AS [Progress]    
            FROM ps_s_ResumeInfo t_ri WHERE [iUserID] = 395007) t_rin  ON ri.[iID] = t_rin.[iID]  
WHERE ri.[iUserID] = 395007 ORDER BY ri.[dtUpdateTime] DESC

Answer 7

ps_s_ResumeInfo.iID--是否唯一，如果是唯一
ps_s_ResumeInfo--連接很多於，調用一次就行了 

Answer 8

 ON ri.[iID] = t_rin.[iID] --最外層連接也是多於的

Answer 9

什么需求要写那么复杂的SQL语句