locked
Stored Procedure call using the BizTalk Orchestration RRS feed

  • Question

  • Hi All,

    If the stored procedure called through BizTalk orchestration for data loading and extracting purpose.  The data loading is very slow and some time getting time out also. Please share your views to improve the data loading performance using the BizTalk.

    Regards,

    Thiru


    Thiru

    Friday, January 2, 2015 2:18 AM

Answers

  • 1 Lakh record :) So these records flow through Biz Talk Message Box at the same time . I would see there would have been inconsistency in BizTalk flow if at same time you are publishing that much record .

    This is good scenario for Some ETL processing were bulk data transfer can be done without Orchestration .

    I would suggest to Use SSIS for your  process as BizTalk is a product deals with with Process flow  were you design your business flow through Orchestration .

    So As been pointed out earlier in my post either you can work with small set of data through BizTalk Or best  bet would have been using SSIS or any other ETL tool .

    Thanks

    Abhishek

    • Marked as answer by Angie Xu Friday, January 9, 2015 1:53 AM
    Saturday, January 3, 2015 6:17 AM
  • 1 Lakh records! I have 2 things to tell you.

    1) Though WCF-SQL adapter support Bulk insert mechanism using “CompositeOperation” in WCF-SQL adapter soap action header tab.

    Bulk Insert using “CompositeOperation” in WCF-SQL BizTalk Adapter

    Walkthrough: Composite Operations with the new WCF-based SQL Adapter

    2) But it still mean that huge files with data will go through BizTalk and will have impact on the performance of BizTalk. A better solution would be SSIS, which provides flexibility, and a much better way to import data into SQL.

    If it's a plain Bulk Insert, I don't see why you want to use BizTalk and expect an optimal performance with it. I think you have to know that SSIS is designed to move and manipulate very large amount of data over extremely high performance batch processing where BizTalk has been designed to move, process, validate, transform, and route comparatively low rate of transactions.

    Rachit



    Saturday, January 3, 2015 6:41 AM
    Moderator

All replies

  • How much of DATA are we talking about here? Multiple Records in a single SP?

    Have you taken any steps to identify the cause of this slow response?

    Monitor SQL usage on the machine where the DB is hosted?

    Monitor the BizTalk Server where this process is executing?

    Regards.

    Friday, January 2, 2015 6:06 AM
  • HI Thiru,

    As been pointed out by Shanky BizTalk is not a ETL tool for bulk data transfer . If size of incoming data is large I would suggest to use SSIS for that .  There are other approaches were you can combine BizTalk Processing with SSIS processing but that's your Architecture call .

    In meanwhile what you can try is to Optimize your SQL Query and see its execution time  . Once done you can have a performance counter set  to monitor BizTalk processing .

    Thanks

    Abhishek

    Friday, January 2, 2015 7:09 AM
  • Before we can offer any advice, we need to know exactly what is timing out.

    Also, it would be very helpful to know exactly what is taking a long time to run.  The Stored Procedure, the Orchestration, the Port?

    Without that, any suggestions would be merely guesses.

    Friday, January 2, 2015 4:00 PM
    Moderator
  • if the same SP takes  4 mins running through SSMS.  it takes much time through  BIzTalk 

    Thiru

    Friday, January 2, 2015 4:30 PM
  • Have you tried to do some Performance analysis of your Environment Yet ? Trying using PAL and MessageBox Viewer  to see any inconsistency in your Environment.

    Again If data size is Large I would say to dissemble it into smaller once through your Stored Procedure so that

    BizTalk should not behave in inconsistent manner

    Thanks

    Abhishek

     
    Friday, January 2, 2015 4:36 PM
  • Ok, and what do you mean 'through BizTalk'?

    Is it the Port, an Orchestration?  What exactly are you doing in the Orchestration?

    You can see the time each takes in the list of Completed Services.

    Friday, January 2, 2015 6:03 PM
    Moderator
  • Not that I am being rude, but it would really help when people would post the questions with proper details and try to be specific.

    I've been on this forum for long and there are really good professionals(when i say "good" it means really damn good) who can provide suggestions/insights. But please be specific.

    Friday, January 2, 2015 8:47 PM
  • 1 Lakh plus records, multiple records in single SP.

    I f executed the SP in the DB server directly , it completes  at maximum 5 mins. if i trying to execute through BizTalk Orchestration , th edb server CPU usage is going high and talking much time.

    Regards,

    Thiru 


    Thiru

    Saturday, January 3, 2015 5:49 AM
  • orchestration 

    Thiru

    Saturday, January 3, 2015 5:50 AM
  • 1 Lakh record :) So these records flow through Biz Talk Message Box at the same time . I would see there would have been inconsistency in BizTalk flow if at same time you are publishing that much record .

    This is good scenario for Some ETL processing were bulk data transfer can be done without Orchestration .

    I would suggest to Use SSIS for your  process as BizTalk is a product deals with with Process flow  were you design your business flow through Orchestration .

    So As been pointed out earlier in my post either you can work with small set of data through BizTalk Or best  bet would have been using SSIS or any other ETL tool .

    Thanks

    Abhishek

    • Marked as answer by Angie Xu Friday, January 9, 2015 1:53 AM
    Saturday, January 3, 2015 6:17 AM
  • 1 Lakh records! I have 2 things to tell you.

    1) Though WCF-SQL adapter support Bulk insert mechanism using “CompositeOperation” in WCF-SQL adapter soap action header tab.

    Bulk Insert using “CompositeOperation” in WCF-SQL BizTalk Adapter

    Walkthrough: Composite Operations with the new WCF-based SQL Adapter

    2) But it still mean that huge files with data will go through BizTalk and will have impact on the performance of BizTalk. A better solution would be SSIS, which provides flexibility, and a much better way to import data into SQL.

    If it's a plain Bulk Insert, I don't see why you want to use BizTalk and expect an optimal performance with it. I think you have to know that SSIS is designed to move and manipulate very large amount of data over extremely high performance batch processing where BizTalk has been designed to move, process, validate, transform, and route comparatively low rate of transactions.

    Rachit



    Saturday, January 3, 2015 6:41 AM
    Moderator
  • So, Bing tell me that 1 Lakh = 100K.  Given that, what you are seeing is not an unexpected behavior.

    It's not the 100K records that the problem 100K records at the same time.  BizTalk favors individual messages over 'batches' so your solution will involve using a different pattern to retrieve and process the messages individually, or at least in smaller chunks.

    Saturday, January 3, 2015 1:53 PM
    Moderator