备案控制台
探索云世界
开发者社区 数据库 文章 正文

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

2013-04-09 810
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 问题背景： 给学校的网站添加一个问卷调查系统，新加功能主要包含三张表 Survey 问卷表（用来存储问卷名） Survey_Question 问卷问题表（用来存储问卷问题） Survey_Answer 问卷答案表（用来存储问卷的答案） 系统是用ASP来写的，我应该算是二次开发吧（本人工作经验尚浅如果名词表达错误请见谅，到现在还是很佩服做这个系统的人，竟然能用ASP写出一个这么复杂的系统，但维护性。

问题背景：

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

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 进去

当时的想法如下图

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

 

操作引发的结果：

当有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完整语句：

 

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完整语句

 

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人填写过了

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

 

 

 

 

 

 

  

 

文章标签：
SQL
.NET
存储
关键词：
SQL查询
SQL JOIN
潇
目录
相关文章
jianz123
|
10天前
|
SQL
sql语句加正则 简化查询
sql语句加正则 简化查询
jianz123
14 0
sql语句加正则 简化查询
穆雄雄.
|
28天前
|
SQL
sql server链接查询
sql server链接查询
穆雄雄.
17 1
穆雄雄.
|
28天前
|
SQL
sql server简单查询
sql server简单查询
穆雄雄.
14 1
java知路
|
30天前
|
SQL 关系型数据库 MySQL
7种SQL Join语句
7种SQL Join语句
java知路
17 1
GoodTime
|
1月前
|
SQL 数据库 C#
C# .NET面试系列十一：数据库SQL查询（附建表语句）
#### 第1题 用一条 SQL 语句 查询出每门课都大于80 分的学生姓名 建表语句： ```sql create table tableA ( name varchar(10), kecheng varchar(10), fenshu int(11) ) DEFAULT CHARSET = 'utf8'; ``` 插入数据 ```sql insert into tableA values ('张三', '语文', 81); insert into tableA values ('张三', '数学', 75); insert into tableA values ('李四',
GoodTime
62 2
C# .NET面试系列十一：数据库SQL查询（附建表语句）
红目香薰
|
2月前
|
SQL 缓存 关系型数据库
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
MySQL技能完整学习列表6、查询优化——3、查询缓存——4、SQL优化技巧
红目香薰
63 0
风水道人
|
18天前
|
SQL 关系型数据库 MySQL
mysql一条sql查询出多个统计结果
mysql一条sql查询出多个统计结果
风水道人
13 0
穆雄雄.
|
28天前
|
SQL
sql高级查询
sql高级查询
穆雄雄.
12 0
小小Java开发者
|
1月前
|
SQL 存储 数据可视化
10个高级的 SQL 查询技巧
10个高级的 SQL 查询技巧
小小Java开发者
48 3
风水道人
|
23天前
|
SQL 缓存 算法
SQL 语句不要过多的 join
SQL 语句不要过多的 join
风水道人
18 1

热门文章

最新文章

  • 1
    阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言，助力业务平滑迁移
  • 2
    一文搞懂SQL优化——如何高效添加数据
  • 3
    Seata常见问题之服务端 error日志没有输出,客户端执行sql报错如何解决
  • 4
    【软件设计师备考 专题 】数据库语言（SQL）
  • 5
    TiDB支持的SQL语法概述
  • 6
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
  • 7
    SQL常见面试题总结2
  • 8
    MyBatis中的9种常用动态sql标签精妙用法
  • 9
    【SQL server】玩转SQL server数据库：第二章 关系数据库
  • 10
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
  • 1
    SQL注入的实现原理以及防止
    26
  • 2
    如何在 Python 中进行 Web 应用程序的安全性管理，例如防止 SQL 注入？
    15
  • 3
    docker如何进入镜像其他的SQL
    47
  • 4
    sql数据库查询语句大全
    21
  • 5
    sql如何新建数据库实例
    62
  • 6
    [MySQL]SQL优化之sql语句优化
    51
  • 7
    [MySQL]SQL优化之索引的使用规则
    35
  • 8
    [MySQL] SQL优化之性能分析
    29
  • 9
    sql文件批处理程序-java桌面应用
    25
  • 10
    《牛客笔试选择题》sql错题集
    23

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • SQL的增删改查及函数应用
  • PolarDB MySQL自动SQL限流
  • 如何开通SQL Server的专属集群
  • 使用DAS实现数据库自动SQL优化
  • 使用DAS实现数据库自动SQL限流
  • 一小时快速掌握 SQL 语法
    • 下一篇
    阿里云oss简介和使用流程