Any performance difference in DB operation

[egsuh]

Hi,
This is not a question on Lazarus or Free Pascal but on database operation performance. I'd like to here your experiences.
What I want to do is to delete some records from data table (Firebird database).

I have two options, using "in" in SQL, and running several times.

First option's SQL is :

Code: Pascal  [Select][+][-]

1. delete from ATable where Number in (3,45,12,44)

And second option is running following statements one by one:

Code: Pascal  [Select][+][-]

1. delete from ATable where Number = 3;
2. delete from ATable where Number = 45;
3. delete from ATable where Number = 12;
4. delete from ATable where Number = 44;

If there are not much difference in performance, I prefer the second option, because I can put them in a stored procedure. The first option is not possible because of the "3,45,12,44" part (the procedure language seems to support defining array, but it is not possible to use array structure in the Lazarus application).

[balazsszekely]

I assume you have to pass the numbers from Lazarus to firebird. There are two ways to do it:

1. Elegant but slow

Code: Pascal  [Select][+][-]

1. var
2.   AI: array of Integer;
3.   I: Integer;
4. begin
5.   SetLength(AI, 4);
6.   AI[0] := 3; AI[1] := 45; AI[2] := 12; AI[3] := 44;
7.  
8.   SQLQuery1.SQL.Text := 'delete from TableName where Number = :pNumber';
9.   for I := Low(AI) to High(AI) do
10.   begin
11.     SQLQuery1.Params.BeginUpdate;
12.     SQLQuery1.ParamByName('pNumber').AsInteger := AI[I]
13.     SQLQuery1.Params.EndUpdate;
14.     SQLQuery1.ExecSQL;
15.   end;
16. end;
17.  

You don't have to do server side coding. If the length of array is not bigger then a few hundred, I would go with this one.


2. Fast but ugly

Code: Pascal  [Select][+][-]

1.  var
2.   AI: array of Integer;
3.   I: Integer;
4.   AIStr: String;
5. begin
6.   SetLength(AI, 4);
7.   AI[0] := 3; AI[1] := 45; AI[2] := 12; AI[3] := 44;
8.  
9.   AIStr := '';
10.   for I := Low(AI) to High(AI) do
11.   begin
12.     if AIStr = '' then
13.       AIStr := IntToStr(AI[I])
14.     else
15.       AIStr := AIStr + ',' + IntToStr(AI[I]);
16.   end;
17.  
18.   SQLQuery1.SQL.Text := 'execute DeleteStoreProcedure(:pStr)';
19.   SQLQuery1.Params.BeginUpdate;
20.   SQLQuery1.ParamByName('pStr').AsString := AIStr;
21.   SQLQuery1.Params.EndUpdate;
22.   SQLQuery1.ExecSQL;
23. end;

The "ugly" part is to write a store procedure that will split the string in substrings. From Firebird 2.5 up, you can find some nice functions like: CHAR_LENGTH(), POSITION(), SUBSTRING() etc., so it shouldn't be to difficult to do it. I done it in the past with UDFs, but is part of a proprietary software, unfortunately I cannot share it. 

[valdir.marcos]

Hi,
This is not a question on Lazarus or Free Pascal but on database operation performance. I'd like to here your experiences.
What I want to do is to delete some records from data table (Firebird database).

I have two options, using "in" in SQL, and running several times.

First option's SQL is :

1st

Code: Pascal  [Select][+][-]

1. delete from ATable where Number in (3,45,12,44)

And second option is running following statements one by one:

2nd

Code: Pascal  [Select][+][-]

1. delete from ATable where Number = 3;
2. delete from ATable where Number = 45;
3. delete from ATable where Number = 12;
4. delete from ATable where Number = 44;

If there are not much difference in performance, I prefer the second option, because I can put them in a stored procedure. The first option is not possible because of the "3,45,12,44" part (the procedure language seems to support defining array, but it is not possible to use array structure in the Lazarus application).

I believe "Number" field is a primary key.

The performance is irrelevant for both options if "ATable" has few rows (less than 10 thousand).

2nd option is faster on old Firebird versions (2.1-).

From Firebird 2.5+, both options have the same performance.

[balazsszekely]

@valdir.marcos

The performance is irrelevant for both options if "ATable" has few rows (less than 10 thousand).

True, but running the scripts at serverside is 1-5 % of the total execution time. Passing parameters from Lazarus to Firebird, then parsing values if needed 95- 99%.

[Rainbow6]

Under „normal“ conditions I would say, the first option should be faster.
a) the DBEs query optimizer can work
b) the statement has to be parsed only once

But I think this will differ from one DBE to another. I don‘t know if there is an general answer.

What I can say is, that option 1 was much faster on DB2/400.

Best regards,
Daniel

[egsuh]

Thank you for your kind advices.

Well the performance itself may not be different, but as far as I know using stored procedure means it is compiled already while using the same statement in TSQLQuery needs compilation.

I do not want to write a procedure doing text parsing because Lazarus application is easier to do that.  So I cannot write a stored procedure like:

 delete from data where number in (:number_list) 

[valdir.marcos]

This is not a question on Lazarus or Free Pascal but on database operation performance. I'd like to here your experiences.
What I want to do is to delete some records from data table (Firebird database).

@valdir.marcos

The performance is irrelevant for both options if "ATable" has few rows (less than 10 thousand).

True, but running the scripts at serverside is 1-5 % of the total execution time. Passing parameters from Lazarus to Firebird, then parsing values if needed 95- 99%.

@GetMem
I agree with you, but it seems that egsuh was only querying about Firebird performance side.

[valdir.marcos]

Thank you for your kind advices.
Well the performance itself may not be different, but as far as I know using stored procedure means it is compiled already while using the same statement in TSQLQuery needs compilation.
I do not want to write a procedure doing text parsing because Lazarus application is easier to do that.  So I cannot write a stored procedure like:
delete from data where number in (:number_list)

Yes, using Firebird stored procedures is always faster than using any programming language to interact with Firebird, but stored procedure is not a panacea and should be used only when it is really necessary.
I can't see the goal of using stored procedures for simple deleting...

[valdir.marcos]

I assume you have to pass the numbers from Lazarus to firebird. There are two ways to do it:

1. Elegant but slow

Code: Pascal  [Select][+][-]

1. var
2.   AI: array of Integer;
3.   I: Integer;
4. begin
5.   SetLength(AI, 4);
6.   AI[0] := 3; AI[1] := 45; AI[2] := 12; AI[3] := 44;
7.  
8.   SQLQuery1.SQL.Text := 'delete from TableName where Number = :pNumber';
9.   for I := Low(AI) to High(AI) do
10.   begin
11.     SQLQuery1.Params.BeginUpdate;
12.     SQLQuery1.ParamByName('pNumber').AsInteger := AI[I]
13.     SQLQuery1.Params.EndUpdate;
14.     SQLQuery1.ExecSQL;
15.   end;
16. end;
17.  

You don't have to do server side coding. If the length of array is not bigger then a few hundred, I would go with this one.


2. Fast but ugly

Code: Pascal  [Select][+][-]

1.  var
2.   AI: array of Integer;
3.   I: Integer;
4.   AIStr: String;
5. begin
6.   SetLength(AI, 4);
7.   AI[0] := 3; AI[1] := 45; AI[2] := 12; AI[3] := 44;
8.  
9.   AIStr := '';
10.   for I := Low(AI) to High(AI) do
11.   begin
12.     if AIStr = '' then
13.       AIStr := IntToStr(AI[I])
14.     else
15.       AIStr := AIStr + ',' + IntToStr(AI[I]);
16.   end;
17.  
18.   SQLQuery1.SQL.Text := 'execute DeleteStoreProcedure(:pStr)';
19.   SQLQuery1.Params.BeginUpdate;
20.   SQLQuery1.ParamByName('pStr').AsString := AIStr;
21.   SQLQuery1.Params.EndUpdate;
22.   SQLQuery1.ExecSQL;
23. end;

The "ugly" part is to write a store procedure that will split the string in substrings. From Firebird 2.5 up, you can find some nice functions like: CHAR_LENGTH(), POSITION(), SUBSTRING() etc., so it shouldn't be to difficult to do it.

Using "Execute Statement" should be much simpler:
https://www.firebirdsql.org/refdocs/langrefupd20-psql-execstat.html
https://firebirdsql.org/refdocs/langrefupd25-psql-execstat.html

I done it in the past with UDFs, but is part of a proprietary software, unfortunately I cannot share it.

UDFs are already deprecated in Firebird 4.0 beta, so you should avoid using them as soon as you can.
https://www.firebirdnews.org/udfs-are-deprecated-in-firebird-4/

[balazsszekely]

@valdir.marcos

Using "Execute Statement" should be much simpler:

"Exectue Statement" looks interesting. I will run a few tests and see how it works. Thanks for the info.
PS: For current case(delete records) works fine and it should be used, but since it doesn't return any value, my spliting method is still valueble for many other cases.

UDFs are already deprecated in Firebird 4.0 beta, so you should avoid using them as soon as you can.

I used UDFs since firebird 1.5 or even before. Currently the main development is done with Firebird 2.5 and I don't plan to upgrade simply because it works just fine. I'm aware that Firebird 3.0 introduced a lot of new cool features...maybe in future projects.

[ttomas]

If you use Firebird 2> then execute block is winner

Code: Pascal  [Select][+][-]

1. execute block
2. as
3. begin
4.   delete from ATable where Number = 3;
5.   delete from ATable where Number = 45;
6.   delete from ATable where Number = 12;
7.   delete from ATable where Number = 44;
8.   exit;
9. end
10.  

[egsuh]

My purpose is to encapsulate data-related tasks within database --- with tables, triggers, and procedures, so that the Lazarus application interact with the database through procedures.

If I use "Execute Statement" within procedure then everything is quite simple. I make up a string like '3,45,12,44' within Pascal application and pass it as a parameter to stored procedure.  Then the commands within procedure would be like:

Code: Pascal  [Select][+][-]

1.   stmt = 'delete from data where number in (' || :param1 || ')';
2.   execute statement stmt;

Problem is that it must be compiled (parsed? whatever) everytime whenever the stored procedure is executed --- nothing to gain in terms of performance.

So I wanted to compare following statements with previous ones.

Code: Pascal  [Select][+][-]

1. for
2.    select number from qdef where (some condition) into :tno  // 3,45,12,44 come from here
3.    do begin
4.       delete from data where number = :tno;
5.       suspend;
6.    end
7.  

   

Based on your advices, I am convinced that this approach has nothing to lose 

[sash]

I believe, for similar operations the fastest way would be "batch sql" pattern:

Code: Pascal  [Select][+][-]

1. // pseudocode
2. PrepareStatement(YourSQL);
3. for ParamsCount do begin
4.   SetParams;
5.   Execute;
6.   // CommitPeriodically;
7. end;
8. // CommitIfRequired;
9.  

[valdir.marcos]

My purpose is to encapsulate data-related tasks within database --- with tables, triggers, and procedures, so that the Lazarus application interact with the database through procedures.

If I use "Execute Statement" within procedure then everything is quite simple. I make up a string like '3,45,12,44' within Pascal application and pass it as a parameter to stored procedure.  Then the commands within procedure would be like:

Code: SQL  [Select][+][-]

1.   stmt = 'delete from data where number in (' || :param1 || ')';
2.   EXECUTE statement stmt;

Problem is that it must be compiled (parsed? whatever) everytime whenever the stored procedure is executed --- nothing to gain in terms of performance.

So I wanted to compare following statements with previous ones.

Code: SQL  [Select][+][-]

1. FOR
2.    SELECT NUMBER FROM qdef WHERE (SOME condition) INTO :tno  // 3,45,12,44 come FROM here
3.    do BEGIN
4.       DELETE FROM DATA WHERE NUMBER = :tno;
5.       suspend;
6.    END

Based on your advices, I am convinced that this approach has nothing to lose 

Using "Execute Statement" would be simpler and faster for this case.
Stored procedures are only internally compiled when their content are changed, not their parameters.