1

Topic: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

All greetings.
Help to solve a problem:
I read from the text file a line, I assort on components and I do different checks. It is thus fulfilled from 4 to 6 simple requests "SELECT" (a maximum the data from two tables).
After prepared the data I interpose into table INSERT or UPDATE
The problem consists that at reading of a file with 18500 lines, storage of application grows from 17 MB to 1470 MB.
Clarified that the request insert or update does not influence storage (carried out this request in the procedure end when queue reached it, storage already occupied 1,4 Gb)
At what after application closing, storage at first decreases in flow of seconds 20, and then application is closed.
Prompt where to look. In advance thanks.

2

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

Free it is necessary to add

3

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

Kikoma wrote:

Clarified that the request insert or update does not influence storage (carried out this request in the procedure end when queue reached it, storage already occupied 1,4 Gb)

Well so localize (or the code show).
Possibly and "SQLite+FireDac" too there is nothing, and business in, how
You read a file, lines store, requests form etc.

4

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

procedure TfmImport1C.Button1Click (Sender: TObject);
var
f: TStringList;
s: string;
i, pos1, pos2, len: integer;
year, month: Word;
LScode, LSname, FIO, city, street, house, flat, uslcode, uslname, rsocode;
rsoINN, period, addr, SBCode: string;
sum, sum_in, sum_nach, sum_pay, sum_out, tarif: real;
usluga_id, id_rso: integer;
begin
with DM.FDQuery4 do begin
Close;
SQL.Text: = ";
end;
FormatSettings. DecimalSeparator: = ', ';
if FileExists (Edit1.Text) then
begin
RE.Lines. Clear;
RE.Lines. Add (' Start... ');
//..... Removed the code any checks and analyses of lines... Not important...
period: = FormatDateTime (' yyyy-mm-dd ', EncodeDate (year, month, 1));
RE.Lines. Add (' the Period: ' + period);
//we Read a file
f: = TStringList. Create;
f. LoadFromFile (Edit1.Text);
ProgressBar1.Max: = f. Count;
ProgressBar1.Position: = 0;
ProgressBar1.Enabled: = True;
RE.Lines. Add (' All records: ' + IntToStr (ProgressBar1.Max));
//we Clear the DB Table
with DM.FDQuery1 do
begin
SQL.Text: = ' DELETE FROM nach1C_temp ';
ExecSQL;
end;
end
else
begin
RE.Lines. Add (' File not found. Execution terminated! ');
exit;
end;
ProgressBar1.Visible: = True;
//Went to read a line
for i: = 0 to f. Count - 1 do
begin
s: = f. Strings [i];
ProgressBar1.Position: = i;
Application. ProcessMessages;
if (s [1] <> ' # ') and (s [1] <> ' = ') then
begin
FormatSettings. DecimalSeparator: = ', ';
pos2: = Pos ('; ', s);
LScode: = Copy (s, 1, pos2 - 1);
Delete (s, 1, pos2);
pos2: = Pos ('; ', s);
LSname: = Copy (s, 1, pos2 - 1);
Delete (s, 1, pos2);
pos2: = Pos ('; ', s);
FIO: = Copy (s, 1, pos2 - 1);
Delete (s, 1, pos2);
pos2: = Pos ('; ', s);
city: = Copy (s, 1, pos2 - 1);
Delete (s, 1, pos2);
pos2: = Pos ('; ', s);
street: = Copy (s, 1, pos2 - 1);
Delete (s, 1, pos2);
//.... The Code reduced.... We Pick out the data from a line
begin//It is service full
SBCode: = fmSettings. GetOption (' SBNumGA ');
//we Begin record in a DB
usluga_id: = 0;
Whether//It is checked there is such service and how many their records
if length (rsocode) = 0 then
begin
if sum_out <> 0 then
RE.Lines. Add (' ERROR: line [' + IntToStr (i) + ']. A LAN [' + LScode +
'] Adr [' + street + ', ' + house + ', ' + flat + '] Service [' +
uslname + '] the Supplier is not filled! It is passed ');
//It is necessary to create history + and in it to search 
//and then to define id services
end
else//if length (rsocode) = 0 then
begin
with DM.FDQuery1 do
begin
Close;
s: = ' SELECT * FROM uslugi WHERE ' + ' (code_usl = ' "+ uslcode +
' ") AND ' + ' (code_rso = '" + rsocode + ' ") ';
SQL.Text: = s;
Open;
end;
if DM.FDQuery1.RecordCount = 1 then
usluga_id: = DM.FDQuery1.FieldByName (' id ').AsInteger
else if DM.FDQuery1.RecordCount> 1 then
begin
//It is found more than one service with such code. We specify on 
RE.Lines. Add (' ERROR: line [' + IntToStr (i) +
'] some services [codeusl = ' + uslcode + ' Are found; rsocode = ' +
rsocode + '] ');
end
else
begin
//it is not found such service, it is necessary to get the new
//we search for the supplier () if there is no that it is got
with DM.FDQuery1 do
begin
Close;
s: = ' SELECT * FROM rso WHERE code = ' "+ rsocode + '"';
SQL.Text: = s;
Open;
end;
if DM.FDQuery1.RecordCount = 1 then
id_rso: = DM.FDQuery1.FieldByName (' id ').AsInteger
else
begin
//Resursnika is not present, we get
with DM.FDQuery1 do
begin
//...... The Given request does not work, since already all necessary data in the table is
Close;
s: = ' INSERT INTO rso (code, inn) ' + ' VALUES (' "+ rsocode +
' ", '" + rsoINN + ' ") ';
SQL.Text: = s;
ExecSQL;
s: = ' SELECT * FROM rso WHERE inn = "' + rsoINN + '" ';
SQL.Text: = s;
Open;
id_rso: = FieldByName (' id ').value;
end;
end;
//we get service
with DM.FDQuery1 do
begin
Close;
//...... The Given request does not work, since already all necessary data in the table is
s: = ' INSERT INTO uslugi (code_usl, name_usl, code_rso, id_rso) ' +
' VALUES (' "+ uslcode + '", ' "+ uslname + '", ' "+ rsocode
+ ' ", ' + IntToStr (id_rso) + ') ';
SQL.Text: = s;
ExecSQL;
s: = ' SELECT * FROM uslugi WHERE ' + ' (code_usl = ' "+ uslcode +
' ") AND ' + ' (id_rso = ' + IntToStr (id_rso) + ') ';
SQL.Text: = s;
Open;
usluga_id: = FieldByName (' id ').value;
RE.Lines. Add (' Inf: service id = ' + IntToStr (usluga_id) + Is got
' name_usl = ' + uslname + ' the supplier  ' + rsoINN);
end;
end;
end;
if usluga_id = 0 then
begin
if sum_out <> 0 then
RE.Lines. Add (' ERROR: line [' + IntToStr (i) +
'] It is passed. Service is not defined! ');
end
else
begin
//we Add record in a DB
Whether//we Search there is such record, UPDATE differently INSERT
with DM.FDQuery2 do
begin
Close;
SQL.Text: = ' SELECT id FROM nach1C WHERE ' + ' lscode = ' + ' "' +
LScode + ' "AND ' + ' period = '" + period + ' "AND usluga_id ='
+ IntToStr (usluga_id);
Open;
if RecordCount> 0 then
id_rso: = FieldByName (' id ').value
else
id_rso: =-1;
end;
if id_rso =-1 then
with DM.FDQuery4 do//Dobovljaem new record
begin
//Close;
if (length (FIO) = 0) or (FIO = ' <> ') then
FIO: = LSname;
addr: = city + ', ' + street + ', ' + house + ', ';
addr: = addr + flat;
FormatSettings. DecimalSeparator: = '. ';
s: = ' INSERT INTO nach1C (lscode, FIO, addr, period, usluga_id, sum_in,'
+ ' sum_nach, sum_opl, sum_out, tarif, SBCode) ' + ' VALUES (' "+
LScode + ' ", '" + FIO + ' ", '" + addr + ' ", '" + period +
' ", ' + IntToStr (usluga_id) + ', ' + FloatToStr (sum_in) + ', ' +
FloatToStr (sum_nach) + ', ' + FloatToStr (sum_pay) + ', ' +
FloatToStr (sum_out) + ', ' + FloatToStr (tarif) + ', ' "+
SBCode + ' "); ';
SQL.Add (s);
//ExecSQL;
end
else
with DM.FDQuery4 do//It is updated existing record
begin
//Close;
if (length (FIO) = 0) or (FIO = ' <> ') then
FIO: = LSname;
addr: = city + ', ' + street + ', ' + house + ', ';
//if Pos (' . ', house) = 0 then
//addr: = addr + ' . ';
//addr: = addr + house + ', ';
//if Pos (' sq. ', flat) = 0 then
//addr: = addr + ' sq. ';
addr: = addr + flat;
FormatSettings. DecimalSeparator: = '. ';
s: = ' UPDATE nach1C SET ' + ' lscode = ' "+ LScode + '"' +
', FIO = ' "+ FIO + '"' + ', addr = ' "+ addr + '"' +
', period = ' "+ period + '"' + ', usluga_id = ' +
IntToStr (usluga_id) + ', sum_in = ' + FloatToStr (sum_in) +
', sum_nach = ' + FloatToStr (sum_nach) + ', sum_opl = ' +
FloatToStr (sum_pay) + ', sum_out = ' + FloatToStr (sum_out) +
', tarif = ' + FloatToStr (tarif) + '; ';
SQL.Add (s);
//SQL.Text: = s;
//ExecSQL;
end;
end;
end;
end;//if (s [1] <> ' # ') and (s [i] <> ' = ')
end;
ProgressBar1.Visible: = False;
RE.Lines. Add (' file Reading is completed, we Write down in a DB... ');
with DM do begin
FDConnection1.StartTransaction;
FDQuery4.ExecSQL;
FDConnection1.Commit;
end;
RE.Lines. Add (' file Import is completed! ');
f. Free;
end;

Check of the data, i.e. requests to a DB, transits all on two FDQuery1 and FDQUery2
Record happens FDQuery4

5

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

GetOption - it is possible to carry out for limits of all cycle. If the problem with storage dares - the sm the code inside means.
As it is incorrectly made with query4 - it is necessary to remove in two places SQL.Add (s); and to add there assignment through Text.
Like more anything suspicious is not present.

6

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

Still it is necessary to add ReportMemoryLeaksOnShutdown: = True somewhere at an application launch that in the end at end there was a report on memory leaks if they were (those objects that it is necessary to release manually in the code through free).

7

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

It is strongly possible and not to understand the code. The main algorithm such

var
f: TStringList;
s: string;
begin
//we read a file
f: = TStringList. Create;
f. LoadFromFile (Edit1.Text);
//it is assorted line by line
for i: = 0 to f. Count - 1 do
begin
s: = f. Strings [i];
//It is picked out the data from a line s
pos2: = Pos ('; ', s);
1: = Copy (s, 1, pos2 - 1);
Delete (s, 1, pos2);
2: = Pos ('; ', s);
LSname: = Copy (s, 1, pos2 - 1);
Delete (s, 1, pos2);
//some requests, that given to check up/add Are done
with DM.FDQuery1 do
begin
Close;
s: = ' SELECT * FROM uslugi WHERE ' + ' (code_usl = ' "+ uslcode +
' ") AND ' + ' (code_rso = '" + rsocode + ' ") ';
SQL.Text: = s;
Open;
end;
if DM.FDQuery1.RecordCount = 1 then
usluga_id: = DM.FDQuery1.FieldByName (' id ').AsInteger
else if DM.FDQuery1.RecordCount> 1 then
begin
//It is found more than one service with such code. We specify on 
RE.Lines. Add (' ERROR: line [' + IntToStr (i) +
'] some services [codeusl = ' + uslcode + ' Are found; rsocode = ' +
rsocode + '] ');
end;
//Such requests of pieces 5;
//As a result we prepare request for adding of the data in the table
//we Add record in a DB
Whether//we Search there is such record, UPDATE differently INSERT
with DM.FDQuery2 do
begin
Close;
SQL.Text: = ' SELECT id FROM nach1C WHERE ' + ' lscode = ' + ' "' +
LScode + ' "AND ' + ' period = '" + period + ' "AND usluga_id ='
+ IntToStr (usluga_id);
Open;
if RecordCount> 0 then
id_rso: = FieldByName (' id ').value
else
id_rso: =-1;
end;
if id_rso =-1 then
with DM.FDQuery4 do//Dobovljaem new record
begin
//Close;
if (length (FIO) = 0) or (FIO = ' <> ') then
FIO: = LSname;
addr: = city + ', ' + street + ', ' + house + ', ';
addr: = addr + flat;
FormatSettings. DecimalSeparator: = '. ';
s: = ' INSERT INTO nach1C (lscode, FIO, addr, period, usluga_id, sum_in,'
+ ' sum_nach, sum_opl, sum_out, tarif, SBCode) ' + ' VALUES (' "+
LScode + ' ", '" + FIO + ' ", '" + addr + ' ", '" + period +
' ", ' + IntToStr (usluga_id) + ', ' + FloatToStr (sum_in) + ', ' +
FloatToStr (sum_nach) + ', ' + FloatToStr (sum_pay) + ', ' +
FloatToStr (sum_out) + ', ' + FloatToStr (tarif) + ', ' "+
SBCode + ' "); ';
SQL.Add (s);
//ExecSQL;
end
else
with DM.FDQuery4 do//It is updated existing record
begin
//Close;
if (length (FIO) = 0) or (FIO = ' <> ') then
FIO: = LSname;
addr: = city + ', ' + street + ', ' + house + ', ';
//if Pos (' . ', house) = 0 then
//addr: = addr + ' . ';
//addr: = addr + house + ', ';
//if Pos (' sq. ', flat) = 0 then
//addr: = addr + ' sq. ';
addr: = addr + flat;
FormatSettings. DecimalSeparator: = '. ';
s: = ' UPDATE nach1C SET ' + ' lscode = ' "+ LScode + '"' +
', FIO = ' "+ FIO + '"' + ', addr = ' "+ addr + '"' +
', period = ' "+ period + '"' + ', usluga_id = ' +
IntToStr (usluga_id) + ', sum_in = ' + FloatToStr (sum_in) +
', sum_nach = ' + FloatToStr (sum_nach) + ', sum_opl = ' +
FloatToStr (sum_pay) + ', sum_out = ' + FloatToStr (sum_out) +
', tarif = ' + FloatToStr (tarif) + '; ';
SQL.Add (s);
//SQL.Text: = s;
//ExecSQL;
end;
end;
//Well also it is at last added, we fulfill request
RE.Lines. Add (' file Reading is completed, we Write down in a DB... ');
with DM do begin
FDConnection1.StartTransaction;
FDQuery4.ExecSQL;
FDConnection1.Commit;
end;
RE.Lines. Add (' file Import is completed! ');
f. Free;
end;

8

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

If there are no leaks the problem can be that at you  a heap of the data from basis in any of dial-ups.

9

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

EvrikaLog with search of leaks or other similar service (but when it is the help of a forum is not so necessary) can be still switched on.

10

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

JaDi wrote:

GetOption - it is possible to carry out for limits of all cycle. If the problem with storage dares - the sm the code inside means.
As it is incorrectly made with query4 - it is necessary to remove in two places SQL.Add (s); and to add there assignment through Text.
Like more anything suspicious is not present.

The problem was with GetOption. Set explicitly and all rose normally.
And in procedure dynamic created TFDQuery, and Free did not do.
Thanks big JaDi!
By the way;

JaDi wrote:

As it is incorrectly made with query4 - it is necessary to remove in two places SQL.Add (s); and to add there assignment through Text.

It as? It is possible more in detail... With Text did not work

11

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

wrote:

and if FDQuery4.SQL did not blow up from an amount

smile)))) It is precisely noticed))))))
Generally record became at once, I it separately carried out it to calculate from what storage grows, from a read or write.
It appeared, from curve pens))))))

12

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

Kikoma wrote:

It as? It is possible more in detail... With Text did not work

I to that the hugest list of requests which then goes on the server there is formed. Probably, at  too clever parcer, which this request assorts on separate parts and sends them in turn. It is wrong. One request is necessary? It is possible then to do through a construction of type SELECT INTO table (x, y) SELECT x, y... But generally, I would send them separately (one request, one call) with informing on progress. And only then already, when will be  in this place - to solve a question, in which image to form packs and to send on the server that all was faster.

13

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

JaDi wrote:

I to that the hugest list of requests which then goes on the server there is formed.

For a long time at SQLite  the server?.
But for the code where requests are closed before usage, it is necessary to tear off hands, certainly.

14

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

Dimitry Sibiryakov wrote:

But for the code where requests are closed before usage, it is necessary to tear off hands, certainly.

What not so that? Teach mind to reason, it is always ready to listen to a practical advice
And that that the huge request is formed, it has been made for problem detection, now all returned back. Though on the other hand, I in this request big do not see problems. It turned out big SQL script which it is successfully fulfilled. A unique minus: progress is not visible. In remaining problems I do not see. Well and anyway, I will repeat, not so actually since returned on single line - one request.

15

Re: Delphi+SQLite+FireDac: 50 thousand serial Select queries devours storage

Kikoma wrote:

What not so that?

Time of existence of a resource is not supervised. Therefore at you and surprises in a type it is not known
Where spent storage. The correct pattern looks so: selected a resource - used
- Released at once as soon as that is not become necessary. At you on the contrary: selected a resource -
Used - forgot about it to the following time.