SQL Query Performance

13 Tips on Improving Efficiencies

Image for post
Image for post

THEORY BEHIND QUERY RUN TIME

MS SQL is just another piece of software that runs on a computer, thus subject to the same limitations as all software — it can only process as much information as its hardware is capable of handling. There are many ways to optimize a DBMS to improve performance. In this brief session, we will concentrate on performance tips when writing our queries. Ultimately, we want to reduce the number of calculations a DBMS must perform to reduce the time it takes to execute our query.

  1. Joins: If your joins substantially increase the row count of the result set, your query is likely to be slow.
  2. Aggregations: Combining multiple rows to produce a result requires more computation than simply retrieving those rows.

Reducing Table Size

Filter the table to return only what you need. Try to use indexed fields for the predicate as non-indexed fields will cause a table scan which can dramatically reduce query performance. For example, if you’ve got time series data, limiting to a small time window can make your queries run much more quickly as we will be doing an index seek versus a table scan.

Image for post
Image for post

Simplifying the Joins

Try to reduce the table size before joining it to another table. For example, the query below joins information about college sports teams onto a list of players at various colleges:

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

13 QUERY PERFORMANCE TIPS

1. Owner/Schema Name

Remember to prefix object names (i.e. table, stored procedure, view) with its owner/schema name.

2. The * Operator

Do not use the * operator in your SELECT statements. Instead, use column names.

3. Nullable Columns

Avoid using NOT IN when comparing with nullable columns. Use NOT EXISTS instead.

4. Table Variables and Joins

Avoid using table variables in joins. Use temporary tables, CTEs (Common Table Expressions) or derived tables in joins instead.

5. Stored Procedure Names

Do not begin your stored procedure’s name with sp_.

6. Use SET NOCOUNT ON

Use SET NOCOUNT ON with DML operations.

7. Avoid Using GROUP BY, ORDER BY and DISTINCT

Avoid using GROUP BY, ORDER BY, and DISTINCT whenever possible.

8. Check Indexes

There should be indexes on all fields used in the WHERE and JOIN portions of the SQL statement.

9. Use Same Datatype on JOIN and WHERE Clauses

This is easier said than done depending on your permissions to make changes to the schema.

10. Avoid Using Calculated Fields in JOIN and WHERE Clauses

This can be done by creating a field with the calculated values used in the join on the table. See below.

Image for post
Image for post
Image for post
Image for post

11. Avoid Multiple Joins in a Single Query

Try to avoid writing a query using multiple joins that include outer joins, cross apply, outer apply and other complex subqueries.

12. Avoid Multi-Statement Table Valued Functions (TVFs)

Multi-statement TVFs are more costly than inline TVFs.

13. Use Data Compression Whenever Possible

If your SQL Servers are I/O bound and not CPU bound, turning on data compression will actually improve performance in your environment.

DISTRIBUTED QUERIES

Linked server four-part queries are also called distributed queries. Using distributed queries, you can refer tables on different data sources/servers in a single query. Query optimizer creates an execution plan by looking at the query and breaking it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to the end user as a single record set.

Image for post
Image for post

OPENQUERY

Execute the specified pass-through query on the specified linked server. SQL Server sends pass-through queries as un-interpreted query strings to an OLE DB data source. That is, SQL won’t apply any kind of logic on the query and won’t try to estimate what that query would do. Instead, it would simply pass the specified query as it is to the target linked server. Open queries are useful when you are not referencing multiple servers in one query. It’s generally fast, as SQL does not break it into multiple operations and does not perform any local action on the output received.

Image for post
Image for post

Written by

Let's turn data challenges into business advantages.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store