Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: Invalid object name '#temptable'.

    Hi

    I have a problem on our production server. Every few weeks #temptable stops working. I get the following error : "Invalid object name '#temptable'."

    Temporary table is used inside a stored procedure that gets called about 100000 times a day. Most of the time it works flawlessly, but every once in a while I get the above mentioned error. When this happens only the instance restart helps.

    The stored procedure has the following logic:
    CREATE TABLE #temptable ( ClientId int IDENTITY PRIMARY KEY, idA int )
    INSERT INTO #temptable ( idA ) SELECT idA FROM a WHERE <some_conditions>
    SELECT * FROM #temptable INNER JOIN b ON <join_predicate>


    The thing is that I can't reproduce this and I can't find out why this happens. If I fetch the query from the SQL Profiler and run it in Management Studio it works fine. But in the same time it doesn't on web page.
    After the restart it works fine.

    Server is MS SQL Server 2005 64bit. Version 9.00.5000.00 (SP4).

    For tempdb I have 4 data files. Each size of 5GB and autogrow by 10%.

    Server works on:
    Windows server 2003 R2
    Standard x64 Edition
    Service Pack 2
    Intel Xeon CPU E5420 @ 2.50GHz
    12GB RAM


    I have searched a lot of forums, blogs.. but I haven't found anything that could help me..

    Does anyone know what could be wrong? Or where should I look for error?

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    This seems to be a prime candidate for conversion from a temporary table to a table variable, with the associated possible performance benefits.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Is the temp table explicitly dropped at the end of the procedure? Dropping temp tables without an explicit drop is done as a sort of "garbage collection" process, and can be delayed just long enough that it interferes with the next run. Although, I have seen this throw more "table already exists" errors, but you might be hitting the other end of it.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I am most definitely speaking off of the top of my head here, so the pros here, please correct me if I am off-base . . .

    I assume that something so basic as creating a database object requires that that object be saved to disk. Is that a correct assumption? My assumption is that if you create a temporary table, you are saving that object to the tempDB database on disk.

    And, if you are doing this a million times a day, you are creating and destroying that object a million times a day, to disk.

    I assume that the creation of a table variable does NOT necessarily save to disk and, as such, for an object that is being created and destroyed a million times a day, that is the way to go.

    Again, just conceptualizing here . . .

    It would take about 15 seconds to reproduce your code as a table variable rather than a temporary table, and the two methodologies function identically. What's the harm in trying it our???
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It depends on how many records are stored in the structure. Table variables are quicker to create, but they can not participate in query parallelism, and suffer from performance problems, if they have significant amounts of data in them (call it a million rows for round numbers, but it depends on a number of factors like hardware, data, other tables involved, etc.). Table variables are also recorded in tempdb, but that may only happen in memory. The log records may be slightly different for the creation of a variable vs a table. You are right, it should be an easy test. But it should definitely be a test.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I've seen table variables perform less efficiently than temp tables with data sets as small as a few thousand records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I tried doing a simple test to determine where the breaking point was between temp tables and table variables. The test i was running was just a simple insert into and select from the table. Nothing else in the test. I was focusing more on the size of the table/variable, but that test might have to be more related to parallelism. Blindman: Was your case something like
    Code:
     where someid in (select id from @table)
    or
    Code:
     insert into @table
    select fields 
    from tables join
      othertables
    
    select * from @table

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good luck finding a breaking point. I'm sure it depends heavily on both your physical environment, recordset cardinality, join complexity, etc, frequency with which the recordset is referenced, etc.
    I start switching to temp tables at around 2000 rows. Always bear in mind that table variable can have, at most, one index (a primary key). For large temporary recordsets that may be referenced several times, the ability add additional indexes to a temporary table often makes a significant difference.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Mcuros,

    Can you tell us the average size of the rowset that goes into this temporary table.

    I am personally leary about using some number of rows to determine whether or not to use a table variable or a temporary table. I feel it is like choosing between cars based upon a comparison of their zero-to-sixty times.

    There are so many other factors that go into making this determination that I think that this talk of performance issues might scare people away from using table variable when, in my experience, it has not been an issue at all.

    Ken
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, people should be concerned about the performance implications. The thing to learn is how to determine what the performance implications are, and better understand if not the kinds of choices, at least the number of choices. In your car example, using a single criteria (zero to sixty time) to pick a car would be a disaster for someone who wants to go off road. Knowing that there are other options (like jeeps) would help a person (programmer) ask "well what happens when I use this thingy".

    Sadly, I have seen all too many developers show no curiosity about whether a thing can be made better, after they have fulfilled the letter of the requirement. Performance, support programmers, and security be damned.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •