none
SQL/Oracle Adapter Performance

    Question

  • Hi,
     
    I'm using SQL Adapter in the orchestration to fetch the data from the database. The polling interval is set to 20 Sec.
    SQL stored procedure is used in SQL adapter configration. SP fetches the data and marks the data as read after select.

    I would like to know,

    1) Would this hamper the performance as Biztalk is polling and repolling after every 20 sec?
    2) How to improve the performace using SQL Adapter.

    I have the same questions for Oracle Adapter as well.

    .Nilesh
    Thursday, January 22, 2009 6:49 AM

All replies

  •  

    Sure BizTalk performance will be affected, but I don’t think it will be a huge performance effect if you have a small size of data to process every 20 seconds. But I recommend to increase the pooling interval 

     

    With low pooling interval value, be careful to set “Poll While Data Found “ to false or enable it  if you are doing proper data read locks before reading the data, so you can  avoid the multiple read overlapping from SQL Database.

     

    Another important point, I think it’s very important to limit the data size that comes from the DB servers, the general rule don’t get all the available data . it’s highly recommend to get an acceptable chunk of data for every DB read using the Top N statement.

     

    For more information about the best practices for the SQL Adapter, refer to this: http://msdn.microsoft.com/en-us/library/cc507804.aspx

     

    -Firas

     

    • Proposed as answer by edhickey Friday, January 30, 2009 1:05 AM
    Thursday, January 22, 2009 10:58 AM
  • I can say this about the polling part: I'd be far more concerned about your database performance than BizTalk's.  If you want to do aggresive polling put the SQL Adapter (and the Oracle one) in their own Hosts / Host Instances so that they are never faced with thread starvation.  You should always use multiple Hosts / Host Instances anyway, it's a good practice and I outline my minimum here

    I also have some good tips on using the SQL Adapter: SQL Server Adapter Port (Ab)Use and More SQL Adapter (Ab)Use.  Firas makes some good points about.  I would also check out what Ben McFarlin has to say here locking with the SQL Adapter can be tricky for many.

    I hope this helps.

    Kind Regards,
    -Dan

    • Proposed as answer by edhickey Friday, January 30, 2009 1:05 AM
    Thursday, January 22, 2009 2:43 PM
  •  

    Definitely check out Fira's link to the SQL Adapter best practices - and pay attention to the way they implemented their sample with table hints (UPDLOCK ROWLOCK READPAST, etc), updating the transaction isolation level (SET TRANSACTION ISOLATION LEVEL READ COMMITTED), using a GUID field to mark the ones that have been read and creating an index around this field.


    Thiago Almeida - http://connectedthoughts.wordpress.com
    Friday, January 23, 2009 12:12 AM
    Answerer