7 T-SQL Development Tips for Faster Queries

sql-monitoring

Today I will let know some essential techniques that never change to make your queries run faster in the Microsoft SQL server!

It is excellent to deploy an optimized product that runs a dozen times faster than the previous version.

Performance tuning is a vast topic that can be a complicated and struggling task to accomplish, and this post can be a good beginning point to start.

1- Do not use SELECT *

You should not use SELECT * in your queries.

It is always a better option and one of the SQL best practices to explicit the column list in a SELECT query than use a * wild card.

The fewer columns you ask for, the fewer data must be loaded from disk when processing your query.

Return only the columns needed

Do not return all the columns from a table, if you do not need them.

This usage approach causes unnecessary IO consumption and increases network traffic.

In addiction, SELECT * prevents the use of covered indexes, further potentially hurting query performance.

Using explicit columns into queries helps you to create maintainable code.

This ensure code will not break when someone adds new columns to your table — especially if you have views that refer to the original table.

2- Restrict the number of rows returned

Always restrict the number of rows, verify your WHERE clause, and use TOP if necessary.

Choose TOP operator when possible

The SET ROWCOUNT and the TOP operator effectively perform the same function.

They work by allowing you to specify a specific number of rows to be returned.

When the specified number of rows is reached, all processing on the query stops, potentially saving SQL Server overhead, and boosting performance.

There are some instances (such as rows returned from an unsorted heap) where it is more efficient.

3- Consider using EXISTS instead of IN

The SQL EXISTS logical operator allows you to check whether a subquery returns any row.

If the subquery requires to scan a large volume of records, stopping the subquery execution as soon as a single record is matched may significantly speed up the query.

Usually, the same is valid for the NOT IN (subquery) or NOT EXISTS (subquery).

See how query went from 3120 seconds down to 92seconds (31,000 times faster)!

SQL Optimizer treats EXISTS, and IN the same way, whenever it can.

So in many cases, you are unlikely to see any significant performance differences.

In any situation, proper testing and understanding of the features are essential for a better solution.

Take care with Null values

Be cautious when using the NOT IN operator if the subquery’s source data contains NULL values. If a NULL value is present in the list, the result set is empty!

4- Use more temporary tables and less variable tables

In most cases, temporary tables beat table variables easily because their ability to have statistics improves query optimization dramatically.

That remains even true with the enhancements in SQL Server 2019 (Table Variable Deferred Compilation).

Make the right choice

Variable tables still the right choice for high frequency and low row volume processes.

Also, for tasks where you don’t need to do anything particularly relational with them. For example, to store lists of databases, tables, and indexes to iterate over.

5- Do delete and update in batches

Deleting or updating large amounts of data can be a nightmare if you don’t do it right.

These statements run as a single transaction, blocking others’ requests for their durations.

Your log file may grow unexpectedly and cause you problems.

If something happens to the system while they’re working, it has to roll back the entire operation, which can take a very long time.

There’s no silver bullet here, but a proper solution is to do deletes or updates in smaller batches.

While the smaller batches are committing to disk, others can sneak in and do some work, so concurrency is greatly enhanced.

Also, if the transaction gets killed for whatever reason, it has a small number of rows to roll back, so the database returns online much quicker.

6- Never take user input to build a query

SQL Server will execute all syntactically valid queries that it receives.

Never concatenate user input that is not validated.

String concatenation is the primary point of entry for script injection.

Any procedure that constructs SQL statements should be reviewed to avoid SQL injection.

Use stored procedures to validate user input.

7- Write Queries for your data structures

Take advantage of the indexes, foreign key constraints, and other structures that you have in place when you are writing your T-SQL code.

Make sure that your clustered index is working well to help drive your queries to your data in an efficient manner.

Take the time to understand how the Optimizer works and write your code in such a way that you help it, not hurt it.

Avoid Multiple SQL Server Joins

Try to avoid writing a SQL query using a large number of joins.

The SQL Server query optimizer may not be able to produce a reasonable plan for retrieving the data.

Break down large queries and store the aggregated or intermediate results in temporary tables.

This allows SQL Server to create new statistics, improving the cardinality estimation.

The result is usually is better performance, and as we see before, prefer temporary tables instead of table variables.

Need SQL Server consulting?
We’d love to help! We tune slow SQL Servers every day.
For over a DECADE now.

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *