Any suggestion on designing the process of dumping all user id and associated Groups in Database.
We have some requirement to expose this data to end user via Database.
Discussion posts and replies are publicly visible
If the user and group data is stored in the database, you can create a simple process to dump the data to an Excel file using the Export Data Store Entity to Excel smart service or one of the smart services available in the Excel Tools plugin, depending on what Appian version you're running.
If the user and group data is not already stored in the database and you want to store it there, you could create a process that leverages the getdistinctusers() function. You could have this function loop over each group in your application to retrieve its users, and then call the user() function on each of them to get the required metadata of the user. Then once you have the data in the format you want, you could write it to the database.
Does this help?
Alternatively, you could leverage the getgroupsformemberuser() function from the People Functions plugin, which may make your dataset cleaner, given you want groups by user, instead of users by group.
Yes, performance is always a concern with these types of activities. I recommend designing your model to batch these activities at a reasonable number of iterations and wait for the previous batch to complete before executing the next one. You should also leverage the Start Process smart service to ensure that requests are properly load balanced.
Finally, you can get around Appian's 1,000 node instance limit by checking the 'Delete previously cancelled/completed instances' selection in the relevant nodes' Other tabs. Keep in mind that this limit is in place for a reason, so be sure that your process is performant before making this change.