Should I commit or rollback a transaction that creates a temp table, reads, then deletes it?

To select information related to a list of hundreds of IDs... rather than make a huge select statement, I create temp table, insert the ids into it, join it with a table to select the rows matching the IDs, then delete the temp table. So this is essentially a read operation, with no permanent changes made to any persistent database tables.

I do this in a transaction, to ensure the temp table is deleted when I'm finished. My question is... what happens when I commit such a transaction vs. let it roll it back?

Performance-wise... does the DB engine have to do more work to roll back the transaction vs committing it? Is there even a difference since the only modifications are done to a temp table?

Related question here, but doesn't answer my specific case involving temp tables: http://stackoverflow.com/questions/309834/should-i-commit-or-rollback-a-read-transaction

EDIT (Clarification of Question):

Not looking for advice up to point of commit/rollback. Transaction is absolutely necessary. Assume no errors occur. Assume I have created a temp table, assume I know real "work" writing to tempdb has occurred, assume I perform read-only (select) operations in the transaction, and assume I issue a delete statement on the temp table. After all that... which is cheaper, commit or rollback, and why? What OTHER work might the db engine do at THAT POINT for a commit vs a rollback, based on this specific scenario involving temp-tables and otherwise read-only operations?

I'm not really asking about the performance of wrapping it in a transaction. The connection is passed to multiple database API functions in succession, and they use a common temp table name/convention, so the transaction guaranteeing the deletion of the temp table is necessary. What I'm interested in is the effect of committing that transaction or rolling it back.

Triynko 2010-05-21 02:11:45

@Triynko - In what respect are you curious about the effect? Accessibility to the temp table from other code?

Thomas 2010-05-21 04:20:45

@Thomas - I'm asking about the performance of commiting vs rolling back the transaction. It's really not intuitive, because rolling back seems to imply that work would be done, but if such a simple uncommitted transaction is rolled back... the work may just be dropped from memory and no work to "apply" the changes would be recorded, like updating database stats. It's hard to say, and so I'm really looking for someone who knows what may go on in SQL Server under the hood during rollbacks and commits in this particular scenario.

Triynko 2010-05-21 16:08:39

@Triynko - I think I understand the confusion. Even if something is in a transaction, the "work" is still done. Even if you create a temp table in a transaction, populate it, do a bunch of work and then rollback, the work of putting the table into tempdb and writing data to disc, as if it were not in a transaction, is still done. The only difference is that this work is not *visible* to code outside the transaction using normal transaction isolation levels.

Thomas 2010-05-21 16:21:04

@Triynko - Keep in mind that if your entire operation is not in a transaction, each DDL or DML statement is itself a transaction. So, now the question is whether a large commit/rollback is cheaper than lots of smaller commits/rollbacks. In the long run, it is not cheaper IMO because of the additional blocking that you might be creating by having a long running transaction.

Thomas 2010-05-21 16:23:17

@Triynko - It should also be noted that any operation you do with respect to temp tables, whether in a transaction or not, is not done purely in memory. It is done just any other operation on any other normal table. Thus, opening a transaction and calling Create Table #Foo, will still create a table on disc. Rollback your transaction and SQL Server removes it from disc (or simply makes it unavailable to be disposed of later) pretty much as if you had called Drop Table #Foo.

Thomas 2010-05-21 16:27:30

@Thomas - I already understand that there's disk access involved in creating a temp-table (sometimes unlike a table variable), but assuming there IS a transaction, and assuming no errors, and assuming I create, perform read-only ops (i.e. select), and delete the temp table.... AT THAT POINT, what's the difference between calling Commit vs Rollback. WHICH causes more or less work to be done.

Triynko 2010-05-21 16:48:08

I said I'm already using #temp tables. But the connection is reused as it's passed to multiple database API functions in rapid succession, so it's important that there's a transaction, which explicitly deletes the table when finished, either by following through with the delete and commit, or by rolling back the transaction. I'm specifically interested in the effects of either committing or rolling back the transaction (assuming the table is definitely deleted before the commit and possibly deleted before the rollback).

Triynko 2010-05-21 02:17:12

ansaurus

tags:

views:

answers:

Should I commit or rollback a transaction that creates a temp table, reads, then deletes it?

related questions