大侠请进，帮忙优化一条SQL语句

[penglewen]

SELECT top 20 *,CONVERT(varchar(20) , HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , InboundDateTime, 20) AS InboundDateTime from InboundLog a
WHERE NOT EXISTS(
SELECT * FROM InboundLog b WHERE a.id<b.id  AND b.InboundDateTime >= '2010-01-01 00:00:00' and b.InboundDateTime <= '2010-01-31 23:59:59'
AND a.callerID=b.callerID AND a.local_dial_number=b.local_dial_number
 AND a.campaignScriptID=b.campaignScriptID AND 
DATEDIFF(hh,a.inboundDateTime,b.inboundDateTime)<1  )
 AND a.InboundDateTime >= '2010-01-01 00:00:00' and a.InboundDateTime <= '2010-01-31 23:59:59' 
order by InboundDateTime desc 

为什么加了a.id<b.id 这个查询条件，速度就慢很多，不加的话，就快。。

[penglewen]

select top 1 dbo.InboundLog.id from InboundLog as a 这种情况要怎么指定 dbo.InboundLog.id

[SQL77]

[Quote=引用 10 楼 penglewen 的回复:]
SQL77
你的，我试了下，运行时间比之前的还要慢。
[/Quote]
InboundDateTime

是哦,呵呵,晕了,这个字段还加在函数里面去了,

不好意思,

你改连接查询吧,那时间条件还是用>=来比较,

连接条件上加索引,InboundDateTime 这个字段考虑加聚集

[penglewen]

[Quote=引用 12 楼 sql77 的回复:]
引用 10 楼 penglewen 的回复:
SQL77
你的，我试了下，运行时间比之前的还要慢。

InboundDateTime

是哦,呵呵,晕了,这个字段还加在函数里面去了,

不好意思,

你改连接查询吧,那时间条件还是用>=来比较,

连接条件上加索引,InboundDateTime 这个字段考虑加聚集
[/Quote]

帮我看下这个问题吧：
select top 1 dbo.InboundLog.id from InboundLog as a 这种情况要怎么指定 dbo.InboundLog.id

[SQL77]

select top 1 A.id from DBO.InboundLog as a

你用别名呀

[penglewen]

OK,try.

[you_tube]

try

SELECT TOP 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime, 
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime 
FROM InboundLog A
WHERE DATEADD(HH,a.inboundDateTime,MAX(a.inboundDateTime)) <= 0 
GROUP BY callerID,local_dial_number, campaignScriptID,ID,HangUpDateTime,InboundDateTime(要筛选取的字段)
HAVING DATEDIFF(hh,max(inboundDateTime),a.inboundDateTime) <= 0 

[you_tube]

错了

SELECT TOP 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime, 
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime 
FROM InboundLog A
GROUP BY callerID,local_dial_number, campaignScriptID,ID,HangUpDateTime,InboundDateTime(要筛选取的字段)
HAVING DATEDIFF(hh,max(inboundDateTime),a.inboundDateTime) <= 0 

[you_tube]

再改下

SELECT TOP 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime, 
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime 
FROM InboundLog A
GROUP BY callerID,local_dial_number, campaignScriptID,ID,HangUpDateTime,InboundDateTime(要筛选取的字段)
HAVING DATEADD(HH,1,A.inboundDateTime) <= max(inboundDateTime)
ORDER BY A.InboundDateTime DESC

[jwwyqs]

SELECT top 20 *,CONVERT(varchar(20) , HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , InboundDateTime, 20) AS InboundDateTime from InboundLog a
WHERE a.InboundDateTime between '2010-01-01' and '2010-01-31'
and NOT EXISTS(
SELECT * FROM InboundLog b WHERE b.InboundDateTime between '2010-01-01' and '2010-01-31'
AND a.callerID=b.callerID AND a.local_dial_number=b.local_dial_number
AND a.campaignScriptID=b.campaignScriptID
and a.id<b.id and DATEDIFF(hh,a.inboundDateTime,b.inboundDateTime)<1
)
order by InboundDateTime desc

[you_tube]

SELECT TOP 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime, 
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime,
COUNT(1) 
FROM InboundLog A
GROUP BY callerID,local_dial_number, campaignScriptID,ID,HangUpDateTime,InboundDateTime(要筛选取的字段)
HAVING DATEADD(HH,1,A.inboundDateTime) <= max(inboundDateTime)
ORDER BY A.InboundDateTime DESC

忘记加聚集函数了

[SQL77]

a.id <b.id

试试加上索引,

[SQL77]

InboundDateTime

这个字段加聚集吧,有BETWEEN AND 还有ORDER BY ,

[penglewen]

id有加索引的。

[budong0000]

order by 很费时费力的

[SQL77]

SELECT 
top 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime 

from InboundLog a,

(SELECT 
	callerID, 
	local_dial_number,
	campaignScriptID,
	MAX(ID)BID
 FROM 
	InboundLog b 
WHERE DATEDIFF(MM, b.InboundDateTime ,'2010-01-01')=0

GROUP BY callerID, local_dial_number,campaignScriptID)AS B

WHERE 
DATEDIFF(hh,a.inboundDateTime,b.inboundDateTime)<1 
AND
DATEDIFF(MM, A.InboundDateTime ,'2010-01-01')=0 
AND a.callerID=b.callerID 
AND a.local_dial_number=b.local_dial_number
AND a.campaignScriptID=b.campaignScriptID 
order by A.InboundDateTime desc 

[Garnett_KG]

将执行计划帖出来吧

[SQL77]

SELECT 
top 20 *,
CONVERT(varchar(20) , A.HangUpDateTime, 20) AS HangUpDateTime,
CONVERT(varchar(20) , A.InboundDateTime, 20) AS InboundDateTime 

from InboundLog a,

(SELECT 
	callerID, 
	local_dial_number,
	campaignScriptID,
	MAX(ID)BID
 FROM 
	InboundLog b 
WHERE DATEDIFF(MM, b.InboundDateTime ,'2010-01-01')=0

GROUP BY callerID, local_dial_number,campaignScriptID)AS B

WHERE 
DATEDIFF(hh,a.inboundDateTime,b.inboundDateTime)<1 
AND A.ID=B.BID AND
DATEDIFF(MM, A.InboundDateTime ,'2010-01-01')=0 
AND a.callerID=b.callerID 
AND a.local_dial_number=b.local_dial_number
AND a.campaignScriptID=b.campaignScriptID 
order by A.InboundDateTime desc 

相应连接条件上加索引,
InboundDateTime 这个字段考虑加聚集索引,

[samyou]

估计是因为b是a的内嵌表，这样加上这个查询条件，SQL就没有进行优化查询哦。

[penglewen]

3Q SQL77
我先试试。

[penglewen]

SQL77
你的，我试了下，运行时间比之前的还要慢。