麻烦高手帮忙看个sql的问题

[yy2226630]

SELECT TOP 20
c.name,b.name as pj_name,a.iccid,a.ims,a.time,a.type
FROM kkGPRS a,kkProject b,dhCompany c
where a.Project_ID = b.id and b.DhCompany_ID = c.id
and b.id in (select Project_ID from kkUser_kkProject where User_ID = 30)
order by a.id desc

SELECT TOP 20
c.name,b.name as pj_name,a.mobile,a.iccid,a.ims,a.time,a.type
FROM kkMessage a,kkProject b,dhCompany c
where a.Project_ID = b.id and b.DhCompany_ID = c.id
and b.id in (select Project_ID from kkUser_kkProject where User_ID = 30)
order by a.id desc

上面 2条语句中的dhCompany 和kkProject 表是同一个表,kkGPRS 和kkMessage 的表结构基本一样.
但是第1条比第 2条要慢上很多,第1条如果不加nd b.id in (select Project_ID from kkUser_kkProject where User_ID = 30) 这个条件也很快,索引我都建好了,就是找不到慢的原因,麻烦大家帮忙看看.非常感谢.

[sql_sf]

--try
SELECT TOP 20
       c.name,
       b.name as pj_name,
       a.iccid,
       a.ims,
       a.time,
       a.type
FROM kkGPRS a,kkProject b,dhCompany c,kkUser_kkProject d 
where a.Project_ID = b.id and b.DhCompany_ID = c.id and b.id=d.Project_ID
and d.User_ID = 30
order by a.id desc

[yy2226630]

快了一点点,但是效果还是没有第2条好.

[htl258_Tony]

对比一下执行计划，代码差不多，没看出什么破绽

[--小F--]

看执行计划 看不是没有用到索引

[htl258_Tony]

dbcc dbreindex('kkGPRS')

执行一下再查询试试

[--小F--]

检查索引碎片 重新建立索引..

[yy2226630]

感谢大家帮忙.
我执行了dbcc dbreindex('kkGPRS')这个,但是还是很慢.郁闷了.

[sql_sf]

[Quote=引用 7 楼 yy2226630 的回复:]
感谢大家帮忙.
我执行了dbcc dbreindex('kkGPRS')这个,但是还是很慢.郁闷了.
[/Quote]
a.id 有没有索引？

[yy2226630]

a表有id、Project_ID、还有TIME的索引

[java__net]

[Quote=引用 1 楼 sql_sf 的回复:]
SQL code
--try
SELECT TOP 20
c.name,
b.name as pj_name,
a.iccid,
a.ims,
a.time,
a.type
FROM kkGPRS a,kkProject b,dhCompany c,kkUser_kkProject d
whe……
[/Quote]

你这个不会报“因为该列没有包含在聚合函数或 GROUP BY 子句中。”的错误么？

[yy2226630]

没有报错.能运行

[master_fengj]

kkGPRS 和 kkMessage 两个表的数据量各为多少,是不是kkGPRS的数据量比kkMessage大很多呢?

[cxmcxm]

查询sql看起来无什么问题。
估计是原始的问题吧（表1的记录比表2的记录多很多，查询自然慢）