SQL Server数据库的代码优化实例

本文我们主要介绍了一个SQL Server数据库代码优化的实例，优化前的代码如下：

定义表变量  
declare @TenantId INT  
select @TenantId=100001 
DECLARE @temp1 table(  
aType INT DEFAULT(0),  
mon int,取今天与入库时间的月份差,如SELECT DATEDIFF(MONTH,'2010-7-1',GETDATE()) = 2  
total int  
)  
DECLARE @temp2 table(  
aType int NOT NULL default 0,  
mon varchar(40),  
total int)  
DECLARE @stat_date DATETIME  
DEClARE @Index int  
declare @s varchar(4000), @sql varchar(4000)  
SET @s = '' 
SET @Index=1 
SET @stat_date = GETDATE()-150  
遍历出要统计的月份  
WHILE month(@stat_date) <= month(GETDATE())  
BEGIN  
Select @s=@s+ 'M'+convert(varchar(20),@Index)+ ' = max(case when mon = '+QUOTENAME(left(CONVERT(varchar,@stat_date,102),7),'''')+ ' then total else 0 end),'  
SET @stat_date=DATEADD(MONTH, 1, @stat_date)  
SET @Index=@Index+1  
END  
Select @s= SUBSTRING(@s,0,len(@s))  
print @s  
应聘总数  
insert into @temp1(aType,mon,total)  
select 1,DATEDIFF(MONTH,CreateDate,GETDATE()) AS mon,COUNT(*) total from [REL_PersonJobStoreDB] where TenantId=@TenantId group by DATEDIFF(MONTH,CreateDate,GETDATE())  
IF(NOT EXISTS(SELECT 1 FROM @temp1))  
BEGIN  
insert into @temp1(aType,mon,total)  
SELECT 1,-1,0  
END  
匹配应聘标识号  
Update @temp1 set aType=1 
已录用人数  
insert into @temp1(aType,mon,total)  
select 2,DATEDIFF(MONTH,CreateDate,GETDATE()),COUNT(*) total from [REL_PersonJobStoreDB] where TenantId=@TenantId and PhaseId = 4 group by DATEDIFF(MONTH,CreateDate,GETDATE())  
IF(NOT EXISTS(SELECT 1 FROM @temp1 WHERE aType=2))  
BEGIN  
insert into @temp1(aType,mon,total)  
SELECT 2,-1,0  
END  
SELECT * FROM @temp1  
匹配应聘标识号  
Update @temp2 set aType=2 
合并表数据  
insert into @temp1 select * from @temp2  
select * from @temp1  
DECLARE @DATE DATETIME  
SET @DATE = GETDATE()  
SELECT GETDATE(),DATEADD(MONTH,-1,GETDATE()),DATEDIFF(MONTH,GETDATE(),DATEADD(MONTH,1,GETDATE()))  
select aType,mon,avg(total) total from @temp1  
group by atype,mon  
select [aType],  
M1 = max(case when mon = 6 then total else 0 end),  
M2 = max(case when mon = 5 then total else 0 end),  
M3 = max(case when mon = 4 then total else 0 end),  
M4 = max(case when mon = 3 then total else 0 end),  
M5 = max(case when mon = 2 then total else 0 end),  
M6 = max(case when mon = 1 then total else 0 end)  
from  
(  
select aType,mon,avg(total) total from @temp1  
group by atype,mon  
) aa  
group by [aType]  
print @sql  
END

优化后的代码如下：

ALTER PROCEDURE [dbo].[Report_ApplyStat]  
@TenantId int  
AS  
BEGIN  
/* 模板表 */  
DECLARE @TEMP TABLE([aType] INT)  
INSERT INTO @TEMP([aType])VALUES(1),(2);  
/* 查询条件 */  
WITH CONDITION AS(  
SELECT  
SC.PersonId,  
ISNULL(RE.PhaseId,0) AS [PhaseId],  
DATEDIFF(MONTH,SC.ApplyDate,GETDATE()) AS DIFF  
FROM SearchCV SC  
LEFT JOIN [REL_PersonJobStoreDB] RE ON SC.PersonId = RE.PersonId  
WHERE SC.TenantId = @TenantId),  
[ApplyCount] AS(SELECT 1 AS [TYPE],DATEDIFF(MONTH,ApplyDate,GETDATE()) AS DIFF,(PersonId) AS [ApplyCount] FROM SearchCV WHERE TenantId = @TenantId),  
[OfferCount] AS(SELECT 2 AS [TYPE],DIFF,(PersonId) AS [OfferCount] FROM CONDITION WHERE PhaseId = 4 ),  
[Result] AS(  
SELECT [TYPE] AS [aType],[1],[2],[3],[4],[5],[6] FROM [ApplyCount] PIVOT(COUNT([ApplyCount]) FOR DIFF IN([1],[2],[3],[4],[5],[6]))AS T UNION ALL  
SELECT [TYPE] AS [aType],[1],[2],[3],[4],[5],[6] FROM [OfferCount] PIVOT(COUNT([OfferCount]) FOR DIFF IN([1],[2],[3],[4],[5],[6]))AS T )  
SELECT  
TP.[aType] AS [aType],  
ISNULL(RE.[6],0) AS [M1],  
ISNULL(RE.[5],0) AS [M2],  
ISNULL(RE.[4],0) AS [M3],  
ISNULL(RE.[3],0) AS [M4],  
ISNULL(RE.[2],0) AS [M5],  
ISNULL(RE.[1],0) AS [M6]  
FROM @TEMP TP  
LEFT JOIN [Result] RE ON TP.[aType] = RE.[aType] Order by TP.aType