SQL Server Performance Forum – Threads Archive
Triggering a Stored Procedure
Hi All! I have a situation where I have a page that is filled with data from my database using a stored procedure. Once the page is generated, it is cached in an html file until the data goes stale. When the data goes stale, the next user to hit that page cause the stored procedure to be called which updates the data first and then returns it causing the old html file to be deleted and regenerated with the new data. The file manipulation is done using ASP. Unfortunately, the stored procedure can take a bit of time, so the unlucky user to be the first to hit that page once the data goes stale is stuck waiting for the stored proc to complete. What I’d like to do instead is this:Once the data goes stale, the next user to hit the page will get served the old html file but will kick off the stored procedure that will run in the background to generate the data. It will also set a new state flag saying that the data has been update and then the next user to hit the page after that will just have to wait for the new html file to generated and served and not the processing and updating of the data. So basically, is there a way to kick off the stored procedure (on a different thread?) so that the user who hit the page to kick it off doesn’t have to wait for it to complete as part of the ASP page execution? Any help is greatly appreciated! Thanks in advance,
Larry
You’d have to do this in the asp side somehow… I can’t think of an easy way to spawn a new thread or do something asynch, perhaps MSMQ might help you…? Cheers
Twan
Piggy-backing on Twan, thishttp://www.tagconsulting.com/SQLandMSMQ.asp shows how can might be done from within SQL Server. However, as it utilizes the sp_OA* procedures, you should consider the security impact there. Not sure what your actual requirements are, but I also think this is better done on the client side.
–Frank
http://www.insidesql.de
Consider having a scheduled job which invokes the stored procedure.
The proc could then output the data to a temporary filename, and as the last thing it does, it moves the new file over the top of the stale one. This way the user does not need to wait for your server to generate the new file. The proc could check a flag in the database to see whether or not to run if needs be.
Good ideas. I’m a bit unfamiliar with MSMQ. What’s it typically used for? Regarding the job idea, how do I pass in paramaters? My stored procedure needs some parameters passed in. Thanks!
Larry
i’m not sure about your general requirements as it sound like you want to preparing this sounds like a data warehousing type of problem but… if you want to run the sp in a seperate from the client the easiest fix would be to wrap up the call in a simple vb component and use ado to access the db asynchronously. alternatively i would just create a request table with the fields required by the sp and run a scheduled task every minute to look for requests. this is a fairly standard model.
Even if I make this a VB component, the ASP page that calls the component will still have to wait for the call to return before the page can be fully loaded. This will still make the user have to wait for the page to load, won’t it?
]]>