August 12, 2011 at 1:10 pm
Hi All,
I have a doubt...
create procedure innersp
(
update mytable
set updateddt = getdate()
-- mytale has 200k rows
-- let's say will take 2 mins too exeute
)
create prodcudre mainsp
(
exec innersp
select getdate() as 'result'
)
Do I need to wait all the way until the inside sp gets finished.
Can I get mainsp result fastly, as not holding to wait until the innersp done???
I really appreciate that.
August 12, 2011 at 1:13 pm
kumar1 (8/12/2011)
Hi All,Do I need to wait all the way until the inside sp gets finished.
Can I get mainsp result fastly, as not holding to wait until the innersp done???
I really appreciate that.
Yep, the execution of the inner sp will have to finish before the main one will continue.
August 12, 2011 at 1:33 pm
You use sp_startjob to start something async
August 12, 2011 at 1:41 pm
Ninja's_RGR'us (8/12/2011)
You use sp_startjob to start something async
or SQL Service Broker as an alternative. Then you don't even have to wait until the job finished. It all depends on the requirements though...
August 12, 2011 at 1:53 pm
Some time ago I wrote a process that was spawn asynchronous Agent jobs to process a range of data. I don't remember the exact details of the work to be done but it went something like this.
I had to run through 500m rows of data. Processing that volume of data was taking somewhere around 10-12 hours to run. So I broke it down into 10 jobs to process 50m per job. I'd get the work done in a fraction of time. I'd beat the crap out of the disk drives and let's just say there wasn't much else that could be done when these jobs kicked off, but it got the overall job done.
I took the idea from the days when I worked on a DEC VAX where it was easy to spawn off jobs to run along side the primary job.
That impressed the boss, big time!
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 12, 2011 at 2:38 pm
@Ninja's_RGR'us: Thanks very much for this solution. I am working closer with this solution.
@Kurt W. Zimmerman: You are obsoltely right.I have done similarly before but used waitfor delay option.
Thank you very much though.
@LutzM : Can you please explain me this, the process which needs we follow??
August 12, 2011 at 3:06 pm
kumar1 (8/12/2011)
@Ninja's_RGR'us: Thanks very much for this solution. I am working closer with this solution.@Kurt W. Zimmerman: You are obsoltely right.I have done similarly before but used waitfor delay option.
Thank you very much though.
@LutzM : Can you please explain me this, the process which needs we follow??
That reminds me of another job that I did. I had a process on one production server that would pull certain metric server data from a variety of machines. I had a separate server that would poll and wait until all of the data was there. I'd wait for about 1 hour in 2 min increments. The process waited until the last source of info was available. Then I'd move the data over via the link server.
The job was designed so that if I ran out of time I'd fire off a email to tell me the job failed. Maybe not the best thing in the world but it worked.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 12, 2011 at 3:34 pm
kumar1 (8/12/2011)
@Ninja's_RGR'us: Thanks very much for this solution. I am working closer with this solution.@Kurt W. Zimmerman: You are obsoltely right.I have done similarly before but used waitfor delay option.
Thank you very much though.
@LutzM : Can you please explain me this, the process which needs we follow??
Service Broker is a SQL Server functionality introduced with SS2K5 (IIRC) and
... provides queuing and reliable messaging for SQL Server. ...
Database applications typically use asynchronous programming to shorten interactive response time and increase overall application throughput.
(copied from BOL, also a good resource to get a better understanding how it works in general).
Here are a few intersting links:
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply