用ADO从文本文件中导入数据库的两种方法比较
我用的这两种方法，不管是哪一种，大体原理上都是打开文本，然后一行行读出，再根据分隔符拆分字段，然后再用ADO来一条条记录插入数据库中，但采用的ADO的方式不同，第一种用普通的ADOQUERY来操作，第二种则采用ADO的原生对象来操作，但在速度上是明显后一种占优的，且是很大的优势，原因我说不来，但认为是个不错的方法，虽然导入文本还有别的很好的方法，例如采用SQLSERVER的DTS等。但这些我还没做过尝试，就暂不说了。
１、
if dlgOpendatain.Execute then
begin
datapath := dlgOpendatain.FileName; //取得文件路径
Filepath := ExtractFileName(datapath); //取得文件名
AssignFile(TeFile1, datapath); //将文件变量与文件关联
Reset(TeFile1); //以读写方式打开类型文件和无类型文件
try
j := 0;
while not Eof(TeFile1) do
begin
application.ProcessMessages;
Readln(TeFile1, str); //一行一行的读文件，str为一行的字符串
recordstr := str;
inc(j);
i := 0;
while pos(#9, recordstr) > 0 do //#9是tab分隔符
begin
str := Copy(recordstr, 1, Pos(#9, recordstr) - 1);
case i of
0:str1 := str;
1:str2 := str;
end;
i := i + 1;
recordStr := copy(recordstr, (Pos(#9, recordstr) + 1),
length(recordstr));
if i = 2 then
str4 := trim(uppercase(recordStr));
end;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.TEXT:='INSERT INTO S1,S2,S3 VALUES('+''''+
str1''''+','+''''+str2+''''+','+''''+str3+''''+')';
ADOQuery1.Prepare;
ADOQuery1.ExecSQL ;
end;
MessageBox(GetactiveWindow(), '当前导入数据完毕！', '提示',
MB_OK + mb_iconexclamation);
except
CloseFile(TeFile1);
end;
end;
2、
按下面的方法，四万条记录全部导入ACCESS，直接从文本中装载23.90秒，当我将文本装载到MEMO1中的时候，八万条记录全部导入ACCESS也不过四十一秒。
unit Unit1;
interface
uses
Windows,
Messages,
SysUtils,
Variants,
Classes,
Graphics,
Controls,
Forms,
Dialogs,
StdCtrls,
ComCtrls,
Gauges,
DB,
ADODB,
ComObj,
ADOInt;
type
TStrArray = array[1..9] of string;
type
TForm1 = class(TForm)
Button1: TButton;
OpenDialog1: TOpenDialog;
StatusBar1: TStatusBar;
Gauge1: TGauge;
ADOConnection: TADOConnection;
Label1: TLabel;
Memo1: TMemo;
procedure Button1Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1 : TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
const
TabChar = #32; //定义空格键
var
i, j : integer;
D1 : Real;
TeFile1 : TextFile;
datapath, Filepath, str: string;
dd : TStrArray;
AdoRecordSet : variant;
FConnectionObject : _Connection;
begin
AdoRecordSet := CreateOleObject('ADODB.RecordSet');
ADOConnection.Connected := True;
FConnectionObject := ADOConnection.ConnectionObject;
AdoRecordSet.CursorLocation := clUseClient;
AdoRecordSet.open('select * from abcdefg where 1=2', FConnectionObject,
adOpenStatic, adLockOptimistic, AdCmdText); //写数据库
try
OpenDialog1.Title := '请选择要打开的文件';
OpenDialog1.Filter := '数据文件(*.txt)|*.txt';
if OpenDialog1.Execute then
begin
DataPath := OpenDialog1.FileName; //取得文件路径
Filepath := ExtractFileName(DataPath); //取得文件名
// Memo1.Lines.Clear;
// Memo1.Lines.LoadFromFile(DataPath); //.LoadFromStream(ms2);
AssignFile(TeFile1, DataPath); //将文件变量与文件关联
Reset(TeFile1); //以读写方式打开类型文件和无类型文件
statusbar1.Panels[1].Text := FormatDateTime('hh:mm:ss', now()); //开始时间
//Gauge1.MaxValue:=Memo1.Lines.Count;
D1 := Now;
try
// for i := 0 to Memo1.Lines.Count do
while not Eof(TeFile1) do
begin
Application.ProcessMessages;
Readln(TeFile1, str); //一行一行的读文件，str为一行的字符串
statusbar1.Panels[3].Text := str;
//str := Memo1.Lines[i];
j := 1;
while pos(TabChar, str) > 0 do
begin
dd[j] := Copy(str, 1, pos(TabChar, str));
Delete(str, 1, Pos(TabChar, str) + 1);
Str := TRIM(Str);
Inc(j);
end;
DD[9] := str;
AdoRecordSet.AddNew;
try
AdoRecordSet.fields[1].value := DD[1];
AdoRecordSet.fields[2].value := DD[2];
AdoRecordSet.fields[3].value := DD[3];
AdoRecordSet.fields[4].value := DD[4];
AdoRecordSet.fields[5].value := DD[5];
AdoRecordSet.fields[6].value := DD[6];
AdoRecordSet.fields[7].value := DD[7];
AdoRecordSet.fields[8].value := DD[8];
AdoRecordSet.fields[9].value := DD[9];
AdoRecordSet.Update;
except
end;
Inc(i);
//Gauge1.Progress:=Gauge1.Progress + 1;
Label1.Caption:=IntToStr(i);
end;
statusbar1.Panels[2].Text := FormatDateTime('hh:mm:ss', now()); //结束时间
statusbar1.Panels[0].Text := '总用时： ' + Floattostr((NOW - D1) * 24 *
60 * 60);
MessageBox(GetactiveWindow(), '当前导入数据完毕！', '提示',
MB_OK + mb_iconexclamation);
except
CloseFile(TeFile1);
end;
end;
finally
AdoRecordSet.Close;
end;
end;
procedure TForm1.FormCreate(Sender: TObject);
var //联接ACCESS数据库的方法。
path : string;
begin
path := ExtractFilePath(Application.ExeName); //程序路径
if path[Length(path)] <> '\' then
path := path + '\';
ADOConnection.Connected := False;
try
ADOConnection.ConnectionString :=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' +
path + 'Data.MDB' + ';Persist Security Info=False';
ADOConnection.Connected := true;
except
MessageBox(GetActiveWindow(), '系统错误!', '警告', MB_OK + MB_ICONWARNING);
application.Terminate;
end;
end;
end.
1、改进 SQL 语句，IN 的效率比 EXISTS 要低得多；
2、使用原生 ADO 对象，从存取对象一级提高效率（别告诉我 ADOExpress 很快）。
3、事务能提高效率？别开玩笑了！
4、代码如下：
uses ComObj;
procedure FastDeleteAInB(DBName: string);
const
adCmdText = $00000001;
var
cmd: OleVariant;
begin
cmd := CreateOleObject('ADODB.Command');
cmd.CommandType := adCmdText;
//cmd.CommandTimeout := 15;
cmd.ActiveConnection := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + DBName;
cmd.CommandText := 'DELETE FROM LinkGrp WHERE EXISTS (SELECT * FROM B WHERE LinkGrp.LGID = B.LGID)';
cmd.Prepared := True;
cmd.Execute;
cmd := Unassigned;
end;
5、有效果的话，和其他几个问题一并结了吧。
