7 tips for faster SQL queries (are really just 5)
I like when people make lists of things and decide on how many things they want in the list before they actually make the list. Usually it means a few items on the list are not so good. And by coincidence I have found another list like that which is related to SQL and I'm seeing a pattern...
- Use CASE when possible instead of UPDATE - Interestingly enough the first item on the list is one of the two I think could have been left out. This is just double-dipping and not knowing when to use temp tables.
- Don't blindly reuse code - This is a weird performance tip for me. This is just common developer sense. So it turns out the first two things in my opinion are the ones that should never have been on the list.
- Do pull only tables you need - A good tip but I thought everybody knew this already...
- Don't double-dip - I like the choice of wording here! It's a funny way to say don't use two queries of you can do it in one and don't query the same thing multiple times.
- Do know when to use temp tables - I'm letting this slip through the cracks even though this is just one way of dealing with double-dipping.
- Do pre-stage data - A simple way to gain performance but also error prone if done without great care.
- Do delete and updates in batches - There are two sides to this one; if you have a big update/delete you want to split it up in smaller batches and if you have a lot of small (i.e. single) update/deletes you should execute them in batches.