IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Not the only reason...
temp tables can stick around for a while (for multiple operations). A single SQL statement (if there were multiple operations) would have have to be reprocessed each time.

I won't mention the read locks that single SQL statement would put on the system. (The corellary danger, is that there aren't read-locks on the system for a temp table. Design your application accordingly. :-)
New Other considerations...
I haven't found a way to tune the indexing on temp tables, which can present some difficulties if the temp tables get large and the lookups are based on multiple keys. In addition, because the data changes each and every time you load the temp tables, the queries against the temp table will usually be automatically re-planned - whereas permanent tables tend to be more stable and the query plans don't have to be compiled with each invocation.

Not sure of the other servers, but SQLServer does have read/write locks on the temp tables. Of course, the tables from which you got the data are not locked - which is what you were probably trying to indicate.

SQLServer has a new concept for procedures called the Table Variables, which oddly enuf are not locked - getting you some performance boost. I like the idea, but it would be nice if their version of T-SQL was consistent and let you pass table variables around like any other variable. As it is, you can't seem to pass the table vars from one stored procedure to another, seriously crimping how you can modularize an application.
     How would you do this? - (drewk) - (11)
         Kludgy, but without subselects I end up using TRANSFORMs - (tseliot) - (3)
             Oops, didn't mean to filter on ordernum - (drewk) - (2)
                 Oops, no they're not. - (tseliot) - (1)
                     It was there when I was hired -NT - (drewk)
         counts? - (tablizer) - (6)
             Sybase, Oracle, Postgres, MSSQL all allow temp tables. - (admin) - (5)
                 I hear they are slower than equiv run-on SQL statements -NT - (tablizer) - (4)
                     Depends on the complexity of the operation... - (ChrisR) - (3)
                         Not the only reason... - (Simon_Jester) - (1)
                             Other considerations... - (ChrisR)
                         Not much larger - (tablizer)

I'm very sorry, but I'm not allowed to argue with you unless you've paid.
76 ms