Building a high performance database queue

Question

2.00/5 (1 vote)

See more:

SQL-Server-2012

I have a project where I get responses from emails that are sent out which may be 100 per second. Eventually I have to insert the information received into a table that will have many indexed columns for reporting purposes. My thought was to set up a primary table which is completely stripped any indexes and the responses coming in will be recorded there. In a different process, these records from this first primary table will be moved to the final table with the indexes in an orderly fashion.

- Does anyone have any suggestions on this method or have seen a more efficient method to handle a large influx of data?

- Is it OK to have a primary table like this in which records are added and deleted continually? Does it fragment in the SQL server too heavily?

Thank you

Posted 5-Jun-13 7:36am

onemorecoke

Add a Solution

Comments

Prasad Khandekar 5-Jun-13 14:16pm

It won't really fragment the database. I will definitely suggest to use a queuing software like MQ to handle this situation. Another approach is to use Oracle Database as it comes with it's own queue implementation called AQ.

RedDk 5-Jun-13 14:46pm

This might sound funny but what you call a "queue" is really just an exposed interface. TSQL provides a good means to address a time continuum as such with it's indexing capacity and the use of TOP. About that "interface". Think of it as a model on rna/protein/transcription , or google THAT; you've got a determinate function in the codon and a discriminate mechanism in whatever a milieu of substrate is capable of providing for the assembly of a specific longchain item ... say an enzyme, etc ...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

onemorecoke · Accepted Answer · 2013-06-05T09:26:00

One solution I came up with was to fire this statement at an interval:

Delete tblUnsubscribeLogQueue
OUTPUT deleted.* INTO tblUnsubscribeLog

I read by using the OUTPUT clause I could be assured that deadlocks would not occur because this was all one operation and I will only have one processes that is deleting records.

Any comments?

Any other thoughts on if this will cause fragmentation?

Thanks