SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GOALTER PROCEDURE dbo.pr月总_截止时间(@StartDate nvarchar(50), @EndDate nvarchar(50), @chvPm nvarchar(300)) AS SELECT dbo.客户.公司名称, Fun上月版费及作版量.数量OfSum, Fun上月版费及作版量.版费OfSum, Fun上月来款.来款OfSum, CONVERT(varchar(7), CAST(@EndDate AS datetime), 121) AS 月份, dbo.片名.片名, (CASE WHEN (fn欠款_截止时间.欠款 IS NOT NULL) THEN fn欠款_截止时间.欠款 ELSE (CASE WHEN (Fun上月来款.来款OFSUM IS NOT NULL) THEN Fun上月来款.来款OFSUM ELSE 0 END) - (CASE WHEN (Fun上月版费及作版量.版费OfSum IS NOT NULL) THEN Fun上月版费及作版量.版费OfSum ELSE 0 END) END) AS 欠款offsum, dbo.客户.selectPrint, (CASE WHEN (fn欠款_截止时间.欠款 < 0) THEN fn欠款_截止时间.欠款 ELSE 0 END) AS 欠款1, fn欠款_截止时间.预付款, fn欠款_截止时间.实欠, Fun上月版费及作版量.转昆山版费, Fun上月版费及作版量.转昆山数量, Fun上月版费及作版量.转昆山来款 FROM dbo.fn欠款_截止时间(@EndDate) fn欠款_截止时间 RIGHT OUTER JOIN dbo.客户 ON fn欠款_截止时间.客户ID = dbo.客户.客户ID LEFT OUTER JOIN dbo.Fun上月版费及作版量(@StartDate, @EndDate) Fun上月版费及作版量 ON dbo.客户.客户ID = Fun上月版费及作版量.客户ID LEFT OUTER JOIN dbo.Fun上月来款(@StartDate, @EndDate) Fun上月来款 ON dbo.客户.客户ID = Fun上月来款.客户ID LEFT OUTER JOIN dbo.片名 ON dbo.客户.片名ID = dbo.片名.片名ID WHERE (CHARINDEX(',''' + RTRIM(CAST(dbo.片名.片名ID AS char)) + ''',', ',' + @chvPm + ',') > 0) AND ((CASE WHEN (fn欠款_截止时间.欠款 IS NOT NULL) THEN 0 ELSE (CASE WHEN (Fun上月来款.来款OFSUM IS NOT NULL) THEN 0 ELSE (CASE WHEN (Fun上月版费及作版量.版费OfSum IS NOT NULL) THEN 0 ELSE 1 END) END) END) <> 1) ORDER BY (CASE WHEN (fn欠款_截止时间.欠款 IS NOT NULL) THEN fn欠款_截止时间.欠款 ELSE (CASE WHEN (Fun上月来款.来款OFSUM IS NOT NULL) THEN Fun上月来款.来款OFSUM ELSE 0 END) - (CASE WHEN (Fun上月版费及作版量.版费OfSum IS NOT NULL) THEN Fun上月版费及作版量.版费OfSum ELSE 0 END) END) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
FROM dbo.fn欠款_截止时间(@EndDate) fn欠款_截止时间 RIGHT OUTER JOIN dbo.客户 ON fn欠款_截止时间.客户ID = dbo.客户.客户ID LEFT OUTER JOIN dbo.Fun上月版费及作版量(@StartDate, @EndDate) Fun上月版费及作版量 ON dbo.客户.客户ID = Fun上月版费及作版量.客户ID LEFT OUTER JOIN dbo.Fun上月来款(@StartDate, @EndDate) Fun上月来款 ON dbo.客户.客户ID = Fun上月来款.客户ID LEFT OUTER JOIN dbo.片名 ON dbo.客户.片名ID = dbo.片名.片名ID 这些结果中每个表或函数不超过200条记录,上个月好着了,这个月有问题,执行速度慢的厉害. 但查询出的结果也不多啊
按理说,如果把连接条件写上,用inner join 和 left outer join应该不至于有这么大的差别吧。 你把用两种连接方式的记录都查出来,看看数据量差别究竟有多大吧。 应该是查询里写了这么多函数的原因吧。
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE dbo.pr月总_截止时间(@StartDate nvarchar(50),
@EndDate nvarchar(50),
@chvPm nvarchar(300))
AS SELECT dbo.客户.公司名称, Fun上月版费及作版量.数量OfSum,
Fun上月版费及作版量.版费OfSum, Fun上月来款.来款OfSum, CONVERT(varchar(7),
CAST(@EndDate AS datetime), 121) AS 月份, dbo.片名.片名,
(CASE WHEN (fn欠款_截止时间.欠款 IS NOT NULL)
THEN fn欠款_截止时间.欠款 ELSE (CASE WHEN (Fun上月来款.来款OFSUM IS NOT NULL)
THEN Fun上月来款.来款OFSUM ELSE 0 END)
- (CASE WHEN (Fun上月版费及作版量.版费OfSum IS NOT NULL)
THEN Fun上月版费及作版量.版费OfSum ELSE 0 END) END) AS 欠款offsum,
dbo.客户.selectPrint, (CASE WHEN (fn欠款_截止时间.欠款 < 0)
THEN fn欠款_截止时间.欠款 ELSE 0 END) AS 欠款1, fn欠款_截止时间.预付款,
fn欠款_截止时间.实欠, Fun上月版费及作版量.转昆山版费,
Fun上月版费及作版量.转昆山数量, Fun上月版费及作版量.转昆山来款
FROM dbo.fn欠款_截止时间(@EndDate) fn欠款_截止时间 RIGHT OUTER JOIN
dbo.客户 ON fn欠款_截止时间.客户ID = dbo.客户.客户ID LEFT OUTER JOIN
dbo.Fun上月版费及作版量(@StartDate, @EndDate) Fun上月版费及作版量 ON
dbo.客户.客户ID = Fun上月版费及作版量.客户ID LEFT OUTER JOIN
dbo.Fun上月来款(@StartDate, @EndDate) Fun上月来款 ON
dbo.客户.客户ID = Fun上月来款.客户ID LEFT OUTER JOIN
dbo.片名 ON dbo.客户.片名ID = dbo.片名.片名ID
WHERE (CHARINDEX(',''' + RTRIM(CAST(dbo.片名.片名ID AS char)) + ''',',
',' + @chvPm + ',') > 0) AND ((CASE WHEN (fn欠款_截止时间.欠款 IS NOT NULL)
THEN 0 ELSE (CASE WHEN (Fun上月来款.来款OFSUM IS NOT NULL)
THEN 0 ELSE (CASE WHEN (Fun上月版费及作版量.版费OfSum IS NOT NULL)
THEN 0 ELSE 1 END) END) END) <> 1)
ORDER BY (CASE WHEN (fn欠款_截止时间.欠款 IS NOT NULL)
THEN fn欠款_截止时间.欠款 ELSE (CASE WHEN (Fun上月来款.来款OFSUM IS NOT NULL)
THEN Fun上月来款.来款OFSUM ELSE 0 END)
- (CASE WHEN (Fun上月版费及作版量.版费OfSum IS NOT NULL)
THEN Fun上月版费及作版量.版费OfSum ELSE 0 END) END)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
dbo.客户 ON fn欠款_截止时间.客户ID = dbo.客户.客户ID LEFT OUTER JOIN
dbo.Fun上月版费及作版量(@StartDate, @EndDate) Fun上月版费及作版量 ON
dbo.客户.客户ID = Fun上月版费及作版量.客户ID LEFT OUTER JOIN
dbo.Fun上月来款(@StartDate, @EndDate) Fun上月来款 ON
dbo.客户.客户ID = Fun上月来款.客户ID LEFT OUTER JOIN
dbo.片名 ON dbo.客户.片名ID = dbo.片名.片名ID 这些结果中每个表或函数不超过200条记录,上个月好着了,这个月有问题,执行速度慢的厉害.
但查询出的结果也不多啊
你把用两种连接方式的记录都查出来,看看数据量差别究竟有多大吧。
应该是查询里写了这么多函数的原因吧。