lookupService.ApplyChanges(lookupTable) --> how many changes in bulk is reasonable ?

Discussions concerning general integration topics.

Moderator: Phil Barton

Post Reply
toddsou
Posts: 51
Joined: Wed Jul 25, 2012 9:39 am

lookupService.ApplyChanges(lookupTable) --> how many changes in bulk is reasonable ?

Post by toddsou » Thu Apr 05, 2018 11:30 am

Hello-

It is my understanding that every time I call lookupService.ApplyChanges(lookupTable), the server will insert any new rows, update any edited rows, and remove those that are so indicated from the corresponding SPS Lookup table. After it finishes all that, I assume it kicks off a job to re-index the table for searches to be up-to-date with the latest edits. Maybe other cache updates, etc are also going on as part of this...?

In any case, if my program has lots of edits to make to a particular lookup table, it seems like I should batch as many edits as possible before calling ApplyChanges( ) on that lookup table, in order to minimize the overhead associated with saving all those changes.

So my questions are: what is a reasonable batch size limit to shoot for? Does the system have any arbitrary limits I should avoid ? What types of success have other folks had in doing the same thing ? Can I tell the indexer to disable itself while I perform my processing and then tell it to turn back on when I'm done so it only needs to make a single pass, thereby speeding up the entire process of sync'ing my lookup data edits?

Thanks.

mhoutz
Posts: 11
Joined: Wed Dec 14, 2016 5:22 pm

Re: lookupService.ApplyChanges(lookupTable) --> how many changes in bulk is reasonable ?

Post by mhoutz » Thu Apr 05, 2018 3:22 pm

Hi,

Why does your program need to make so many edits to a lookup table? Is there a business problem you are trying to solve?

Batch updating the lookup tables may cause performance issues, but if you tell me what your overall goal is, maybe we can find a better way.

Mike

toddsou
Posts: 51
Joined: Wed Jul 25, 2012 9:39 am

Re: lookupService.ApplyChanges(lookupTable) --> how many changes in bulk is reasonable ?

Post by toddsou » Fri Apr 06, 2018 8:56 am

Hey Mike-

Consider the case where we buy another firm. We would want to import all those new Contacts.

-Todd

BobRichards
Posts: 1006
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: lookupService.ApplyChanges(lookupTable) --> how many changes in bulk is reasonable ?

Post by BobRichards » Tue Apr 10, 2018 4:35 pm

Sorry for the delay. I am still trying to find an appropriate contact who has experience in this area and can respond to your question. Please be patient. Thanks.
Bob Richards, Software Developer, SoftPro

BobRichards
Posts: 1006
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: lookupService.ApplyChanges(lookupTable) --> how many changes in bulk is reasonable ?

Post by BobRichards » Wed Apr 11, 2018 4:57 pm

Sorry this has taken so long. It is hard to find users that have actually tried to put a large number of rows in a Lookup table. This is not a common operation. After speaking to a few folks, I have a few observations. I hope it helps but I cannot give you a number that you cannot exceed.
  • You are correct that you should batch as many row CRUD operations before you call ApplyChanges(). This is the most efficient (read - fastest) method.
  • The number of items that can be inserted does not seem to have a high limit (one customer added 100,000 new rows to a table before calling ApplyChanges()), but if the count of rows is "too big", the operation will timeout. This is a function of mid-tier loading and I cannot give you an actual number of rows you can add.
  • You cannot control the index operation.
Bob Richards, Software Developer, SoftPro

toddsou
Posts: 51
Joined: Wed Jul 25, 2012 9:39 am

Re: lookupService.ApplyChanges(lookupTable) --> how many changes in bulk is reasonable ?

Post by toddsou » Thu Apr 12, 2018 12:38 pm

Thank you very much for the research.

Does the timeout result in a specific Type of exception that I can catch? or some other means of detecting that particular failure vs any other failures ?

....maybe I can fashion some sort of retry behavior with a fraction of the original attempt...

Regards-

Post Reply