问题1:执行后需要半小时,如果在过程里把@BEGTIME和@ENDTIME 直接用
'2008-01-01 12:00:00' , '2008-01-01 12:00:00'表示,只需要几秒钟,不知道是什么原因 以前没出现过这种情况,过一段时间不替换参数在几秒钟也能出来,不知道是什么原因. UV_POSXFDMXB 大概有16万条,UV_POSXFDB 大概有6千条
SQL SERVER用的是7.0 + SP4
不会把,适当建立索引
'2008-01-01 12:00:00' , '2008-01-01 12:00:00'表示,只需要几秒钟,不知道是什么原因 以前没出现过这种情况,过一段时间不替换参数在几秒钟也能出来,不知道是什么原因. UV_POSXFDMXB 大概有16万条,UV_POSXFDB 大概有6千条
SQL SERVER用的是7.0 + SP4
不会把,适当建立索引
把数据库导入另外的机器就不会出现上述问题,而且还很快.两台机器的配置差不多.
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE USP_POSReport_SellEarning
@BEGTIME DATETIME,
@ENDTIME DATETIME,
@XSDBH VARCHAR(2) = '%'
AS SET NOCOUNT ONIF OBJECT_ID(N'TEMPDB..#LSB1') IS NOT NULL
BEGIN
DROP TABLE #LSB1
ENDIF @XSDBH IS NULL OR LTRIM(RTRIM(@XSDBH))='' SET @XSDBH='%'
SELECT
B.XSDB_BH,
A.FKFSB_BM,
A.FKFSB_MC,
A.XFDMXB_JE AS 'LJJE'
INTO #LSB1
FROM UV_POSXFDMXB A,UV_POSXFDB B
WHERE
A.XFDB_BH = B.XFDB_BH AND
A.XFDMXB_SPLX = '9' AND
A.XFDMXB_ZT = '1' AND
B.XFDB_ZT = 'O' AND
XFDB_JZYYRQ BETWEEN @BEGTIME AND @ENDTIME AND
B.XSDB_BH LIKE @XSDBH AND FKFSB_BM IS NOT NULL SELECT
CASE WHEN @XSDBH='%' THEN '(全部)' ELSE (SELECT XSDB_MC FROM POSXSDB WHERE XSDB_BH=@XSDBH) END AS 'XSD',
(SELECT XSDB_MC FROM POSXSDB WHERE XSDB_BH=#LSB1.XSDB_BH) AS 'XSDMC',
FKFSB_BM,FKFSB_MC,SUM(LJJE) AS 'JE',@BEGTIME AS 'KSSJ',@ENDTIME AS 'JSSJ'
FROM #LSB1
GROUP BY
XSDB_BH,FKFSB_BM,FKFSB_MC
DROP TABLE #LSB1 GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
建议增加红色部分