Just a quick note, not much to see here. I've been working on a report for a client that queried over a lot of tables. As its first step, it created a temporary, in-memory table, @users, into which I was INSERTing filtered IDs (based on user-entered search criteria). I then used that @users table in a bunch of subsequent queries so that I didn't have to re-run the filtering for each piece of calculated data.
Anyway, long story short, the report has recently started to timeout (as the data tables have started to grow quickly in size) and the report has a Request Timeout of about 5 minutes. I took a look at the SQL (which I haven't touched in a long time) to see what could be optimized. Two of the calculated columns used a SQL IN clause on the @users table; something to this effect:
... WHERE id IN ( SELECT u.id FROM @users u )
Since these were one-to-one type relationships (only one record per-user-id in the parent query), I changed the IN clauses to INNER JOIN clauses. After making this change in two places, the query went from timing out after 300 seconds to executing in about 15 seconds.
There's still a lot of indexing that needs to be done (still working with the senior tech guy to get approval on that), but this was a HUGE performance difference. I am sure many of you are looking at this and thinking, "IN clause - that's such a rookie mistake!" Yeah, yeah it is, but I thought I would point it out here so others can learn from my mistake. Sometimes I get concerned that the joins will take a lot of time, but as you can see above, it is the much-lesser of two evils.
Want to use code from this post? Check out the license.