Perform multiple SQL Server updates without looping

Question

I have something like this:


var
   text: string ;
   fid: string;
        
for a = 1 to 100 do
begin
text:=stringgrid.cells[5,a];
fid:=stringgrid.cells[0,a];  
query.SQL.Text := 'Update dbtable Set atext=' + text + ' Where id=' + fid;
  query.ExecSQL;
end;

Is there a way to avoid calling ExecSQL() 100 times?

why nozt use prepared statemen ts https://sqlserverperformace.blogspot.com/2021/03/all-about-prepared-statements-when-to.html — nbk, Oct 24 '21 at 15:54
Please [edit] your post to be more specific about where `aText` is coming from than `// data i take from table in my loop`. Provide specific code about how you're getting that data, as it makes a difference in how to answer your specific question. The best way to ask is to post your **actual code** in the form of a [mre]. — Ken White, Oct 24 '21 at 22:55
You can also write many update statements in one query.SQL.Text. When you send multiple lines of updates (or others statements) in one go, sql server will be happy to perform them all for you — GuidoG, Oct 25 '21 at 14:46
There is no way to avoid a loop update in this case, there is nothing to be gained here performance wise. — whosrdaddy, Oct 26 '21 at 22:13
Maybe it is better to use a dbaware component so you can have updates at the row level instead of updating the whole table over and over for no good reason. — whosrdaddy, Oct 26 '21 at 22:16

Remy Lebeau · Answer 1 · 2021-10-25T03:25:12.010

In this particular instance, yes, it is easy to do the updates with a single call to ExecSQL().

If the IDs are sequential, you can do this:

var
  text: string ; // data i take from table in my loop.

query.SQL.Text := 'Update dbtable Set atext=' + QuotedStr(text) + ' Where id >= 1 and id <= 100';
query.ExecSQL;

If the IDs are not sequential, you could use an in clause instead, but this is less efficient if you have a large list of IDs:

var
  text: string ; // data i take from table in my loop.

query.SQL.Text := 'Update dbtable Set atext=' + QuotedStr(text) + ' Where id in [1, ..., 100]';
query.ExecSQL;

Another option would be to create a stored procedure in the DB. Have it take the text and IDs as paramenters, and then perform any needed looping internally. You can then perform a single SQL statement in your code to execute the stored procedure with parameter values.

Hi Remy, I fail to understand how your solutions works, the text is different for each Id? Your code basically sets the same text for all Id's?? — whosrdaddy, Oct 26 '21 at 21:58
@whosrdaddy the OP's question has been edited since I posted my answer. The text wasn't originally being modified inside of the loop. Now it is, which apparently invalidates my examples. — Remy Lebeau, Oct 26 '21 at 22:00

Freddie Bell · Answer 2 · 2021-10-27T04:29:02.310

-1

procedure TForm1.Button1Click(Sender: TObject);
var
  a: Integer;
  text: String;
begin
  query.SQL.Clear;
  for a := 1 to 100 do
  begin
    query.SQL.Add('Update dbtable Set atext=' + QuotedStr(text) + 
      ' Where id=' + IntToStr(a) + ';');
  end;
  query.ExecSQL;
end;

edited Oct 27 '21 at 04:29

answered Oct 24 '21 at 18:17

Freddie Bell

2,186
24
43

2

First, this wouldn't work, because you've not delimited the end of each statement with `;`.Second, this would require 100 different statements to be executed, which is totally inefficient. – Ken White Oct 24 '21 at 22:44
1

Statements don't have to be delimited with ; if they are delimited with CR/LF. Secondly, the entire 100 statements are submitted to the server at once, and executed as a batch within an implicit transaction. I've done this sort of thing with 1000's of statements in 1 batch, and achieved performance gains in orders of magnitude above individual executes. Such is the nature of SQL Server using ADO. – Freddie Bell Oct 25 '21 at 17:48
@KenWhite Secondly, the question was "Is there a way to avoid calling ExecSQL() 100 times?". This answers that question. – Freddie Bell Oct 25 '21 at 17:50
Ι tried this method but didnt work for me. Something missing maybe need GO . – Giannis Oct 25 '21 at 18:45
2

@Giannis: GO is [SQL management studio specific thing](https://stackoverflow.com/a/2299255/800214), it is not part of TSQL language. – whosrdaddy Oct 26 '21 at 22:03
`;` is the correct delimiter for multiple statements – whosrdaddy Oct 26 '21 at 22:14
1

@whosrdaddy and the ; isn't required by SQL Server. The above code was actually tested in a complete sample application, but I'll add the ; if it makes you feel more comfortable. – Freddie Bell Oct 27 '21 at 03:57

score -1 · Answer 3 · answered Oct 25 '21 at 14:31

With a minimum of information I suggestd you can use the functionality of Delphi.

Do something like this:

var
  Text: string ; // data i take from table in my loop.
    
query.open('select * from dbtable where id between 1 and 100');
query.beginBatch;    

for a = 1 to 100 do
begin
  if query.locate('id',id) then
  begin
    query.edit;
    query.fieldbyname('atext').asString:=text;
  end;
end;
query.endBatch;
query.post;

I can't say anything to the performance of this. Yout have to test it by your own.

Perform multiple SQL Server updates without looping

3 Answers3