For most developers, worrying about optimization when writing SQL queries is not an issue until performance becomes a problem with an application. Although SQL query optimization is important, it can be seen as a tedious process that some programmers fail to follow thoroughly. Some best practices can be observed during the development process that will help maximize performance for future application scalability.
Use SELECT Instead of Using SELECT *
First, using select * should be rarely used if ever especially on tables with many columns. Using select * on large tables will return an unneeded number of columns which will lower performance overall. The other reason to never use select * is in case table structure changes. While column names rarely change, using select * will return all columns regardless of new structure. This could adversely affect the application if the columns returned are referenced by the ordered array.
Use WITH (NOLOCK) When Querying Production Data
Whenever using queries on production servers, use WITH (NOLOCK) to avoid locking rows that will return errors to users. Occasionally, you may need to query production data to find a solution for a bug or error. If you fail to use NOLOCK on tables, your query may interfere with other processes run on the SQL server. The locks can become an issue especially when querying large amounts of data across many tables.
Use Joins Instead of Subqueries
Some developers like to use subqueries within the WHERE clause of an SQL query. Most of these subqueries can be traded for joins within the JOIN phrases. When using a join, it is best to join on indexes within the table. Joining on indexed columns will greatly improve query performance.
Query on Indexes
Although it is very common to query on primary keys of tables, when developing queries using the indexes in the WHERE clause can greatly increase query performance. It is unbelievably faster to query on indexes and creates an extremely high overhead by using multiple clauses on fields that are not indexed.
Overall, the best way to analyze proper SQL development is to create an execution plan that shows developers where the queries are lacking in performance. Execution plans should be run with even the simplest of queries to help IT departments keep the best performance in their SQL servers. Maintaining the best performance will keep emergency discussions involving heavy performance degradation to a minimum so technology staff can focus on production rather than fixing errors.