请问如何提高数据库使用的速度?

alkyl 2003-08-23 01:11:41
我有一个记录数在40000到45000左右的access数据库,字段只有3个。

现在我打算用vb写一个程序来操作它,但是记录数那么多,操作的速度略微难以忍受。

请问各位前辈,如何提高数据库的操作速度?
...全文
17 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
vcbug 2003-08-25
  • 打赏
  • 举报
回复
good
alkyl 2003-08-23
  • 打赏
  • 举报
回复
虽然是转帖的,但是对我这样的新手也是非常有帮助的!

谢谢你们的帮忙!现在就结帖 ^_^
neo40 2003-08-23
  • 打赏
  • 举报
回复
转帖还真多呀,^o^
viena 2003-08-23
  • 打赏
  • 举报
回复
几种优化的方法:(from 泰山)
一、表的设计
当在表中添加字段的时候,应该选择长度最小的数据类型,这样表在内存中每页可以存储更多的记
录。如:“姓名”字段一般设置为TEXT类型,长度为10一般就够用,则比默认的255要好的多。整
型Integer的长度是2,在使用整型Integer就可以解决问题的地方不要使用Single、Long、
Double、Currency,因为它们的长度分别为4、4、8、8,都比2大。在建立表后一定要建立
索引,这可以大大提高查询速度,是提高速度最基本的要求。
二、压缩数据库
JET数据库的查询优化是有代价的,随着数据库的不断扩大,优化将不再起作用。压缩数据库会改
变数据库的状态,并重新优化所有查询。同时,随着数据库的增大,会产生很多碎片。而压缩数据
库可以把一个表中的数据数据写到硬盘中连续的页里,提高了顺序搜索的速度。
压缩数据库使用CompactDatabase命令,下面的语句压缩数据库并产生一个数据库备份:
DBEngine.CompactDatabase “C:\VB\BIBLIO.MDB”, “C:\VB\BIBLIO2.MDB”
Kill “C:\VB\BIBLIO.BAK” Name “C:\VB\BIBLIO.MDB” As “C:\VB\BIBLIO.BAK”
Name “C:\VB\BIBLIO2.MDB” As “C:\VB\BIBLIO.MDB”
注意,如果数据库很大的话,可能需要整夜的时间来压缩数据库。
三、避免查询输出里面多余的计算
当查询的结果作为另外一个查询的数据源的时候,可能引起查询优化问题。在这个时候第一次查询
里面尽量避免大量的计算。在如下示例中,Query1是第一个查询的结果,然后它作为第二个查询
的数据源。
Dim DB As Database
Dim RS As RecordSet
Set DB = DBEngine.Workspaces(0).Opendatabase(“Biblio.MDB”)
DB.CreateQueryDef(“Query1”, _
“SELECT IIF(Au_ID=1,’Hello’,’Goodbye’) AS X FROM Authors”)
Set RS = DB.OpenRecordSet(“SELECT * FROM Query1 WHERE X=’Hello’”)
由于在第一个查询Query1中的IIF()表达式不能被优化,,所以在第二个查询中的WHERE子句也不
能被优化。如果一个表达式在一个查询树中埋藏的很深的话,则这个查询不可被使用,它是不可优
化的。
如果可能的话,把这个SQL语句合并为一个没有嵌套的SQL语句:
Set RS = DB.OpenRecordSet(“SELECT * FROM Authors WHERE Au_ID=1”)
对于更灵活的嵌套查询,尽量在SQL语句中使用字段名,如:
DB.CreateQueryDef(“Query1”, _
“SELECT IIF(Au_ID=1,’Hello’,’Goodbye’) AS X, Au_ID, FROM Authors”)
Set RS = DB.OpenRecordSet(“SELECT * FROM Query1 WHERE Au_ID=1”)
如果在查询输出中实在无法避免计算式的话,尽量把计算式放在最外层,不要放在最内层。
四、只输出需要的字段
在建立查询的时候,仅返回需要的字段,这样可以节省不必要的开支。如果某个字段不是你需要
的,不要在查询语句中出现。上面的事例正说明了这个问题。
五、分组、合并及汇总
这里要说明的主要是合并,当你需要把两个表合并,就是说:当你要根据“Customer Name”对两
个表进行合并,要肯定GROUP BY field (Customer Name)和汇总(Sum, Count, and 等)的字段
是来自同一张表。
例如:下列语句优化性较差,因为SUM子句来自Ord表,而GROUP BY子句来自Cust表:
SELECT Cust.CustID,
FIRST(Cust.CustName) AS CustName,
SUM(Ord.Price) AS Total
FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID
GROUP BY Cust.CustID
如果按照Ord.CustID分组,查询性能就好的多了:
SELECT Ord.CustID,
FIRST(Cust.CustName) AS CustName,
SUM(Ord.Price) AS Total
FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID
GROUP BY Ord.CustID
六、尽量减少分组的字段
SQL语句中分组(GROUP BY)的字段越多,执行查询的时间越长。在GROUP BY子句中尽量用
aggregate函数来减少字段的数量。
如:
GROUP BY As Few Fields As Possible
SELECT Cust.CustID,
Cust.CustName,
Cust.Phone,
SUM(Ord.Price) AS Total
FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID
GROUP BY Cust.CustID, Cust.CustName, Cust.Phone
可以改为::
SELECT Ord.CustID,
FIRST(Cust.CustName) AS CustName,
FIRST(Cust.Phone) AS Phone,
SUM(Ord.Price) AS Total
FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID
GROUP BY Ord.CustID
七、在合并之前嵌套GROUP BY子句
如果要合并两张表,而且只在一张表中分组,把查询分为两个SELECT语句要快的多。
如:
SELECT Ord.CustID,
FIRST(Cust.CustName) AS CustName,
FIRST(Cust.Phone) AS Phone,
SUM(Ord.Price) AS Total
FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID
GROUP BY Ord.CustID
可改为:
查询1:
SELECT CustID, SUM(Price) AS Total
FROM Ord
GROUP BY CustID
查询2:
SELECT Query1.CustID, Cust.CustName, Cust.Phone, Query1.Total
FROM Cust INNER JOIN Ord ON Cust.CustID = Ord.CustID
八、在合并的时候两边的字段都设置索引
在合并表的时候,尽量使两边的字段都设置索引。这在执行查询的时候查询优化器可以更多的使用
sophisticated 内部合并策略。
当然,在关系型数据库中,表要设计的尽量小,(最好1-2K页),这样删除表的索引的时候要快
的多,这是因为内存中读入了很少的页。这需要根据实际情况多次测试。
九、添加索引来提高查询和排序的速度
为合并或查询条件中的所有使用字段建立索引。Microsoft Jet 2.0极其以后版本的数据库引擎
使用使用了Rushmore查询优化技术,因此支持一张表的复合索引。
要尽量避免在查询条件中进行计算或在查询条件中使用未索引的字段。排序更是如此,绝对要避免
计算或使用未索引的字段。
十、使用可优化的表达式
重新构造查询语句,以便于Rushmore技术可以对其进行优化。Rushmore是一种数据访问技术,使
用它可以提高记录集的查询速度。使用Rushmore的时候,若在查询条件中使用具体类型的表达
式,查询速度将非常快。Rushmore不会自动为你的查询提高速度,你必须按照具体的方法修改查
询语句,以便于Rushmore可以优化它们。
十一、用COUNT(*)代替 COUNT([Column Name])
Microsoft Jet数据库引擎有特别的优化方法,它在使用COUNT(*)要比用COUNT([Column Name])
快得多。
注意,这两个运算符是有差别的:
Count(*) 计算所有的行。
Count([Column Name])计算所有Column Name非空的行。
十二、在变量中避免使用LIKE
由于在查询完成的时候变量的值不确定,所以无法使用索引,这样,建立的索引就失去了意义,这
就严重制约着查询速度。
十三、避免LIKE和统配符同时使用
如果要把LIKE运算符同统配符一起使用,为了使用索引,必须把统配符放在后面。
如,下列语句利用了索引。
Like "Smith"
Like "Sm*"
而下列语句根本没有使用索引:
Like "*sen"
Like "*sen*"
十四、测试合并约束
如果要在合并中使用表达式约束一个字段的数值,需要测试表达式放在合并的一侧,还是其他地
方,看哪种查询的速度较快。在一些查询中,表达式放在合并关键词join一侧反而比较快。
十五、使用中间结果表
用SELECT INTO建立工作表,尤其是结果集用于几个查询的时候,尽量使用中间结果表。在查询前
做的准备工作越多,查询速度越快。
十六、避免子SELECT语句和NOT IN同时使用
子SELECT语句和NOT IN同时使用很难优化,取反嵌套的查询或OUTER JOINs影响很大。
下列事例查询不在orders表中的用户:
优化前:
SELECT Customers.*
FROM Customers
WHERE Customers.[Customer ID]
NOT IN (SELECT [Customer ID] FROM Orders);
优化后:
SELECT Customers.*
FROM Customers LEFT JOIN Orders
ON Customers.[Customer ID] = Orders.[Customer ID]
WHERE ((Orders.[Customer ID] Is Null));
qingming81 2003-08-23
  • 打赏
  • 举报
回复
各个字段添加索引!
neo40 2003-08-23
  • 打赏
  • 举报
回复
全面优化ADO kinmax(转贴)

关键字 ADO

出处 http://www_aspsky_net.htm/


1 Connection
1.1 Pooling
在Web Application中,常常会出现同时有很多用户同时访问数据库的情况,而且ASP中的对象作用域是页面级的,也就是
说,每个页面都要联接和断开数据库,岂不是会很慢?而且每个到SQL Server数据库的联接会带来37k的系统开销,怎么
办?
可能有人会想到用Application和Session来解决问题,但是,这是不可取的,如果用Application,那么会出现多个用户同时通过一个Connection访问数据库的情况,虽然节省了建立连接的时间,但是访问数据库的速度就会变得非常慢。如果用Session,出现的问题就是,Session超时怎么办?如果把Session.Timeout设得很大,那用户离开之后,连接还会保留一段时间,也会带来额外的开销。
其实根本不用考虑这个问题,通过OLE DB访问数据库,它会替你解决这个问题,OLE DB有一个Resource Pooling,它会代
理你的连接请求,然后把别人刚用过的连接给你接着用。(具体机制不再阐述,其实我也没搞太明白,嘻嘻)
1.2 Provider
可能没有多少人用过这个Property吧,它的缺省值是MSDASQL,还有MSIDXS和ADSDSOObject,但是在ADO2.0(见VS98)和
ADO2.1(见SQL7)里面提供了一些新的Provider:
MSDAORA (OLE DB Provider for Oracle)
Microsoft.Jet.OLEDB.3.51(OLE DB Provider for Microsoft Jet( for ACCESS))
SQLOLEDB(Microsoft SQL Server OLE DB Provider)
如果你所用的数据库是这些的话,用这些新的Provider就可以不通过ODBC而直接访问数据库,提高的效率就可想而知了。
2 Command
2.1 CommandType
缺省值是adCmdUnknown,ADO会逐个判断你的CommandType,直到它认为合适为止,不建议采用。(在Recordset.Open和
Connection.Execute的时候也可以用)
adCmdText是照原样执行你的SQL语句,但是如果你的SQL Language是以下几种的话,通过使用别的CommandType就可以提高
你的SQL语句执行效率
objCmd.Execute "Select * from table_name", adCmdText可替换为objCmd.Execute "table_name",adCmdTable
objCmd.Execute "Exec proceuure_name",adCmdText可替换为objCmd.Execute "proceuure _name", adCmdStoredProc
还有很重要的一点就是,如果你的SQL语句没有返回记录集,如insert和update等,那么使用adExecuteNoRecords
(ADO2.0)可以减低系统开销(可以加到adCmdText 和adCmdStoredProc上,如adCmdStoredProc + adExecuteNoRecords)
还有adCmdTableDirect和adCmdFile(ADO2.0),我还不太清楚怎么用,adCmdFile可用于访问一个XML文件。
2.2 Prepared
如果你需要重复的执行类似的SQL语句,那么你可以预编译你的SQL语句,提高的效率也很可观
objCmd.CommandText = "SELECT spell from TYPER.wordspell where word = ? "
objCmd.Prepared = True
objCmd.Parameters.Append objCmd.CreateParameter("word", adVarChar, , 2)
For i = 1 To Len(strName)
strChar = Mid(strName, i, 1)
objCmd("word") = strChar
Set objRS = objCmd.Execute
If objRS.EOF Then
strNamesame = strNamesame & strChar
Else
strNamesame = strNamesame & objRS("spell")
End If
Next ''i = 1 To Len(strName)
3 Recordset
3.1 LockType
缺省是adLockReadOnly,如果你不用修改数据,就不要改成adLockOptimistic之类的,否则也会减低速度和增加开销的
adLockReadOnly > adLockPessimistic > adLockOptimistic > adLockBatchOptimistic
3.2 CursorType
缺省是adOpenForwardOnly,如果你只用MoveNext Method,也最好不要改,速度影响140%左右
adOpenForwardOnly > adOpenDynamic > adOpenKeyset > adOpenStatic
3.3 CursorLocation
缺省是adUseServer,其实不好,它可以随时反映数据库服务器上的改动,但是系统开销很大,而且需要维持和数据库服务
器的连接,但是在数据库服务器和Web Server在一起的时候要快些。不过在adLockOptimistic的时候使我无法使用
RecordCount等Property。
使用用adUseClient的话,你可以对数据做再排序,筛选,shape等操作
如果对数据的实时性没有要求的话,尽量用adUseClient
4 其它
4.1 Early bind
用ASP这一点就不用看了,如果用VB的话
Dim objConn As ADODB.Connection 比 Set objConn = CreateObject("ADODB.Connection")要好
4.2 ADO 2.1里的shape真是好玩
4.3 ADO 2.1可以用objRS.Fields.Append来建立一个Recordset
4.4 把Recordset的一列数据直接变成一个数组来操作速度快一些,但是系统开销要大一些


neo40 2003-08-23
  • 打赏
  • 举报
回复
在主页上搜索‘全面优化ADO’有一篇文章讲的就是这个,就是你要的
frankwong 2003-08-23
  • 打赏
  • 举报
回复
在创建表的界面中创建索引

1,216

社区成员

发帖
与我相关
我的任务
社区描述
VB 数据库(包含打印,安装,报表)
社区管理员
  • 数据库(包含打印,安装,报表)社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧