11 Replies Latest reply on May 31, 2018 1:44 PM by Charles THIERY

    Fastest way to update a value

    Gareth Spencer

      Can someone please advise whether it is faster to update the same value in one data field for a large quantity of leads (100K+) by:

      a) Smart Campaign

      b) List Import

       

      Thanks!

        • Re: Fastest way to update a value
          Charles THIERY

          Hello,

           

          I know that is a tricky question and I'm adding the best experts known in Marketo world to help us out?

           

          Grégoire Michel

          Sanford Whiteman

          Josh Hill [RingCentral]

           

          We have seen that marketo takes 7 hours for updating 1 field with same value for 170K persons through a smart campaign "data value change" command.

           

          Quite surprising in a database update world, we don't know if it's due to GDPR's effect that the marketo server is so slow (maybe everybody in marketo is making update right now).

           

          We wonder if there is way to perform this kind of action faster, maybe through a excel import?

           

          BR,

          Charles

            • Re: Fastest way to update a value
              Grégoire Michel

              Bonjour Charles,

               

              We are talking about 24k records /hours which is not that bad. First look at you campaign queue to see whether some other, higher priority activity is slowing down this one. If you are running a smart campaign which first flow step is a data value change, it makes it a medium priority one and that alone could explain the duration of the process.

               

              Alternatives:

              • I am not sure an import might have been faster, as AFAIK, it usually processes about 20k records / hour.
              • I am not sure the bulk API performance would be better, as to me it's similar to an import.
              • The standard REST API Lead controller has it's own limitation (100 calls / 20 seconds, 1MB body size, Nb of rows per calls...) it would take some computation to know if it can be faster and also some look at the API consumption from other services.

               

              -Greg

               

              Kenny Elkington

              1 of 1 people found this helpful
              • Re: Fastest way to update a value
                Sanford Whiteman

                we don't know if it's due to GDPR's effect that the marketo server is so slow (maybe everybody in marketo is making update right now)

                Ah, don't think it's GDPR.

                 

                But there is a limited rate of intake for any of the import processes, not only because of some overall resource governor, but also because (we can guess from the behavior) it's not one big transaction but rather row-by-row. (Otherwise, you couldn't get a row-level exception list from the bulk import endpoint, for example.)

                 

                If it were truly a SQL-level bulk import with no resource caps, then naturally it would be many, many times faster if run by itself -- but also would be far less cooperative with concurrent processes.

                 

                My personal preference is to do this via bulk import or list, though I don't know if there's a substantive difference.

                1 of 1 people found this helpful
              • Re: Fastest way to update a value
                Kevin Delgado

                This is from my personal experience. I like list import as its faster to make mass updates and doesn't queue up my campaigns and cause a bottleneck. The setback with this that I've noticed is that I have a program if an email address changes, update invalid email to false if it was true. If you have something similar, turn it off temporarily so it doesn't cause invalid email addresses to revive by accident just because of a capital letter difference. kdelgado vs Kdelgado.

                 

                One other thing is that list imports will only grab one "person" that matches the email address. If you have duplicates, it may not update the correct one you wanted. You may want to do the list import and then run a smart campaign to cover anything that may have been missed(field is blank or not [value]), which should be relatively low. Or export and cross-check, I do this sometimes with excel so I can verify.

                 

                Also when doing list imports, its not suppose to create duplicates with "persons" with the same email address but I see it happen still(very few though). I use lead source on my side that doesn't get overwritten. I make lead source, "LI_persona-update_20180528". If anything has that value and was created in the past 2 days, just to be safe, I review and merge/remove if necessary.

                 

                Another also to note, if you're updating a field that is synced to your CRM, you may want to turn off the sync temporarily so it doesn't use up too many api calls. Once the update is complete, then go ahead and turn on again. I prefer it this way as it bulk updates instead of one at a time or a few at a time. It uses less api calls from what I've seen. Hope the above helps!

                • Re: Fastest way to update a value
                  Selene Regan

                  I personally have found that list import is significantly faster than using a campaign for updating data.

                  • Re: Fastest way to update a value
                    Sharon Freas

                    For what it's worth, I tried updating one field on 600k+ records with a smart campaign. I killed the campaign when it had not completed after 4 days and we did a list import which was done the same day.

                    1 of 1 people found this helpful
                    • Re: Fastest way to update a value
                      Scott Nash

                      For simple, large-volume changes, Bulk Import is your best bet - it works in 1k chunks and does direct writes to the database.  Smart Campaigns are overkill for that amount of change. Performance can vary based on a few factors, mainly standard attributes versus custom attributes.

                       

                      I actually talked about both in my Under the Hood session at Summit.  And by total coincidence, I just recorded the audio to the slides yesterday.  Enjoy!

                       

                      Scott

                       

                      5 of 5 people found this helpful
                        • Re: Fastest way to update a value
                          Gareth Spencer

                          hi Scott

                           

                          Thanks for this.

                           

                          Does this slide:
                          Screen Shot 2018-05-31 at 10.50.52.pngsuggest that importing a list and "bulk import" via API in fact go through the same process exactly, once loaded?  And therefore does this take the same total time to process?

                           

                          Also, what reporting is available after bulk import via API, in case of errors that require amendment (v the error reporting available in list import)?

                           

                          i.e. What are the pros and cons of each method?

                           

                          Thanks.

                        • Re: Fastest way to update a value
                          Charles THIERY

                          ok, after testing both way, here are the figures within a same instance:

                           

                          - 170 000 persons one field updated = 420 minutes // Smart campaign

                          - 170 000 persons one field updated =   30 minutes // List import

                           

                          I'm going to compare with bulk API too.

                           

                          BR,

                          Charles

                          2 of 2 people found this helpful