问题描述
我的程序需要通过Ado访问Access数据库,处理过程中需要遍历一个超过100000行记录的表并分析其中的数据,当我的程序启动的时候所占用的系统内存并不多,从windows任务管理器中可以看到该程序不超过10M Bytes。但是当我遍历了整个数据库表后,由于大量的纪录转移到了内存中,这时程序所消耗的内存大大增加,从windows任务管理器中可以看到甚至会超过50M Bytes。奇怪的是,当我把所用的AdoQuery控件Free掉后,程序所占用的内存依然是那么多。所以,当我的程序在不退出的情况下多处理几次数据后,程序所占用的内存就相当惊人了,甚至造成了系统虚拟内存的不足,程序的执行速度也大为降低。现请教针对这种情况应该如何处理?原理性程序的源代码://========================================================================================================
//=================================Unit1.dfm==============================================================
//========================================================================================================
object Form1: TForm1
Left = 382
Top = 250
Width = 486
Height = 179
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
PixelsPerInch = 96
TextHeight = 13
object btnStart: TButton
Left = 16
Top = 24
Width = 75
Height = 25
Caption = 'Start'
TabOrder = 0
OnClick = btnStartClick
end
object btnFree: TButton
Left = 16
Top = 112
Width = 75
Height = 25
Caption = 'Free'
TabOrder = 1
OnClick = btnFreeClick
end
object btnStop: TButton
Left = 16
Top = 64
Width = 75
Height = 25
Caption = 'Stop'
TabOrder = 2
OnClick = btnStopClick
end
object btnInsert: TButton
Left = 368
Top = 24
Width = 75
Height = 25
Caption = 'Insert'
TabOrder = 3
OnClick = btnInsertClick
end
object Button1: TButton
Left = 320
Top = 80
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 4
end
object ADOConnection1: TADOConnection
CursorLocation = clUseServer
LoginPrompt = False
Mode = cmShareDenyNone
Provider = 'Microsoft.Jet.OLEDB.4.0'
Left = 152
Top = 72
end
object ADOQuery1: TADOQuery
CacheSize = 100
Connection = ADOConnection1
CursorLocation = clUseServer
Parameters = <>
SQL.Strings = (
'select * from td2ms1')
Left = 208
Top = 72
end
object Timer1: TTimer
Enabled = False
Interval = 1
OnTimer = Timer1Timer
Left = 208
Top = 104
end
end
//========================================================================================================
//=================================Unit1.pas==============================================================
//========================================================================================================unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, ExtCtrls, StdCtrls;type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
btnStart: TButton;
Timer1: TTimer;
btnFree: TButton;
btnStop: TButton;
btnInsert: TButton;
Button1: TButton;
procedure Timer1Timer(Sender: TObject);
procedure btnFreeClick(Sender: TObject);
procedure btnStartClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure btnStopClick(Sender: TObject);
procedure btnInsertClick(Sender: TObject);
private
a:integer;
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Timer1Timer(Sender: TObject);
var
i:integer;
begin
for i:=1 to 10 do
begin
if not adoquery1.Eof then
adoquery1.Next
else begin
timer1.Enabled:=false;
showmessage('stoped');
exit;
end; if adoquery1.RecNo mod 200 =0 then caption:=inttostr(adoquery1.RecNo);
end;
end;procedure TForm1.btnFreeClick(Sender: TObject);
begin
adoquery1.Close;
adoquery1.Free;
end;procedure TForm1.btnStartClick(Sender: TObject);
begin
if adoquery1.active then adoquery1.Close;
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('select * from td2ms1');
AdoQuery1.Open;
adoquery1.First;
timer1.Enabled:=true;
end;procedure TForm1.FormCreate(Sender: TObject);
var
sPath:String;
begin
sPath:=ExtractFilePath(Application.ExeName);
if AdoConnection1.Connected then AdoConnection1.Close;
AdoConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;'+
'Data Source='+sPath+'aaa.mdb;Persist Security Info=False';
a:=0;
end;procedure TForm1.btnStopClick(Sender: TObject);
begin
timer1.Enabled:=false;
end;procedure TForm1.btnInsertClick(Sender: TObject);
var
i:Integer;
sSql:String;
begin
if adoquery1.active then adoquery1.Close;
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('select Count(*) from td2ms1');
AdoQuery1.Open;
if AdoQuery1.RecordCount>100000 then
begin
ShowMessage('已经有足够的记录可供测试');
exit;
end;
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('Delete from td2ms1');
AdoQuery1.ExecSQL;
for i:=1 to 100500 do
begin
sSql:='insert into td2ms1 (dataitemid,cputime,flag,MessageType,SubMessageType,'+
'DecodedData0,DecodedData1,DecodedData2,DecodedData3,DecodedData4,DecodedData5,'+
'DecodedData6,DecodedData7,DecodedData8,DecodedData9,Lon,Lat,OriginalData) '+
'values ('+IntToStr(i)+','''+FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',Now)+
''',''1'',''MessageType'',''SubMessageType'',''DecodedData0'',''DecodedData1'','+
'''DecodedData2'',''DecodedData3'',''DecodedData4'',''DecodedData5'','+
'''DecodedData6'',''DecodedData7'',''DecodedData8'',''DecodedData9'',''Lon'','+
'''Lat'',''OriginalData'')';
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add(sSql);
AdoQuery1.ExecSQL;
Caption:=inttostr(i);
end;
end;end.
我的程序需要通过Ado访问Access数据库,处理过程中需要遍历一个超过100000行记录的表并分析其中的数据,当我的程序启动的时候所占用的系统内存并不多,从windows任务管理器中可以看到该程序不超过10M Bytes。但是当我遍历了整个数据库表后,由于大量的纪录转移到了内存中,这时程序所消耗的内存大大增加,从windows任务管理器中可以看到甚至会超过50M Bytes。奇怪的是,当我把所用的AdoQuery控件Free掉后,程序所占用的内存依然是那么多。所以,当我的程序在不退出的情况下多处理几次数据后,程序所占用的内存就相当惊人了,甚至造成了系统虚拟内存的不足,程序的执行速度也大为降低。现请教针对这种情况应该如何处理?原理性程序的源代码://========================================================================================================
//=================================Unit1.dfm==============================================================
//========================================================================================================
object Form1: TForm1
Left = 382
Top = 250
Width = 486
Height = 179
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
OnCreate = FormCreate
PixelsPerInch = 96
TextHeight = 13
object btnStart: TButton
Left = 16
Top = 24
Width = 75
Height = 25
Caption = 'Start'
TabOrder = 0
OnClick = btnStartClick
end
object btnFree: TButton
Left = 16
Top = 112
Width = 75
Height = 25
Caption = 'Free'
TabOrder = 1
OnClick = btnFreeClick
end
object btnStop: TButton
Left = 16
Top = 64
Width = 75
Height = 25
Caption = 'Stop'
TabOrder = 2
OnClick = btnStopClick
end
object btnInsert: TButton
Left = 368
Top = 24
Width = 75
Height = 25
Caption = 'Insert'
TabOrder = 3
OnClick = btnInsertClick
end
object Button1: TButton
Left = 320
Top = 80
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 4
end
object ADOConnection1: TADOConnection
CursorLocation = clUseServer
LoginPrompt = False
Mode = cmShareDenyNone
Provider = 'Microsoft.Jet.OLEDB.4.0'
Left = 152
Top = 72
end
object ADOQuery1: TADOQuery
CacheSize = 100
Connection = ADOConnection1
CursorLocation = clUseServer
Parameters = <>
SQL.Strings = (
'select * from td2ms1')
Left = 208
Top = 72
end
object Timer1: TTimer
Enabled = False
Interval = 1
OnTimer = Timer1Timer
Left = 208
Top = 104
end
end
//========================================================================================================
//=================================Unit1.pas==============================================================
//========================================================================================================unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, ExtCtrls, StdCtrls;type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
btnStart: TButton;
Timer1: TTimer;
btnFree: TButton;
btnStop: TButton;
btnInsert: TButton;
Button1: TButton;
procedure Timer1Timer(Sender: TObject);
procedure btnFreeClick(Sender: TObject);
procedure btnStartClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure btnStopClick(Sender: TObject);
procedure btnInsertClick(Sender: TObject);
private
a:integer;
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Timer1Timer(Sender: TObject);
var
i:integer;
begin
for i:=1 to 10 do
begin
if not adoquery1.Eof then
adoquery1.Next
else begin
timer1.Enabled:=false;
showmessage('stoped');
exit;
end; if adoquery1.RecNo mod 200 =0 then caption:=inttostr(adoquery1.RecNo);
end;
end;procedure TForm1.btnFreeClick(Sender: TObject);
begin
adoquery1.Close;
adoquery1.Free;
end;procedure TForm1.btnStartClick(Sender: TObject);
begin
if adoquery1.active then adoquery1.Close;
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('select * from td2ms1');
AdoQuery1.Open;
adoquery1.First;
timer1.Enabled:=true;
end;procedure TForm1.FormCreate(Sender: TObject);
var
sPath:String;
begin
sPath:=ExtractFilePath(Application.ExeName);
if AdoConnection1.Connected then AdoConnection1.Close;
AdoConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;'+
'Data Source='+sPath+'aaa.mdb;Persist Security Info=False';
a:=0;
end;procedure TForm1.btnStopClick(Sender: TObject);
begin
timer1.Enabled:=false;
end;procedure TForm1.btnInsertClick(Sender: TObject);
var
i:Integer;
sSql:String;
begin
if adoquery1.active then adoquery1.Close;
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('select Count(*) from td2ms1');
AdoQuery1.Open;
if AdoQuery1.RecordCount>100000 then
begin
ShowMessage('已经有足够的记录可供测试');
exit;
end;
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add('Delete from td2ms1');
AdoQuery1.ExecSQL;
for i:=1 to 100500 do
begin
sSql:='insert into td2ms1 (dataitemid,cputime,flag,MessageType,SubMessageType,'+
'DecodedData0,DecodedData1,DecodedData2,DecodedData3,DecodedData4,DecodedData5,'+
'DecodedData6,DecodedData7,DecodedData8,DecodedData9,Lon,Lat,OriginalData) '+
'values ('+IntToStr(i)+','''+FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',Now)+
''',''1'',''MessageType'',''SubMessageType'',''DecodedData0'',''DecodedData1'','+
'''DecodedData2'',''DecodedData3'',''DecodedData4'',''DecodedData5'','+
'''DecodedData6'',''DecodedData7'',''DecodedData8'',''DecodedData9'',''Lon'','+
'''Lat'',''OriginalData'')';
AdoQuery1.SQL.Clear;
AdoQuery1.SQL.Add(sSql);
AdoQuery1.ExecSQL;
Caption:=inttostr(i);
end;
end;end.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货