How I Optimize SQL Queries

When writing SQL queries, your goal shouldn't be to write something that works.

It should be to write something that works efficiently.

I’ve been going down a damn SQL optimization rabbit hole these last two weeks since I'm spending so much time with stored procedures and views.

After yesterday’s LinkedIn post, many people asked me HOW I approach a query to optimize it. My process is still rusty, but I’ll share with you what I’ve got so far…

I leverage two built-in SQL Server tools at the moment:

- the Execution Plan
- the SQL Profiler

Here’s a simple explanation of each…

The execution plan breaks up a script into multiple sections so that you can see how each one performs. This helps me identify what parts of my queries slow performance the most.

The SQL Profiler compares the performance of two queries. This comes in handy when I’m testing two ideas. I can see which one runs faster and uses less computing power.

Now, here’s the catch with these two tools…

There TOOLS. And tools only.

They help me identify what to improve, but they don’t tell me HOW to change queries. To do that, I use intuition and pattern recognition.

Over the last four months, I’ve spent every day writing SQL code.

So, I’ve recognized what tricks and tactics improve performance and which decrease it. After using the tools I mentioned above, I go to the slow sections of my query and look to do some of the following:

- Limit the number of joins and the type of joins
- Use as few temp tables as possible
- Filter data as early as possible
- Remove complex subqueries

What do you think of this? Is there anything else you look for while optimizing SQL queries?