series of transactions

Question

series of transactions

salomon.frid

SSC Rookie

Points: 26
More actions
July 31, 2014 at 2:24 pm

#310420

I need to run a series of transactions on a table which i split into multiple groups of records, and then process one group at the time; This is due to locking and performance considerations.
I also want to pause between groups , allowing then other tasks to do their work ;
I have heard that wait function is not the prefer way of doing it; Any suggestions?
Salomon
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

If you want to wait, that's WAITFOR with either delay or time.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

salomon.frid SSC Rookie Points: 26 More actions · Answer 2

Thank you Gail

I am aware of that one, just some people in my shop have told me that is not the preferred way of doing this ...

😉

Any alternatives?

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 3

What is their reason for not using WAITFOR?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

salomon.frid (7/31/2014)
Thank you Gail
I am aware of that one, just some people in my shop have told me that is not the preferred way of doing this ...
😉
Any alternatives?

You could ask the local "experts" that told you that is not the preferred way what the preferred method is. I would be curious to hear what they say.

salomon.frid SSC Rookie Points: 26 More actions · Answer 5

Truth be told, is part of what I am trying to find out as well 🙂

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 6

salomon.frid (7/31/2014)
Truth be told, is part of what I am trying to find out as well 🙂

I would put the onus on them to say why it isn't preferred and use it until they can prove otherwise.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

salomon.frid (7/31/2014)
I am aware of that one, just some people in my shop have told me that is not the preferred way of doing this ...

So according to your local experts, WAITFOR is not the best way to wait for a period of time...

And their reason is?

Do they recommend against using kettles to boil water too?

Any alternatives?

Well, I suppose you could put a WHILE loop 1 to a few million with a select statement inside. That'll certainly cause a delay....

Or write the whole thing in C# and use it's sleep function. Or call out directly to the Windows API and it's thread suspend (but that could have some horrible side effects)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

salomon.frid (7/31/2014)
Truth be told, is part of what I am trying to find out as well 🙂

Go and ask them. They're the ones making the claim, the burden of proof is on them

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 9

GilaMonster (7/31/2014)
salomon.frid (7/31/2014)
I am aware of that one, just some people in my shop have told me that is not the preferred way of doing this ...
So according to your local experts, WAITFOR is not the best way to wait for a period of time...
And their reason is?
Do they recommend against using kettles to boil water too?
Any alternatives?
Well, I suppose you could put a WHILE loop 1 to a few million with a select statement inside. That'll certainly cause a delay....
Or write the whole thing in C# and use it's sleep function. Or call out directly to the Windows API and it's thread suspend (but that could have some horrible side effects)

I like the last one, must remember to arrange the offsite backups before I try it

😎