Service Broker queue insert vs. table insert

• aaroww11

  SSC Veteran

  Points: 248

  More actions

  July 6, 2011 at 3:18 am

  #240441

  I tested from one machine with 40 threads inserts into SB queue and into table.

  It seems that insert into table is faster, but the source was one machine.

  I presume that if we use several sources and applications that queue should be faster.

  Can somebody confirm it?

  I would like to use SB to achieve better performance.

  DECLARE @handle uniqueidentifier

  BEGIN DIALOG CONVERSATION @handle FROM SERVICE MySendingService TO

  SERVICE 'MyReceivingService' ON CONTRACT MyContract ;

  --Sends a message

  SEND ON CONVERSATION @handle MESSAGE TYPE MyMessage ( '<fldFiled1>' + @filed1 + '</fldFiled1><fldFiled2>' + @filed2 + '</fldFiled2><fldfiled3>' + @ filed23+ '</fldfiled4 ><filed4>' + cast(GetDate() as varchar(50))+ '</ filed4>' )

  --And ends the conversation

  Insert to table example :

  --INSERT INTO tblTest

  -- (

  -- fldFiled1,

  -- fldFiled2,

  -- fldFiled3,

  -- fldFiled4,

  -- fldFiled5,

  -- fldFiled6,

  -- fldFiled7,

  -- fldFiled8

  -- )

  --VALUES (

  -- @filed1,

  -- @filed2,

  -- @ filed3,

  -- @ filed4,

  -- @ filed5,

  -- @ filed6,

  -- @ filed7,

  -- @ filed8

  -- )

• sturner

  SSC-Insane

  Points: 22411

  More actions

  July 6, 2011 at 7:09 am

  #1348919

  Insert to a table will always be faster. The service broker "send" does a lot of things behind the scenes.... if you understand what it is actually doing and why, you'll not be surprised.

  On the other hand, service broker does offer some unique capabilities that a simple table would not. If you have performance metrics you need to maintain you'll need to benchmark under typical loading to see if this architecture will meet your requirements.

  The probability of survival is inversely proportional to the angle of arrival.

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  July 6, 2011 at 10:11 pm

  #1349415

  Don't use Service Broker as an optimization tool. It's there for concurrency load and multi-server asynchronous transfers. You will never get better performance from the broker on the command the broker is handling. It can, however, help with the server load in general, improving generic performance across the board.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• Fraggle-805517

  SSCrazy Eights

  Points: 8907

  More actions

  July 6, 2011 at 10:29 pm

  #1349416

  Both of the above statements are true.

  As stated above, Service Broker is a queuing mechanism. Meaning that you send a message to a queue and then at some point in the future that message gets processed and removed from the queue. The duration for this to occur can be millisecond or hours depending upon the load, max_queue_reader value, system resources, and code complexity.

  Couple of things that we use Service Broker for where I work are as follows:

  1) When posting an order, we send data to a 3rd party vendor via a web service call. However, we don't want to slow down the finalization of the order while we wait on the traffic back and forth between the vendor and us. So we send the needed information to the queue and once the message is in the queue, the order can continue to process. Saved anywhere between 5-10 seconds during order finalization by doing this.

  2) We have an XML auditing system that audits our tables. All off these message are sent to the queue for processing during down hours in the middle of the night when server resources are more available, rather than auditing everything at that time. The trigger is quicker to execute on both small and large updates. However, it is more resource intensive due to the nature of XML and SQL Server, hence why we do the processing at night

  So in your scenario, asking which is faster might not be the right question. The better question might be is when do those inserts have to occur? If you are worried about locking/blocking in the middle of the day due to the way your system does inserts, but you don't really need the inserts for a 24 hour period, then you could setup a process that stores all of the inserts into the queue until a specific time frame. Then just process the messages during that time.

  Again, this is all dependent upon what you are trying to accomplish. If sheer speed is you desire, then the insert will be fastest. If you are trying to delay the inserts so your select statements are faster on the table, then that is an entirely different question and solution.

  Fraggle