下面是公司一储存过程,查询速度很慢,如果按2009-05-08 8:00 到2009-12-31 8:00 查询足足要2分多钟,可以查询出来就10条数据,希望高手帮忙看下,下面哪些地方还可以优化的。set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--===================================================
-- 工序过账记录表:实时记录
--
-- EXEC USP_REPORT219 '2010-01-01 8:00','2010-01-25 8:00','','','','','','',''
--===================================================
ALTER PROCEDURE [dbo].[USP_REPORT219]
(
@IN_TIME_FROM VARCHAR(20), --入账时间
@IN_TIME_TO VARCHAR(20), --入账时间
@CUSTOMER_PART_NUMBER VARCHAR(20)='',--生产型号
@CP_REV VARCHAR(2)='', --版本
@FROM_DEPT_CODE VARCHAR(10)='', --入账工序编号
@FROM_DEPT_NAME VARCHAR(30)='', --入账工序
@TO_DEPT_CODE VARCHAR(10)='', --出账工序编号
@TO_DEPT_NAME VARCHAR(30)='' , --出账工序
@WORK_ORDER_NUMBER VARCHAR(30)='' --工单
)
AS
SET NOCOUNT ON SELECT
RKEY,
TPUT_PTR,
WO_PTR,
TDATE,
TTIME,
QTY_PROD,
QTY_REJECT
INTO #D48
FROM DATA0048 WITH(NOLOCK)
WHERE CONVERT(CHAR(10),TDATE,120) BETWEEN LEFT(@IN_TIME_FROM,10) AND LEFT(@IN_TIME_TO,10)
SELECT
DATA0006.WORK_ORDER_NUMBER,
DATA0060.SALES_ORDER,
DATA0050.CUSTOMER_PART_NUMBER,
DATA0050.CP_REV,
DATA0034_1.DEPT_NAME TO_DEPT_NAME,
DATA0002.UNIT_NAME,
(DATA0048.QTY_PROD+DATA0056.QTY_REJECTED) TO_DEPT_QTY,
DATA0056.QTY_REJECTED,
DATA0034_2.DEPT_NAME FROM_DEPT_NAME,
CASE WHEN DATA0034_1.DEPT_NAME IS NOT NULL THEN DATA0048.QTY_PROD ELSE 0 END FROM_DEPT_QTY,
CONVERT(CHAR(20),DBO.TODATETIME(DATA0048.TDATE,DATA0048.TTIME),120) TDATE,
DATA0005.EMPLOYEE_NAME,
DATA0050.REPORT_UNIT_VALUE3*(DATA0048.QTY_PROD+DATA0048.QTY_REJECT) TO_DEPT_FT2,
DATA0047_1.UNIT_VALUE PCWP
FROM DATA0057 WITH(NOLOCK)
LEFT JOIN DATA0005 WITH(NOLOCK) ON DATA0057.EMPL_PTR = DATA0005.RKEY
JOIN
(
SELECT * FROM #D48
WHERE CONVERT(CHAR(20),DBO.TODATETIME(TDATE,TTIME),120) BETWEEN @IN_TIME_FROM AND @IN_TIME_TO
)DATA0048 ON DATA0048.RKEY = DATA0057.TRAN_PTR
JOIN DATA0056 WITH(NOLOCK) ON DATA0056.RKEY = DATA0048.TPUT_PTR
JOIN DATA0034 DATA0034_1 WITH(NOLOCK) ON DATA0034_1.RKEY = DATA0056.D_G_W_PTR
LEFT JOIN DATA0034 DATA0034_2 WITH(NOLOCK) ON DATA0034_2.RKEY = DATA0056.NEXT_DEPT_PTR
LEFT JOIN DATA0002 WITH (NOLOCK) ON DATA0002.RKEY = DATA0034_1.UNIT_PTR
JOIN
(
SELECT * FROM DATA0006 WITH(NOLOCK)
WHERE WORK_ORDER_NUMBER LIKE '%'+RTRIM(@WORK_ORDER_NUMBER)+'%'
)DATA0006 ON DATA0006.RKEY = DATA0048.WO_PTR
JOIN
(
SELECT * FROM DATA0050 WITH(NOLOCK)
WHERE RKEY = PRODUCTION_PART_PTR
AND CUSTOMER_PART_NUMBER LIKE '%'+RTRIM(@CUSTOMER_PART_NUMBER)+'%'
AND CP_REV LIKE '%'+RTRIM(@CP_REV)+'%'
)DATA0050 ON DATA0050.RKEY = DATA0006.CUST_PART_PTR
JOIN DATA0047 DATA0047_1 WITH(NOLOCK) ON (DATA0047_1.TTYPE=2) AND (DATA0047_1.SOURCE_POINTER=DATA0050.RKEY) AND (DATA0047_1.UNIT_POINTER=4 ) --单元/生产板
LEFT JOIN
(
SELECT
MAX(SO_PTR)SO_PTR,WO_PTR
FROM DATA0054 WITH(NOLOCK)
GROUP BY WO_PTR
)DATA0054 ON (CASE WHEN SUBSTRING(DATA0006.WORK_ORDER_NUMBER,1,1)IN('A') OR RIGHT(WORK_ORDER_NUMBER,3)<>'000'
THEN DATA0006.PARENT_PTR ELSE DATA0006.RKEY END)= DATA0054.WO_PTR
LEFT JOIN DATA0060 WITH (NOLOCK) ON DATA0060.RKEY = DATA0054.SO_PTR
WHERE ISNULL(DATA0034_1.DEPT_CODE,'') LIKE '%'+RTRIM(@TO_DEPT_CODE)+'%'
AND ISNULL(DATA0034_1.DEPT_NAME,'') LIKE '%'+RTRIM(@TO_DEPT_NAME)+'%'
AND ISNULL(DATA0034_2.DEPT_CODE,'') LIKE '%'+RTRIM(@FROM_DEPT_CODE)+'%'
AND ISNULL(DATA0034_2.DEPT_NAME,'') LIKE '%'+RTRIM(@FROM_DEPT_NAME)+'%' SET NOCOUNT OFF
set QUOTED_IDENTIFIER ON
GO
--===================================================
-- 工序过账记录表:实时记录
--
-- EXEC USP_REPORT219 '2010-01-01 8:00','2010-01-25 8:00','','','','','','',''
--===================================================
ALTER PROCEDURE [dbo].[USP_REPORT219]
(
@IN_TIME_FROM VARCHAR(20), --入账时间
@IN_TIME_TO VARCHAR(20), --入账时间
@CUSTOMER_PART_NUMBER VARCHAR(20)='',--生产型号
@CP_REV VARCHAR(2)='', --版本
@FROM_DEPT_CODE VARCHAR(10)='', --入账工序编号
@FROM_DEPT_NAME VARCHAR(30)='', --入账工序
@TO_DEPT_CODE VARCHAR(10)='', --出账工序编号
@TO_DEPT_NAME VARCHAR(30)='' , --出账工序
@WORK_ORDER_NUMBER VARCHAR(30)='' --工单
)
AS
SET NOCOUNT ON SELECT
RKEY,
TPUT_PTR,
WO_PTR,
TDATE,
TTIME,
QTY_PROD,
QTY_REJECT
INTO #D48
FROM DATA0048 WITH(NOLOCK)
WHERE CONVERT(CHAR(10),TDATE,120) BETWEEN LEFT(@IN_TIME_FROM,10) AND LEFT(@IN_TIME_TO,10)
SELECT
DATA0006.WORK_ORDER_NUMBER,
DATA0060.SALES_ORDER,
DATA0050.CUSTOMER_PART_NUMBER,
DATA0050.CP_REV,
DATA0034_1.DEPT_NAME TO_DEPT_NAME,
DATA0002.UNIT_NAME,
(DATA0048.QTY_PROD+DATA0056.QTY_REJECTED) TO_DEPT_QTY,
DATA0056.QTY_REJECTED,
DATA0034_2.DEPT_NAME FROM_DEPT_NAME,
CASE WHEN DATA0034_1.DEPT_NAME IS NOT NULL THEN DATA0048.QTY_PROD ELSE 0 END FROM_DEPT_QTY,
CONVERT(CHAR(20),DBO.TODATETIME(DATA0048.TDATE,DATA0048.TTIME),120) TDATE,
DATA0005.EMPLOYEE_NAME,
DATA0050.REPORT_UNIT_VALUE3*(DATA0048.QTY_PROD+DATA0048.QTY_REJECT) TO_DEPT_FT2,
DATA0047_1.UNIT_VALUE PCWP
FROM DATA0057 WITH(NOLOCK)
LEFT JOIN DATA0005 WITH(NOLOCK) ON DATA0057.EMPL_PTR = DATA0005.RKEY
JOIN
(
SELECT * FROM #D48
WHERE CONVERT(CHAR(20),DBO.TODATETIME(TDATE,TTIME),120) BETWEEN @IN_TIME_FROM AND @IN_TIME_TO
)DATA0048 ON DATA0048.RKEY = DATA0057.TRAN_PTR
JOIN DATA0056 WITH(NOLOCK) ON DATA0056.RKEY = DATA0048.TPUT_PTR
JOIN DATA0034 DATA0034_1 WITH(NOLOCK) ON DATA0034_1.RKEY = DATA0056.D_G_W_PTR
LEFT JOIN DATA0034 DATA0034_2 WITH(NOLOCK) ON DATA0034_2.RKEY = DATA0056.NEXT_DEPT_PTR
LEFT JOIN DATA0002 WITH (NOLOCK) ON DATA0002.RKEY = DATA0034_1.UNIT_PTR
JOIN
(
SELECT * FROM DATA0006 WITH(NOLOCK)
WHERE WORK_ORDER_NUMBER LIKE '%'+RTRIM(@WORK_ORDER_NUMBER)+'%'
)DATA0006 ON DATA0006.RKEY = DATA0048.WO_PTR
JOIN
(
SELECT * FROM DATA0050 WITH(NOLOCK)
WHERE RKEY = PRODUCTION_PART_PTR
AND CUSTOMER_PART_NUMBER LIKE '%'+RTRIM(@CUSTOMER_PART_NUMBER)+'%'
AND CP_REV LIKE '%'+RTRIM(@CP_REV)+'%'
)DATA0050 ON DATA0050.RKEY = DATA0006.CUST_PART_PTR
JOIN DATA0047 DATA0047_1 WITH(NOLOCK) ON (DATA0047_1.TTYPE=2) AND (DATA0047_1.SOURCE_POINTER=DATA0050.RKEY) AND (DATA0047_1.UNIT_POINTER=4 ) --单元/生产板
LEFT JOIN
(
SELECT
MAX(SO_PTR)SO_PTR,WO_PTR
FROM DATA0054 WITH(NOLOCK)
GROUP BY WO_PTR
)DATA0054 ON (CASE WHEN SUBSTRING(DATA0006.WORK_ORDER_NUMBER,1,1)IN('A') OR RIGHT(WORK_ORDER_NUMBER,3)<>'000'
THEN DATA0006.PARENT_PTR ELSE DATA0006.RKEY END)= DATA0054.WO_PTR
LEFT JOIN DATA0060 WITH (NOLOCK) ON DATA0060.RKEY = DATA0054.SO_PTR
WHERE ISNULL(DATA0034_1.DEPT_CODE,'') LIKE '%'+RTRIM(@TO_DEPT_CODE)+'%'
AND ISNULL(DATA0034_1.DEPT_NAME,'') LIKE '%'+RTRIM(@TO_DEPT_NAME)+'%'
AND ISNULL(DATA0034_2.DEPT_CODE,'') LIKE '%'+RTRIM(@FROM_DEPT_CODE)+'%'
AND ISNULL(DATA0034_2.DEPT_NAME,'') LIKE '%'+RTRIM(@FROM_DEPT_NAME)+'%' SET NOCOUNT OFF
AND ISNULL(DATA0034_1.DEPT_NAME,'') LIKE '%'+RTRIM(@TO_DEPT_NAME)+'%'
AND ISNULL(DATA0034_2.DEPT_CODE,'') LIKE '%'+RTRIM(@FROM_DEPT_CODE)+'%'
AND ISNULL(DATA0034_2.DEPT_NAME,'') LIKE '%'+RTRIM(@FROM_DEPT_NAME)+'%'
你这样的LIKE语句就是有索引也用不到,而且连接还那么多,条件上加上相应索引试试看咧,
05以上可以用参数标志,或强制参数化