How to Pre Stage Queries on SQL Server?

Question

I've looked for tips of how to speed up sql intensive application and found this particularly useful link.

In point 6 he says

6. Do pre-stage data This is one of my favorite topics because it's an old technique that's often overlooked. If you have a report or a procedure (or better yet, a set of them) that will do similar joins to large tables, it can be a benefit for you to pre-stage the data by joining the tables ahead of time and persisting them into a table. Now the reports can run against that pre-staged table and avoid the large join.

You're not always able to use this technique, but when you can, you'll find it is an excellent way to save server resources.

Note that many developers get around this join problem by concentrating on the query itself and creating a view-only around the join so that they don't have to type the join conditions again and again. But the problem with this approach is that the query still runs for every report that needs it. By pre-staging the data, you run the join just once (say, 10 minutes before the reports) and everyone else avoids the big join. I can't tell you how much I love this technique; in most environments, there are popular tables that get joined all the time, so there's no reason why they can't be pre-staged

From what I understood you join the tables once and several SQL Queries can "benefit" from it. That looks extremely interesting for the application I'm working at.

The thing is, I've been looking for pre staging data around and couldn't find anything that seems to be related to that technique. Maybe I'm missing a few keywords ?

I'd like to know how to use the described technique within SQL Server. The link says it's an old technique so it shouldn't be a problem that I'm using SQL Server 2008.

What I would like is the following: I have several SELECT queries that run in a row. All of them join the same 7-8 tables and they're all really heavy, that impacts performance. So I'm thinking of joining them, run the queries and them drop this intermediate table. How/Can it be done ?

Answer 1

If your query meets the requirements for an indexed view then you can just create such an object materialising the result of your query. This means that it will always be up-to-date.

Otherwise you would need to write code to materialise it yourself. Either eagerly on a schedule or potentially on first request and then cached for some amount of time that you deem acceptable.

The second approach is not rocket science and can be done with a TRUNCATE ... INSERT ... SELECT for moderate amounts of data or perhaps an ALTER TABLE ... SWITCH if the data is large and there are concurrent queries that might be accessing the cached result set.

---

Regarding your edit it seems like you just need to create a #temp table and insert the results of the join into it though. Then reference the #temp table for the several SELECTs and drop it. There is no guarantee that this will improve performance though and insufficient details in the question to even hazard a guess.