separate threads? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

separate threads?

I’m thinking through a problem , and have come up with 2 potential options:
Firstly , the problem is: a .Net application is executing some stored procedures.
One of these stored procedures takes about 3 minutes to run . Whilst this is going on the application
waits for a response from the sql server to say the stored procedure has completed
What I would like to get to is just trigger the stored procedure from the application, close the connection,
BUT still allowing the stored procedure to run in the background.An return later to pick up the results .
The 2 potential options are :
option 1 = the App updates a column in MyTable , which triggers the stored procedure in it#%92s own thread.Is this possible?
Option 2 = Hit a covering sp which triggers the stored procedure ,which run in it’s own session Or is it more efficient to have multiple threads running in the clieet application?
When a client triggers a stored procedure , will it only run if the connection remains open ? ___________________________________
Need an IT job? –http://www.ITjobfeed.com
you can’t just close the connection and then go and reopen it and get the results [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />your best bet would be to use SqlCommand.Begin* and SqlCommand.End* methods that do stuff in async way by default.<br /><br />_______________________________________________<br />Causing trouble since 1980<br />blog:<a target="_blank" href=http://weblogs.sqlteam.com/mladenp>http://weblogs.sqlteam.com/mladenp</a>
You can setup ADO to execute asynchronously, so it doesn’t wait for the execution to end before continuing with the rest of the code. Later on in your procedure, you check the status of the ADO connection to see if execution has ended, or perhaps there is an error … whatever you need to do.
Spirit1 and Adriaan , thanks for the responses, except that implementing a asynchronous as suggested still requires one socket connection with database busy until I get response to callback function.
How is it possible to trigger the asynchronous command and then close the connection , allowing SQL Server to keep processing the stored procedure? ___________________________________
Need an IT job? –http://www.ITjobfeed.com
Upload your parameters to a holding table. Schedule a job that executes the procedure with the parameters. Or have it check every X seconds to see if new parameters were added to the holding table.
Jack Vamvas:
this is a very unusuall requirement. do you mind explaining why do you think this is neccesary? _______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp
There is another option, to resolve this. With SQL Server you can use Query Notifications.
Please check the follwing article from microsofthttp://msdn2.microsoft.com/en-us/library/ms175110.aspx
Maybe using a combination of Asyn ADO Calls and Query Notifications you can solve your problem Regards
Any chance can you take help of session state management from .net in this case or have I misunderstood the requirement.[<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Jack Vamvas</i><br /><br />Spirit1 and Adriaan , thanks for the responses, except that implementing a asynchronous as suggested still requires one socket connection with database busy until I get response to callback function. <br />How is it possible to trigger the asynchronous command and then close the connection , allowing SQL Server to keep processing the stored procedure?<br /><br />___________________________________<br />Need an IT job? -<a target="_blank" href=http://www.ITjobfeed.com>http://www.ITjobfeed.com</a><br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br />@<a target="_blank" href=http://www.askasqlguru.com/>http://www.askasqlguru.com/</a><br /><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>
well since we’re going all out… you can use service broker for completly async operations in sql server.
acctualy this might be exactly what you need. _______________________________________________
Causing trouble since 1980
blog:http://weblogs.sqlteam.com/mladenp
]]>