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 I hear they are slower than equiv run-on SQL statements
________________
oop.ismad.com
New Depends on the complexity of the operation...
...as well as which specific server you are talking about. I know SQLServer 6.5 had real problems with Joins on more than 4 tables (up to about 6 or 7 on the later ones). So if you have joins involving more than this threshold, temp tables outperform the more complex (but singular) SQL constructs.\r\n\r\nGenerally, though, a single SQL statement will perform better, but it's usually because the programmers use temp tables not as an optimization trick - rather it's a way to break the problem up into smaller and more easily to manipulate chunks. So, yes, if you can state the problem in a more concise single statement, you'll usually have much better throughput.
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.
New Not much larger
Generally, though, a single SQL statement will perform better, but it's usually because the programmers use temp tables not as an optimization trick - rather it's a way to break the problem up into smaller and more easily to manipulate chunks. So, yes, if you can state the problem in a more concise single statement, you'll usually have much better throughput.

I don't think the one-big-statement approach is necessarily more "concise". It is generally just naming a chunk(s) and referencing the name instead of embedding that chunk within a statement. It might take a few more characters, but not much. In some cases it may shrink the total size because there may be less need for table qualifiers on columns because the name pool for tables is smaller if you divide.
________________
oop.ismad.com
     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)

Fight me!
82 ms