Hi Svein (or Set – not sure which name is appropriate)



Thanks for getting back to me and for your trouble to write out the example
using parameters.



When I started writing the backup procedure, I realised that I needed to do
something to make it as fast as possible and thought of using parameters,
though in the past I have only used parameters in stored procedures and
UDF’s. Many years ago I also used parameters briefly when I was still using
BDE components to access Interbase / Firebird. So I wasn’t sure how to go
about it using IBX, and especially where to put the Prepare statement. I
couldn’t find any examples on the internet using parameters to do something
similar to what I was doing. That is why I am grateful for you modifying my
code like that. The fact is, I did eventually, by trial and error, figure
out how to use parameters. Amazingly the code that I produced was almost
verbatim to what you produced! So that gives me some confidence.
Unfortunately I had the same problem when using parameters with the INSERT
statements getting stuck on every 200 or so records. I then thought that the
problem might be something to do with my handling of parameters and reverted
to building up a normal string SQL. Although I can see that it is slightly
slower, it is still fast enough that I could live with this – except that it
also gets stuck!



The strange thing is that it always gets stuck on the same records. I then
thought that it might be something to do with those particular records and
deleted the first one where it gets stuck. But then it would just get stuck
on the next PLU record – so it seems to have something to do with the amount
of information transferred before it gets stuck. I have another table AAMPLU
which is similar to AAPLU. It has about double the number of fields than
AAPLU but fewer records, though it still contains over a thousand records.
The point is that AAMPLU copies without getting struck. It just doesn’t make
sense.



The only conclusion that I can come to at present is that something
interferes in Firebird when I read multiple records from one database and
write them one by one, to another database. I also tried doing a commit
after every INSERT (obviously slowing down the general performance) but it
still got stuck on those particular records. The only thing that works so
far is if I read everything into an array, and then afterwards write the
array to the other database.



What I want to try today is to install the latest Firebird (2.51) to see if
it has the same problem. The reason I am still using version 1.52 is that we
have a lot of legacy applications that were written for this version. When
2.0 was released I seem to recall that there were some major changes that
had to be dealt with and I didn’t have the time to research this. If 2.51
solves my problem, I might now have to spend that time!



I’ll keep you informed what happens with 2.51. If anything else comes to
mind, please let me know.



Pierre





From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 09 May 2012 11:25
To: 'firebird-support@yahoogroups.com'
Subject: RE: [firebird-support] Performing multiple INSERT statements causes
long delay at every Nth statement





Hi Pierre!

>I am using Firebird 1.52, Delphi 2009 on a Windows Vista Business OS. The

Delphi tool

>I use for database access is Interbase Express 12.12.

I know nothing about IBX, but

...

> with DMAq do begin
> IBSQL3.Transaction.StartTransaction;
> with IBQuery1 do begin
> SQL.Text:='select * from '+tbl;
> Transaction.StartTransaction;
> Open;
> while not eof do begin
> s:='insert into '+tbl+' values (';
> for N:=0 to FldCnt-1 do begin
> case FldTp[N] of
> 1: s:=s+','+fields[N].AsString;
> 2: s:=s+','+fields[N].AsString;
> 3: s:=s+','+FloatToStr(fields[N].AsFloat);
> 4: s:=s+','''+formatdatetime('mm/dd/yyyy hh:nn:ss',

fields[N].AsDateTime)+'''';

> 5: s:=s+','''+trim(fields[N].AsString)+'''';
> end;
> end;
> IBSQL3.SQL.Text:=s+')';
> IBSQL3.ExecQuery;
> next;
> end;
> Close;
> Transaction.Commit;
> IBSQL3.Transaction.Commit;
> end;
> end;

This is a very time consuming way to transfer records. It is a lot better to
use parameters rather than fixed values, i.e. not use

INSERT INTO MyTable VALUES (1, 'My first text'...);
INSERT INTO MyTable VALUES (2, 'My second text'...);

But rather
INSERT INTO MyTable VALUES (:Param1, :Param2...);

So change your code to something like

with DMAq do begin
IBSQL3.Transaction.StartTransaction;
with IBQuery1 do begin
SQL.Text:='select * from '+tbl;
Transaction.StartTransaction;
Open;
s:='insert into '+tbl+' values (Param0';
for N:=1 to FldCnt-1 do
s:=s+', :Param'+inttostr(N);
IBSQL3.SQL.Text:=s+')';
IBSQL3.Prepare;
while not eof do begin
for N:=0 to FldCnt-1 do begin
if fields[N].IsNull then
IBSQL3.Params[N].Clear
else
case FldTp[N] of
1: IBSQL3.Params[N].AsString:=fields[N].AsString;
2: IBSQL3.Params[N].AsString:=fields[N].AsString;
3: IBSQL3.Params[N].AsFloat:=fields[N].AsFloat;
4: IBSQL3.Params[N].AsDateTime:=fields[N].AsDateTime;
5: IBSQL3.Params[N].AsString:=trim(fields[N].AsString);
end;
end;
IBSQL3.ExecQuery;
next;
end;

Hopefully, this will speed up your program considerably,
Set





[Non-text portions of this message have been removed]