I wish to make use of a database table like a queue. I wish to place inside it and take components from it within the placed order (FIFO). My primary consideration is performance because I've 1000's of those transactions each second. So I wish to make use of a SQL query that provides me the very first element without searching the entire table. I don't remove a row after i see clearly. Does Choose TOP -.... help here? Must I use any special indexes?
If you don't remove your processed rows, then you will take some kind of flag that signifies that the row was already processed.
Put a catalog on that flag, as well as on the column you will order by.
Partition your table over that flag, therefore the dequeued transactions aren't blocking your queries.
Should you really get
1.000 messages every second, that will lead to
86.400.000 rows each day. You might like to think about some means of cleaning up old rows.
I'd make use of an IDENTITY area because the primary answer to supply the distinctively incrementing ID for every queued item, and stick a clustered index onto it. This could represent an order where the products were queued.
To help keep the products within the queue table when you process them, you'd require a "status" area to point the present status of the particular item (e.g. =waiting, 1=being processed, 2=processed). This is required to prevent a product be processed two times.
When processing products within the queue, you'd must find the following item within the table NOT presently being processed. This will have to be in a way in order to prevent multiple processes obtaining exactly the same item to process simultaneously as shown below. Note the table hints UPDLOCK and READPAST which you should know of when applying queues.
e.g. inside a sproc, something similar to this:
DECLARE @NextID INTEGER BEGIN TRANSACTION -- Find the next queued item that is waiting to be processed SELECT TOP 1 @NextID = ID FROM MyQueueTable WITH (UPDLOCK, READPAST) WHERE StateField = 0 ORDER BY ID ASC -- if we've found one, mark it as being processed IF @NextId IS NOT NULL UPDATE MyQueueTable SET Status = 1 WHERE ID = @NextId COMMIT TRANSACTION -- If we've got an item from the queue, return to whatever is going to process it IF @NextId IS NOT NULL SELECT * FROM MyQueueTable WHERE ID = @NextID
If processing a product fails, would you like to have the ability to repeat the process later? If that's the case, you will need to either totally reset the status to or something like that. That will need more thought.
Alternatively, avoid using a database table like a queue, but something similar to MSMQ - just thought I'd throw that within the mix!
Everything is dependent in your database engine/implementation.
For me personally simple queues on tables with following posts:
id / task / priority / date_added
may even work.
I made use of priority and task to group tasks and just in case of bending task i choosed the main one with bigger priority.
And do not worry - for modern databases "1000's" is certainly not special.
Produce a clustered index on the date (or autoincrement) column. This can keep your rows within the table roughly in index order and permit fast index-based access whenever you
ORDER BY the indexed column. Using
TOP X (or
LIMIT X, based on your RDMBS) will only retrieve the very first x products in the index.
Performance warning: it is best to evaluate the execution plans of the queries (on real data) to ensure the optimizer does not do unpredicted things. Likewise try to benchmark your queries (again on real data) to have the ability to make informed choices.
This won't be any problem whatsoever as lengthy while you use something to keep an eye on the datetime from the place. See for the mysql options. Now you ask , whether you simply ever require the absolute most lately posted item or whether you have to iterate. If you want to iterate, then what you ought to do is grab a chunk by having an
ORDER BY statement, loop through, and recall the last datetime to ensure that you should use that after you grab the next chunk.