Access 视图 查询优化过程例 By Stabx

标题: Access 视图 查询优化过程例 By Stabx

正文:

QUOTE:

Access 视图 查询优化过程例

1. 需要的列 / 共9个
gid, gname, gcontent, a.gcid, gdate, a.gscid, subgname, gcat, gscat

1.1 原视图 / 共选择 23个列
SELECT a.*, b.*, c.*
FROM (ctglossary AS a LEFT JOIN ctglossarysupercat AS b ON a.gcid=b.gcid) LEFT JOIN ctglossarysubcat AS c ON a.gscid=c.gscid;

1.2 新视图 / 共选择 9个列
SELECT gid, gname,gcontent,a.gcid,gdate,a.gscid,subgname,gcat,gscat
FROM (ctglossary AS a LEFT JOIN ctglossarysupercat AS b ON a.gcid=b.gcid) LEFT JOIN ctglossarysubcat AS c ON a.gscid=c.gscid;

2. 需要的列 / 共13个
a.articleid, title, content, a.classid, a.nclassid, hits, dateandtime, class, nclass, tagid, tag, tagview, tagdate

2.1 原视图 / 共选择30个
SELECT a.*, b.*, c.*, d.*
FROM (ctarticle AS a LEFT JOIN ctclass AS b ON a.classid=b.classid) LEFT JOIN (ctnclass AS c RIGHT JOIN cttag AS d ON c.nclassid=d.nclassid) ON a.articleid=d.articleid;

2.2 新视图 / 共选择13个
SELECT a.articleid,title,content,a.classid,a.nclassid,hits,dateandtime,class,nclass,tagid,tag,tagview,tagdate
FROM (ctarticle AS a LEFT JOIN ctclass AS b ON a.classid=b.classid) LEFT JOIN (ctnclass AS c RIGHT JOIN cttag AS d ON c.nclassid=d.nclassid) ON a.articleid=d.articleid;

方法相当简单, 不需要的不要拿出来, 几千行的表可能没感觉, 十万行以上的表可不是好玩的.
可能的话,为每个需要的列都建立索引, 索引据说会降低编辑数据的效率, 鄙人没这发现, 再则查询永远比更新添加的时候多.

shawl.qiu<shawl.qiu@gmail.com>
2006-5-29