Trigger Question
-
Hi Experts!
I have a question about triggers.
I have a stored procedure that I call from an ASP page that can take a longer amount of time than would be good to keep a user waiting for the page to load. I was thinking to change the call to the stored procedure to a simple update of a single column in a single table row that would fire a trigger that calls the stored procedure in order reduce the time the the user waits for the page to load. This would only work if one assumption that I'm making is correct. That assumption is that events will occur in the following order:
1. Update query called from ASP page
2. Trigger fires, check updated column. If correct column is updated, stored proc is called.
3. Update query completes and ASP page fully loads.
4. Some time later, the stored proc completes
Is this how things work with a trigger or will the update query that fired the trigger not complete until the trigger fully executes? If it is the latter, than this will gain no advantage for the users experience of having a faster loading ASP page.
If I can't use a trigger to do what I want, does anyone have a suggestion regarding what I can do to get a stored proc to trigger from an ASP page and run in the background so the page can fully load without the stored proc completing? The ASP page is not dependent on the results of the stored proc call, so this is OK.
Any help would be greatly appreciated.
Thanks!
-
Basically the trigger action has to complete before the asp finishes because those actions are part of the transaction. If you need to delay that, you can flag a field in a table, then run a job every minute or two checking to see if that flag is set. If it is run the proc to finish the task.
-
Well, the way it works, is that the stored proc operates on a subset of records that pertain to a group of users. When a user in the group hits that page when the flag is set, the stored procedure is called with a group ID parameter to operate on the records that are pertinent to that group. The flag gets set for ALL groups at once but the stored procedure only gets called on demand by a user going to the ASP page. This helps break up the processing.
Using a job like you describe, the job would find ALL flags set and call the stored procedure for ALL groups which will take a long time to run. Shortening the processing time and strain on system resources was the purpose for breaking up the transaction in the first place.
Things work pretty well as is except for a user in a large group who hits the page when the flag is set. The stored proc for that user could take up to 20 seconds which is quite long when waiting for a page to load.
How about creating a unique job that calls the stored proc and then deletes itself. I could then schedule the job to run immediately. Would this work and run in the background? I'm not sure exactly how to do this though.
Thanks in advance!
-
You can always execute the job right away. But I'm not sure that this is called as an external process... but that would be pretty easy for you to test.
-
I confirm that it runs in another process (checked using a waitfor delay in a job that I fired from an alert).
-
Yeah, I got this to work too! Now, I'm concerned that this is improper use of the Job facility of SQL Server. I could have a couple hundred of these jobs created in a short amount of time. Is this OK to do? Will this cause more overhead for SQL Server than just calling the stored proc to the point that it bogs the server down?
-
I might understand this process for huge batch processing that can't hold the user from doing something else. But I can't see that being used on almost every part of the app???
Have you looked into optimizing the current code/dbs/servers? Or maybe upgrading the server if it's no longer adequate?
-
to start a job from a Trigger is bad. Jobs are NOT re-entrant, can you imagine what would happen if you try to start a job that hasn't finished yet?
I think you need to redisign your app. You can as posted above poll with a job the "flagged" records and perform the corresponding actions. To avoid the ALL records processing that you mention try to think about it as a different table in which you have two columns GroupID and "ProcessedFlag" The trigger inserts the GroupID then on the job you can call the proc with First Group that has not been processed and when done, Flag it as well ... Done!
* Noel -
Thanx, looks like we agree again
. -
Well, here's what would happen:
1. A Flag gets set marking that all groups need an update. I only want the a groups update to happen if a user of that group makes a request by going to that ASP page.
2. In the ASP page, that groups Flag is CLEARED within code that checks the flag, so other users of the group hitting the ASP page can not trigger the update.
3. After clearing the flag, a stored proc is called which creates a job and starts the job. From TSQL a job can be created with a dynamic name, so I'd append the unique group ID to the job name creating a unique job name. So with the flag protection and unique job name, there would be no reentrancy since a users of the same group hitting the ASP page would no longer see the flag set. The created job would simply call my other stored proc that acutally updates the data.
4. The job is deleted upon completion.
Like I said, the original stored proc that updates the data is working pretty well considering how much it is doing. But, for the user experience of a web page loading, it is taking too long, so I'd like to have it run in the background.
Any other thoughts on this idea?
-
the problem with creating jobs on the fly is that you are assuming alot!
1. The Account that created the job needs access to msdb (something I don't like particulary from ASP)
2. That Name has to be moved back and forth inorder to create and delete somenthing that can be static
3. If you still feel happy about your decision feel free to do it but you are really generating more activity than what's really needed. Tables are meant for that .. a lot of DML but not the system ones (in my opinion)
Cheers
* Noel -
Hmmm. Well I understand the ASP issue with access to msdb. The name is entirely used by the single stored procedure that creates the job so handling of that is pretty tidy. I'm not sure how it could be static unless the job was handling things in batch.
Regarding generating more activity, is it really? In one case, I call a stored proc from ASP that takes some time (10-20 secs) causing the page to load slowly. In the other case, I call a small stored proc that generates and starts a job that calls the other slower stored proc which can then run in the background. The small stored proc that creates the job returns quickly and the ASP page loads quickly. The job runs and deletes itself. Seems like a little bit of extra activity but a better user experience. I am concerned that this may violate some usage of jobs however.
How frequently can I make a job run? If I had a job running frequently that would call the updating stored proc on all groups with the flag set, maybe that would spread out the processing enough that it wouldn't be so bad. Then no processing would be done from the ASP call. Is that what you're suggesting?
-
Yes that was my sussestion but wasn't expecting high frequency
Officially you can't run a job in intervals of less than a minute. There are ways around it like round robin the minute with #seconds/#Jobs or using undocumented settings for the frequency. If you really need high frequency I woud do it in the FRONT end somehow (it is ussually more flexible )
* Noel -
There is an undocumented way to run a job every 10 seconds.
Here is an example how we use it:
DECLARE @nJobId AS UNIQUEIDENTIFIER
DECLARE @szJobName AS VARCHAR(1000)
DECLARE @DBName as VARCHAR(1000)
SET @DBName = DB_NAME() --Database to run the command on
SET @szJobName = 'Queue Count Refresh ' + @DBName
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @szJobName) BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = @szJobName
END
EXEC msdb.dbo.sp_add_job @job_name = @szJobName,
@enabled = 1,
@description = 'Calls QueueCountJob to update the QueueCount table',
@job_id = @nJobId OUTPUT
EXEC msdb.dbo.sp_add_jobstep @job_id = @nJobId,
@step_id = 1,
@step_name = 'Queue Count Refresh',
@subsystem = 'TSQL',
@command = 'EXECUTE QueueCountRefresh', -- Command to execute
@database_name = @DBName --Database to run the command on
EXEC msdb.dbo.sp_add_jobschedule @job_id = @nJobId,
@name = 'Every 10 seconds',
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 0x2, -- Second
@freq_subday_interval = '10' -- 10 seconds
EXEC msdb.dbo.sp_add_jobserver @job_id = @nJobId
GO
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply