none
Performance Issue with WCF-Custom adapter with SQL Binding RRS feed

  • Question

  • Guys,

    Hope some one has seen this issue.

    I'm using WCF-Custom adapter with SQL Binding with Insert Operation (Not using StoreProc).

    I'm sending records to the wcf-sql send port and it's taking too long time to process (1 hrs to process 2000 records). 

    Does anyone know how to boost the performance of this insert operation?

    Thanks,

    Rakesh

     

    Wednesday, October 12, 2011 11:18 PM

Answers

  • You should use a WCF-SQL composite operation as explained here: http://www.packtpub.com/article/soa-capabilities-in-bizTalk-wcf.

    Then if this is not performant enough the best way I have found is to use a stored procedure with SQL table types called over WCF-SQL. This will feel like a bulk upload because the data will get sent in one transaction and can then be processed in the stored procedure.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, October 13, 2011 1:37 PM
    Moderator

All replies

  • Rakesh,

    What kind of ports are you using, dynamic or static ports .. which version of biztalk server are you using and what is the configuration. 1 hr for 2000 records by any standards is too much .. Are you seeing any warnings or errors in the event log. you can also look at these for troubleshooting biztalk performance and this for troubleshooting WCF Adapters


    Regards,
    Bali
    MCTS: BizTalk Server 2010,BizTalk Server 2006 and WCF
    My Blog:dpsbali-biztalkweblog
    -----------------------------------------------------
    Mark As Answer or Vote As Helpful if this helps.
    Thursday, October 13, 2011 2:30 AM
  • It's static port with BizTalk 2009 with all default configuration. 
    Thursday, October 13, 2011 2:33 AM
  • I believe that is common, if you are debatching the 2000 records in the orchestration and send line by line. That incurs 2000 connection to the server. 

     

    Try debatching the records in the pipeline: http://www.enterpriseapplicationsdevelopment.com/2010/04/xml-debatch-in-pipeline.html

     

    Another option is to send in an array of records into SQL Server stored procedure, and "debatch" it from the stored procedure.

     


    Wei Chung, Low
    Thursday, October 13, 2011 7:49 AM
  • I'm debatching records in pipeline and and orchestration get the one by one. If I write a store proc and send records one by one, how should I stop creating new connection for each records or how can I catch conection?

    Thanks, Rakesh

     

    Thursday, October 13, 2011 1:31 PM
  • You should use a WCF-SQL composite operation as explained here: http://www.packtpub.com/article/soa-capabilities-in-bizTalk-wcf.

    Then if this is not performant enough the best way I have found is to use a stored procedure with SQL table types called over WCF-SQL. This will feel like a bulk upload because the data will get sent in one transaction and can then be processed in the stored procedure.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, October 13, 2011 1:37 PM
    Moderator