February 20, 2002 at 11:53 am
I have to create a report from an ASP file that will take at least 5 minutes. Can't have the person sit there looking at a blank white browser window, so I thought I could create a Queue for the report. Here is what I did...
1. Put all search elements in a SQL table,
2. Added a trigger on Insert that uses xp_cmdshell to call a VBS file,
3. The VBS file runs the report.
Problem is the Trigger hangs waiting for the VBS script to complete.
I would really like the trigger to call the VBS file and return, letting the VBS go on it's own.
Am I doing the right thing in this situation, is there a better way, or do I simply need to add something to make it not hang and wait?
Thanks in advance.
February 20, 2002 at 1:14 pm
I wouldn't do this in a trigger. Instead, use the trigger or the original code to insert a value into some process table. Then have something that runs every minute or so and runs the VBS file if it finds something to do in the process table.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 20, 2002 at 7:48 pm
quote:
Problem is the Trigger hangs waiting for the VBS script to complete.I would really like the trigger to call the VBS file and return, letting the VBS go on it's own.
To add on to what Steve is saying, the trigger is going to wait for all its commands to execute successfully, because remember that a trigger is really not much more than a specialized stored procedure. This will cause the original query results not to return until everything is completed. So what you are seeing is the proper behavior for the trigger. This will hang up a response back to the client. That's why there is such a strong aversion to triggers by quite a few DBAs. Long running triggers can hammer performance and cause disgruntled users just as long running stored procedures and ad hoc queries can.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 20, 2002 at 8:00 pm
Thanks for the info, that is very helpful.
Does anyone have a solution to the problem I am having?
February 20, 2002 at 8:06 pm
How about trying to reduce the 5 min?
What about publishing the query and tables / indexes on this thread. How many bytes in the result set. Sounds like an interesting problem.
February 20, 2002 at 8:10 pm
It probably won't be 5 minutes, it will most likely be more than that.
We are searching Billions of records. We have to search one database, grab certain information, then use that information to grab more information.
So, trying to chip away at the 5 minutes is probably not going to work.
Indexes have been created, the individual results come back VERY quickly, it is just createing the 30 plage report that will take some time.
That is the whole idea behind creating the queue system.
February 20, 2002 at 8:16 pm
Your best bet is to implement a process like what Steve has suggested. Use a trigger or a stored procedure to insert a record into a table indicating that a report needs to be generated. Return back to the client that the report has been queued if the insert is successful.
Have a job scheduled in SQL Agent to run every minute, 5 minutes, whatever you plan the interval to be , that queries the table and builds the reports. If you need to notify, the job could use xp_sendmail to fire off an email when the report is built. The table represents your queue and the job in SQL Agent is what checks the queue and executes accordingly.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 20, 2002 at 8:24 pm
I agree - insert a record into your queue, then either poll the table until status shows complete or just tell the user that they will get an email (with either link or attachment) when done. Leon has a system like this in place at work, does well.
There are definitely times that no matter what you do, it will take more than 10-15 seconds to produce a report. An added benefit of a queue is that it serializes the process, limiting the load placed on the reporting server. Once you have the concept of a queue its a logical extension to build a process to push reports into it for off-peak processing as well.
Andy
February 20, 2002 at 8:38 pm
Here I go, wish me luck!
February 21, 2002 at 4:51 am
Another method you could use to save having a job run unnecessarily is to create a job to run your script but disable it. Then in your trigger you can do the following.
EXEC msdb.dbo.sp_update_job @job_name = 'YourJobNameHere', @enabled = 1
Now when I set up the job it is scheduled to run every 1 minute, so it will run within 1 minute of the trigger enabling. Then it performs it's first task and moves along to a final task of
EXEC msdb.dbo.sp_update_job @job_name = 'YourJobNameHere', @enabled = 0
which disables it until the trigger fires again re-enabling it.
This way the job only runs when there is something to do.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply