...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.

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.