To select information related to a list of hundreds of identifiers ... instead of making a huge selection operator, I create a temporary table, insert identifiers into it, attach it to the table to select rows matching identifiers, then delete the temporary table. Thus, it is essentially a read operation, without constant changes being made to any persistent database tables.
I do this in a transaction to ensure that the temporary table will be deleted when I finish. My question is: what happens when I make such a transaction and let it roll it back?
Performance ... should the database engine do more work to cancel a transaction against committing it? Is there even a difference, since only changes are made to the temp table?
A related question, but does not answer my specific case with temporary tables: Should I commit or cancel a read transaction?
EDIT (clarification of the question):
Do not seek advice to the point of fixation / rollback. A transaction is absolutely necessary. Suppose no errors occur. Suppose I created a temporary table, suppose I know that the actual "work" write to tempdb has occurred, suppose that I am performing read-only operations (select) in a transaction, and I assume that I am issuing a delete statement in the temp table. In the end, what ... is cheaper to commit or roll back, and why? What OTHER action can a db engine do in THAT POINT to commit and rollback based on this particular scenario, including temporary tables and other read-only operations?
source
share