Re: update performance

On Thu, 15 Feb 2001, Martin A. Marques wrote: >El Mar 13 Feb 2001 16:53, Jonathan Leffler escribi': >> I don't fully understand the question, but I'll take a stab at it >> anyway. >> >> UPDATE YourTable SET BoolColumn = 1 >> WHERE BoolColumn = 0 AND ...whatever other conditions you need... >> UPDATE YourTable SET BoolColumn = 0 >> WHERE BoolColumn = 1 AND ...whatever other (different) conditions...> >Yes, this is what I worte. The other condition you put there is a >bool_id IN (....) and in the programing script I build the list of ids. > >> If the processing has to scan the table anyway, then updating the >> columns even when not needing to be changed may even be quicker than >> double scanning the table; that depends on the other conditions, any >> indexes, and so on. > >That was what I was asking. How would the performance of the update be if I >do it in two updates with 2 conditions or with 2 updates with one condition >each? I mean what consumns more resources, the update of the row, or the >search? The simplest way to find out is to run the system with SET EXPLAIN ON (after running UPDATE STATISTICS); this will give you query plan costs. It will also show you whether the optimizer is doing what you expect or something completely unexpected. Or, simply time the program. If this is a one-off problem, just get on with it. I assume, though, that this is going to be done often enough that performance is critical, in which case, testing what works best is a good idea. I think you are saying that the UPDATES are of the form: UPDATE YourTable SET BoolColumn = 1 WHERE BoolColumn = 0 AND bool_id IN (id1, id2, id3, ..., idN) UPDATE YourTable SET BoolColumn = 0 WHERE BoolColumn = 1 AND bool_id IN (ida, idb, idc, ..., idZ) If only 5% of the bool_ids in each list actually need changing and the lists are quite long, then it will save a fair amount of disk writing if you keep the BoolColumn = X condition. If 95% of the bool_ids in each list need changing, or if the lists are short, then the saving is probably not significant. Assuming the bool_id values are primary keys, then there shouldn't be any scanning involved -- the optimizer should be able to pick out the rows to read and then write back the change for only the limited number of rows that need the change. I'd still measure the relative performances of the updates. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"