SQL Query Optimization Tips and Common Pitfalls

SQL queries often need optimization to improve efficiency. Below is a collection of practical tips and best practices for writing performant SQL statements.

01. Avoid full table scans – create indexes on columns used in WHERE and ORDER BY clauses.

02. Do not use != or <> in WHERE – these operators prevent the engine from using indexes and cause a full scan.

03. Avoid NULL checks in WHERE – set a default value (e.g., 0) for the column so the query can use the index.

select id from t
where num is null

select id from t
where num = 0

04. Avoid OR in WHERE – rewrite using UNION ALL to keep index usage.

select id from t where num = 10 or num = 20

select id from t where num = 10
union all
select id from t where num = 20

05. Avoid leading wildcards in LIKE – LIKE '%abc%' forces a full scan; consider full‑text search instead.

06. Parameters in WHERE can cause scans – force index usage with a hint.

select id from t with(index(索引名))
where num = @num

07. Do not apply functions or arithmetic on indexed columns in WHERE – rewrite the expression so the column itself is compared.

select id from t where num/2 = 100

select id from t where num = 100*2

08. Avoid functions on indexed columns – e.g., replace substring(name,1,3)='abc' with name like 'abc%'.

select id from t where substring(name,1,3)='abc'

select id from t where name like 'abc%'

09. Do not put functions on the left side of ‘=’ – the engine cannot use the index.

10. Composite indexes – the leftmost column must be used in the predicate; keep column order consistent with the index definition.

11. Avoid meaningless queries – e.g., creating an empty result set with SELECT ... INTO #t WHERE 1=0. Use CREATE TABLE instead. create table #t(...) 12. Prefer EXISTS over IN for better performance.

select num from a where exists (select 1 from b where num = a.num)

13. Low‑cardinality indexes may be ineffective – an index on a column like sex (male/female) often does not improve query speed.

14. Limit the number of indexes – too many indexes slow down INSERT and UPDATE; keep the total per table to around six.

15. Avoid updating clustered index columns frequently – changes force row reordering and consume resources.

16. Use numeric types for numeric data – numeric comparison is faster than string comparison.

17. Prefer VARCHAR/NVARCHAR over CHAR/NCHAR – variable‑length fields save space and improve search speed.

18. Do not use SELECT * – list only the required columns.

19. Prefer table variables to temporary tables – they have limited indexing but avoid some temp‑table overhead.

20. Minimize creation and deletion of temporary tables – reduces system‑table lock contention.

21. Use SELECT INTO for large one‑time inserts into temp tables; otherwise CREATE TABLE then INSERT .

22. Explicitly drop temporary tables at the end of a stored procedure – TRUNCATE then DROP to release locks.

23. Avoid cursors for large data sets – rewrite set‑based logic when processing more than ~10,000 rows.

24. Prefer set‑based solutions over cursor or temp‑table approaches – they are generally more efficient.

25. For small data sets, a FAST_FORWARD cursor may be acceptable, but set‑based aggregation is usually faster.

26. Set NOCOUNT ON at the start of stored procedures and triggers, and NOCOUNT OFF at the end to reduce network traffic.

27. Avoid returning excessively large result sets to the client – evaluate whether the data volume is necessary.

28. Keep transactions short – large transactions hurt concurrency.

Source: CSDN, author: 青春微凉不离殇