Jobs - Calling a stored procedure every 10 seconds
-
Is there any way you can schedule a job for every 10 seconds?
The job scheduler seems to only allow down to 1 minute. I did find some code which you can place a loop in the job, and use the "WAITFOR" command to have the job run a section every 10 seconds and exit after 60 seconds, however, the SQL is executed in the job as a batch. So all those commands get "queued" up until the job finishes, then runs 6 times. Is there any way around this behavior?
Here is the code
CREATE PROCEDURE
QueueCountJob AS
BEGIN
SET NOCOUNT ON
DECLARE @jUpdate DATETIME
DECLARE @jCount INT
SET @jCount = 1
SET @jUpdate = GETDATE()
WHILE(@jCount <= 6)
BEGIN
SET @jUpdate = DateAdd(Second, 10, @jUpdate)
EXEC sp_executesql N'EXECUTE QueueCountRefresh'
WAITFOR TIME @jUpdate
SET @jCount = @jCount + 1
END
SET NOCOUNT OFF
END
-
Maybe some more typing could solve this :
EXEC dbo.QueueCountRefresh
GO
WAITFOR DELAY 00:00:10
EXEC dbo.QueueCountRefresh
GO
... 6 times
-
What happens to the time taken for executing the commands.
If the job is scheduled for every minute and the execution takes 1 min 10 sec. Will the job continue or it will wait for next minute.
Regards,
gova -
There is an undocummented way of doing it
but why do you need a job to run that often?
* Noel -
I got that doubt too. If that is the case make the call in endless loop with 10 sec gap.-- Bad IdeaRegards,
gova -
What happens is that it will wait for the next minute mark effectively running at 1min and 50sec
* Noel -
Let's hear it
. -
So Noeld how can we make sure the call is made every 10 seconds.
Regards,
gova -
OK... OK...
Create the job as you normally would
script the job out
in BOL look at the parameter:
@freq_subday_type of sp_add_jobschedule
the possible values are :
0x1
At the specified time
0x4 Minutes
0x8 Hours
can you guess what 0x2 could be?
just change it on the script and run it back in ... Enjoy!
* Noel -
No we can't, tell us pleaaaaaaaaaaaaaaaaaase.
Seriously, good week-end
. -
WHY 10 sec interval?
We operate a real time data entry production line where literally every second counts. If an operator has to wait 1 sec or egad 5 sec between the next piece of work, then we lose thousands of dollars a day.
Currently, at one site, there are 50 stations that need to know what work is ready to do in a particular queue and there are about 30 queues. These stations query the database with a query that takes 100 ms to run when there are 10,000 items amongst the 30 different work queues. It takes longer on a busy day when there might be 30,000 items in the work queues. The customer doesnt like waiting to see what has to be done on a 60 sec interval, so we refresh every 10 sec...in our line of business, every second costs money. With 50 stations refreshing that often, this was a bottleneck. We know that if we scheduled a job to do the long query every 10 sec and stored the result in a temp table, we save 49 long queries per 10 sec interval.
This was our second "quick fix" idea. The first one was to make a stored proc that checked the last time the temp table was updated before returning the temp table. The check was making the query take 2x as long. We decided to make the job, to avoid 50 checks to see if the table needed updating.
Our long term fix is to not use SQL server to do this, but we cant do that now, since 100's of different queries are running against the current queing schema.
-
Thanks Noel, I'll give that a shot.
-
I worked as a SCADA engineer a long time ago. Your solution is to transfer info the other way around.
1.The Pooling stations register with the server.
2.There is a trigger that fires and update some status table on the server
3.There is an App on the server that does the pooling on the status table.
4.Once the App on the server detects the change, that same app reads what is needed and BROADCAST to all registerd Listeners the results
I Hope is easy to understand
Goog Luck!
* Noel -
we were thinking the trigger on the "idQueue" field could add an item to an mts queue. Then the stations would get the work from the mts queues. Any thoughts?
-
the thing with the queues is that traffic is still there and is another technology to the pile (SP, set up etc). Doable though!
In my last incursions in the SCADA world we ended up writing the broadcast to the listeners and that worked very well for large number of variables!!
All in all The idea is the same just different technology and your delivery mechaninsm will probably be more roubust than the one we implememted thoguh slower.
I don't know all of your architecture but you are definitely in the right path. One thing: DO NOT put heavy code in the Trigger!!
hth
* Noel
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply