Database Programming: Sharing a Temp Table Among Processes

One of the great things about my involvement with the SQL Ranger community is that I occasionally hear from past Candidates asking for help with issues in the field. So it was earlier this week when Binh Cao, an MCS Senior Consultant II based in San Diego, looped me in on a question his customer was asking him:

Just wondering if you know if the SQL Server team is planning to address the inability for different processes to reuse the query plan when a temp table is involved? For example, if a process creates a temp table and executes a stored procedure that loads the temp table based on its input parameters and then performs other queries joining to the temp table, we notice that if another process does the same thing (different input params) a minute later, the plan is not reused. We see the statements being recompiled to generate a new query plan. 

We use this technique quite a bit and it would be nice to know if it is being address or if we should consider a new technique. Note: We favor this technique because we can write the SPs in a set-oriented fashion using the temp table. Often our SPs expects a temp table to exist and the records in the temp table is what drives the business logic within the proc.

Binh approached his time in the MCA: Database program with great passion and determination, so I was only too happy to help. I pointed him to the discussion of plan recompilation dynamics I posted here, here, here, and here during the summer of 2006.

Subsequent conversation, though, uncovered the realization that the customer didn't just want to reuse the plan across connections, they in fact wanted to share the temp table among processes. The clue was embedded in an almost plaintive statement from the customer:

I was hoping to see it generate only one plan with a total execution count of 6 (similar to what I see if I used a permanent table).

I started to believe that I'd been answering the wrong question. I suggested to Binh that his customer use a global temp table rather than a local temp table (in that global temp tables are available to all connections, in this sense they mimic the behavior of permanent tables). A global temp table name is prepended with two pound signs (create table ##GlobalTempTable) while a local temp table is prepended with a single pound sign (create table #LocalTempTable).

Binh reported that the global temp table was exactly what the customer was after.

It's a great day when you get to help an MCA: Database Candidate resolve a customer-facing issue. When he refers to you as "professor," that's just icing on the cake.

Thanks for the kind words, Binh, and for looping me in on your customer's issue!

     -wp