请各位大神帮忙看一个SP,那些地方可以优化提高效率
[code=SQL][/code]
DROP PROCEDURE [dbo].[SP_ODRFM0000150]
GO
CREATE PROCEDURE [dbo].[SP_ODRFM0000150]
--SP參數：
@RPT_NAME NVARCHAR(50), --報表名稱
@SDATE DATETIME, --統計日期起
@EDATE DATETIME, --統計日期迄
@FIRST_DEPT_ID NVARCHAR(50), --一層機關
@OD_OU_ID NVARCHAR(MAX), --承辦機關/單位
@COMP_ID NVARCHAR(50), --公司代號
@TYPE NVARCHAR(10), --報表類別：YEAT年報表、MONTH月報表
@COMP_COD NVARCHAR(50), --列印公司代號
@USR_COD NVARCHAR(50), --列印人員
@DateType NVARCHAR(10),
@QUERY_YEAR_NO NVARCHAR(10),--年度號
@ARCH_DEPT NVARCHAR(20),--檔案室號
@INPUT_DT NVARCHAR(50),
@UnitHaveDesk NVARCHAR(50) --是否科室無登記桌
AS
BEGIN
DECLARE @COMP_NAME NVARCHAR(50)
DECLARE @USR_NAME NVARCHAR(50)
DECLARE @REPORT_YEAR NVARCHAR(50)
DECLARE @FIRST_DEPT_NAME NVARCHAR(50)
SELECT @COMP_NAME=dbo.GetCompName(@COMP_COD)
,@USR_NAME=dbo.GetUsrName(@COMP_COD,@USR_COD)
,@FIRST_DEPT_NAME=dbo.GetOUName(@COMP_COD,@FIRST_DEPT_ID)
IF @TYPE = 'YEAR' --年報表
BEGIN
--刪除舊資料
DELETE FROM ODRFM0000150 WHERE COMP_COD = @COMP_COD AND USR_COD = @USR_COD
--
IF @OD_OU_ID<>''
BEGIN
INSERT INTO ODRFM0000150
(COMP_COD,USR_COD,RPT_USR,RPT_COMP,RPT_DT,INPUT_DT,INPUT_YEAR,COLUMN_ID,COLUMN_NAME
,MON1,MON2,MON3,MON4,MON5,MON6,MON7,MON8,MON9,MON10,MON11,MON12
,NO_DATA)
SELECT @COMP_COD,@USR_COD,@USR_NAME,@RPT_NAME,dbo.ConvertDate(getdate(),@DateType,'N')
,@INPUT_DT,@QUERY_YEAR_NO,T1.OD_OU_ID,T1.OU_NAME
,SUM(CASE MONTH(CATLOG_DT) WHEN '1' THEN 1 ELSE 0 END) AS MON1
,SUM(CASE MONTH(CATLOG_DT) WHEN '2' THEN 1 ELSE 0 END) AS MON2
,SUM(CASE MONTH(CATLOG_DT) WHEN '3' THEN 1 ELSE 0 END) AS MON3
,SUM(CASE MONTH(CATLOG_DT) WHEN '4' THEN 1 ELSE 0 END) AS MON4
,SUM(CASE MONTH(CATLOG_DT) WHEN '5' THEN 1 ELSE 0 END) AS MON5
,SUM(CASE MONTH(CATLOG_DT) WHEN '6' THEN 1 ELSE 0 END) AS MON6
,SUM(CASE MONTH(CATLOG_DT) WHEN '7' THEN 1 ELSE 0 END) AS MON7
,SUM(CASE MONTH(CATLOG_DT) WHEN '8' THEN 1 ELSE 0 END) AS MON8
,SUM(CASE MONTH(CATLOG_DT) WHEN '9' THEN 1 ELSE 0 END) AS MON9
,SUM(CASE MONTH(CATLOG_DT) WHEN '10' THEN 1 ELSE 0 END) AS MON10
,SUM(CASE MONTH(CATLOG_DT) WHEN '11' THEN 1 ELSE 0 END) AS MON11
,SUM(CASE MONTH(CATLOG_DT) WHEN '12' THEN 1 ELSE 0 END) AS MON12
,''
FROM ODVORG_UNIT T1(NOLOCK)
LEFT JOIN ( SELECT COMP_ID,CATLOG_DT,HDL_DEPT_IDENT,HDL_UNIT_IDENT,dbo.GetDisplayHdlUnit_DEPT(COMP_ID,CNO_CODE) AS REAL_DEPT_IDENT FROM FMMITEM(NOLOCK)
WHERE COMP_ID=@COMP_ID AND ARCH_DEPT=@ARCH_DEPT
AND CATLOG_DT>=@SDATE
AND CATLOG_DT<=@EDATE
AND YEAR_NO=@QUERY_YEAR_NO
AND FILE_STAT IN ('2','3'))T2
ON T1.OU_COMP_ID=T2.COMP_ID AND(CASE @UnitHaveDesk WHEN 'Y' THEN T2.HDL_DEPT_IDENT
ELSE T2.REAL_DEPT_IDENT END)=T1.OD_OU_ID
WHERE T1.OU_COMP_ID= @COMP_ID
AND T1.OU_IS_ENABLE='Y'
AND T1.OUTORG_UNIT=''
AND T1.STRUCT_CODE<>''
AND (CASE WHEN @OD_OU_ID='' THEN '##'+T1.OD_OU_ID+'##' ELSE @OD_OU_ID END) LIKE '%##'+T1.OD_OU_ID+'##%'
GROUP BY T1.OU_NAME,T1.OD_OU_ID
ORDER BY T1.OD_OU_ID
END
IF @FIRST_DEPT_ID<>''
BEGIN
INSERT INTO ODRFM0000150
(COMP_COD,USR_COD,RPT_USR,RPT_COMP,RPT_DT,INPUT_DT,INPUT_YEAR,COLUMN_ID,COLUMN_NAME
,MON1,MON2,MON3,MON4,MON5,MON6,MON7,MON8,MON9,MON10,MON11,MON12
,NO_DATA)
SELECT @COMP_COD,@USR_COD,@USR_NAME,@RPT_NAME,dbo.ConvertDate(getdate(),@DateType,'N')
,@INPUT_DT,@QUERY_YEAR_NO,@FIRST_DEPT_ID,@FIRST_DEPT_NAME
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '1' THEN 1 ELSE 0 END),0) AS MON1
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '2' THEN 1 ELSE 0 END),0) AS MON2
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '3' THEN 1 ELSE 0 END),0) AS MON3
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '4' THEN 1 ELSE 0 END),0) AS MON4
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '5' THEN 1 ELSE 0 END),0) AS MON5
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '6' THEN 1 ELSE 0 END),0) AS MON6
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '7' THEN 1 ELSE 0 END),0) AS MON7
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '8' THEN 1 ELSE 0 END),0) AS MON8
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '9' THEN 1 ELSE 0 END),0) AS MON9
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '10' THEN 1 ELSE 0 END),0) AS MON10
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '11' THEN 1 ELSE 0 END),0) AS MON11
,ISNULL(SUM(CASE MONTH(CATLOG_DT) WHEN '12' THEN 1 ELSE 0 END),0) AS MON12
,''
FROM( SELECT COMP_ID,CATLOG_DT,HDL_DEPT_IDENT FROM FMMITEM(NOLOCK)
WHERE COMP_ID=@COMP_ID AND ARCH_DEPT=@ARCH_DEPT
AND CATLOG_DT>=@SDATE
AND CATLOG_DT<=@EDATE
AND YEAR_NO=@QUERY_YEAR_NO
AND FILE_STAT IN ('2','3')
AND dbo.GetDisplayHdlUnit_DEPT(COMP_ID,CNO_CODE)=''
AND HDL_DEPT_IDENT=@FIRST_DEPT_ID
)T2
END
END
ELSE --月報表
BEGIN
--刪除舊資料
DELETE FROM ODRFM0000150_DAY WHERE COMP_COD = @COMP_COD AND USR_COD = @USR_COD
--
DECLARE @MAX_DAYS INT
DECLARE @intDAYS INT
SELECT @MAX_DAYS=DAY(@EDATE)
SET @intDAYS = 1
IF @OD_OU_ID<>''
BEGIN
WHILE @intDAYS<=@MAX_DAYS
BEGIN
INSERT INTO ODRFM0000150_DAY
(COLUMN_ID,COLUMN_NAME,THEDAY,NUM,INPUT_DT,INPUT_YEAR,RPT_COMP,RPT_USR,RPT_DT,NO_DATA,COMP_COD,USR_COD)
SELECT T1.OD_OU_ID,T1.OU_NAME,@intDAYS,0,@INPUT_DT,@QUERY_YEAR_NO,@RPT_NAME,@USR_COD,dbo.ConvertDate(GETDATE(),'2','Y'),'',@COMP_COD,@USR_COD
FROM ODVORG_UNIT T1(NOLOCK)
WHERE T1.OU_COMP_ID= @COMP_ID
AND T1.OU_IS_ENABLE='Y'
AND T1.OUTORG_UNIT=''
AND T1.STRUCT_CODE<>''
AND (CASE WHEN @OD_OU_ID='' THEN '##'+T1.OD_OU_ID+'##' ELSE @OD_OU_ID END) LIKE '%##'+T1.OD_OU_ID+'##%'
GROUP BY T1.OU_NAME,T1.OD_OU_ID
ORDER BY T1.OD_OU_ID
SET @intDAYS=@intDAYS+1
END
END
IF @FIRST_DEPT_ID<>''
SET @intDAYS = 1
BEGIN
WHILE @intDAYS<=@MAX_DAYS
BEGIN
INSERT INTO ODRFM0000150_DAY
(COLUMN_ID,COLUMN_NAME,THEDAY,NUM,INPUT_DT,INPUT_YEAR,RPT_COMP,RPT_USR,RPT_DT,NO_DATA,COMP_COD,USR_COD)
SELECT @FIRST_DEPT_ID,@FIRST_DEPT_NAME,@intDAYS,0,@INPUT_DT,@QUERY_YEAR_NO,@RPT_NAME,@USR_COD,dbo.ConvertDate(GETDATE(),'2','Y'),'',@COMP_COD,@USR_COD
SET @intDAYS=@intDAYS+1
END
END
--UPDATE 資料
UPDATE ODRFM0000150_DAY
SET NUM=(SELECT COUNT(1) FROM (SELECT COMP_ID,CATLOG_DT,HDL_DEPT_IDENT,HDL_UNIT_IDENT,dbo.GetDisplayHdlUnit_DEPT(COMP_ID,CNO_CODE) AS REAL_DEPT_IDENT FROM FMMITEM(NOLOCK)
WHERE COMP_ID=@COMP_ID AND ARCH_DEPT=@ARCH_DEPT
AND CATLOG_DT>=@SDATE
AND CATLOG_DT<=@EDATE
AND YEAR_NO=@QUERY_YEAR_NO
AND FILE_STAT IN ('2','3'))T2
WHERE (CASE @UnitHaveDesk WHEN 'Y' THEN T2.HDL_DEPT_IDENT WHEN 'Y1' THEN (CASE WHEN REAL_DEPT_IDENT ='' OR REAL_DEPT_IDENT IS NULL THEN T2.HDL_DEPT_IDENT ELSE T2.REAL_DEPT_IDENT END) ELSE T2.REAL_DEPT_IDENT END)=ODRFM0000150_DAY.COLUMN_ID
AND DAY(CATLOG_DT)=ODRFM0000150_DAY.THEDAY
)
END
end
[解决办法]
这么长？？子查询的能不能放入临时表？
[解决办法]
子查询放临时表。
[解决办法]
