SoftPro Developer Forum

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.

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

Hey Mike-

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

-Todd

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.

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.

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-