目录
任务表(Task)中,大概有30列,大概有35万的数据量。
1.select 具体字段和select *的效率
set statistics time on
select * from x.dbo.et_task
select id,name from x.dbo.et_task
select id,name,createtime,lastupdatetime from x.dbo.et_task
set statistics time off
查询结果集是345789行
Select * | 71671 ms | 70068 ms | 85452 ms |
Select id,name | 13793 ms | 15803 ms | 12520 ms |
Select | 17281 ms | 17096 ms | 17678 ms |
可以看出,select 会检索所有行,随着列的增加,时间也会随之增加。
2.exists 和 in的效率(关联字段都是索引列)
这时候我需要引出一个项目表(project)和任务明细表(taskassignmentdetail),项目表大概有311条数据,任务明细表大概有65W条数据,我们利用大表和小表的嵌套测试下对应的效率。
我们需要查询正在执行状态的项目对应的任务的数据
set statistics time on
select * from x.dbo.et_task a where exists (select id from x.dbo.et_taskassignmentdetail b where a.id=b.taskid)
select * from x.dbo.et_task a where id in (select taskid from x.dbo.et_taskassignmentdetail b)
set statistics time off
set statistics time on
select * from x.dbo.et_task a where exists (select id from x.dbo.et_project b where a.projectid=b.id)
select * from x.dbo.et_task a where projectid in (select id from x.dbo.et_project b)set
statistics time off
et_task(A 大) et_project(B 小) | et_task(A小) et_task_detail (B大) | |
exists | 5781 | 2533 ms |
exists | 4939 | 2469 ms |
exists | 3966 | 2388 ms |
in | 5976 | 2507 ms |
in | 4578 | 2463 ms |
in | 3298 | 2301 ms |
比较下来,其实in和exists的效率差不多。