Skip to main content

Posts

Showing posts from July 29, 2020

The missing guide to Application DBA tricks

Some tips about database developmentBe that guy...
Image by CommitStripTable of Contents
Update Only What Needs UpdatingUPDATE is a relatively expensive operation. To speed up an UPDATE command it's best to make sure you only update what needs updating.Take this query for example that normalizes an email column:db=#UPDATEusersSETemail=lower(email);UPDATE 1010000Time: 1583.935 ms (00:01.584) Looks innocent, right? the query updated emails of 1,010,000 users. But, did all rows really needed to update?db=#UPDATEusersSETemail=lower(email)db-#WHEREemail!=lower(email);UPDATE 10000Time: 299.470 ms Only 10,000 rows needed to update. By reducing the amount of affected rows, the execution time went down from 1.5 seconds to just less than 300ms. Updating fewer rows also saves the database maintenance later on.Update Only What Needs UpdatingThis type of large updates are very common in data migration scripts. So the next time you write a migration script, make sure to only update what needs updat…