我的视图效率很高，但是在它的基础上加上统计函数就慢了很多。
SELECT DISTINCT
ISNULL(A.kaicode, 0) AS kaicode,
ISNULL(A.dennumb, 0) AS dennumb,
CONVERT(char(7), A.denymd, 120) AS denymdx,
ISNULL(A.ukecode, 0) AS autcode,
ISNULL(A.ukecode, 0) AS ukecode,
ISNULL(A.ukename, '') AS ukename,
CAST(CONVERT(char(10), A.denymd, 120) AS datetime) AS denymd,
ISNULL(c.kmkkubn, 0) AS kmkkubn,
ISNULL(A.gyou, 0) AS gyou,
ISNULL(A.kmkcode, 0) AS kmkcode,
ISNULL(A.hojcode, 0) AS hojcode,
ISNULL(A.bumcode2, 0) AS bumcode2,
ISNULL(A.kmkcode2, 0) AS kmkcode2,
ISNULL(A.hojcode2, 0) AS hojcode2,
ISNULL(A.tekname, '') AS tekname,
ISNULL(B.kainame, '') AS kainame,
B.kessans,
B.kessane,
ISNULL((SELECT ISNULL(SUM(zenzan), 0) FROM kekbhojzan
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode) AND
(bumcode = A.bumcode) AND
(CONVERT(char(7), kymd, 120) = CONVERT(char(7), A.denymd, 120))), 0)
AS zenzanb,
ISNULL((SELECT ISNULL(SUM(zenzan), 0) FROM kekhojzan
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode) AND
(CONVERT(char(7), kymd,120) = CONVERT(char(7), A.denymd, 120))), 0) AS zenzan, ISNULL(A.bumcode, 0)
AS bumcode,
ISNULL((SELECT ISNULL(money1, 0) FROM keksiwdat
WHERE (taikubn = 1) AND
(kaicode = A.kaicode) AND
(dennumb = A.dennumb) AND
(denymd = A.denymd) AND
(gyou = A.gyou)), 0)
AS money1,
ISNULL((SELECT ISNULL(money1, 0) FROM keksiwdat
WHERE (taikubn = 2) AND
(kaicode = A.kaicode) AND
(dennumb = A.dennumb) AND
(denymd = A.denymd) AND
(gyou = A.gyou)), 0)
AS money2,
ISNULL((SELECT ISNULL(bumname, '') FROM kekbummst
WHERE (kaicode = A.kaicode) AND
(bumcode = A.bumcode)), '')
AS bumname,
ISNULL((SELECT ISNULL(bumname, '') FROM kekbummst
WHERE (kaicode = A.kaicode) AND
(bumcode = A.bumcode2)), '')
AS bumname2,
ISNULL((SELECT ISNULL(hojname, '') FROM kekhojmst
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode)), '')
AS hojname,
ISNULL((SELECT ISNULL(hojname, '') FROM kekhojmst
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode2) AND
(hojcode = A.hojcode2)), '')
AS hojname2,
ISNULL((SELECT ISNULL(kmkname, '') FROM kekcommst
WHERE (kmkcode = A.kmkcode)), '')
AS kmkname,
ISNULL ((SELECT ISNULL(kmkname, '') FROM kekcommst
WHERE (kmkcode = A.kmkcode2)), '')
AS kmkname2
FROM keksiwdat AS A LEFT OUTER JOIN
kekkaimst AS B ON A.kaicode = B.kaicode LEFT OUTER JOIN
kekhojmst AS D ON A.hojcode = D.hojcode AND A.hojcode2 = D.hojcode AND A.kaicode = D.kaicode LEFT OUTER JOIN
kekcommst AS E ON A.kmkcode = E.kmkcode AND A.kmkcode2 = E.kmkcode LEFT OUTER JOIN
kekbummst AS F ON A.bumcode = F.bumcode AND A.bumcode2 = F.bumcode AND A.kaicode = F.kaicode LEFT OUTER JOIN
kekauthead AS h ON A.kaicode = h.kaicode LEFT OUTER JOIN
kekcommst AS c ON c.kmkcode = A.kmkcode INNER JOIN
kekkmkmst AS I ON A.kaicode = I.kaicode AND A.kmkcode = I.kmkcode
WHERE (I.hojumu <> 0)
这个视图叫做 view_0900
这个很快
然后我又在外边嵌套语句如下
效率很低这是为什么呢？
select * ,
isnull((select sum(isnull(money1,0)) from view_0900 where kaicode=t1.kaicode and bumcode=t1.bumcode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode and hojcode =t1.hojcode),0) as bsummoney1 ,
isnull((select sum(isnull(money2,0)) from view_0900 where kaicode=t1.kaicode and bumcode=t1.bumcode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode and hojcode =t1.hojcode ),0) as bsummoney2,
isnull((select sum(isnull(money1,0)) from view_0900 where kaicode=t1.kaicode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode) ,0) as summoney1 ,
isnull((select sum(isnull(money2,0)) from view_0900 where kaicode=t1.kaicode and denymd between t1.kessans and t1.kessane and kmkcode =t1.kmkcode),0) as summoney2
from
view_0900 as t1
[解决办法]
你用了嵌套语句了 当然慢很多。
[解决办法]
select *from view_0900 as t1cross apply( select sum(money1) as bsummoney1, sum(money2) as bsummoney2 from view_0900 where kaicode = t1.kaicode and bumcode = t1.bumcode and denymd between t1.kessans and t1.kessane and kmkcode = t1.kmkcode and hojcode = t1.hojcode) t2cross apply( select sum(money1) as summoney1, sum(money2) as summoney2 from view_0900 where kaicode = t1.kaicode and denymd between t1.kessans and t1.kessane and kmkcode = t1.kmkcode) t3
[解决办法]
问一下，你视图执行的时间是多少，然后数据返回大概是多少；如果视图很快，并且数据量很小的话，可以先Insert到临时表，然后再统计，因为当视图用在多个统计时，会将视图定义引入sql语句，重新编译执行计划。
[解决办法]
借助临时表进行处理效率会很高的。
