"ACCESS2000数据库与DBF数据库的比较",麻烦大了!(200分)

  • 主题发起人 主题发起人 baoling
  • 开始时间 开始时间
B

baoling

Unregistered / Unconfirmed
GUEST, unregistred user!
我做了一个很简单的练习
用ADO动态创建ACCESS数据库,然后加上10000笔数据.
用Halcyon69动态创建DBF数据库,然后加上10000笔数据.
比较:1、数据的加入删除速度
2、加入10000笔数据后数据库的大小

ACCESS数据库中表结构,DBF数据库结构跟ACCESS数据库表的结构一致:
编号 INTEGER
姓名 VARCHAR (16) NOT NULL
工资 CURRENCY
婚否 LOGICAL

1)发现了一个非常奇怪的问题,在Win98下运行（ADO2.6），加入10000笔
数据后Access的数据库竟然达到310M，而加入10000笔数据的DBF数据库大小仅为340K，
我在Win2000下运行,加入10000笔数据后,ACCESS数据库的大小为420K,我把在
Win98运行生成的310M的数据库压缩了一下,数据库的大小变为420K左右,这是怎
么回事?
2)为什么用DeleteRecords(arAll)删除Access数据表中的数据会出错?有什么更好的快速
删除记录的方法?


//递增10000个数据

procedure TForm1.Button3Click(Sender: TObject);
var
I, k: Integer;
begin
k := ADOTable1.RecordCount;
for I := 1 to 10000 do // Iterate
begin
Application.ProcessMessages;
Label1.caption := inttostr(I );
ADOTable1.Append;
ADOTable1.FieldByName('编号').AsInteger := I+K;
ADOTable1.FieldByName('姓名').AsString := '张老' + inttostr(i+k);
ADOTable1.FieldByName('工资').AsFloat := 3000.33;
ADOTable1.FieldByName('婚否').AsBoolean := true;
end;
ADOTable1.Post;
LookFileSize; //显示文件尺寸
end;

执行文件:http://hwave.myetang.com/File/size.zip ( 300K)
源码文件:http://hwave.myetang.com/File/unit1.txt (10K)
 
呵呵,Access数据库不经搞。
 
算了,我把整个源码帖进来吧,希望大家多多指点
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
ExtCtrls, AdoMakeMdb, Db, ADODB, Grids, DBGrids, StdCtrls, ZIPACCESS,
Halcn6db, MyPath;

type
TForm1 = class(TForm)
ADOTable1: TADOTable;
AdoMakeMdb1: TAdoMakeMdb; //用ADO动态生成数据库的控件
Panel1: TPanel;
Panel2: TPanel;
DBGrid1: TDBGrid;
DBGrid2: TDBGrid;
Panel3: TPanel;
Panel4: TPanel;
Panel5: TPanel;
Panel6: TPanel;
Button2: TButton;
Button3: TButton;
Button4: TButton;
Button5: TButton;
Button6: TButton;
Button7: TButton;
Button8: TButton;
Button9: TButton;
Button10: TButton;
Button11: TButton;
Button12: TButton;
MyPath1: TMyPath; //包含Windows系统路径.本程序路径的控件
DataSource1: TDataSource;
CreateHalcyonDataSet1: TCreateHalcyonDataSet;
HalcyonDataSet1: THalcyonDataSet;
DataSource2: TDataSource;
ZipAccess1: TZipAccess; //ACCESS数据库压缩控件
ADOQuery1: TADOQuery;
Panel7: TPanel;
Panel8: TPanel;
Button1: TButton;
Label1: TLabel;
Label2: TLabel;
ADOConnection1: TADOConnection;
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
procedure Button7Click(Sender: TObject);
procedure Button10Click(Sender: TObject);
procedure Button11Click(Sender: TObject);
procedure Button8Click(Sender: TObject);
procedure Button9Click(Sender: TObject);
procedure Button12Click(Sender: TObject);
procedure Button6Click(Sender: TObject);
private
procedure LookFileSize;
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.FormCreate(Sender: TObject);
var
s: string;
begin
//建立空DBF数据库
HalcyonDataSet1.DatabaseName := MyPath1.SelfPath;
HalcyonDataSet1.TableName := 'CDBF.DBF';
S := MyPath1.AddPath('CDBF.DBF');
if not FileExists(S) then
begin
with CreateHalcyonDataSet1.CreateFields do
begin
Add('编号;N;10;0');
ADD('姓名;C;16;0');
ADD('工资;N;7;2');
ADD('婚否;L;1;0');
end;
CreateHalcyonDataSet1.Execute;
end
else
HalcyonDataSet1.Active := true;

S := MyPath1.AddPath('CMDB.MDB');


//建立空的Access库
ADOConnection1.ConnectionString :=
'Provider=Microsoft.Jet.OLEDB.4.0;' +
'Password="";' +
'User ID=Admin;' +
'Data Source=' + S + ';' +
'Mode=Share Deny None;' +
'Extended Properties="";' +
'Locale Identifier=2052;' +
'Jet OLEDB:System database="";' +
'Jet OLEDB:Registry Path="";' +
'Jet OLEDB:Database Password="";' +
'Jet OLEDB:Engine Type=5;' +
'Jet OLEDB:Database Locking Mode=1;' +
'Jet OLEDB:Global Partial Bulk Ops=2;' +
'Jet OLEDB:Global Bulk Transactions=1;' +
'Jet OLEDB:New Database Password="";' +
'Jet OLEDB:Create System Database=False;' +
'Jet OLEDB:Encrypt Database=False;' +
'Jet OLEDB:Don''t Copy Locale on Compact=False;' +
'Jet OLEDB:Compact Without Replica Repair=False;' +
'Jet OLEDB:SFP=False';

ADOQuery1.ConnectionString := ADOConnection1.ConnectionString;

if not FileExists(S) then
begin
//建库
AdoMakeMdb1.DBName := S;
AdoMakeMdb1.MakeNewDB;

//建表
ADOQuery1.Close;
ADOQuery1.SQL.Text :=
'CREATE TABLE CS (' +
'编号 INTEGER,' +
'姓名 VARCHAR (16) NOT NULL,' +
'工资 CURRENCY ,' +
'婚否 LOGICAL)';
ADOQuery1.ExecSQL;
end;
ADOTable1.TableName := 'CS';
ADOTable1.Active := true;
//显示尺寸

LookFileSize;
end;

procedure TForm1.LookFileSize;
var
FF: TSearchrec;
begin
if FindFirst(MyPath1.SelfPath + 'CMDB.MDB', faAnyFile, FF) = 0 then
panel7.Caption := 'MDB数据库尺寸: ' + inttostr(FF.Size) + ' 字节';
if FindFirst(MyPath1.SelfPath + 'CDBF.DBF', faAnyFile, FF) = 0 then
panel8.Caption := 'DBF数据库尺寸: ' + inttostr(FF.Size) + ' 字节';
findclose(FF);
end;

//删除全部数据

procedure TForm1.Button4Click(Sender: TObject);
var
I: Integer;
begin
//ADOTable1.DeleteRecords(arAll);

i := 0;
ADOTable1.First;
while not ADOTable1.eof do
begin
INC(i);
Application.ProcessMessages;
Label1.Caption := inttostr(i);
ADOTable1.DeleteRecords(arCurrent);
end;

end;

//递增100个数据

procedure TForm1.Button1Click(Sender: TObject);
var
I, k: Integer;
begin
k := ADOTable1.RecordCount;
for I := 1 to 100 do // Iterate
begin
Application.ProcessMessages;
Label1.caption := inttostr(I );
ADOTable1.Append;
ADOTable1.FieldByName('编号').AsInteger := I+k;
ADOTable1.FieldByName('姓名').AsString := '张老' + inttostr(i+k);
ADOTable1.FieldByName('工资').AsFloat := 3000.33;
ADOTable1.FieldByName('婚否').AsBoolean := true;
end;
ADOTable1.Post;
LookFileSize;
end;

//递增1000个数据

procedure TForm1.Button2Click(Sender: TObject);
var
I, k: Integer;
begin
k := ADOTable1.RecordCount;
for I := 1 to 1000 do
begin
Application.ProcessMessages;
Label1.caption := inttostr(I );
ADOTable1.Append;
ADOTable1.FieldByName('编号').AsInteger := I+k;
ADOTable1.FieldByName('姓名').AsString := '张老' + inttostr(i+k);
ADOTable1.FieldByName('工资').AsFloat := 3000.33;
ADOTable1.FieldByName('婚否').AsBoolean := true;
end;
ADOTable1.Post;
LookFileSize;
end;

//递增10000个数据

procedure TForm1.Button3Click(Sender: TObject);
var
I, k: Integer;
begin
k := ADOTable1.RecordCount;
for I := 1 to 10000 do // Iterate
begin
Application.ProcessMessages;
Label1.caption := inttostr(I );
ADOTable1.Append;
ADOTable1.FieldByName('编号').AsInteger := I+K;
ADOTable1.FieldByName('姓名').AsString := '张老' + inttostr(i+k);
ADOTable1.FieldByName('工资').AsFloat := 3000.33;
ADOTable1.FieldByName('婚否').AsBoolean := true;
end;
ADOTable1.Post;
LookFileSize;
end;

//压缩MDB

procedure TForm1.Button5Click(Sender: TObject);
begin
ADOTable1.Active := False;
ADOConnection1.Close;
ADOConnection1.Mode:=cmUnknown;
ZipAccess1.Zip;
ADOConnection1.Open;
ADOTable1.Active := True;
LookFileSize;
end;

//DBF增加100个数据

procedure TForm1.Button7Click(Sender: TObject);
var
I, k: Integer;
begin
k := HalcyonDataSet1.RecordCount;
for I :=1 to 100 do // Iterate
begin
Application.ProcessMessages;
Label2.caption := inttostr(I );
HalcyonDataSet1.Append;
HalcyonDataSet1.FieldByName('编号').AsInteger := I+k;
HalcyonDataSet1.FieldByName('姓名').AsString := '张老' + inttostr(i+k);
HalcyonDataSet1.FieldByName('工资').AsFloat := 3000.33;
HalcyonDataSet1.FieldByName('婚否').AsBoolean := true;
end;
HalcyonDataSet1.Post;
LookFileSize;
end;

procedure TForm1.Button10Click(Sender: TObject);
var
I, k: Integer;
begin
k := HalcyonDataSet1.RecordCount;
for I := 1 to 1000 do // Iterate
begin
Application.ProcessMessages;
Label2.caption := inttostr(I);
HalcyonDataSet1.Append;
HalcyonDataSet1.FieldByName('编号').AsInteger := I+k;
HalcyonDataSet1.FieldByName('姓名').AsString := '张老' + inttostr(i+k);
HalcyonDataSet1.FieldByName('工资').AsFloat := 3000.33;
HalcyonDataSet1.FieldByName('婚否').AsBoolean := true;
end;
HalcyonDataSet1.Post;
LookFileSize;
end;

procedure TForm1.Button11Click(Sender: TObject);
var
I, k: Integer;
begin
k := HalcyonDataSet1.RecordCount;
for I := 1 to 10000 do // Iterate
begin
Application.ProcessMessages;
Label2.caption := inttostr(I );
HalcyonDataSet1.Append;
HalcyonDataSet1.FieldByName('编号').AsInteger := I+k;
HalcyonDataSet1.FieldByName('姓名').AsString := '张老' + inttostr(i+k);
HalcyonDataSet1.FieldByName('工资').AsFloat := 3000.33;
HalcyonDataSet1.FieldByName('婚否').AsBoolean := true;
end;
HalcyonDataSet1.Post;
LookFileSize;
end;

procedure TForm1.Button8Click(Sender: TObject);
var
I: Integer;
begin
i := 0;
HalcyonDataSet1.First;
while not HalcyonDataSet1.eof do
begin
INC(i);
Application.ProcessMessages;
Label2.Caption := inttostr(i);
HalcyonDataSet1.Delete;
end;
LookFileSize;
end;

procedure TForm1.Button9Click(Sender: TObject);
begin
HalcyonDataSet1.Active:=false;
HalcyonDataSet1.Exclusive:=True;
HalcyonDataSet1.Active:=True;
HalcyonDataSet1.Pack;
LookFileSize;
end;

procedure TForm1.Button12Click(Sender: TObject);
begin
HalcyonDataSet1.Active:=false;
HalcyonDataSet1.Exclusive:=True;
HalcyonDataSet1.Active:=True;
HalcyonDataSet1.ZAP;
LookFileSize;
end;

procedure TForm1.Button6Click(Sender: TObject);
var
I,K:Integer;
begin

k := ADOTable1.RecordCount;
ADOTable1.Active:=False;
for I := 1 to 100 do
begin
Application.ProcessMessages;
Label1.caption := inttostr(I );
ADOQuery1.Close;
ADOQuery1.SQL.Text :=
'INSERT INTO CS (编号,姓名,工资,婚否) VALUES ('+
inttostr(I+k)+',"张老'+inttostr(I+k)+'",'+'3333.33,'+'TRUE)';
ADOQuery1.ExecSQL;
end;
Application.ProcessMessages;
ADOTable1.Active:=true;
DBGrid1.Refresh;
end;

end.

 
access数据库在进行多次插入删除后数据库将会变大，但是你可以用程序压缩compactdatabase
我才插入几条记录后就有几M，压缩后就只有几百k了.
 
to yanghai0437:==>"access数据库在进行多次插入删除后数据库将会变大"
新建的数据库首次插入数据也是如此,不信你可下载一试
http://hwave.myetang.com/File/size.zip
 
1:
win98中就是这样啦！用Access也这样！例如：你倒一笔数据进去，文件
变的很大，然后再删除，文件的大小居然没变！估计是系统的问题。设置错误
的可能性较小！在‘退出’或大批量操作后你就把‘修护/压缩’当 ‘刷新’
用吧；
2：
看看HELP吧！
事先用Supports方法测试数据集是否支持特定的操作，否则要抛出错误！
还有与ADOTABLE的设置有关！自己试试吧！
 
您必须登录或注册才可回复。

Similar threads

S
[转]ClientDataSet加上TDataSetProvider的数据保存问题
回复
0
查看
3K
SUNSTONE的Delphi笔记
S
S
[转]ClientDataSet加上TDataSetProvider的数据保存问题
回复
0
查看
2K
SUNSTONE的Delphi笔记
S
I
ADO对象存取ODBC数据库
回复
0
查看
783
import
I
I
XML 与数据库表的转换
回复
0
查看
674
import
I
I
OleContainer和数据库问题
回复
0
查看
563
import
I
后退
顶部