CODEWITHSUNDEEP
sqlsql-servert-sql
csteifel
csteifel

Reputation: 2934

T-SQL return results and continue processing

I have a stored procedure that I am hoping to speed up by returning the data early from the stored procedure and then allowing it to continue processing afterwards.

Basically right now I have something like so:

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
    @someargshere
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE lastAccessedTime = GETUTCDATE() WHERE based on someargs;
    SELECT some fields WHERE based on someargs;
END

I don't actually care about what lastAccessedTime is for the SELECT statement but the only way I have found to return a result set of rows is to do the SELECT at the end, because all the docs I've found have said RETURN only takes an integer expression. I would like to return results early so that I get the absolute fastest response time possible to my application so that it can continue processing while still having the server do the update. So I would like to do something like

SELECTT.....
RETURN results to app
UPDATE after connection has sent results

Is that possible or is there a better approach to this?

Upvotes: 2

Views: 512

Answers (1)

Rahul
Rahul

Reputation: 77866

NO, don't think you can do that cause it's a sequential processing. Moreover even you are able to do that then you will end up reading the old data and not the updated data actually; which is also known as DIRTY READ.

What you posted in first part is the right thing to do. Perform the UPDATE first and then SELECT the data and return to application. Your procedure will return only when the entire processing is over and not partially likewise you are saying (Or likewise happens in a multithreaded programming language).

Though you can separate the SELECT and UPDATE in different procedure / adhoc query and based on your application programming language (if it's support multi threading); have two thread running where one will SELECT from table and another will perform UPDATE (if C# then you can use TPL; specifically async - await). Though you will have to set the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED probably in that case.

Upvotes: 6

Related Questions