none
WCF SQL send adapter is slower if it has not been used for a while - How can I make it 'hot' ? RRS feed

  • Question

  • How can I make a WCF SQL adapter 'hot' all the time?

    If my WCF SQL send adapter is used freqently the request/response completes in less than 0.2 seconds. This after I have tuned the host that it runs on for low latency. I measure this time by looking at the orchestration instance in the orchestration debugger.

    If no message goes through the send port for about 1-2 minutes then the next request/response cycle takes a lot longer to complete . It is typically about 0.9 second.

    Who cares about 0.7 second you say? Well I do because my entire low latency application has to complete in less than 1 second 99% of the time. I can achieve that if the WCF SQL adapter  did not show the behaviour described above.

    I have used SQL profiler to trace what is happening on the target SQL server and it shows that on SQL server the transaction always completes in less than 10 milliseconds.

    The SQL adapter is currently set to the standard binding defaults that you use for a SQL adapter and I am using BizTalk 2010 64-bit with two nodes. All my test measurements have been run with one node turned off because if you run on two nodes the results are a bit variable because every time the WCF SQL adapter swaps to the other node you get the same slow request/response.

    Friday, February 18, 2011 6:06 PM

All replies

  • So what does the 0.2 seconds represent? The time it takes to deserialize the message while the adapter executes the request?

    Do you have transactions enabled on the port?

    What kind of db object are you calling through the adapter?

    The load balance effect sounds like a thread switch. Do you have a dedicated host for just the WCF-SQL calls? You could even go as far as a single dedicated host per port to free up threads.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Sunday, February 20, 2011 6:17 PM
    Moderator
  • I had not thought about what the 0.2 seconds is due to. A good question for me to think about.

    I have isAmbientTransaction set to true. So I guess i have transactions enabled on the port. I am going to set it to false because the stored prcoedure I am calling only selects data.

    I have a dedicated host for the all send ports including the WCF-SQL call. This has been tuned for low latency.

    Sunday, February 20, 2011 7:34 PM
  • I have done a bit more investigation and tried the following without making any difference to the above behaviour. I have set isAmblientTransaction to false, open timeout to 10 and close timeout to ten.

    I have also monitored the SQL connections to the SQL database. I have noticed that the SQL connection for the WCF SQL adapter has a very short connection lifetime less than 1 minute. The default connection lifetime on the SQL server is set to 10 minutes and some of the connections are always being resused. I am beginning to think if no transactions go through for a while then the a new connection has to be created to SQL server each time. I think that this is what is causing the slow response if the adapter is idle for a minute or so. I cannot find any way to make the WCF SQL connection stay around for a bit longer so that it is reused. Unless i can find a way of doing this then I believe that you cannot use a WCF-SQL adapter in a low latency scenario because of increased latency when the connection is created each time.

    What does anyone else think about his theory?

    Wednesday, February 23, 2011 5:15 PM
  • Hi,

    This is still an issue in BizTalk 2013 R2. If I want low  latency I have reverted to using in line SQL.

    Friday, August 25, 2017 8:44 PM
  • What if you setup a Polling Receive Location to the same Database?  Using a static query like SELECT 'FOO' FROM SomeLittleUsedTable.

    I'm not necessarily proposing this as a solution, but it may provide a clue to support the connection timeout theory.

    Monday, August 28, 2017 1:49 PM