SP's -- mixing sycnhronous and async? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP's -- mixing sycnhronous and async?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-10-01 : 15:09:32

I'm familiar with calling SP's from ADO with the adExecuteAsync option, but I've got a situation where I'd like to have my cake and eat it too. I'd like the call to be synchrnous and get a recordset back, but I'd like the stored procedure to return that recordset and then continue executing asynchronously.

Can anyone think of a way to accomplish that? I could have the SP stuff the data needed for the async part into a table and then have a scheduled task pick up from there, but that defeats part of the purpose -- that insert would affect response time.

Thanks
-b

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-01 : 15:15:51

Take the asynchronous tasks and put them into a separate procedure, then create a job that calls that procedure. You can then include sp_start_job 'myAsyncJob' in the regular procedure, just before or just after you return rows from that procedure. If you have to pass those rows to the job, then you'll just have to insert them...you can't create data instantaneously out of thin air.

I don't know of any other way of doing what you want, and mixing synchronous with asynchronous in the same procedure is a bad idea; the potential problems outweigh any advantages there might be.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-10-01 : 21:18:01

Well, that's kind of what I was afraid of. Thing is, my application is very high volume OLTP. The procedure in question serves two purposes: return data to the client, and log the client's request. I could it in two, but that would double the number of middle tier <-> DB round trips, which is something we're sensitive to.

I can definitely see how mixing sync and async willy-nilly could cause problems, but I don't see what the harm would be of having a "send all current results to client, consider SP done from clients perspective, but continue processing on server" command -- I can't think of a way it could compromise ACID.

Anyways, I definitely get the "queue the changes for later processing", but that sheer step of queueing would be just as bad as what I've got now. Maybe I'll play with splitting it up and executing the logging part async and see if the performace trade off of another round trip (or half of one) is worth it.

Thanks!
-b

SamC
White Water Yakist

3467 Posts

Posted - 2002-10-01 : 21:24:08

Here's a couple of examples on starting / creating jobs in SQL..
There are lots of nuances getting this to work.
Any comments / corrections appreciated.

Sam

CREATE PROCEDURE dbo.StartJob
/*************************************************************************************************
Purpose: Create and Start a background Job to run an SP

*************************************************************************************************/
AS
DECLARE @JOBNAME VARCHAR (200)
DECLARE @resultcode INT

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

SET @JOBNAME = ' Name this job for database: ' + DB_NAME() -- Only 1 job per Database

-- Start the job. It will run IMMEDIATELY, even though the schedule is NOT enabled
EXEC @resultcode = msdb.dbo.sp_start_job @job_name = @JOBNAME

IF @resultcode = 1 BEGIN -- Job not created - Create the job (happens only once)
EXEC @resultcode = dbo.CreateJob @job_name = @JOBNAME
EXEC @resultcode = msdb.dbo.sp_start_job @job_name = @JOBNAME
END

SET NOCOUNT OFF
SET ANSI_WARNINGS ON
RETURN(@resultcode)
GO
GRANT EXECUTE ON dbo.StartJob TO Public
GO

DROP PROCEDURE dbo.CreateJob
GO
CREATE PROCEDURE dbo.CreateJob
/*************************************************************************************************

Purpose: Create a background SQL Job to run an SP

*************************************************************************************************/
@Jobname VARCHAR (200)
AS
DECLARE @PROCNAME VARCHAR (200)
DECLARE @resultcode INT, @returncode INT
DECLARE @DBN varchar (200)

SET @DBN = DB_NAME() -- The DBN cannot be used as a function in one procedure below

-- Your asynchronous procedure name goes in the next line - or parameterize it
SET @PROCNAME = 'EXEC '+DB_NAME()+'.dbo.EmailAdminQueueExec' -- The procedure that does the Send Work

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET @returncode = 0 -- Follow nomenclature 0 being success

-- Step 1, Create a named job for this database
-- Print 'Step1'
EXEC @resultcode = msdb.dbo.sp_add_job @job_name = @JOBNAME, -- The JOB is NAMED here
@enabled = 1,
@category_name = 'Web Assistant' ,
@description = 'Describe your job here'

IF @resultcode = 1
SET @returncode = 101 -- StepN x 100 + error code
-- Step 2, Add a Job Step, to the named job pointing to the Email Procedure
-- Print 'Step2'
IF @resultcode = 0 -- Run next step only if no prior error
EXEC @resultcode = msdb.dbo.sp_add_jobstep @job_name = @JOBNAME, -- This is the only job step
@step_name = 'Step 1 ',
@subsystem = 'TSQL',
@command = @PROCNAME,
@database_name = @DBN

IF @resultcode = 1
SET @returncode = 201 -- StepN x 100 + error code
-- Step 3, The Job MUST refer to a specific server (incredible isn't it?)
-- Print 'Step3'
IF @resultcode = 0 -- Run next step only if no prior error
EXEC @resultcode = msdb.dbo.sp_add_jobserver @job_name = @JOBNAME -- DEFAULTS TO ( 'local' ) server

IF @resultcode = 1
SET @returncode = 301 -- StepN x 100 + error code
-- Step 4, The Job MUST have a schedule, even though we want it to execute NOW, and only once
-- To prevent unnecessary scheduling, this schedule is NOT ENABLED
-- Print 'Step4'
IF @resultcode = 0 -- Run next step only if no prior error
EXEC @resultcode = msdb.dbo.sp_add_jobschedule @job_name = @JOBNAME,
@name = 'Execute Immediately',
@freq_type = 4, -- daily
@enabled = 0,
@active_start_date = 19900101,
@active_start_time = 0,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959

IF @resultcode = 1
SET @returncode = 401 -- StepN x 100 + error code

SET NOCOUNT OFF
SET ANSI_WARNINGS ON
RETURN(@returncode)
GO
GRANT EXECUTE ON dbo.CreateJob TO Public
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-01 : 21:33:31

Well, how complicated is the "log the client's request" part? Unless you're making a copy of the data that's being sent to them, I can't see how that would require more than one call to an SP to log user name, date & time, and maybe the SQL they executed. That should execute in a couple of milliseconds and really shouldn't slow anything down. If you're inserting rows into a table you might get a hotspot but there's not much you can do about that.

Async processing isn't a problem for maintaing ACID properties, it's a question of how a thread executes. You can't get a single thread to work both sync and async, and you wouldn't want to even if you could. And IMHO async execution on SQL Server should NOT involve an actual SQL Server or data process. Sending an email or transmitting some TCP/IP traffic would be an example of acceptable async processing, but putting rows in a table wouldn't.

Another option that *might* help you out:

http://www.sqlteam.com/item.asp?ItemID=2290

The technique there allows SQL-DMO to open a separate connection to the SQL Server in order to log errors and still roll back the transaction. You could modify it to call your async routines without running a connection to your middle tier. A similar technique could be used with a custom COM component that runs on the SQL Server itself, but you'd have to make sure that it was written as lightweight as possible. Even then you MUST be diligent in closing and destroying objects properly and you'd need to monitor the server for a bit to make sure it works properly.

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-10-02 : 00:31:06

Thanks, SamC -- definitely looks like an interesting solution. I don't think it will help with this particular issue, but I can see other applications that I'm just itching to try.

Rob, the issue is indeed an insert with hotspots. I've tried to put the clustered index on a column with high variability, but it's still an issue. Not a *huge* issue -- average insert times are well under 20ms. However, when there are a couple of million inserts a day, about 5% of them take as much as 100ms, and .5% take 1s, and about .05% take 3s. .5% of two million is 10,000, and .05% is 1000. That's 1000 user transactions a day that have 3 second latency, which isn't acceptable.

Thanks for the link... that looks pretty promising to me. I'll play with it over the next few days.

I'm also looking at using MMQ to maybe do something about this, and as I said, it may make sense to break the selects and inserts into seperate procedurs and just run the inserts async from the middle tier.

I still don't really see what problems a "flush output to client and tell the client that processing is done, but continue executing code on the server" directive would have. Sure, you wouldn't be able to tell the client you had rolled back after that, but in my case, that would be fine. There would definitely be severe limitations, and you'd have to be aware of them, but within those limitations it seems like it would work fine.

Heck, I would rather outright *lose* a couple of thousand transactions a day than have high latency on those transactions; I'd settle for a "if you can't do it in 100ms, give up on the insert" directive.

(The transactions in question are not banking related

)

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources