April 26, 2012 at 8:21 am
Current situation: In SQL 2008 environment, proc1 is called by a single-threaded, third-party Windows application ("App"). proc1 has about ~2500 lines of code. I am in process of dividing the proc1 logic into parameter-specific procs (procA, procB, etc.) for manageability and performance.
Goal: Have proc1 call the secondary proc (such as, procA), but proc1 needs to complete without waiting for the secondary proc's completion. The App's architecture is creating a performance chokepoint. And while I cannot directly change that, if proc1's cycle time was faster, then life would be better.
Note: proc1 needs no return variables from the secondary proc; it does not care if the secondary proc fails or not.
--Create Secondary procs with WAITFOR to mimic processing time
CREATE PROCEDURE dbo.procA as
WAITFOR DELAY '00:00:15'
SELECT GETDATE() AS procATime
GO
CREATE PROCEDURE dbo.procB as
WAITFOR DELAY '00:00:15'
SELECT GETDATE() AS procBTime
GO
CREATE PROCEDURE dbo.procC as
WAITFOR DELAY '00:00:15'
SELECT GETDATE() AS procCTime
GO
--Create proc1, which calls the secondary procs
ALTER PROCEDURE dbo.proc1
(@input CHAR(1))
as
DECLARE @sqlvarchar(max),
@startdatetime,
@enddatetime
--Grab the start time
SELECT @start = GETDATE()
SELECT @sql=
CASE
WHEN @input = 'a' THEN 'procA'
WHEN @input = 'b' THEN 'procB'
WHEN @input = 'c' THEN 'procC'
END
--Now call secondary proc
EXEC (@sql)
--Grab the time the secondary proc returns control
SELECT @end = GETDATE()
SELECT @start AS Proc1StartTime, @end AS Proc1EndTime
GO
--Then execute proc1
EXECUTE dbo.proc1 'b'
GO
With the setup above, proc1 does not complete until the secondary proc is done. Can that be changed? Thanks.
April 26, 2012 at 8:26 am
No, it can't per se.. The calls are synchronous and there is no flag or option to change that. However, you could use Service Broker with an auto activated handler to create an asynchronous process. Its more indirect but it will accomplsh what you are asking.
CEWII
April 26, 2012 at 8:28 am
Nope. Proc1 will wait until procB returns to proc1. The calls are syncronous.
If you are looking at implementing asyncronous processing you should look into using Service Broker.
April 26, 2012 at 8:32 am
Elliott and Lynn,
Thanks, I will look into that. Are there any concerns about reliability with Service Broker?
April 26, 2012 at 8:35 am
None that I know of but you do need to be aware of Poison messages.
CEWII
April 26, 2012 at 8:36 am
cs2data (4/26/2012)
Elliott and Lynn,Thanks, I will look into that. Are there any concerns about reliability with Service Broker?
I haven't used it in an application, but I have used it as part of a mirror failover process and I had no issues with it.
It was designed to be used for asyncronous processing.
April 26, 2012 at 9:07 am
you can always use the sp_oa proc calls to asyncronously execute stored procs
that way you don't have to use service broker
here is some sample code i provided to some of our test engineers a while ago so that they could "stress" our test server
I'm sure you'll be able to cannibalise it
The first thing you need to do is to ensure that your SQL server (2000,2005,2008 or R2) can support OLE Automation. After you have installed and configured your SQL Server you need to open Management studio and run the following Script
Use Master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Ole Automation Procedures',1
go
reconfigure with override
go
you should then see the following results
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ole Automation Procedures' changed from 1 to 1. Run the RECONFIGURE statement to install.
Next we need a database to call some procedures from (and some procedures) so I’ll create a dummy procedure (for the purposes of this demonstration only). Run the script below and you should have a new database, procedure and table that we can use
USE master
GO
--CREATE THE DATABASE
CREATE DATABASE InjectorTest
GO
USE Injectortest
GO
--CREATE A TABLE TO STORE SOME DATA
CREATE Table execlog(id int identity(1,1) PRIMARY KEY,msg varchar(100),dt datetime)
GO
--CREATE A PROCEDURE THAT INSERTS SOME DATA, WAITS 10 SECONDS AND THEN INSERTS SOME MORE DATA
CREATE proc usp_delay @call int as
insert into execlog(msg,dt) select 'starting call '+convert(varchar(10),@call),getdate()
waitfor delay '00:00:10'
insert into execlog(msg,dt) select 'ending call '+convert(varchar(10),@call),getdate()
GO
To test the procedure we have just created you can execute the following script – each call of the procedure will write a single record to the table, wait 10 seconds and the write to the table again. With 2 calls to the procedure (different parameters each time) we will be expecting the script to take 20 seconds
truncate table execlog
exec usp_delay 1
exec usp_delay 2
select * from execlog
from the results you can see it’s all as expected – 10 seconds for the first call and 10 seconds for the 2nd call – total 20 seconds
id msg dt
1 starting call 1 2011-04-28 14:29:28.300
2 ending call 1 2011-04-28 14:29:38.317
3 starting call 2 2011-04-28 14:29:38.317
4 ending call 2 2011-04-28 14:29:48.363
The next thing to do is create a queue so that we can send items into the Injector
CREATE TABLE injectorqueue (id int identity(1,1) primary key, iText varchar(1000) )
Then lets insert our 5 items we want to run in parallel – you’ll notice that these are simple command commands you can run from the command prompt . if you haven’t used SQLCMD before then it is a simple DOS command line utility to call SQL server code. – here we are adding into the queue our 5 items to execute (3 calls to our procedure and 2 copy commands)
insert into injectorqueue(iText) select 'SQLCMD -S N998J5L1\SQLEXPRESS -E -d Injectortest -Q"Exec usp_delay 1"'
insert into injectorqueue(iText) select 'copy "c:\myfile1.txt" "\\anotherserver\c$\myfile1.txt" '
insert into injectorqueue(iText) select 'SQLCMD -S N998J5L1\SQLEXPRESS -E -d Injectortest -Q"Exec usp_delay 2"'
insert into injectorqueue(iText) select 'SQLCMD -S N998J5L1\SQLEXPRESS -E -d Injectortest -Q"Exec usp_delay 3"'
insert into injectorqueue(iText) select 'copy "c:\myfile2.txt" "\\anotherserver\c$\myfile2.txt"'
Now we have everything prepared it’s time to launch our processes –the script below will read from the queue and throw them at your server as fast as is possible
--clear the execution log table so we can see the new results more clearly
truncate table execlog
--declare variables for use in the script
declare @hr int
declare @obj int
declare @cmd varchar(1000)
declare @src varchar(255)
declare @desc varchar(255)
--create the handle to windows scripting
exec @hr=sp_oacreate 'wscript.shell',@obj OUT,5
if @hr<>0
begin
print @hr
exec sp_oageterrorinfo @obj,@src out,@desc out
print @src
print @desc
end
--load the data from our queue
declare curs1 cursor for select iText from injectorqueue order by id asc
open curs1
fetch next from curs1 into @cmd
while @@FETCH_STATUS=0
begin
--inside the loop throw data into the injector without waiting for any response (using the RUN method of windows scripting)
exec @hr=sp_oamethod @obj,'RUN',NULL,@CMD
if @hr<>0
begin
print @hr
exec sp_oageterrorinfo @obj,@src out,@desc out
print @src
print @desc
end
fetch next from curs1 into @cmd
end
--close up and deallocate resources
close curs1
deallocate curs1
--destroy the handle to the windows scripting object
exec @hr=sp_oadestroy @obj
if @hr<>0
begin
print @hr
exec sp_oageterrorinfo @obj,@src out,@desc out
print @src
print @desc
end
MVDBA
April 26, 2012 at 9:32 am
Seems like a lot of work to setup OA Automation when SQL Server and Service Broker already have a lot of the plumbing already in place. I guess it is just personal preference here, but I rather keep things within SQL Server where I can and Service Broker allows that. No need to call extended stored procedures and such.
April 26, 2012 at 9:44 am
I agree that Service Broker would allow you to call procs asynchronously from within a proc. However, personally, if I would have the same needs, I would do it from within managed code (.net).
Managing of multiple threads will be better done within client application.
April 26, 2012 at 10:54 am
Eugene Elutin (4/26/2012)
I agree that Service Broker would allow you to call procs asynchronously from within a proc. However, personally, if I would have the same needs, I would do it from within managed code (.net).Managing of multiple threads will be better done within client application.
It also depends on what you are attempting to accomplish as well. Service Broker can also be used to implement asyncronous triggers. Asyncronous processing between databases (or segments of a database) handled outside of the processing of the application, though triggered by application updates (insert/update/delete).
It is simply another tool to be used in development.
April 26, 2012 at 10:57 am
Another possibility is to use batch processing, such as SQL Agent or Control-M. Jobs can execute stored procedures asynchronously if needed.
April 26, 2012 at 11:03 am
Steve Cullen (4/26/2012)
Another possibility is to use batch processing, such as SQL Agent or Control-M. Jobs can execute stored procedures asynchronously if needed.
Actually, I think that is backwards. Procedures inside a Job will run syncronously. A stored procedure could start a job that will run asyncronously from the stored procedure. Only problem with this is if a subsequent run of the same stored procedure can't start another asyncronous run of the same job if it is still running.
April 26, 2012 at 11:30 am
+1 for Service Broker (SB)...the use-case described might be the most common of all. And no, no concerns about reliability, Microsoft uses SB within the SQL Server Database Mail subsystem, a dog-fooding if you will, that gives it instant credibility.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2012 at 1:19 pm
I can't under ANY circumstances endorse an sp_OA method to doing this. As a matter of fact I'm of the opinion that those should not be used anymore under any circumstances.. I strongly recommend going SQLCLR for most things you wold typically use sp_OA methods for..
I also agree with Lynn, thats a LOT of plumbing to accomplish the same thing with Service Broker, which has some required plumbing of its own.
CEWII
April 26, 2012 at 1:52 pm
Thank you for all the comments posted. I'm trying the SB path. I'll report back my experience.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply