Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 7 of 7 total |
Update list of records |
Wed, Dec 17 2014 10:10 AM | Permanent Link |
Beni | 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 |
Wed, Dec 17 2014 11:03 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Dec 17 2014 3:48 PM | Permanent Link |
Beni | 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 |
Thu, Dec 18 2014 2:23 AM | Permanent Link |
Uli Becker | 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 |
Thu, Dec 18 2014 6:29 AM | Permanent Link |
Adam Brett Orixa Systems | 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 |
Thu, Dec 18 2014 7:18 AM | Permanent Link |
Uli Becker | 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 |
Thu, Dec 18 2014 11:26 AM | Permanent Link |
Beni | Added index for the 2 "ID" fields and now it's working a lot faster...
Thanks for the help! |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |