MSCRM Principal Object Access Table

We are using MS CRM 4.0 and are facing some performance issues.
#1 - We have been noticing that the Principal Object Access grows very fast and then the system slows down. Looking at the processes running on the database we found that most of the queries running on the database are inserts and updates on this PrincipalObjectAccess table and they take time. What is the reason for this tale growing and can we clean up the data in this table?
#2 - When users go to the Workplace tab in CRM, the select query to populate the activity screen take a long time or eventually times. The query for this is "ActivityPointerBase". If I change the Paging Limit it works for some time, but after some time it again starts timing out.

What's the best way to deal with this?
CAHCOAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
CAHCOConnect With a Mentor Author Commented:
We ended up truncating the POA table every week to keep the size small.
0
 
GMGeniusCommented:
The PrincipalObjectAccess is where all the shared record information is recorded.
Do you have a very large number of activity records that are shared?
0
 
CAHCOAuthor Commented:
In our setup all customer records are accessible to all users. The only check we have is at the role level to distinguish between a read-only or read-write access.
So should there be explicit need to sharie records. Does CRM share the customer record with the user before it opens it up. Is there a way to tell CRM to by pass the sharing?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
GMGeniusCommented:
It depends if you have had some development done to setup some form of auto share.
Each record is owned by a specific user and if other users need access to it then that record must be shared with them.
 
0
 
CAHCOAuthor Commented:
Can the record be owned by the organization and all users in the organization can see it?
0
 
CAHCOAuthor Commented:
This is the query that times out or takes along time. changing the paging limit (ex: Top 61 to Top 91) helps for some time.

)select  DISTINCT  top 61 activitypointer0.Subject as 'subject', activitypointer0.ActivityId as 'activityid', activitypointer0.ScheduledStart as 'scheduledstart', activitypointer0.RegardingObjectId as 'regardingobjectid', activitypointer0.PriorityCode as 'prioritycode', activitypointer0.ScheduledEnd as 'scheduledend', activitypointer0.ActivityTypeCode as 'activitytypecode', activitypointer0.RegardingObjectIdDsc as 'regardingobjectiddsc', activitypointer0.RegardingObjectTypeCode as 'regardingobjecttypecode', activitypointer0.RegardingObjectIdYomiName as 'regardingobjectidyominame', activitypointer0.RegardingObjectIdName as 'regardingobjectidname' from ActivityPointer as activitypointer0 join ActivityParty as aa on (activitypointer0.ActivityId  =  aa.ActivityId and (aa.PartyId = @PartyId0)) where ((activitypointer0.StateCode in (@StateCode0, @StateCode1)) and (activitypointer0.ActivityTypeCode != @ActivityTypeCode0 or activitypointer0.ActivityTypeCode is null) and (activitypointer0.DeletionStateCode in (@DeletionStateCode0))) order by activitypointer0.ScheduledEnd as
0
 
GMGeniusCommented:
Can you run
select count(*) from activitypointer
This will tell you how many rows you have
0
 
CAHCOAuthor Commented:
select count(*) from activitypointer
returns 11999578 rows
0
 
GMGeniusCommented:
Thats a awfull lot of records
Try the same query
select count(*) from activitypointer where deletionstatecode!=2
I would be looking at trying to remove some, are there really that many real activities?
 
0
 
CAHCOAuthor Commented:
select count(*) from activitypointer returns 12016891 rows
select count(*) from activitypointer where deletionstatecode!=2 returns 12016836 rows

The number of activities is correct. We have approx 5 million beneficiaries and we create an activity whenever we have any type of contact with them , phone, letter, fax etc. We have been doing this for over a year now.
0
 
CAHCOAuthor Commented:
Is there a way to tell CRM to not share the records. In our scenario, if a person has access to the entity thru his role, he should be seeing everything. So there is no need to share each record and make the PrinicpalObjectAccess table so large.
0
 
Aftab_KhateebCommented:
Yes, We can remove share priviliges to any role for that particular entity, in that way people will not be able to share the records.
When you open a role (navigating by selecting Settings > Administration-> Security roles), You could find that "Share" as last option (right after assign) columnwise at the top. In order to remove the share rights, just make that circle for that particular entity (on the left) empty (without any access rights). In that way you could avoid people from sharing records.

Also you could classify a set of people as "Team" (Under administration) and classify the users who are not having share rights etc.

Hope this helps
0
 
Aftab_KhateebCommented:
Hi would like to know if you were able to execute the steps i mentioned? If you require any further help let me know
0
 
CAHCOAuthor Commented:
I looked at the roles and the share option is already removed for all entities in all roles, except the Activity and Incident which are set to user level.
0
 
Aftab_KhateebCommented:
So are you saying that the users are seeing "Sharing" option in the menu even though they dont have share access rights?
0
 
CAHCOAuthor Commented:
Which menu are you talking about? Under the role screen I see sharing removed for all but Incident and Acivity.
0
 
Aftab_KhateebCommented:
Menu which is "More Actions". If you land on any entity you might have a menu right below the view.
You would find "Sharing" as a menu item under that menu. Let me know if you still didnt get it
0
 
CAHCOAuthor Commented:
Principal Object Table woes still continue.
I will move the other question about the Activity screen to a new thread.
0
 
CAHCOAuthor Commented:
None of the other posts helped us in solving the problem at hand.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.