2015-9-25小记
先放结论:1、执行视图的时间是执行SQL语句时间的4倍以上;2、编写sql语句的时候,可以先各种join之后再添加where过滤条件。
1、昨天写了本文,作为小记。但是临近下班项目发布后的执行情况又让我大跌眼镜,如果调用视图,直接报超时错误。之后换做了执行SQL语句。不再报超时错误。
2、刚开始编写sql语句的时候,把各种条件都添加到了各个UNION的select语句之中。查询时间为40秒以上。效率实在不过关,因此把查询结果放入了临时表,之后对临时表进行where筛选。此时时间缩短为10秒以上。
因此得出上述结论,并推翻昨天文章的内容。并且,实践证明:若考虑效率,尽量避免使用视图!
2015-9-24 小记
此文为小记。
最近处理项目遗留的问题。多数是数据查询分析这块内容的BUG.刚开始很不解为什么会有那么多视图。由于对视图具有天生的鄙视,总是觉得视图只是查询方便一些,但是控件和效率总是不及直接执行SQL语句快。
但是今天,这种想法被颠覆了一下
SELECT t .FileID, t .Title, t .Subject, cp.TeacherId, MAX(t .Point) AS point, COUNT(DISTINCT t .UserId) AS testcount, cp.ReceiveCount, MAX(t .Score) AS maxscore, MIN(t .Score)
AS minscore, ROUND(AVG(t .Score), 1) AS avgscore, cp.DownDate, max(t .UploadTime) UploadTime,u.School
,tt.Name
FROM dbo.View_Csw_TestRult_First AS t INNER JOIN
dbo.Csw_users AS u ON t .UserId = u.userid INNER JOIN
dbo.CWInforTable AS cw ON t .FileID = cw.FTPFileName INNER JOIN
(SELECT COUNT(cu.userid) AS ReceiveCount, hm.FileName, cc.ClassID, cl.TeacherId, MAX(hm.DownDate) AS DownDate
FROM dbo.HomeWork AS hm INNER JOIN
dbo.PK_HomeWorkAndClass AS cc ON hm.pkid = cc.HomeWorkID INNER JOIN
dbo.Csw_users AS cu ON cu.ClassId = cc.ClassID INNER JOIN
dbo.Csw_Class AS cl ON cu.ClassId = cl.ClassId
WHERE hm.FileName IS NOT NULL
GROUP BY hm.FileName, cc.ClassID, cl.TeacherId) AS cp ON cw.ID = cp.FileName AND u.ClassId = cp.ClassID
left join Csw_TypeTable as tt on tt.ID=t.ColumnType
where 1=1
and school='bsdfz' and teacherID='yqjnjbzr1' and cp.DownDate>='2015-08-28 00:00:00' and cp.DownDate<='2015-09-24 23:59:59'
and tt.ID!=7
and tt.Type=3
GROUP BY t .FileID, t .Title, t .Subject, cp.TeacherId, cp.ReceiveCount, cp.DownDate,u.School
,tt.Name
红色条件加入前后执行时间差别很大。一个是一分八秒,一个是两秒。看来还是空间换时间比较划算