On 5/3/07, MB Software Solutions <[EMAIL PROTECTED]> wrote: > Question: rather than just blindly run this, would it be better > performancewise I wonder if I should do a SELECT first to see if any > such records exist prior to calling the update sql? Example (after > dynamically built):
The fastest statement is the one that isn't run. It's hard to beat zero CPU time, bandwidth or diskspace. In a recent app I developed, I found with a little forethought I could construct my queries and the data I presented to the users in such a way that they could not select an operation that made no sense. In your example, if you are interacting with users, I'd check to see if there were any active addresses and only offer them the option of inactivating address if there were any addresses to inactivate. If this was a batch operation instead, perhaps you could get a summary of all of the account records for which there existed one or more active addresses, and then only run the UPDATE command against those. As a general rule, the less potentially data-damaging commands (INSERT, UPDATE, DELETE) you run, the less chance you have of messing up the data. If you can issue a non-destructive command (SELECT) to determine whether or not you need to write to disk and avoid it, you've saved wear-and-tear on the write heads, journal entries, statistics, I/O, etc. A general rule of thumb (with many exceptions, of course) is that read operations are much less expensive than write operations. YMMV, of course. On the other, other hand ;) if you're going to flag all the addresses as inactive, why bother to check their current state? In some cases, it may be more efficient to just drop the second filter requirement (and iactive = 1) and update them all. Benchmarking with your data, in your environment and with your machines can tell you what's the optimal thing to do. -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.com _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/%(messageid)s ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
