July 1, 2008 at 7:42 am
While trying to optimize what I call a poorly written report that is a long running - but totally temp table driven - sproc, I decided to ask our programmers from using temp tables whereever possible. My bias being obvious to most DBA, hopefully.
Then this arrives in my mailbox this morning.
"Since temp tables are scoped per connection, if we replace them with permanent tables, how do you recommend we deal with concurrency? Is there a report instance ID or connection ID that can be added to the primary key? And how do we deal with orphaned rows from reports that are abnormally terminated?"
Is concurrency a valid issue? -
(Orphaned rows would just get truncated - a non issue.)
July 1, 2008 at 8:25 am
I'm curious ... why are you against temp tables? If you're configured properly they will perform just as good as a real table and are (in my mind) much better than 'temporary real tables'.
July 1, 2008 at 8:57 am
Indexing temp tables seems to be an unprofitable experience. Even if you can create the index, getting SQL to use it makes it not worthwhile.
Using the Temp db is bad form...fill it up/lock it up, and things shut down.
Using our mid tier hardware and SQL 2005, I don't want to see more than a couple thousand rows hit a temp table because of those two items above.
Fast reports mean good indexing with large result sets.
I am open to where my logic is flawed on this one.
July 1, 2008 at 9:56 am
I don't think your logic is that great on this one.
TempDB is nothing to be afraid to use. Is it overused at times - absolutely, but many large operations against actual tables - even with proper indexing - will be handled by the database engine in TempDB. As far as filling it and locking it - well, if you have operations running that make TempDB too large, you either do not have enough space for TempDB, or the operation itself is poorly done. Someone can easily write a query against two very large tables that will cause TempDB to expand too much also.
I would be more afraid of developers doing what the developer suggested he was doing - using a real table for staging data in a procedure and trying to manage concurrency. My experience with developers doing this has not been good. I would also be afraid developers would decide to avoid temp tables by creating cursors - a much worse proposition in most cases.
So, I think you may simply need to educate your developers to writing good queries and when to explicitly create temp tables. Making a hard rule about never using them sounds to me like more trouble than it will solve.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply