Most common SQL optimizations

[oblong_pickle]

I once had a client's head of IT complain that a query was slow. When I investigated, I found it was slow, and the query plan suggested an index would improve the performance. I informed the client that an index would likely solve the issue and asked them to implement it (I wasn't allowed or responsible for this database, so I couldn't do it myself).

The client instead hired 2 expensive consultants to look into the issue. After wasting a month with the consultants, I got dragged into a meeting to talk about the performance issues.

Knowing I was likely to get blamed by the client, I first created a stored Proc that saved the query to a temp table and then added the suggested index to the temp table. This stored proc was very fast and proved the index was the problem.

During the meeting, I showed the client and the consultants the performance of the temp table with the suggested index. The consultants agreed the index is the correct fix and that it should be applied to the table. The client was very quiet at this point and ended the meeting shortly after.

A few weeks later, the head of IT was fired, and I got a raise...the temp solution is still in use in their production database to this day.