I have the following query:
UPDATE [ATable] SET [Field1] = [Value1], [Field2] = [Value2]
WHERE [Field3] = [Value3] AND [Field4] = [Value4]

I have run this query for around 2000-3000 records in a table (Value1, Value2 and Value4 are different for each record).
- I did try it with running a simple query and the process was done in around 5 minutes
- I did try it with a query with parameters prepared first and the process was done in around 10 minutes
- I did try it with a script (added all the lines into a script) and the process was done in around 8 minutes
(all these made in Delphi)

How can I speed up things? What is the right approach in such situations. Generally speaking if I have to update a set of records from a table with different values for each record what is the right solution?

Thanks for the help,
Beni

Beni


Step 1:

Run the query in EDBManager with Request Execution Plan ticked. If the plan doesn't help you then post it here.

Without more information (which the execution plan will give you) my guess would be that you don't have indices on Field3 or Field4

However, I'm puzzled you say (Value1, Value2 and Value4 are different for each record) which to me implies that you must be running in a loop and setting a lot of parameters so running a lot of queries.

I also think that you must be doing something wrong with your basic code since creating and destroying queries should never be faster than using a parameterised query. Can you post teh actual code you're running?

Roy Lambert

this is my code

if NewStatus = INV_BOOK_STATUS_UNKNOWN then
 setString := 'Processed = FALSE'
else
 setString := 'Processed = TRUE';
setString := setString + ', ' + 'NewStatus = ' + IntToStr(NewStatus);

with dmApp.qWork do
begin
 Close;
 SQL.Clear;
 SQL.Add('UPDATE InventoryDetails SET ' + setString);
 SQL.Add('WHERE (IDInventory = ' + IntToStr(AInvID) + ') AND (IDBarcode = ' + IntToStr(ABarcodeID) + ')');
 ExecSQL;
end;

[IDInventory + IDBarcode] are the primary key of the InventoryDetails table. All the "Values" are parameters in a function which I do run in a loop as many times as needed: can be 100 times or 20 000 times

Beni,

how about this:

1. Prepare the query

with dmApp.qWork do
begin
  Close;
  SQL.Clear;
  SQL.Add('UPDATE InventoryDetails SET ');
  SQL.Add('Processed = :FProcessed,');
  SQL.Add('NewStatus = :FNewStatus ');
  SQL.Add('WHERE IDInventory = :FAInvID  AND IDBarcode = FABarcodeID');
  Prepare;
end;

2. Execute the query:

with dmApp.qWork do
begin
  ParamByName('FProcessed').AsBoolean := NewStatus NOT
INV_BOOK_STATUS_UNKNOWN;
  ParamByName('FNewStatus').AsInteger := NewStatus ;
  ParamByName('FAInvID').AsInteger := AInvID;
  ParamByName('FABarcodeID').AsInteger := ABarcodeID;
  ExecSQL;
end;

If the indices are set properly, that should be the fastest way.

Uli

Beni

Other comments prior to my post are spot on. Uli's point about preparing the Query with params is the way I always do this type of thing too, and it is usually super-fast.

If you have an index on [Field3], [Field4] and your [Values] are not large BLOB or CLOBs each ROW should take < 1ms, so the whole process shouldn't take more than a few seconds.

Issues which could slow it up:

* Complex constraints or checks / triggers on the table which must execute after each update.
* Usual issues with network data traffic slowing the passage of each update through the pipe.

Adam

Beni,

in addition to what Adam wrote:

maybe the compound index could cause the problem, have a look here:

http://tinyurl.com/nf4hoqp

Maybe it would be worth trying separate indices.

BTW: You can always execute the query within EDBManager and check the
execution plan. That will show you if the indices are used or not.

Uli

Added index for the 2 "ID" fields and now it's working a lot faster...
Thanks for the help!