优化SQL语句

[aaajedll]

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秒以内,请高手帮忙

[中国风]

改改連接方法，子查詢放在連接處

[gw6328]

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

[AcHerat]

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

[gw6328]

[Quote=引用 5 楼 acherat 的回复:]
SQL code

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

[中国风]

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

[中国风]

樓主測測以上腳本的時間和結果

[aaajedll]

[Quote=引用 8 楼 roy_88 的回复:]
樓主測測以上腳本的時間和結果
[/Quote]

不好意思,有点错误,我先改一下,再测试速度

[aaajedll]

[Quote=引用 8 楼 roy_88 的回复:]
樓主測測以上腳本的時間和結果
[/Quote]
执行时间为11秒,快了2秒,详细看下图

[gw6328]

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

[aaajedll]

忘记缩小图片了,看不到执行时间

[aaajedll]

[Quote=引用 11 楼 jinfengyiye 的回复:]
后面ri都带了条件，楼主有没有考虑把 left join 改成inner join
[/Quote]
因为必须以左边为主,要不然数据就会变少了,业务逻辑不对了

[AcHerat]

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

[叶子]

count太多，要多次扫描表。

[aaajedll]

[Quote=引用 14 楼 acherat 的回复:]
SQL code

SELECT
ri.[iID] AS ResumeID,
ri.[iUserID] AS UserID,
ri.[tiActive],
ri.[nvcName] ResumeName,
ri.[tiPublicLvl],
ri.[tiActive],
CASE
W……
[/Quote]

你的是8秒

[AcHerat]

[Quote=引用 16 楼 aaajedll 的回复:]
引用 14 楼 acherat 的回复:
SQL code

SELECT
ri.[iID] AS ResumeID,
ri.[iUserID] AS UserID,
ri.[tiActive],
ri.[nvcName] ResumeName,
ri.[tiPublicLvl],
ri.[tiActive],
CASE
W……


你的是8秒
[/Quote]

LZ确定该建的索引都建呢？

[aaajedll]

[Quote=引用 17 楼 acherat 的回复:]
引用 16 楼 aaajedll 的回复:
引用 14 楼 acherat 的回复:
SQL code

SELECT
ri.[iID] AS ResumeID,
ri.[iUserID] AS UserID,
ri.[tiActive],
ri.[nvcName] ResumeName,
ri.[tiPublicLvl],
ri.[tiActive],
CASE
W……
……
[/Quote]
是的,关联的字段,都是主键,主键默认是建了索引的

[中国风]

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

[toumingkongjian]

还是把执行计划贴出来看下把

[rfq]

执行计划 好好看看

[一品梅]

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