SQL is the leading language for developing and querying databases, but it has a few quirks. In my last article, I shared 7 SQL mistakes to avoid. Now, let's take a look at 9 best practices for writing faster SQL queries.
9 best practices for faster SQL queries
- Retrieve only the columns you need
- Use CASE instead of UPDATE for conditional column updates
- Keep large-table queries to a minimum
- Pre-stage your data
- Perform deletes and updates in batches
- Use temp tables to improve cursor performance
- Use table-valued functions over scalar functions
- Use partitioning to avoid large data moves
- Use stored procedures for performance, use ORMs for convenience
Retrieve only the columns you need
A common SQL habit is to use SELECT *
on a query, because it's tedious to list all the columns you need. Plus, sometimes those columns may change over time, so why not just do things the easy way?
To read this article in full, please click here