Service Broker queue insert vs. table insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Service Broker queue insert vs. table insert

Author

Topic

aaroww11
Starting Member

12 Posts

Posted - 2011-07-06 : 05:20:28

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
-- )

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-07-06 : 07:47:34

I've never used SB myself but I'm aware of how it works, and I think it's pretty safe to say that in comparison with a standard insert SB will loose easily (given that both SB and the insert are running on the same server). There is a lot more overhead when inserting in to an SB queue than to a table and it doesn't really make any sense either when you have such short transactions. SB is created for sending messages across a (potentially slow) network with guaranteed delivery. Not for working locally.

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

aaroww11
Starting Member

12 Posts

Posted - 2011-07-06 : 08:32:56

I agree with you, but I only testedi it localy.
I would have at list 20 sources(web services, application with threads,...) which will insert the data.

In that case I think that SB is more secure solution.
Maybe I am wrong.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-06 : 09:09:22

Remus Rusanu has an excellent blog that covers Service Broker performance, particularly this link: http://rusanu.com/2006/10/16/writing-service-broker-procedures/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources