Hi!
I'm looking for other good ideas how to implement an queue where
multiple
jobs.

For now I have a single table with identity column and some
information about what to do. Combined with multiple jobs "eating"
from queue, e.g. they
take specific rows for that job corresponding to id % #queue
(modulus).

But each jobs take different time, so I see often that only half of
queues running
while long running jobs blocks for run of waiting jobs.

Best regards
Bjorn

On 4 Mar, 19:25, B D Jensen <bjorn.d.jen...@gmail.com> wrote:
> Hi!
> I'm looking for other good ideas how to implement an queue where
> multiple
> jobs.
>
> For now I have a single table with identity column and some
> information about what to do. Combined with multiple jobs "eating"
> from queue, e.g. they
> take specific rows for that job corresponding to id % #queue
> (modulus).
>
> But each jobs take different time, so I see often that only half of
> queues running
> while long running jobs blocks for run of waiting jobs.
>
> Best regards
> Bjorn

Have you considered using Service Broker?
http://msdn2.microsoft.com/en-us/library/ms166043.aspx

--
David Portas

B D Jensen (bjorn.d.jensen@gmail.com) writes:
> I'm looking for other good ideas how to implement an queue where
> multiple jobs.
>
> For now I have a single table with identity column and some information
> about what to do. Combined with multiple jobs "eating" from queue, e.g.
> they take specific rows for that job corresponding to id % #queue
> (modulus).
>
> But each jobs take different time, so I see often that only half of
> queues running while long running jobs blocks for run of waiting jobs.

THe unavoidable question is: what's wrong with Service Broker?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

> For now I have a single table with an IDENTITY column [sic: table property] and some information about what to do. Combined with multiple jobs "eating" from queue <<

If you really want to mimic a queue in SQL for some strange reason,
then get out an old book on batch systems from the 1970's. You can
mimic them with a little effort:

CREATE TABLE Queue
(job_nbr INTEGER NOT NULL PRIMARY KEY,
job_submission_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
job_priority INTEGER DEFAULT 100 NOT NULL,
etc.);

You pick the job to run with something like this:

SELECT job_nbr, ...
FROM Queue AS Q1
WHERE Q1.job_nbr
= (SELECT MIN(job_nbr)
FROM Queue AS Q2
WHERE Q2.job_priority
=(SELECT MIN(Q3.job_priority)
FROM Queue AS Q3));

Every now and then, run a procedure that decrements the job_priority
of rows that have been in the queue for over (n) seconds until it is
zero and will be done next. If you want to force a job to the top of
the queue, insert it with a low (non-zero) job_priority value.
Remember to delete the jobs as they are processed. It is easy to see
that all jobs will clear the queue.

Hi!
Thank to all ;-)
There's probably nothing wrong with Service Broker, actually I
expected there must be something usefull built-in :-)

What is better than "mimic a queue in SQL"?
Best regards
Bjorn

>> What is better than "mimic a queue in SQL"? <<

Service Broker or a batch operating system of some kind. Hey, they
work great on Mainframes!

Hi again!
I'm still trying to find out how to use Service Broker (SB) -
I will see what I can find, but maybe you have time
to explain me:
can I make some custom ordering of queue usage, e.g. handling that
request from bosses are more important than standard
requests (for now i use simple ordering on table column storing info
about source of request)?

where to find a simple example for paralell batch processesing using
SB?

Thanks
Bjorn

>> I'm still trying to find out how to use Service Broker (SB) - I will see what I can find, but maybe you have time to explain me: <<

I would have to learn it first There are more experienced people
on this newsgroup that can help you.

>> can I make some custom ordering of queue usage, e.g. handling requests from bosses is more important than standard requests (for now I use simple ordering on table column storing info about source of request)? <<

That is the idea of the priority number I showed you in my posting. A
worker can come into the queue with (priority = 100), middle
management can come into the queue with (priority = 50) and the
company president can start at (priority = 0). But the decrement
procedure will assure that every job eventually gets to (priority =
0), so nobody is live-locked.

I did an article decades ago for INFORMATION SYSTEMS NEWS when I had a
column on software Engineering about the different queue handling
methods; I compared them to traffic signals. I don't know if I have
an electronic copy of it, but it was funny and might help

Hi Joe!
I now looked a little more at SB, it seems a bit complex for this type
for this purpose (=> much more code than I had written in my
solution)...
.... but I probably should read more so I fully understand what it can
give...

I looked also at your select statement picking job to do, but my
problem is not to find out wich
job to process. I have 8 scheduled jobs. Each job/queue has its "own"
rows to process, but since some reports are faster than others, some
jobs will be delayed, while not all queues have something to do after
a while.

The reason why each row is related to a specifik job/queue is to
ensure, that only one single jobs tries to handle the request (row).
And I also like to see which job is processing which row, so I can
stop the right job,
if I need to stop processing manually because it tooks too long.

Thank you for your ideas ;-)
Best Regards
Bjorn

Hi Erland!
There is nothing wrong wil SB, and as I found out in SS2008 it also
support
"ordering" after priority.

It seems a bit complex, but thats probably because it's new to me ;-)
Thank you for your help
Best Regards
Bjorn