大批量、多条记录插入如何加快速度(50分)

to Walone
用存储过程移植性差，最好少用。
to yubo
台湾同胞写的SQLSTMT：执行多条SQL语句。

unit SQLSTMT;
(**************************************************************************
TSQLStmts : ??Ω磅︽?? SQL statements ?じン.
?セ: 1.0
??: 兢?, Robin Li.
***************************************************************************)

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls,
Forms, Dialogs,DBTables,DB ,DsgnIntf;

type
TRunSQLErrorEvent=
procedure(E:Exception;BeginLine,EndLine:Integer; var Continue:Boolean) of Object;

TSQLStmts = class(TComponent)
private
{ Private declarations }
FSQLStmts:TStrings;
FSeparator:string;
FRemarks:TStrings;
FLogOnRunning:Boolean;
FAbortOnException:Boolean;
FOnRunSQLError:TRunSQLErrorEvent;
procedure SetRemarks(ss: TStrings);
procedure SetSQLStmts(ss: TStrings);
function GetDatabaseName:string;
procedure SetDatabaseName(s:string);
function GetSessionName:string;
procedure SetSessionName(s:string);

protected
SqlBeginLine:Integer;
{ヘ玡磅︽い? SQL 癬﹍︽腹 , zero based}

SqlEndLine:Integer;
{ヘ玡磅︽い? SQL 挡?︽腹 , zero based}

procedure DoOnRunSQLError(E:Exception;BeginLine,EndLine:Integer;
var Continue:Boolean); virtual;
{讽 Query.ExecSQL 祇ネ Exception ??岿粇矪瞶祘?}

function IsSeparator(s:String):Boolean; virtual;
{?耞琌?? SQLStmtsい, –?? SQL statements ぇ丁?だ筳?﹃}

function IsRemark(s:String):Boolean; virtual;
{?耞琌?? Remark ?﹃}

public
Query:TQuery;
{RunSQLStmts ?┮ノ?? TQuery じン}

Logs:TStrings;
{?? RunSQLStmts ?┮玻ネ? Log}

constructor Create(AOwner: TComponent); override;
{TSQLStmts ?篶じ}

destructor Destroy;
{TSQLStmts 秆篶じ}

function RunSQLStmts:Boolean;
{磅︽ property SQLStmts ?ず甧}

published
property DatabaseName:string
read GetDatabaseName write SetDatabaseName;
{磅︽ SQLStmts ┮祅??戈?畐?嘿 ,or BDE Alias}

property SessionName:string
read GetSessionName write SetSessionName;
{磅︽ SQLStmts ┮蹦ノ? Session}

property SQLStmts:TStrings
read FSQLStmts write SetSQLStmts;
{?? SQL statements}

property Separator:string
read FSeparator write FSeparator;
{SQLStmts い, –? statement ぇ丁?だ筳?﹃}

property Remarks:TStrings
read FRemarks write SetRemarks;
{SQLStmts い, 穦ノㄓ讽? Remarks ??﹃}

property LogOnRunning:Boolean
read FLogOnRunning write FLogOnRunning;
{?磅︽ RunSQLStmts ?琌??笆玻ネ Logs}

property AbortOnException:Boolean
read FAbortOnException write FAbortOnException;
{?磅︽ RunSQLStmts ?, ?狦琘? SQL statement 祇ネ岿粇, 琌?膥尿
磅︽??? SQL statement}

property OnRunSQLError:TRunSQLErrorEvent
read FOnRunSQLError write FOnRunSQLError;
{?磅︽ RunSQLStmts 讽い, SQL statement 祇ネ岿粇??ㄆン矪瞶祘?}

end;

procedure Register;

implementation

{ TDBStringProperty }
type
TDBStringProperty = class(TStringProperty)
public
function GetAttributes: TPropertyAttributes; override;
procedure GetValueList(List: TStrings); virtual; abstract;
procedure GetValues(Proc: TGetStrProc); override;
end;

function TDBStringProperty.GetAttributes: TPropertyAttributes;
begin
Result := [paValueList, paSortList, paMultiSelect];
end;

procedure TDBStringProperty.GetValues(Proc: TGetStrProc);
var
I: Integer;
Values: TStringList;
begin
Values := TStringList.Create;
try
GetValueList(Values);
for I := 0 to Values.Count - 1 do Proc(Values);
finally
Values.Free;
end;
end;

{ TDatabaseNameProperty }

type
TDatabaseNameProperty = class(TDBStringProperty)
public
procedure GetValueList(List: TStrings); override;
end;

procedure TDatabaseNameProperty.GetValueList(List: TStrings);
begin
{(GetComponent(0) as TDBDataSet).DBSession.GetDatabaseNames(List);}
Session.GetDatabaseNames(List);
end;
{ TRbSessionNameProperty }
type
TRbSessionNameProperty = class(TDBStringProperty)
public
procedure GetValueList(List: TStrings); override;
end;

procedure TRbSessionNameProperty.GetValueList(List: TStrings);
begin
DBTables.Sessions.GetSessionNames(List);
end;
procedure Register;
begin
RegisterComponents('Data Access', [TSQLStmts]);
RegisterPropertyEditor(TypeInfo(string), TSQLStmts, 'SessionName', TRbSessionNameProperty);
//RegisterPropertyEditor(TypeInfo(string), nil, 'DatabaseName', nil);
RegisterPropertyEditor(TypeInfo(string), TSQLStmts, 'DatabaseName', TDatabaseNameProperty);
end;

{ **** TSQLStmts ****}

constructor TSQLStmts.Create(AOwner: TComponent);
begin
inherited Create(AOwner);
Query:=TQuery.Create(self);
FSQLStmts:=TStringList.Create;
FRemarks:=TStringList.Create;
Logs:=TStringList.Create;
FLogOnRunning:=True;
FAbortOnException:=True;
FSeparator:='!!';
FRemarks.Add('//');
FRemarks.Add('#');
SqlBeginLine:=0;
SqlEndLine:=0;
{FRemarks.Add('/* , */');}
{FRemarks.Add('(* , *)');}
end;

destructor TSQLStmts.Destroy;
begin
Logs.Free;
FRemarks.Free;
FSQLStmts.Free;
Query.Free;
inherited Destroy;
end;

procedure TSQLStmts.SetSQLStmts(ss: TStrings);
begin
FSQLStmts.Assign(ss);
end;

procedure TSQLStmts.SetRemarks(ss: TStrings);
begin
FRemarks.Assign(ss);
end;

function TSQLStmts.GetDatabaseName:string;
begin
if Assigned(Query) then
Result:=Query.DatabaseName;
end;

procedure TSQLStmts.SetDatabaseName(s:string);
begin
if not Assigned(Query) then exit;
if Query.Active then Query.Close;
Query.DatabaseName:=s;
end;

function TSQLStmts.GetSessionName:string;
begin
if Assigned(Query) then
Result:=Query.SessionName;
end;

procedure TSQLStmts.SetSessionName(s:string);
begin
if not Assigned(Query) then exit;
if Query.Active then Query.Close;
Query.SessionName:=s;
end;

procedure TSQLStmts.DoOnRunSQLError(E:Exception;BeginLine,EndLine:Integer;
var Continue:Boolean);
begin
Continue:=not FAbortOnException;
If Assigned(FOnRunSQLError) then
FOnRunSQLError(E,BeginLine,EndLine,Continue);
end;

function TSQLStmts.IsSeparator(s:String):Boolean;
begin
Result:=(Uppercase(Trim(s))=Uppercase(Trim(FSeparator)));
end;

function TSQLStmts.IsRemark(s:String):Boolean;
var i:Integer;
begin
Result:=False;
for i:=0 to FRemarks.Count-1 do
begin
s:=Trim(s);
Result:=((Pos(FRemarks,s)=1) or (s=''));
if Result then exit;
end;
end;

function TSQLStmts.RunSQLStmts:Boolean;
var
ss:TStrings;
line:string;
i:Integer;
GoContinue:Boolean;
begin
Result:=True;
if FSQLStmts.Count=0 then
raise Exception.Create('No SQL Statements to run');
ss:=TStringList.Create;
GoContinue:=True;
try
SqlBeginLine:=-1;
SqlEndLine:=0;
i:=0;
ss.Assign(FSQLStmts);
Query.SQL.Clear;
Logs.Clear;
Result:=False;
while (ss.Count >0) and (GoContinue) do
begin
line := ss[0];
ss.Delete(0);
if not IsRemark(line) then
begin
if SqlBeginLine=-1 then SqlBeginLine:=i;
if IsSeparator(line) then
begin
SqlEndLine:=i;
try
if FLogOnRunning then Logs.Add(
Format('[Process SQL] Lines : %d to %d',[SqlBeginLine,SqlEndLine])
);
Query.ExecSQL;
Query.SQL.Clear;
SqlBeginLine:=-1;
except
On E:Exception do
begin
Result:=True;
if FLogOnRunning then Logs.Add('[Error] '+E.Message);
DoOnRunSQLError(E,SqlBeginLine,SqlEndLine,GoContinue);
Query.SQL.Clear;
SqlBeginLine:=-1;
if not GoContinue then Abort;
end;
end;{try}
end {end of if IsSeparator}
else Query.SQL.Add(line); {end of if IsSeparator else}
end; {if not IsRemark(line)}
inc(i);
end;{while}
finally
ss.Free;
end;
end;

end.

我用的是MSSQL server，6.5/7.0都支持一次运行多条SQL。
另外MySQL也支持多条insert，但不是多条SQL语句，
而是values后面可以跟多组记录值。（不知道Oracle是否可以）

因为你的BDE设置肯定都是SQL passthru的，
因此无论多少条SQL，都是直接传给DBMS的，
不存在所谓“由BDE所限制的”问题。

因而只要DBMS支持一次运行多条SQL，就肯定是没问题的。
可惜我没问清楚你使用的数据库，就给你"支了招"，有点好心帮倒忙的意思

来晚了,这两天大富翁我上不来!
谢谢朋友们的帮忙!
因分太少,就不给大伙分了,请大家原谅!

接受答案了.