我想将1千万个数字录入到一个数据表中,希望象各种彩票软件对号码排序那样。我用delphi的database desktop编写了一个数据表,有两个字段a,b分别是i型和a型,然后用bed administor建立好数据库引,再编了以下代码,可用了十分钟左右才录入不到十万个,而彩票软件只要不到一分钟时间,他们是怎么做到的,我错在哪呢。
unit shiyan;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, DBTables, Grids, DBGrids, StdCtrls;type
TForm1 = class(TForm)
Button1: TButton;
Button2: TButton;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Query1: TQuery;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;
s:tstringlist;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);//通过tstringlist变量录入1千万个数据给数据表并显示 var i:integer;
begin with query1 do
begin
for i:= 0 to s.Count-1 do
begin
close;
sql.Clear;
sql.Add('insert into aa values(:a,:b)');
params[0].asinteger:=i;
params[1].AsString:=s[i];
execsql;
end; end;
with query1 do
begin
close;
sql.Clear;
sql.Add('select * from aa');
open;
end;
s.Free;
end;procedure TForm1.Button2Click(Sender: TObject);//删除数据表中的数据
begin
with query1 do
begin
close;
sql.Clear;
sql.Add('delete from aa ');
execsql;
end;end;procedure TForm1.FormCreate(Sender: TObject);//将1千万个数赋给tstringlist变量
var f:integer;d:string;
begin
s:=tstringlist.Create;
for f:=1 to 10000000 do
begin
d:=inttostr(f);
s.Add(d);
end;
end;
end.
unit shiyan;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, DBTables, Grids, DBGrids, StdCtrls;type
TForm1 = class(TForm)
Button1: TButton;
Button2: TButton;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Query1: TQuery;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;
s:tstringlist;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);//通过tstringlist变量录入1千万个数据给数据表并显示 var i:integer;
begin with query1 do
begin
for i:= 0 to s.Count-1 do
begin
close;
sql.Clear;
sql.Add('insert into aa values(:a,:b)');
params[0].asinteger:=i;
params[1].AsString:=s[i];
execsql;
end; end;
with query1 do
begin
close;
sql.Clear;
sql.Add('select * from aa');
open;
end;
s.Free;
end;procedure TForm1.Button2Click(Sender: TObject);//删除数据表中的数据
begin
with query1 do
begin
close;
sql.Clear;
sql.Add('delete from aa ');
execsql;
end;end;procedure TForm1.FormCreate(Sender: TObject);//将1千万个数赋给tstringlist变量
var f:integer;d:string;
begin
s:=tstringlist.Create;
for f:=1 to 10000000 do
begin
d:=inttostr(f);
s.Add(d);
end;
end;
end.
//如果用MSSQL可这样做,若在database desktop,可以访问系统表做连接,直接插入到表中
//另:数据量插入这么大,最好先不创建索引,因为在创建索引后,每次除了插入数据外,还要动态校正一下B+索引树。
//可以插入数据后动态创建索引!
//以下是MSSQL2005下的语句,一次插入100000,用时30秒
CREATE TABLE #(ID INT)INSERT TOP (100000) INTO #
SELECT ROW_NUMBER() OVER(ORDER BY A.id DESC)
FROM master..syscolumns A,master..syscolumns B
CREATE TABLE [dbo].[A_Test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AStr] [varchar] (32) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--写入数据,
declare @i int
insert into A_Test(AStr)values('1');
set @i=0
while (@i<17)
begin
insert into A_Test(AStr) select AStr from A_Test
set @i=@i+1
end;select count(ID) from A_Test得到 131072 条记录-----------------------------------
你这样一条条的象数据库提交肯定慢了,应该将多条语句成批提交.