SQL2000滥用 join 语句 带来的悲催后果(查询效率低下)

问题背景:

给学校的网站添加一个问卷调查系统,新加功能主要包含三张表

Survey 问卷表(用来存储问卷名)

Survey_Question 问卷问题表(用来存储问卷问题)

Survey_Answer 问卷答案表(用来存储问卷的答案)

系统是用ASP来写的,我应该算是二次开发吧(本人工作经验尚浅如果名词表达错误请见谅,到现在还是很佩服做这个系统的人,竟然能用ASP写出一个这么复杂的系统,但维护性。。。真的纠结)

由于本人没学过ASP。所以一开始还是比较纠结的

最终耗时二个星期的时间完成了以下功能:

1.问卷的添加、修改、删除

2.问卷问题的添加、修改、删除

3.开启问卷调查、关闭问卷调查(每次只允许开启一张问卷)

4.限制个别专业的学生填写问卷

5.问卷的统计(包含按专业统计问卷、按系统计问卷、按问题统计问卷、查看不满意问卷的不满意原因)PS:问题就出在按专业统计问卷,当然其他统计也应该有类似的问题只是结果较少所以没有出现这么严重的问题。

 (不知道小弟的工作效率怎么样。。。。)

正题(问题出现):

Survey_Answer表的表结构是

ID   Student_No    SQ_Id(问题ID)  A_Content( 问题答案) A_Reason(不满意问题的理由)

当时统计各专业问卷数据的时 是想着 东西出来就好了所以写SQL的时候也没有太在意。能拼出来就很高兴了

当时的SQL 是这么写的(简写)(具体的SQL还是比较复杂的。也可能是我写复杂了,我在文章结尾会附上完整SQL语句)

Select 系,专业,满意,不满意,一般,基本满意,满意度  ,

from  (

left jion (从Survey_Answer里查询出各专业满意的数量

left join (从Survey_Answer里查询出各专业不满意的数量)

。。。。。。。。。。。。))

就这样一直将各专业的满意数量、不满意数量等一个个查出来然后left join  或right join 进去

当时的想法如下图

SQL2000滥用 join 语句 带来的悲催后果(查询效率低下)

将查询的结果一个个拼进去。

 

操作引发的结果:

当有2000个学生填写后 survey_answer表的数据达到了2W的量(非常小的数据,一个学生填写11个问题)

当进行查询的时候发现查询专业数据统计时竟然需要40秒到1分钟的时间,

纠结啊

但是放到SQL2008里面则完全不会出现这样的问题

一样的查询语句,一样的数据 查询秒查。(SQL2008真心做了不少优化)

 

解决办法:

一开始考虑学习原系统的办法(建立专门建一张表用来存统计数据,然后加一个更新按钮,点击更新按钮就更新数据,否则直接查询该表的数据)

可是这种方法。。。显然是很。。。个人认为。。。低级的方案。。

自然不能考虑了。

果断修改SQL语句,一步步剥离子查询。然后分析,最终将查询缩短到了3-5秒

 

具体操作是这样的

Select 系,专业,满意,不满意,一般,基本满意,满意度  ,

from 

 (从Survey_Answer里查询出各专业满意的数量) as t1,

 (从Survey_Answer里查询出各专业不满意的数量)as t2,

......

通过这样的拼接来查询

因为有些数据为0,但你又必须查询出来,所以我才想到了用join 来做的

 

问题分析:

个人分析了下应该是这样的原因

因为学校有37个系

所以在使用join 语句的时候。。。4种结果(满意,不满意,基本满意、一般)应该会做

37*37*37*37 大约200W次吧。。效率自然低下了

而统计系的时候应该是8*8*8*8 的关系所以还不是特别明显,只是感觉有点慢

(本人的分析可能是有误,如果知道正确原因的大牛请指点,小弟万分感谢)

而用了新的SQL语句后。。

效率只是 “+”的关系了,,自然就快了

 总结:

 小小的总结一下。。。以后写SQL要注意呀。。。。


------------华丽的分割线-------------

老SQL完整语句:

 

SQL2000滥用 join 语句 带来的悲催后果(查询效率低下)SQL2000滥用 join 语句 带来的悲催后果(查询效率低下)View Code
 1 Select isnull((T5.Satisfaction_Count+T5.BSatisfaction_Count)*1.0/nullif(T5.General_Count+T5. BSatisfaction_Count+T5.UnSatisfaction_Count+T5.Satisfaction_Count,0),0) as lastinfo,
 2 T6.Faculty_Name as Faculty_Name, T5.Subject_Name as Subject_Name, T5. UnSatisfaction_Count as UnSatisfaction_Count , T5.Satisfaction_Count as Satisfaction_Count, T5.BSatisfaction_Count as BSatisfaction_Count, T5.General_Count as General_Count, T5.Survey_Count as Survey_Count 
 3 
 4 from (
 5     select * from Faculty where Faculty.Faculty_Flag=1)as T6
 6     -------------------------t5
 7     left join 
 8       (
 9         Select  [Subject].Subject_Faculty as Faculty_Id, isnull([Subject].Subject_Name,T4.Subject_Name )as Subject_Name, isnull(T4. UnSatisfaction_Count,0)as UnSatisfaction_Count ,  
10         isnull(T4.Satisfaction_Count,0)as Satisfaction_Count,  isnull(T4.BSatisfaction_Count,0)as BSatisfaction_Count, isnull(T4.General_Count,0)as General_Count, isnull(T4.Survey_Count,0)as Survey_Count
11             from [Subject] 
12             -------------------------t4
13 left join  
14 (
15 
16                 Select isnull(Survey.Subject_Name,T3.Subject_Name )as Subject_Name, (T3. UnSatisfaction_Count)  , (T3.Satisfaction_Count) , (T3.BSatisfaction_Count) , (T3.General_Count), Survey.Survey_Count 
17             from ( 
18             select [Subject].Subject_Name as Subject_Name,     count(DISTINCT Survey_Answer.Student_No) as Survey_Count from Student,VClass,[Subject],Survey_Answer,Survey,Survey_Question
19             where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and  Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 
20             and  Survey_Answer.Student_No=Student.Student_No and  VClass.VClass_Years='2013'  and  Survey.ID=1 Group by [Subject].Subject_Name
21              ) as Survey
22 -------------------------t3
23 Right join 
24 ( 
25 select isnull(UnSatisfaction.Subject_Name,T2.Subject_Name )as Subject_Name, isnull (UnSatisfaction.UnSatisfaction_Count,0) as UnSatisfaction_Count, T2.Satisfaction_Count, T2.BSatisfaction_Count, T2.General_Count  From (SELECT  [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) as UnSatisfaction_Count 
26  from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question
27   where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and  Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id and  VClass.VClass_Years='2013'  and  Survey.ID=1 and A_Content='不满意' Group by Subject_Name
28    ) as UnSatisfaction
29 ------------------------T2
30  Right join  
31  ( 
32  Select isnull(General. Subject_Name,T1.Subject_Name )as Subject_Name, isnull (General.General_Count,0) as General_Count, T1.Satisfaction_Count, T1.BSatisfaction_Count
33   From (
34   SELECT  [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) as General_Count  
35   from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 
36   where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and  Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id and  VClass.VClass_Years='2013'  and  Survey.ID=1 and A_Content='一般' Group by Subject_Name
37    ) as General
38    
39    
40    -------------------------T1
41  right join  
42  ( 
43  select isnull(BSatisfaction.BSatisfaction_Count,0) as BSatisfaction_Count,  isnull(Satisfaction. Satisfaction_Count,0) as  Satisfaction_Count,  isnull(Satisfaction. Subject_Name,BSatisfaction.Subject_Name )as Subject_Name 
44   from (
45   SELECT  [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) as BSatisfaction_Count 
46    from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 
47    where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and  Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id and  VClass.VClass_Years='2013'  and  Survey.ID=1 and A_Content='基本满意' Group by Subject_Name
48     ) as BSatisfaction 
49     
50 right join 
51  (
52  SELECT  [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) as Satisfaction_Count
53    from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 
54    where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass and   Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id and  VClass.VClass_Years='2013'  and  Survey.ID=1 and A_Content='满意' Group by Subject_Name 
55    ) as Satisfaction 
56    
57    on Satisfaction.Subject_Name=BSatisfaction.Subject_Name  )as T1 
58    ------------------------------T1end
59    
60     on T1.Subject_Name=General.Subject_Name )as T2 
61     -----------------------------T2END
62     
63      on  T2.Subject_Name=UnSatisfaction.Subject_Name )As T3 
64      
65      ---------------------------------t3end
66      
67      ON T3.Subject_Name=Survey.Subject_Name   )As T4
68      
69      -------------------------t4end
70       ON T4.Subject_Name=[Subject].Subject_Name   )as T5 
71       -------------------------t5end
72       on T5.Faculty_Id=T6.Faculty_Id  

 

 


------------华丽的分割线-------------

新SQL完整语句

 

SQL2000滥用 join 语句 带来的悲催后果(查询效率低下)SQL2000滥用 join 语句 带来的悲催后果(查询效率低下)View Code
 1  select Faculty_Name,T1.Subject_Name,Survey_Count, UnSatisfaction_Count,BSatisfaction_Count ,Satisfaction_Count ,General_Count ,
 2 isnull((Satisfaction_Count+BSatisfaction_Count)*1.0/nullif(General_Count+BSatisfaction_Count+UnSatisfaction_Count+Satisfaction_Count,0),0) as lastinfo
 3  from (
 4 select [Subject].Subject_Name ,isnull(Survey_Count,0)as  Survey_Count
 5 from [Subject]
 6 left join 
 7 
 8 (select [Subject].Subject_Name as Subject_Name, 
 9        count(DISTINCT Survey_Answer.Student_No) as Survey_Count
10         from Student,VClass,[Subject],Survey_Answer,Survey,Survey_Question 
11         where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass
12          and  Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 
13          and  Survey_Answer.Student_No=Student.Student_No and  VClass.VClass_Years='2013'  
14          and  Survey.ID=1 Group by [Subject].Subject_Name )as T1
15 on T1.Subject_Name=[Subject].Subject_Name
16 )as T1,
17 
18 
19 (select [Subject].Subject_Name ,isnull(UnSatisfaction_Count,0) as UnSatisfaction_Count
20 from [Subject]
21 left join 
22 (SELECT  [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) 
23           as UnSatisfaction_Count  from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 
24           where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass 
25           and  Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No 
26           and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id
27            and  VClass.VClass_Years='2013'  and  Survey.ID=1 and A_Content='不满意' 
28            Group by Subject_Name)as T1
29 on T1.Subject_Name=[Subject].Subject_Name
30 ) as T2,
31 
32 
33 (select [Subject].Subject_Name ,isnull(BSatisfaction_Count,0)  as BSatisfaction_Count
34 from [Subject]
35 left join 
36 (SELECT  [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) 
37               as BSatisfaction_Count  from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question
38                where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass
39                 and  Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No 
40                 and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 
41                 and  VClass.VClass_Years='2013'  and  Survey.ID=1 and A_Content='基本满意' 
42                 Group by Subject_Name )as T1
43 on T1.Subject_Name=[Subject].Subject_Name
44 )as T3,
45 
46 
47 
48 (select [Subject].Subject_Name ,isnull(Satisfaction_Count,0) as Satisfaction_Count
49 from [Subject]
50 left join 
51 (SELECT  [Subject].Subject_Name 
52                 as Subject_Name, count(Survey_Answer.ID) as Satisfaction_Count  
53                 from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 
54                 where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass
55                  and   Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No
56                   and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 
57                   and  VClass.VClass_Years='2013'  and  Survey.ID=1 and A_Content='满意' Group by Subject_Name  )as T1
58 on T1.Subject_Name=[Subject].Subject_Name
59 
60 )as T4,
61 
62 
63 
64 (select [Subject].Subject_Name ,isnull(General_Count,0) as General_Count
65 from [Subject]
66 left join 
67 (SELECT  [Subject].Subject_Name as Subject_Name, count(Survey_Answer.ID) 
68             as General_Count  from Student,VClass,Survey_Answer,[Subject],Survey,Survey_Question 
69             where  VClass.VClass_Subject=[Subject].Subject_Id and VClass.VClass_Id=Student.Student_VClass 
70             and  Survey_Answer.Student_No=Student.Student_No and Survey_Answer.Student_No=Student.Student_No 
71             and Survey.ID=Survey_Question.Survey_ID and Survey_Question.ID=Survey_Answer.SQ_Id 
72             and  VClass.VClass_Years='2013'  and  Survey.ID=1 and A_Content='一般' Group by Subject_Name
73   )as T1
74   
75 on T1.Subject_Name=[Subject].Subject_Name
76 
77 )as T5,
78 (select Faculty.Faculty_Name ,Subject_Name
79 from [Subject],Faculty
80 where Faculty.Faculty_Id =[Subject].Subject_Faculty)as T6
81 
82 
83 where T6.Subject_Name=T1.Subject_Name and 
84  T6.Subject_Name=T2.Subject_Name and 
85 T6.Subject_Name=T3.Subject_Name and 
86 T6.Subject_Name=T4.Subject_Name and
87 T6.Subject_Name=T5.Subject_Name


 后记:

问卷调查使用一周后 已经有2300人填写过了

下面附带一些统计的效果图

SQL2000滥用 join 语句 带来的悲催后果(查询效率低下)

SQL2000滥用 join 语句 带来的悲催后果(查询效率低下)

 

 

 

 

 

 

  

 

上一篇:写代码真正该注意的地方


下一篇:SpringIOC