Hi everyone! I have a very brief question... I have 10 queues in my database and each of them are sent equal number of messages... There are instances where they execute/activate the stored procedures very fast but there are times where they don't, does anyone have an idea why this happens?
Thank you very much for taking the time to read my post. :)
What is inconsistent, the activation of the first proc for the queue, or the activation of subsequent procs for the queue.
How many messages are being sent to the queues?
What do the procs do?
|||
I'm sorry I wasn't clear abt my question :)
The exact scenario is: I have 10 queues. Each of these queues activate a stored procedure that inserts 150 records in a table. I insert 5 messages for each queue. So, in total, I insert 7500 records in the said table.
The thing is, I don't get the same speed at the everytime. Sending all the messages in all the queues will take up 30 seconds, but there are instances that it takes 50 seconds or more. When I check the table that I'm inserting to during the sending of messages, for a certain time it would insert 4500 at such a fast rate and then would be idle for a while. By idle, I meant that no additional rows are being added, even if the messages have all been sent in the queues.
If it's necessary for me to post the SQL code for this, pls tell me.
Thanks a bunch, guys! :)
|||Please, your situatation would indicate a designe feature and performance issue with your code on an issue witht eh SB code|||
This is the SQL code for sending the message:
BEGIN TRANSACTION ;
DECLARE @.message XML ;
SET @.message = '<root><ctr counter="' + CAST(@.Ctr AS nvarchar(20)) + '"></ctr></root>';
DECLARE @.conversationHandle UNIQUEIDENTIFIER ;
BEGIN DIALOG CONVERSATION @.conversationHandle
FROM SERVICE InitiatorService1
TO SERVICE 'TargetService1'
ON CONTRACT Contract1
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE MsgType1 (@.message) ;
COMMIT TRANSACTION ;
This is the SQL code for the stored proc that is activated when message is received by the queue:
WHILE (1 = 1)
BEGIN
DECLARE @.conversation_handle UNIQUEIDENTIFIER,
@.conversation_group_id UNIQUEIDENTIFIER,
@.message_body XML,
@.message_type_name NVARCHAR(128);
BEGIN TRANSACTION ;
WAITFOR(GET CONVERSATION GROUP @.conversation_group_id
FROM [dbo].[TargetQueue1]), TIMEOUT 1 ;
IF @.conversation_group_id IS NULL
BEGIN
ROLLBACK TRANSACTION ;
BREAK ;
END ;
WHILE 1 = 1
BEGIN
RECEIVE TOP(1)
@.conversation_handle = conversation_handle,
@.message_type_name = message_type_name,
@.message_body =
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'<none/>' AS XML)
END
FROM [dbo].[TargetQueue1]
WHERE conversation_group_id = @.conversation_group_id ;
IF @.@.ROWCOUNT = 0 OR @.@.ERROR <> 0 BREAK;
END CONVERSATION @.conversation_handle ;
END;
COMMIT TRANSACTION ;
DECLARE @.Ctr int
SELECT @.Ctr = @.message_body.value('(/root/ctr/@.counter)[1]', 'int')
EXEC InsertRecords2 @.Ctr, 1 -- stored proc that inserts 150 records in the tableEND
|||
Looks like the stored procedure above will always receive messages from TargetQueue1. How does it receive messages from the other 9 queues?
Also, you are doing the insert outside the message loop. If you receive more than one message in a conversation group (doesn't look like you are from your SEND code), you will end up with inserting only the last message received. Also the insertion is not being done in the same transaction scope as the receive... so it is possible in case of failure that you would lose some messages.
|||Is it also not the case that the rollback will result in the queue being disabled due to poison message protection. it should really commit if no conversation group is found.|||
I have 10 of this code, one for each queue.
I will try to rewrite my code and take into account all of your inputs. Will give you feedback ASAP.
Thank you very much, you've all been very helpful :)
|||
Hi guys, I've changed my code for RECEIVE into this:
DECLARE @.conversation_handle UNIQUEIDENTIFIER,
@.conversation_group_id UNIQUEIDENTIFIER,
@.message_body XML,
@.message_type_name NVARCHAR(128);
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;
RECEIVE TOP(1)
@.conversation_handle = conversation_handle,
@.message_type_name = message_type_name,
@.message_body =
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'<none/>' AS XML)
END
FROM [dbo].[TargetQueue1] ;
IF (@.@.ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK ;
END
END CONVERSATION @.conversation_handle ;
COMMIT TRANSACTION ;
DECLARE @.Ctr int
SELECT @.Ctr = @.message_body.value('(/root/ctr/@.counter)[1]', 'int')
EXEC InsertRecords2 @.Ctr, 1
END
|||
Is there any place in this code where locking is being made which delays some of the insertion that I'm doing? That's my only speculation but I can't seem to pinpoint at which part this happens.
I didn't include the stored procedure for insertion inside the transactions for fear that this may delay the entire process more. I'll try now with that inside the transactions.
Thanks again for looking into this. I'm just new with the technology, so I hope you could understand why there are some flaws in my code :)
|||
I just noticed that I'm only ending the conversation in the TargetQueue and never in the InitiatorQueue (this is associated with the service that initiated the conversation). Could this be a possible cause? I noticed that after all the processing, the InitiatorQueue contains a lot of message with message type of http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog
Thanks again! :D
