SQL 优化的问题

[月光下的土豆]

DECLARE @FStatDate datetime
DECLARE @FEndDate datetime
SET @FStatDate='2013-5-1'
SET @FEndDate='2013-5-30'

select a.fdate,a.user_no from admin_v4.dbo.view1 a inner join (select a.FDate,a.user_no 
	from admin_v4.dbo.view1 a where a.FDate>=@FStatDate and a.FDate<=@FEndDate   group by a.user_no,a.FDate  having count(a.user_no)=2) b on a.user_no=b.user_no and a.fdate=b.fdate
	
	 
group by a.user_no,a.fdate

不加group by a.user_no,a.fdate 执行时间不到1秒，加上group by a.user_no,a.fdate巨慢

[BzTech_123]

请问你为什么里外都要加 GROUP BY a.user_no, a.fdate ? 你究竟想要什么结果呢?

[铁歌]

可以给user_no,fdate建立复合索引。SQL优化的通常过程是 1. 首先打开 预估执行计划，如果在SQL SERVER2005以上版本，系统会提示missing index,可以根据提示的影响率建立相应inde 2. 关键查看开销最大的操作符，例如是聚集扫描还是嵌套索引，楼主查询SQL估计在流聚合计算上面开销较大，那么就可以在该操作上进行调整，调整的方法可以是建立缺失index,可以建立临时表，通过空间换时间， 开销cost由CPU开销和I/O开销组成，如果I/O开销大多半是索引的问题，如果CPU开销大，说明执行了排序和GROUP BY的操作。

[---涛声依旧---]

楼主无非是查询有重复记录的，建议改为exists

[jack15850798154]

我不太明白楼主为什么还要再group by a.user_no,a.fdate 一下做什么是问了取出重复吗？如果只为取出重复可以用：

SELECT DISTINCT a.fdate,a.user_no 
from admin_v4.dbo.view1 a 
inner join (
	select a.FDate,a.user_no 
	from admin_v4.dbo.view1 a where a.FDate>=@FStatDate and a.FDate<=@FEndDate   
	group by a.user_no,a.FDate  having count(a.user_no)=2
) b on a.user_no=b.user_no and a.fdate=b.fdate

--或者用下面的语句测试一下是否会提升速度

DECLARE @FStatDate datetime
DECLARE @FEndDate datetime
SET @FStatDate='2013-5-1'
SET @FEndDate='2013-5-30'

SELECT a.fdate,a.user_no 
INTO #T_CSDN_ONE
from admin_v4.dbo.view1 a 
inner join (
	select a.FDate,a.user_no 
	from admin_v4.dbo.view1 a where a.FDate>=@FStatDate and a.FDate<=@FEndDate   
	group by a.user_no,a.FDate  having count(a.user_no)=2
) b on a.user_no=b.user_no and a.fdate=b.fdate
	
	 
SELECT a.user_no,a.fdate FROM #T_CSDN_ONE group by a.user_no,a.fdate

DROP Table #T_CSDN_ONE