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 descSELECT 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) 这个条件也很快,索引我都建好了,就是找不到慢的原因,麻烦大家帮忙看看.非常感谢.
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 descSELECT 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) 这个条件也很快,索引我都建好了,就是找不到慢的原因,麻烦大家帮忙看看.非常感谢.
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
执行一下再查询试试
我执行了dbcc dbreindex('kkGPRS')这个,但是还是很慢.郁闷了.
你这个不会报“因为该列没有包含在聚合函数或 GROUP BY 子句中。”的错误么?
估计是原始的问题吧(表1的记录比表2的记录多很多,查询自然慢)