Any performance difference in DB operation lazarus / 16-Databases ---------------------------------------------------------------------- egsuh · 2019-05-17 03:21 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i delete from ATable where Number in (3,45,12,44) And second option is running following statements one by one: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i delete from ATable where Number = 3; delete from ATable where Number = 45;delete from ATable where Number = 12;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 · 2019-05-17 04:51 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  AI: array of Integer;  I: Integer;begin  SetLength(AI, 4);  AI[0] := 3; AI[1] := 45; AI[2] := 12; AI[3] := 44;   SQLQuery1.SQL.Text := 'delete from TableName where Number = :pNumber';  for I := Low(AI) to High(AI) do  begin    SQLQuery1.Params.BeginUpdate;    SQLQuery1.ParamByName('pNumber').AsInteger := AI[I]    SQLQuery1.Params.EndUpdate;    SQLQuery1.ExecSQL;  end;end;  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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  var  AI: array of Integer;  I: Integer;  AIStr: String;begin  SetLength(AI, 4);  AI[0] := 3; AI[1] := 45; AI[2] := 12; AI[3] := 44;   AIStr := '';  for I := Low(AI) to High(AI) do  begin    if AIStr = '' then    AIStr := IntToStr(AI[I])    else      AIStr := AIStr + ',' + IntToStr(AI[I]);  end;   SQLQuery1.SQL.Text := 'execute DeleteStoreProcedure(:pStr)';   SQLQuery1.Params.BeginUpdate;  SQLQuery1.ParamByName('pStr').AsString := AIStr;  SQLQuery1.Params.EndUpdate;  SQLQuery1.ExecSQL;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 · 2019-05-17 04:53 Quote from: egsuh on May 17, 2019, 05:21:23 amHi, 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i delete from ATable where Number in (3,45,12,44) And second option is running following statements one by one: 2nd Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i delete from ATable where Number = 3; delete from ATable where Number = 45;delete from ATable where Number = 12;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 · 2019-05-17 05:07 @valdir.marcos QuoteThe 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 · 2019-05-17 05:08 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 · 2019-05-17 05:37 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 · 2019-05-17 05:46 Quote from: egsuh on May 17, 2019, 05:21:23 amThis 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). Quote from: GetMem on May 17, 2019, 07:07:12 am@valdir.marcos QuoteThe 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 · 2019-05-17 05:52 Quote from: egsuh on May 17, 2019, 07:37:27 amThank 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 · 2019-05-17 06:01 Quote from: GetMem on May 17, 2019, 06:51:55 amI 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  AI: array of Integer;  I: Integer;begin  SetLength(AI, 4);  AI[0] := 3; AI[1] := 45; AI[2] := 12; AI[3] := 44;   SQLQuery1.SQL.Text := 'delete from TableName where Number = :pNumber';  for I := Low(AI) to High(AI) do  begin    SQLQuery1.Params.BeginUpdate;    SQLQuery1.ParamByName('pNumber').AsInteger := AI[I]    SQLQuery1.Params.EndUpdate;    SQLQuery1.ExecSQL;  end;end;  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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  var  AI: array of Integer;  I: Integer;  AIStr: String;begin  SetLength(AI, 4);  AI[0] := 3; AI[1] := 45; AI[2] := 12; AI[3] := 44;   AIStr := '';  for I := Low(AI) to High(AI) do  begin    if AIStr = '' then    AIStr := IntToStr(AI[I])    else      AIStr := AIStr + ',' + IntToStr(AI[I]);  end;   SQLQuery1.SQL.Text := 'execute DeleteStoreProcedure(:pStr)';   SQLQuery1.Params.BeginUpdate;  SQLQuery1.ParamByName('pStr').AsString := AIStr;  SQLQuery1.Params.EndUpdate;  SQLQuery1.ExecSQL;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 QuoteI 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 · 2019-05-17 06:41 @valdir.marcos QuoteUsing "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. QuoteUDFs 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 · 2019-05-17 11:08 If you use Firebird 2> then execute block is winner Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i execute blockasbegin  delete from ATable where Number = 3;  delete from ATable where Number = 45;  delete from ATable where Number = 12;  delete from ATable where Number = 44;  exit;end ---------------------------------------------------------------------- egsuh · 2019-05-17 15:22 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   stmt = 'delete from data where number in (' || :param1 || ')';  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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i for    select number from qdef where (some condition) into :tno  // 3,45,12,44 come from here   do begin      delete from data where number = :tno;      suspend;   end   Based on your advices, I am convinced that this approach has nothing to lose ---------------------------------------------------------------------- sash · 2019-05-17 16:16 I believe, for similar operations the fastest way would be "batch sql" pattern: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i // pseudocodePrepareStatement(YourSQL);for ParamsCount do begin  SetParams;  Execute;  // CommitPeriodically;end;// CommitIfRequired; ---------------------------------------------------------------------- valdir.marcos · 2019-05-17 16:44 Quote from: egsuh on May 17, 2019, 05:22:16 pmMy 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   stmt = 'delete from data where number in (' || :param1 || ')';  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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i FOR    SELECT NUMBER FROM qdef WHERE (SOME condition) INTO :tno  // 3,45,12,44 come FROM here   do BEGIN      DELETE FROM DATA WHERE NUMBER = :tno;      suspend;   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.