5 Ways to Improve Your SQL Queries

Tips for Writing Efficient SQL Queries

In this video, the speaker shares some advice on writing efficient SQL queries based on their experience working with various databases. The tips include starting with a filtered data set, using temp tables and CTEs, avoiding repetition, and more.

Starting with a Filtered Data Set

• Queries can be slow if everything is being done at the same time without any filtering.

• Start by isolating just the data that you care about to avoid processing anything on data you don't care about.

• Figure out what is the actual granularity that you're concerned with before writing a query to return something on a customer level.

Using Temp Tables and CTEs

• Use temp tables or CTEs to organize your query in a much better way.

• Isolate operations into temp tables to avoid performing them on every single record.

• Only return columns that are needed to avoid wasting resources and cluttering up your query.

Avoiding Repetition

• Avoid repeating yourself in code as it leads to inefficiencies.

• Use common table expressions (CTEs) or subqueries instead of repeating code blocks.

Other Tips

• Use indexes appropriately to speed up queries.

• Be mindful of how much data is being returned by your queries.

• Understand how joins work and use them effectively.

Using Indexes in SQL Server

In this section, the speaker talks about the importance of using indexes in SQL Server to improve query performance.

Understanding Indexes

• Use indexes on road-based databases like SQL Server.

• Understand what the indexes are on the tables you're working with, such as primary keys, clustered and non-clustered indexes, and foreign key relationships.

• Understanding these things will help the execution plan find what it needs much quicker.

Clustered vs Non-Clustered Indexes

• Clustered indexes order the table based on an ID or a certain criteria. It helps quickly locate a record based on its ID.

• Non-clustered indexes are more like an appendix that creates reference points for your table. It helps locate data by using it as a reference point.

Adding Indexes to Temp Tables

• Adding clustered or non-clustered indexes to temp tables can help improve query performance. However, test it yourself because sometimes adding an index may slow down queries.

Be Consistent

In this section, the speaker emphasizes the importance of being consistent when creating code in terms of indentation spacing naming structure.

Being Consistent

• Being organized and consistent in what you're creating is important.

• Don't change your coding style throughout your project. Stick to one style throughout your project.

Importance of Organization

In this section, the speaker emphasizes the importance of organization in one's work and how it can impact how others view your work.

The Impact of Organization

• Being organized can separate you from others in terms of how your work is viewed.

• It is important to have all things in the right places and tucked away so that you know exactly where things are.

• Laziness should be avoided when it comes to organization.

Conclusion

In this section, the speaker concludes the video by encouraging viewers to share their own experiences and ideas on the topic discussed.

Encouragement to Share Ideas

• Viewers are encouraged to share their own experiences and ideas on organization.

• Talking about ideas with others can help improve one's approach.

• The speaker may do more videos like this if viewers find them helpful.