为了解决重复问题,使用了distinct,结果查询时间太长了,导致系统卡死了。现在分析下distinct和group by的性能区别
384条件数据,里面只有六条数据,其他都是这6条的重复值
distinct耗时:
SQL Server Execution Times: CPU time = 203 ms, elapsed time = 222 ms. |
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. |
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. |
group by 耗时:
SQL Server Execution Times: CPU time = 15 ms, elapsed time = 7 ms. |
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. |
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. |
现在讲数据更新为6144条。
distinct耗时:
SQL Server Execution Times:
CPU time = 21078 ms, elapsed time = 22342 ms.
group by 耗时:
SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 74 ms.
综上所述可以看出,distinct在大数据情况下非常影响性能,建议在项目允许的情况下不要使用distinct。