ibubi
ibubi

Reputation: 2539

Strange behaviour on writing to db while using service broker

I am testing both service broker external activator and polling based client on behalf of process speed performances of each.

For external activator, I have built a command line application which is being notified when any change occur some table and writes to the same db. Code inside exe looks like as follows

 private static void ProcessRequest()
    {
        using (var connection = new SqlConnection(ServiceConstant.ConnectionString))
        {
            connection.Open();
            do
            {
                using (var tran = connection.BeginTransaction())
                {
                    //Get a message from the queue
                    byte[] message = QueueProcessorUtil.GetMessage(ServiceConstant.QueueName, connection, tran, ServiceConstant.WaitforTimeout);
                    if (message != null)
                    {
                        MessageReceiving = true;
                        try
                        {
                            //Write it to the db
                            ProcessMessage(message);
                        }
                        catch (Exception ex)
                        {
                            logger.Write("Fail: " + ex);
                        }
                        tran.Commit();

                    }
                    else
                    {
                        tran.Commit();
                        MessageReceiving = false;
                    }
                }
            }
            while (MessageReceiving);
        }
    }

When I insert 20 messages to the queue, total duration of insertion of all the messages is approx 10ms

enter image description here

When I extract the ProcessMessage function above which writes the messages to the db to an another separate console application and then call this function 20 times as follows, this time it takes approx 50ms

class Program
    {
        static void Main(string[] args)
        {
            for (var i = 1; i <= 20; i++)
            {
                string message = "mm";
                ProcessMessaage(message);
            }
        }
    }

enter image description here

ProcessMessage function

string sql = @"INSERT INTO [Workflow].[dbo].[TestOrderLog]([OrderId],[RecordTime]) 
                        VALUES (@orderId, GETDATE()) SELECT SCOPE_IDENTITY()";

                using (SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["SqlConnection"].ToString()))
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    con.Open();
                    com.CommandType = CommandType.Text;
                    com.Parameters.AddWithValue("@orderId", 1);
                    try
                    {
                        var result = com.ExecuteScalar();
                        var id = (result != null) ? Convert.ToInt32(result) : 0;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    con.Close();
                }

I don't understand and am surprised although there are costly processing blocks (query a message) inside the loop of external activator code, it takes faster to write db than pure loop in console app code.

Why would pure insertion in a loop be slower than insertion inside the external activator exe instance's code?

Side note, in EAService.config file, <Concurrency min="1" max="1" />

Upvotes: 0

Views: 53

Answers (1)

ibubi
ibubi

Reputation: 2539

It was an absurd mistake of mine, first one is compiled and deployed running code and the second one is running with debugger inside visual studio, so the intervals became normal running without debugger.

Upvotes: 1

Related Questions