3. All-in-one transactions
Suppose you want to delete data from ten tables. In this situation, you may be tempted to perform all deletions in a single transaction. Leave it.
Recommendation: Instead, treat the operations for each table separately. If you need to perform deletes across tables atomically, you can split them into many smaller transactions. For example, if you need to delete 10,000 rows in 20 tables, you could delete the first thousand rows in one transaction for all 20 tables, then the next thousand in another transaction—and so on. This is a good use case for a task queue mechanism in your business logic to manage such operations.
4. Volatile clustering
Global Unique Identifiers (GUIDs) are random numbers and are used to assign a unique identifier to objects. Various databases support this schema as a native column type. However, GUIDs should not be used to cluster the rows that contain them. Since these are random numbers, the clustering causes the table to become heavily fragmented. This, in turn, can cause table operations to run several orders of magnitude slower.
Recommendation: Do not cluster on columns with high randomness. Limit yourself to date or ID columns – these work best.
5. Counting rows inefficiently
To determine whether certain data exists within a table, commands like SELECT COUNT(ID) FROM table1 often inefficient. Although some databases are able to SELECT COUNT()-Optimize operations intelligently, but not all of them. The better approach (if your SQL dialect supports it):
IF EXISTS (SELECT 1 from table1 LIMIT 1) BEGIN ... END
