Optimizing SQL queries using SARGable queries
Optimizing queries in SQL is an important factor for the performance and efficiency of a database. One way of achieving it is by using SARGable queries.
SARGable stands for Search Argument Able. SARGable queries allow the database engine to quickly filter and retrieve rows from tables, resulting in better query performance. Some tips to write SARGable queries:
- Avoid functions or expressions on columns in WHERE clauses:
For example, don’t write ‘WHERE YEAR(order_date) = 2023’. Instead, write ‘WHERE order_date ≥ ‘2023–01–01’ AND order_date ≤ ‘2023–01–01’.
If we need to use a function on a column, consider creating a computed column or a function based index, if the database supports it.
2. Use parameters instead of expressions:
Use parameters for dynamic values rather than embedding them directly in the SQL statement. It helps with query plan caching and reuse.
For example,
use ‘WHERE order_date ≥ @StartDate AND order_date < @EndDate’
3. Avoid using wildcards at the beginning of LIKE patterns:
Avoid using patterns like ‘%expression%’ , instead try to use ‘expression%’.
4. Use indexed columns for filtering:
For filtering, use columns that are indexed. Indexes significantly speed up data retrieval.
5. Multiple OR conditions can make queries non-sargable:
Try to use UNION or UNION ALL instead.
Consider this non-sargable query:
SELECT * FROM Products WHERE Category = ‘A’ OR Category = ‘B’;
You can rewrite it as:
SELECT * FROM Products WHERE Category = ‘A’
UNION ALL
SELECT * FROM Products WHERE Category = ‘B’;
6. Optimize complex JOIN conditions:
Use indexed columns wherever possible and avoid joining on columns with functions or expressions.
By following these steps, one can make SQL queries more SARGable. It is important to understand that the exact strategies may vary depending on the database used.
