Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Sreeram Venkatramani
    Connect PHP for Bulk dataloadsAnswered
    Topic posted November 8, 2018 by Sreeram VenkatramaniGreen Ribbon: 100+ Points, tagged Connect PHP, REST 
    97 Views, 7 Comments
    Title:
    Connect PHP for Bulk dataloads
    Content:

    We are in the process of migrating data from Legacy applications to Oracle Service Cloud. The Legacy Data needs to be migrated to both Standard Objects and a few Custom Objects in Service Cloud.

    For some transactional entities, the volume of data is to the tune of 1 Million - 2 Million. And it is necessary to migate historical data to the new Service Cloud solution for reporting purposes.

    Also all references need to be built during data upload. Example: An Incident needs to be linked to the correct Organization and Contact.

    Currently we are using Connect PHP to import CSV data in bulk. But of late we see the throughput is very small. The PHP scripts have embedded ROQLs to fetch the IDs of the references (like Organization Id of the Organization to be linked to the Incident etc.)

    Would like to understand -

    a. is the use of Connect PHP a correct approach to load Bulk data into Service Cloud
    b. is Connect PHP architecture such that it is inherently slow in loading data
    c. what are the best practices when using Connect PHP for such Bulk data uploads
    d. will the use of REST APIs give better performance compared to Connect PHP

    Any guidance/pointers would help us in taking the next steps to improve performance of our loads.

    Regards,
    Sreeram V

    Version:
    18A

    Best Comment

    Vlad

    a. is the use of Connect PHP a correct approach to load Bulk data into Service Cloud
    b. is Connect PHP architecture such that it is inherently slow in loading data
    c. what are the best practices when using Connect PHP for such Bulk data uploads
    d. will the use of REST APIs give better performance compared to Connect PHP

    a. If possible, the data import wizard is preferred. It supports up to 1,000,000 records (CSV rows) in a single import. I suspect there is a problem that can be addressed if you see the console freezing or crashing. I would recommend submitting a service request with tech support to have that looked at.
    b. It's slower than the data import wizard. But you can add custom logging to your script to identify where it's taking longer and where it may be improved. For example if you have a ROQL query executed for every record, the query may be optimized, fields may be indexed etc.
    c. Use logging in a smaller batch to see where the delay comes from and try to improve/optimize that area of the code. Also, make sure to suppress on save(). This ensures that the rule base and Custom Processes are not executed and can improve performance significantly. Lastly, consider adding an explicit commit every 1000 records or so, instead of relying on the implicit commit when the script is done.
    d. To my knowledge, Connect PHP, REST and SOAP are all based on PHP. I would be surprised if you noticed better performance over REST, which was not really designed for bulk data uploads. SOAP may be better suited for that, but I think that the first options would be 1) data import wizard, 2) ConnectPHP, 3) SOAP

    Comment

     

    • Vlad
      Have you considered using the out of the box data import wizard?
    • Sreeram Venkatramani

      Hi Vlad,

       

      We have tried the out-of-the-box Data import Wizard. Infact, we are using the Wizard to load Objects where only a couple of thousand records need to be loaded.

      We have noticed that it does not perform well for loads more than 10000 records per CSV file. For bigger load sizes, the Wizard just hangs and the agent desktop application crashes eventually (after a long wait).

      And this number changes from Object to Object. For few Objects, we can load only 2000-3000 per file, using the Wizard.

      If the volume of data to load is 1 Million records, it becomes a painfully manual job to load hundreds of CSV files of 5000 (or 10000) records per file.

       

      So, we are looking for an alternate approach that is robust and can handle greater number of records per file.

      Regards,

      Sreeram V

       

    • Vlad

      a. is the use of Connect PHP a correct approach to load Bulk data into Service Cloud
      b. is Connect PHP architecture such that it is inherently slow in loading data
      c. what are the best practices when using Connect PHP for such Bulk data uploads
      d. will the use of REST APIs give better performance compared to Connect PHP

      a. If possible, the data import wizard is preferred. It supports up to 1,000,000 records (CSV rows) in a single import. I suspect there is a problem that can be addressed if you see the console freezing or crashing. I would recommend submitting a service request with tech support to have that looked at.
      b. It's slower than the data import wizard. But you can add custom logging to your script to identify where it's taking longer and where it may be improved. For example if you have a ROQL query executed for every record, the query may be optimized, fields may be indexed etc.
      c. Use logging in a smaller batch to see where the delay comes from and try to improve/optimize that area of the code. Also, make sure to suppress on save(). This ensures that the rule base and Custom Processes are not executed and can improve performance significantly. Lastly, consider adding an explicit commit every 1000 records or so, instead of relying on the implicit commit when the script is done.
      d. To my knowledge, Connect PHP, REST and SOAP are all based on PHP. I would be surprised if you noticed better performance over REST, which was not really designed for bulk data uploads. SOAP may be better suited for that, but I think that the first options would be 1) data import wizard, 2) ConnectPHP, 3) SOAP

      • Sreeram Venkatramani

        Hi Vlad,

        Thanks for the detailed response.

        We would like to build logging into the PHP Scripts to identify performance bottlenecks.

        Can you please us provide pointers on how to add custom logging to the Connect PHP Script. A reference to Oracle documentation or a piece of code showing how this is implemented will help us understand the approach.

        Regards,
        Sreeram V

    • Sreeram Venkatramani

      Thanks a lot for the detailed response, Vlad.

      We will check with Oracle Support on the Data Import Wizard performance that we are noticing on our site.

       

      We have quite a few Custom Objects to load and we have created many custom attributes on the Standard Objects. Also, the average number of attributes to load per Object is around 40-50.

      Based on your experience, do you still think Data Import Wizard will be able to load 1,000,000 records (CSV rows) in a single import.

       

      Regards,

      Sreeram V

       

      • Vlad

        1,000,000 is not a guarantee that it will work regardless of the data size (number of columns). That said, 10K, 50K and up to 100K sounds reasonable to me. And at no point the console should crash/freeze. In my opinion it should either process the data with the system remaining responsive or fail gracefully. I think it's worth reaching out to tech support to advise on this. If you reach out to support make sure include as much information as you can, steps to reproduce etc.