October 25, 2018 at 10:36 am
Hi all.... I've got some code that I'm going to need to place into a transaction so that something like the Agent Job that runs the stored procedure, being cancelled due to other considerations outside of the scope of my code, what it does to permanent tables gets rolled back, but I do NOT want any temp table inserts to get rolled back. Using temp tables instead of table variables for performance reasons. Might there be a way to achieve that? I also need to be sure that I can run queries within the transaction that rely on the successful completion of previous ones (iow, as if they had been committed.). Do I have any options in this regard without the considerably adverse nightmare of table variables?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 25, 2018 at 1:44 pm
Nope. Temp tables are part of user transactions, and if there's a rollback any operations against them within the transaction will be rolled back. Table variables are the only thing that don't participate in user transactions. They're not that bad if all you're doing is inserting into and selecting from, it's joins and such that tend to have problems.
You could also have temp tables and do an INSERT INTO #Table SELECT * FROM @Table after each commit/rollback if you're really worried about the table variables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2018 at 1:58 pm
In addition to Gail's comments, you can can use OPTION(RECOMPILE) on the table variable query and the optimizer will actually come up with a more accurate row estimations.
October 25, 2018 at 2:40 pm
sgmunson - Thursday, October 25, 2018 10:36 AMHi all.... I've got some code that I'm going to need to place into a transaction so that something like the Agent Job that runs the stored procedure, being cancelled due to other considerations outside of the scope of my code, what it does to permanent tables gets rolled back, but I do NOT want any temp table inserts to get rolled back. Using temp tables instead of table variables for performance reasons. Might there be a way to achieve that? I also need to be sure that I can run queries within the transaction that rely on the successful completion of previous ones (iow, as if they had been committed.). Do I have any options in this regard without the considerably adverse nightmare of table variables?
I must be missing something. If the agent job is cancelled or ends abnormally outside your code it won't matter if the data in # temp tables is kept or not as that data will go away when the job ends.
October 26, 2018 at 7:38 am
Lynn Pettis - Thursday, October 25, 2018 2:40 PMsgmunson - Thursday, October 25, 2018 10:36 AMHi all.... I've got some code that I'm going to need to place into a transaction so that something like the Agent Job that runs the stored procedure, being cancelled due to other considerations outside of the scope of my code, what it does to permanent tables gets rolled back, but I do NOT want any temp table inserts to get rolled back. Using temp tables instead of table variables for performance reasons. Might there be a way to achieve that? I also need to be sure that I can run queries within the transaction that rely on the successful completion of previous ones (iow, as if they had been committed.). Do I have any options in this regard without the considerably adverse nightmare of table variables?I must be missing something. If the agent job is cancelled or ends abnormally outside your code it won't matter if the data in # temp tables is kept or not as that data will go away when the job ends.
Okay, this is where I need to focus. Recognizing that a cancellation is taking place, would a TRY/CATCH block get triggered under the circumstances?
Edit: Just realized I had forgotten to include the reason why... I wanted a try/catch block to handle things and "clean up", but maybe that's not how things work and such a block would not get triggered? So if there's no triggering a try catch, and you don't have one, or a transaction, what is supposed to happen ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 26, 2018 at 11:26 am
sgmunson - Friday, October 26, 2018 7:38 AMLynn Pettis - Thursday, October 25, 2018 2:40 PMsgmunson - Thursday, October 25, 2018 10:36 AMHi all.... I've got some code that I'm going to need to place into a transaction so that something like the Agent Job that runs the stored procedure, being cancelled due to other considerations outside of the scope of my code, what it does to permanent tables gets rolled back, but I do NOT want any temp table inserts to get rolled back. Using temp tables instead of table variables for performance reasons. Might there be a way to achieve that? I also need to be sure that I can run queries within the transaction that rely on the successful completion of previous ones (iow, as if they had been committed.). Do I have any options in this regard without the considerably adverse nightmare of table variables?I must be missing something. If the agent job is cancelled or ends abnormally outside your code it won't matter if the data in # temp tables is kept or not as that data will go away when the job ends.
Okay, this is where I need to focus. Recognizing that a cancellation is taking place, would a TRY/CATCH block get triggered under the circumstances?
If the job is stopped, no, no more than cancelling a query via timeout/SSMS's cancel hits the try catch. If it hits an error, yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2018 at 11:53 am
GilaMonster - Friday, October 26, 2018 11:26 AMsgmunson - Friday, October 26, 2018 7:38 AMLynn Pettis - Thursday, October 25, 2018 2:40 PMsgmunson - Thursday, October 25, 2018 10:36 AMHi all.... I've got some code that I'm going to need to place into a transaction so that something like the Agent Job that runs the stored procedure, being cancelled due to other considerations outside of the scope of my code, what it does to permanent tables gets rolled back, but I do NOT want any temp table inserts to get rolled back. Using temp tables instead of table variables for performance reasons. Might there be a way to achieve that? I also need to be sure that I can run queries within the transaction that rely on the successful completion of previous ones (iow, as if they had been committed.). Do I have any options in this regard without the considerably adverse nightmare of table variables?I must be missing something. If the agent job is cancelled or ends abnormally outside your code it won't matter if the data in # temp tables is kept or not as that data will go away when the job ends.
Okay, this is where I need to focus. Recognizing that a cancellation is taking place, would a TRY/CATCH block get triggered under the circumstances?
If the job is stopped, no, no more than cancelling a query via timeout/SSMS's cancel hits the try catch. If it hits an error, yes.
Okay, then the only issue that remains is how to be able to make use of temp tables populated during the transaction when they are not yet committed changes. What will my queries see when selecting from the recently populated temp table (but not yet a committed change)? Does SAVE TRAN help in this kind of situation?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 26, 2018 at 12:10 pm
sgmunson - Friday, October 26, 2018 11:53 AMGilaMonster - Friday, October 26, 2018 11:26 AMsgmunson - Friday, October 26, 2018 7:38 AMLynn Pettis - Thursday, October 25, 2018 2:40 PMsgmunson - Thursday, October 25, 2018 10:36 AMHi all.... I've got some code that I'm going to need to place into a transaction so that something like the Agent Job that runs the stored procedure, being cancelled due to other considerations outside of the scope of my code, what it does to permanent tables gets rolled back, but I do NOT want any temp table inserts to get rolled back. Using temp tables instead of table variables for performance reasons. Might there be a way to achieve that? I also need to be sure that I can run queries within the transaction that rely on the successful completion of previous ones (iow, as if they had been committed.). Do I have any options in this regard without the considerably adverse nightmare of table variables?I must be missing something. If the agent job is cancelled or ends abnormally outside your code it won't matter if the data in # temp tables is kept or not as that data will go away when the job ends.
Okay, this is where I need to focus. Recognizing that a cancellation is taking place, would a TRY/CATCH block get triggered under the circumstances?
If the job is stopped, no, no more than cancelling a query via timeout/SSMS's cancel hits the try catch. If it hits an error, yes.
Okay, then the only issue that remains is how to be able to make use of temp tables populated during the transaction when they are not yet committed changes. What will my queries see when selecting from the recently populated temp table (but not yet a committed change)? Does SAVE TRAN help in this kind of situation?
Since temp tables are local to your session, you always see what's in them regardless of what transactions you have open. Same as permanent tables, you can always see the changes that you are making. Other sessions can't see your changes.
And SAVE TRANSACTION is completely unrelated here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2018 at 2:21 pm
GilaMonster - Friday, October 26, 2018 12:10 PMsgmunson - Friday, October 26, 2018 11:53 AMGilaMonster - Friday, October 26, 2018 11:26 AMsgmunson - Friday, October 26, 2018 7:38 AMLynn Pettis - Thursday, October 25, 2018 2:40 PMsgmunson - Thursday, October 25, 2018 10:36 AMHi all.... I've got some code that I'm going to need to place into a transaction so that something like the Agent Job that runs the stored procedure, being cancelled due to other considerations outside of the scope of my code, what it does to permanent tables gets rolled back, but I do NOT want any temp table inserts to get rolled back. Using temp tables instead of table variables for performance reasons. Might there be a way to achieve that? I also need to be sure that I can run queries within the transaction that rely on the successful completion of previous ones (iow, as if they had been committed.). Do I have any options in this regard without the considerably adverse nightmare of table variables?I must be missing something. If the agent job is cancelled or ends abnormally outside your code it won't matter if the data in # temp tables is kept or not as that data will go away when the job ends.
Okay, this is where I need to focus. Recognizing that a cancellation is taking place, would a TRY/CATCH block get triggered under the circumstances?
If the job is stopped, no, no more than cancelling a query via timeout/SSMS's cancel hits the try catch. If it hits an error, yes.
Okay, then the only issue that remains is how to be able to make use of temp tables populated during the transaction when they are not yet committed changes. What will my queries see when selecting from the recently populated temp table (but not yet a committed change)? Does SAVE TRAN help in this kind of situation?
Since temp tables are local to your session, you always see what's in them regardless of what transactions you have open. Same as permanent tables, you can always see the changes that you are making. Other sessions can't see your changes.
And SAVE TRANSACTION is completely unrelated here.
Thanks, Gail. That's what I needed to know. I'm good to go...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply