时间戳

首页 > 解决方案 > 查询花费大量时间

sql - 查询花费大量时间

问题描述

我有三个表 Student_details 和 Student_Marks 和 subject_Name。

主题名称

+----+------------------+
| ID |     Sub_Name     |
+----+------------------+
|  1 | Maths            |
|  2 | Physics          |
|  3 | Chemistry        |
|  4 | Biology          |
|  5 | Computer Science |
+----+------------------+

学生详情

+----+------------+-----------+-----+-------+--+
| ID | First_Name | Last_Name | Age | Class |  |
+----+------------+-----------+-----+-------+--+
|  1 | Rohit      | Sharma    |  28 |     3 |  |
|  2 | Shikhar    | Dhavan    |  27 |     2 |  |
|  3 | Virat      | Kohli     |  29 |     3 |  |
|  4 | MS         | Dhoni     |  30 |     2 |  |
|  5 | Hardik     | Pandya    |  25 |     3 |  |
+----+------------+-----------+-----+-------+--+

Student_Marks

+----+------------+------------+---------------+
| ID | Student_Id | Subject_Id | Subject_Marks |
+----+------------+------------+---------------+
|  1 |          1 |          1 |            90 |
|  2 |          1 |          2 |            82 |
|  3 |          1 |          3 |            85 |
|  4 |          1 |          4 |            75 |
|  5 |          1 |          5 |            92 |
|  6 |          2 |          1 |            90 |
|  7 |          2 |          2 |            82 |
|  8 |          2 |          3 |            85 |
|  9 |          2 |          4 |            75 |
| 10 |          2 |          5 |            92 |
| 11 |          3 |          1 |            90 |
| 12 |          3 |          2 |            82 |
| 13 |          3 |          3 |            85 |
| 14 |          3 |          4 |            75 |
| 15 |          3 |          5 |            92 |
+----+------------+------------+---------------+

我正在使用这个查询来获取结果。

select sd.First_Name
      ,sd.Last_Name
      ,sm.Subject_Id
      ,sm.Subject_Marks
from Student_Marks sm
    inner join Student_details sd
       on sm.Student_Id = sd.ID
where sm.Subject_Id in (select ID
                        from Subject_Name
                        where Sub_Name in ('Maths', 'Physics')
                       )
  and Student_Id in (select ID
                     from Student_details
                     where class in (2,3)
                    );

现在，这个查询花费了太多时间来获得结果。

如何优化此查询？

编辑：

我正在使用休眠来获取结果，因此实际查询是：

select sd.First_Name
      ,sd.Last_Name
      ,sm.Subject_Id
      ,sm.Subject_Marks
from Student_Marks sm
    inner join Student_details sd
        on sm.Student_Id = sd.ID
where sm.Subject_Id in :SubName
  and Student_Id in (select ID
                     from Student_details
                     where class in :ClassIds
                    );

标签： sqlsql-serverquery-optimization

解决方案

尝试这个 ：

select sd.First_Name
      ,sd.Last_Name
      ,sm.Subject_Id
      ,sm.Subject_Marks
from Student_Marks sm
     inner join Student_details sd on sm.Student_Id = sd.ID
     inner join Subject_Name sn on sm.Subject_Id = sn.ID AND Sub_Name in ('Maths','Physics')
     inner join Student_details on Student_Id = Student_details.ID AND class in (2,3)

推荐阅读