July 20, 2009 at 2:34 pm
In a sproc scenario where the order of INSERT statement getting executed before OR after the UPDATE statement is not important.
Would the inserts go first or the updates?
I'm thinking if new data is being inserted and the logic within the insert statement get flawed for some weird, the logic within the update statement could correct it. So executing update statements after insert statements should be good rather than the other way around.
All expert viewpoints, analysis appreciated.
Thanks,
S
--
:hehe:
July 20, 2009 at 2:41 pm
I would perform updates first, then inserts. If you perform inserts first, then you are going to update the rows that were just inserted - which is just a waste of time and resources on the system.
If I had issues with either the updates or inserts - I would rollback the entire process and not have any rows updated or inserted.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 20, 2009 at 2:55 pm
Jeffrey,
Thanks for the response.
After reading your comment, its true updating just inserted fresh data based on logic of the table its being inserted in would be a waste of system resources.
How would you handle the rolls back? Using TRY/CATCH blocks?
Thanks,
S
--
:hehe:
July 20, 2009 at 3:30 pm
I would handle it using transactions. Whether that was in a try/catch block or not would depend on what was being done.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 20, 2009 at 3:34 pm
Slick84 (7/20/2009)
Would the inserts go first or the updates?
Two questions...
1- Are we talking about a small OLTP alike transaction OR are we talking about a large ETL alike transaction?
2- If this is an ETL alike process, wouldn't you consider MERGE statement instead of insert/update on the same table?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 20, 2009 at 3:40 pm
PaulB (7/20/2009)
Slick84 (7/20/2009)
Would the inserts go first or the updates?Two questions...
1- Are we talking about a small OLTP alike transaction OR are we talking about a large ETL alike transaction?
2- If this is an ETL alike process, wouldn't you consider MERGE statement instead of insert/update on the same table?
MERGE is not available in SQL Server 2005 - it was introduced in SQL Server 2008.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 20, 2009 at 3:43 pm
Jeffrey Williams (7/20/2009)
I would handle it using transactions. Whether that was in a try/catch block or not would depend on what was being done.
That sounds about right. I'm thinking transactions within try/catch blocks. Wonder if the raise error command from the exception handling can be emailed. Something to look up for me.
Thanks,
S
--
:hehe:
July 20, 2009 at 3:45 pm
PaulB (7/20/2009)
Slick84 (7/20/2009)
Would the inserts go first or the updates?Two questions...
1- Are we talking about a small OLTP alike transaction OR are we talking about a large ETL alike transaction?
2- If this is an ETL alike process, wouldn't you consider MERGE statement instead of insert/update on the same table?
I was thinking small OLTP transactions. I think SSIS would be well suited for larger transactions. Not sure...
--
:hehe:
July 20, 2009 at 3:47 pm
Jeffrey Williams (7/20/2009)
PaulB (7/20/2009)
Slick84 (7/20/2009)
Would the inserts go first or the updates?Two questions...
1- Are we talking about a small OLTP alike transaction OR are we talking about a large ETL alike transaction?
2- If this is an ETL alike process, wouldn't you consider MERGE statement instead of insert/update on the same table?
MERGE is not available in SQL Server 2005 - it was introduced in SQL Server 2008.
Yeah sorry I forgot to mention. I'm mainly assuming SQL 2005 environment.
--
:hehe:
July 20, 2009 at 3:56 pm
Jeffrey Williams (7/20/2009)MERGE is not available in SQL Server 2005 - it was introduced in SQL Server 2008.
I stand corrected. Thank you Jeff.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 20, 2009 at 4:09 pm
The issue I have seen with SSIS and transaction is two different pipelines in the dataflow can block each other, they do not exist in the same locking space. This is a little tricky when you want to do inserts and updates in a single transaction with multiple pipelines. And don't get me wrong, I love SSIS, its just not real handy with this situation..
CEWII
July 20, 2009 at 6:44 pm
Elliott W (7/20/2009)
The issue I have seen with SSIS and transaction is two different pipelines in the dataflow can block each other, they do not exist in the same locking space. This is a little tricky when you want to do inserts and updates in a single transaction with multiple pipelines. And don't get me wrong, I love SSIS, its just not real handy with this situation..CEWII
Oh no, I wouldnt use SSIS for this...
I was just saying because Pablo 'Paul' mentioned what kind of transactions was I referring to. I said I was referring to smaller OLTP transactions which will not require the use of SSIS. But for larger ETL type stuff, SSIS would be used.
--
:hehe:
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply