Required help for inserting data through MSMQ or service broker

Question

Hi,

I am new in MSMQ or Service broker. 

My Requirement: I want to insert 1 million records in one snapshot. In my system its take 2 min and user get confirmation after 2 min so I want to use service broker for reducing this time. It would be great full If anyone can guide me with examples for implementation of service broker on Insert statement on a table with 5-6 column.

Thanks in advance.

Regards

Deepak Goyal

Answer 1

Hi Deepak,

Thank you for your question.
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Deepak,

From a support perspective this is really beyond what we can do here in the forums. If you cannot determine your answer here or on your own, consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.

Perhaps you could find some helpful hints or examples on CodePlex or in our tutorials:

Service Broker Tutorials
http://technet.microsoft.com/en-us/library/bb839489(v=SQL.105).aspx

CodePlex Examples
http://msftsbprodsamples.codeplex.com/

Thanks,

Cathy Miller

Answer 3

Hi Deepak,

What is the front end of this application from which you would to insert 1 million records ? Have you explored BULK INSERT or BCP as possible option ? 

With Service Broker, you insert one row at a time on the Queue. its a 2 step process ;

1. BEGIN DIALOG

2. SEND MESSAGE

3. Repeat for next message

After all 1 million message are on the queue, another application, job or internal activation can fire off a RECEIVE command and fetch the message off the queue ( 1 message at a time) and insert it into a table.

Here is an example - http://blogs.msdn.com/b/steven_bates/archive/2006/01/05/simple-service-broker-message-queue.aspx 

I doubt if you will get any benefit by using service broker in your situation.

---

Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com