How to submit stored proc to run in background

I have an ASP application which would create a series of sql statements based on user input. Then when user clicks 'execute', asp page would submit query and web page would freeze until results returned (dealing w/very large databases..some queries run 5-10 minutes..). Not good as could even get timeout error (another topic...was due to some isp's cutting off connections longer than 2 minutes)

My developer decided to take user input, create a stored proc from it, and when the user clicked submit, the plan was to insert the name of the stored proce into a table and had a trigger on this table which would immediately submit the stored proc. However, this still freezes up until results of stored proc are returned...???

I vaguely recall in Unix that a user could submit a job via command line and via a switch (something like a /p) the job ran in the background and user could submit other commands. Without the swith,the user would have to wait until job done.

IS there anything like this in Sql server? Has anyone else not had this problem?

thank you very much

asynchronous processing is not supported by SQLServer itself, but you can use Message Queueing or SQL Jobs to achieve this. Presumably the user does not care about the results popping back on their screen?

If so, then rather than using a trigger to start the proc, write a SQL job which polls the table periodically

Cheers
Twan

Oh - I'm not worried abou the 5-10 minutes at all. I'll have to look into Message Queing. Looking for a way to submit a sql server query from a web page so that the query runs in the background and control of the web page is returned to the user..

quote:Originally posted by satya

You need to assess the execution plan for the stored procedure used and also indexes for the search arguments mentioned.

How about h/w resources and database size and SQL settings?

Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

A SQL job is likely to be a simpler option, if you're not worried about showing the results back to the user

Cheers
Twan

Twan is right. SQL Job is simple. You can also print
informational or error msg to Application Log in
your Windows Event View.

Well, a couple things
Now-when user points/clicks and clicks 'execute', screen freezes up.
Tried having a database trigger such that when user clicks execute, query info inserted into table triggering the stored proc to run. However, this too 'freezes' until results come back.

If we do the sql job, if I'm understadning what you're saying, is that when user clicks 'execute', we could just have program insert a value in a table, and have a SQL job which 'polls' the table looking for that value. If it finds one that says 'execute',it'll submit the job...this would run the job but the screen would be free for the user, and we could have code to make sure if the user clicked 'execute' 2 or 3 times it woudln't keep submitting the monster query(ies).

Concerns:
1)W/ the polling...I think it can only check like every minute. That's not ideal since some queries can come back in 20 seconds or less so a shame to wait a whole minute before kicking off.
2)W/ a job constantly running to scan thru a table every minute (or every few seconds) won't that eat up a lot of resources slowing things down?

ps ideally the counts would refresh on the screen when results done. (let me clarify -the results of the query is always a list of customer id's in a table. When the query is done, a few counts are run on the resulting table and this is what is popped into window users see where they are kicking off the request)

w/the query submitted directly from teh asp web page, the counts show up when query is done.

If we submit via the stored proc which is kicked off by a database insert trigger, nothing shows up until user refreshes...this is still kind of kludgy...

not sure about 1), but there must be a way.

2) as the last step of the job that returns the results, have it remove the value from the table, or keep it trimmed once a day or so. Then you wont have much of a table to scan through.

you could use
a) an infinite loop and the WAITFOR command inside the SQL job (or proc called by the SQL job) this will mean that the only way to stop the job is to cancel the job in EM
b) write a trigger on the table which starts the job if it is not already running

It sounds like what you're doing is what MS used to call Dashboard, whose aim was to be able to push information back out to a browser. I think that this is achieved using queued components (which is COM over MSMQ)

The normal architecture for a browser application is a pull architecture, so it is quite a difference to introduce a push scenario where results are pushed out to the client (and refreshing the browser periodically can add a significant load to the web/database servers)

Cheers
Twan

I'll check on a)

b)trigger on the table which starts the job is what we've got now. Our expectation was the trigger would kick off the job and return to the page...however, the trigger doesn't return control until after the query is done which defeats the purpose of wanting control to return to the page..

Will check out the other

I have never done this myself but I'm planning to and I think it's possible: You create a job using TSQL that deletes itself after it has been run, and you schedule it to run say 1 second after it has been created. This can be acomplished using the system stored procedures sp_add_job, sp_add_jobstep and sp_add_jobschedule. Look it up in BOL to see how it's done...

--
Frettmaestro
"Real programmers don't document, if it was hard to write it should be hard to understand"

What I meant by job is to create an SQL job which polls the table, and then in the trigger using sp_start_job to kick it off. This sp will return as soon as the job has been started and won't wait till the job has finished.

Cheers
Twan

Agree with Twan on this.....

Also the screen is hanging when triggers are used because even if the operation is being done in the trigger, still trigger execution is necessary for the insert statement to complete execution. Having sp_start_job in the trigger as suggested by Twan would solve the problem.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Almost there!! Getting closer...two things
1)is there a way to pass parameters to a job?

2)getting some weird errors re context switching...will dig in more to see what'up...it appears to happen when the same user tried to kick off another job (ie another sp_start_job) the sp_start_job doesn't start(until the first job finished)... does this make sense?

You could save the parameters in a table, which the job reads...?

the job could loop around to process everything it can.

perhaps create a table with
id column identity
parameter columns

clustered index on id

a procedure written using the following type of psuedo code


select top 1
@id = id,
@variables = parameter columns
from table
order by id

set @rowcount = @@rowcount, @rc = 0

while @rowcount = 1 and @rc = 0
begin

exec @rc = proc_process_this_row @id, @variables

if @rc = 0
begin
delete from table where id = @id

select top 1
@id = id,
@variables = parameter columns
from table
order by id

set @rowcount = @@rowcount
end

end

The proc_process_this_row should then return 0 if no error has occurred, although if an erro has occurred, you may want to decide whether to flag this as a problem, and mark the row to try to avoid an infinite loop, etc. but this should give you the general idea...?

Cheers
Twan

I think I've got it! Sorry for being dense, but thank you all so much. Really saved me!! I'm writing here so I don't waste anyone's time getting the above answered. If curious, here was the scoop:

1)re passing parameters - duh,freetmaestro above gave me the info when he said try using sp_add_job,sp_add_jobstep. In the latter, you can indicate to add a jobstep that will consiste of running a tsql statemeent and then you pass the actual command..in this case, I'd pass 'exec mystoredproc....' and pass parameters there that hte job would in turn use when calling stored proc

2)initially I set up one standard job and called it w/sp_start_job. You can't call the same job twice though...at least you can't run the same job at the same time. Our solution is to create a unique job whenever user hits execute using the add_job,add_jobstep w/required params. We just have to monkey around a bit to have the screen refresh so counts get pushed back to asp page and that the job gets deleted after it's done...

Briant: when you create the job using sp_add_job you need to set the parameter @delete_level to 3. This means that the job will delete itself and all it's history after running *once*. It would be something like this:
EXEC sp_add_job @job_name = 'MyJob',
@enabled = 1,
@description = '',
@owner_login_name = 'sa',
@delete_level = 3
This will delete the job automatically when it has been run...

--
Frettmaestro
"Real programmers don't document, if it was hard to write it should be hard to understand"

I think shoul be the last post..just wanted to reply to Twan
The code you submitted above is what we tried to do but found couldn't execute stored proc again
until 1st one was done. Since we have 10 users each potentially submitting more than one request that would be a problem.
The addjob,addjobstep,startjob seems to be the answer. In final testing now w/this
thx again to all

oh well,<br /><br />glad you have a solution <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan