1

I'm using EF and Dapper as ORMs. With some queries, I've optimized down to creating temporary tables like so: 

Create Table #temp(FromUserId int, ToUserId int, FromAction int, ToAction int, IsMatch int)

My concern is - if I have a high number of users on my app all hitting this query at the same time, does each instance of the context get its own temp table?

Do I have to worry about naming the temp tables something unique (like the session ID?)

Usually my connections are made using something like the following from my service layer: 

 using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) {

Any help would be awesome.

RobVious
  • 12,685
  • 25
  • 99
  • 181
  • 2
    Does each instance of the context get its own temp table?: Yep. # is limited to scope of connection; and sb reset even in connection pooling. http://stackoverflow.com/questions/2219714/sql-server-tables-what-is-the-difference-between-and Do I have to worry about naming the temp tables something unique (like the session ID?) Nope. – xQbert May 12 '16 at 21:34
  • 1
    See [this](http://stackoverflow.com/a/34081438/861716). – Gert Arnold May 12 '16 at 21:35
  • You can test this by opening two different connections to your database, creating a temp table in one and trying to select from it in another. If you are using SMSS, notice that each tab has its own connection to the database, so you could also create a temporary table in one tab and try to select from it in another. Food for thought. – Geeky Guy May 12 '16 at 21:36
  • 1
    And on the other hand; yes. Temp tables are always reused when defined as ##sometable. The double hash makes the temp table a global table accessible to all connections, and it lives as long as the connection that created it is alive. The table is dropped when the creator disconnects. – Ricardo C May 12 '16 at 22:20
  • If the desired scope of the temp table is only the current batch, consider using table variable instead. SQL Server will keep a single page stub for the table variable in tempdb for optimization and reuse, – Dan Guzman May 13 '16 at 02:08
  • http://dba.stackexchange.com/q/73514/6171 and http://dba.stackexchange.com/q/86852/6171 – gotqn May 13 '16 at 14:25

0 Answers0