怎样提高查询速度？

[RealSmart]

后台是access数据库，执行如下数据查询：

string strSql="select count(*) AS 总数,sum(Times) AS 频次 from tbl01 where ID in (select distinct ID from tblAdd01 where Format like '"+strText+"') group by TheYear ";

tbl01中大概1.3万多条数据，tblAdd01中大概2.5万多条数据，字查询得到的ID数大概1千多条（也就是说最后的结果集为1千多条记录），结果进度条动的很慢，跑了很久网页还出不来。是不是在access数据库中建立查询速度会快？可是条件在子查询中，不知道怎么把条件值传进去……有没有更好的办法？谢谢！

[wu896222]

改为:
select count(a.id) as 总数,sum(a.Times) as 频次
from tbl01 a,(select distinct id from tblAdd01 where Format like '"+strText+"') b
where a.id=b.id
group by a.TheYear

[wu896222]

1.tbl01表与tblAdd01表分别建立ID 字段的索引;
2.SQL语句优化,不要用in:
select count(a.id) as 总数,sum(a.Times) as 频次 from tbl01 a,tblAdd01 b where a.id=b.id and b.Format like '"+strText+"' group by a.TheYear

[luoxia001]

楼上说得不错
补充一下:
楼主你的程序不可能一页就把1000多条数据显示出来吧
所以最好每次只取每页显示的记录条数,如果一次性取出的话,就算再怎么优化,也是很慢的

[RealSmart]

2.SQL语句优化,不要用in:
select count(a.id) as 总数,sum(a.Times) as 频次 from tbl01 a,tblAdd01 b where a.id=b.id and b.Format like '"+strText+"' group by a.TheYear

不行啊，因为tblAdd01中ID不是唯一的，因此需要先distinct，不然累加的结果就是错的

[xwjss]

第一页:
string strSql="select count(*) AS 总数,sum(Times) AS 频次 from tbl01 where ID in (

select distinct top pageSize(每页的记录数) ID from tblAdd01 where Format like '"+strText+"'

) group by TheYear ";
大于第一页:
string strSql="select count(*) AS 总数,sum(Times) AS 频次 from tbl01 where ID in (

select distinct top pageSize(每页的记录数) ID from tblAdd01 where Format like '"+strText+"' and ID not in (select distinct top pageSize(每页的记录数)*(pageIndex(页码)-1) ID from tblAdd01 where Format like '"+strText+"')

) group by TheYear ";

[RealSmart]

我说错了，最后count的就只有几条了……sorry，表达有误