Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Measure Transaction size


Measure Transaction size

Author
Message
karthik M
karthik M
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2045 Visits: 2582
All,

I have two queries like below.

declare cursor c1...

open c1

fetch c1 into ...

while @@sql_status ..

begin

BEGIN TRAN

insert t1
insert t2
insert t3
update t4

COMMIT

fetch c1 into ...

END

close ...
deallocate

The same query is used and the location of BEGIN TRAN is
changed now.

#2)

BEGIN TRAN

declare c1 cursor...

open c1

fetch c1 into ...

while @@sql.status ..

begin


insert t1
insert t2
insert t3
update t4


fetch c1 into ...

END

close ...
deallocate

COMMIT


which one is good to have? how to measure the usage of
tranaction size for the second one? which one is good if i
have
2000000 records processed within the cursor ?

Note: Cursor is not the right one to process 2000000 records. But the requirement is like that.

karthik
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
It depends.

If the insert and updates are not inter-related then I would suggest that you put them into different transactions as this would lead to holding of Transaction Locks for lesser amount of time at one continuous stretch.

If the inserts are inter-related or not inter-related, but some other transactions are supposed to read data from the tables on which the inserts and updates are taking place, then it depends on what Isolation Level you want to implement. From the sample code that you have provided.....I see that you are working on four different tables. You are performing Inserts into three and Updating one. IMHO, you should use different Transactions for all the inserts and updates as it would lead to holding of Locks for a lesser time.

If you use the approach that you have posted then the transaction would hold locks till the entire transaction is completed and that would be really costly.

Following links would be useful:

Transaction & Locking Architecture

Locking in the Database Engine

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17573 Visits: 32257
There's almost no difference between the two. Both are likely to cause HUGE issues. Updating that much data using a cursor AND through individual INSERT & UPDATE statements is going to be a giant transaction. You'll need to have a very large log file. You'll also probably need a much larger tempdb. Plus, you're going to have a very large number of locks inside your database leading to massive blocking of other processes.

In general, this is a very poor choice. The requirements may say this, but this is where you, as the technical resource, push back against the requirements in order to arrive a technically supportable solution.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
vinu512
vinu512
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1149 Visits: 1618
Karthik could you brief us upon the requirement a bit??...It won't be that much of an effort......Plus you might get opinions that might make this requirement easier for you cause in any given case this approach is going to kill your performance completely. ;-)

PS: Don't forget to set up some sample data and show us a working example. :-D

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden ;-)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3939 Visits: 6671
I suggest a hybrid method.

Do something like the first method except that you begin new transactions only at certain record counts, for example, every 2000 values processed:

record #1; record #2001; record #4001; etc.

You can adjust the number up or down as needed to reduce locking.

Every one in a separate transaction is huge overhead in creating and committing trans.

All in one transaction is huge overhead if it has to rollback after doing a lot of inserts/updates.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search