In one of our projects, the customer set the requirements to create a reference data system and use this data in MS SharePoint Server 2013 and MS Project Server 2013. Artezio team built a database that contained data directories and an interface to fill them. To use these directories in MS SharePoint Server 2013 and MS Project Server 2013, it was required to create modules for synchronizing these directories. We didn’t face any problems with the creation of modules for directories synchronization in MS SharePoint Server 2013, however, a number of problems occurred with the data synchronization in MS Project Server 2013.
MS Project Server 2013 supports Lookup Tables that can be used as directories in a user interface. These Lookup Tables can be created/modified either from the administrator interface or using the program API (the so-called CSOM and PSI are available for use where CSOM is a wrapper on PSI).
Using CSOM within the project, we implemented a solution that synchronized data between the database and MS Project Server 2013. It was tested on a directory that contained 1,000 records. The download speed made up 1,000 records per minute in our test bed. The customer planned to use directories containing up to 20,000 records that could be uploaded approximately in 20 minutes (however, the real testing was not performed).
Having deployed the system at the customer’s side, it turned out that synchronization of directories with 20,000 records took several days, and an attempt to upload a similar directory in our test bed was constrained by limited resources in MS Project Server 2013 already at 10,000 records, and afterwards we had to restart the server.
After careful study of this question, it became clear that Microsoft developers had overthought a problem. In the MS Project Server 2013 database there is a special field LT_VALUE_SORT_INDEX of a DECIMAL type for hierarchical records that stores a sorting sequence to output a large-scale directory tree.
Below is a query result to an object in the Lookup Table that contains our directory inside the MS Project Server 2013 database:
select LT_VALUE_SORT_INDEX, LT_VALUE_FULL from [pub].[MSP_LOOKUP_TABLE_VALUES] where LT_UID=’7687E9AD-214E-47F7-A660-2D818586ED60′ order by LT_VALUE_SORT_INDEX
The specified field LT_VALUE_SORT_INDEX allows quickly outputting a large-scale tree using a single query without recursive construction. However, when adding or modifying any records (applying a user interface in particular), MS Project Server 2013 builds in its memory a tree of the whole directory (!) and then updates the LT_VALUE_SORT_INDEX field in the database, therefore the time to insert and save records depends on the number of records in the directory and, as a result, requires great resources!
Thus, at first the saving speed was very high (1,000 records per minute). After 5,000 records were saved, saving of one record took 2-3 minutes, and after 10,000 records were added, it took 10 minutes!
If the Microsoft developers had just sorted the records by the LT_VALUE_FULL field, the result would have been the same, and while saving, it would have just been necessary to save the full path correctly.
As an experiment, we decided to try a method of direct data load into the MS Project Server 2013 database updating the LT_VALUE_SORT_INDEX field and using sorting by the LT_VALUE_FULL field (a full name of the directory node path). In this case, the whole directory was uploaded within several seconds, but this method was rejected by the customer.
For syncing, we used the PSI technology that was much quicker according to Microsoft. The synchronization logic was made more complex, namely if large volumes of data were transferred, then data was saved in MS Project Server 2013 by sets of 500 records, not by one record.
Thus, a directory of 20,000 records was uploaded within approximately 1-2 hours on the customer’s powerful servers, and the customer was satisfied with the result. However, we faced some new challenges.
When synchronizing some records, the error “The FullName field can’t be longer than 255 characters” occurred. Moreover, these records couldn’t be entered from the MS Project Server 2013 user interface, which indicated the internal problem with MS Project Server 2013.
In fact, the full path of the hierarchical directory was longer than 255 characters for some real records. At the same time, the corresponding LT_VALUE_FULL field was created as nvarchar(MAX) in the DB allowing a user to save a much longer line, and the error was generated by some incorrect check inside the PSI library. Besides, if a longer entry was saved directly in the MS Project Server 2013 database, then it was displayed correctly.
We couldn`t wait for any patches from Microsoft due to lack of time, therefore it was required to make the sync algorithm more complex. When adding or changing a node, the full path of the node and its child nodes was checked, and if there were more than 255 characters, a special algorithm deleted excess characters from the node names specifying the ellipsis character at the end (“…”). As a result, the full path of records could look like this: “Russia / Severo-Zapa… / Murmanskaya o…/Apa…”.
Finally, below are some general recommendations to avoid project problems: