Solved

MSCRM Principal Object Access Table

Posted on 2010-09-07
20
2,261 Views
Last Modified: 2013-11-24
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?
0
Comment
Question by:CAHCO
  • 11
  • 4
  • 4
  • +1
20 Comments
 
LVL 12

Expert Comment

by:GMGenius
ID: 33621066
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
 

Author Comment

by:CAHCO
ID: 33621466
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
 
LVL 12

Expert Comment

by:GMGenius
ID: 33621725
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
 

Author Comment

by:CAHCO
ID: 33621773
Can the record be owned by the organization and all users in the organization can see it?
0
 

Author Comment

by:CAHCO
ID: 33621775
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
 
LVL 12

Expert Comment

by:GMGenius
ID: 33622025
Can you run
select count(*) from activitypointer
This will tell you how many rows you have
0
 

Author Comment

by:CAHCO
ID: 33622080
select count(*) from activitypointer
returns 11999578 rows
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33624877
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
 

Author Comment

by:CAHCO
ID: 33628858
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
 

Author Comment

by:CAHCO
ID: 33666376
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 8

Expert Comment

by:Aftab_Khateeb
ID: 33667531
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
 
LVL 8

Expert Comment

by:Aftab_Khateeb
ID: 33687897
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
 

Author Comment

by:CAHCO
ID: 33694245
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
 
LVL 8

Expert Comment

by:Aftab_Khateeb
ID: 33697341
So are you saying that the users are seeing "Sharing" option in the menu even though they dont have share access rights?
0
 

Author Comment

by:CAHCO
ID: 33836035
Which menu are you talking about? Under the role screen I see sharing removed for all but Incident and Acivity.
0
 
LVL 8

Expert Comment

by:Aftab_Khateeb
ID: 33836543
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
 

Author Comment

by:CAHCO
ID: 35315024
Principal Object Table woes still continue.
I will move the other question about the Activity screen to a new thread.
0
 

Expert Comment

by:martinjras
ID: 37732663
0
 

Accepted Solution

by:
CAHCO earned 0 total points
ID: 38483247
We ended up truncating the POA table every week to keep the size small.
0
 

Author Closing Comment

by:CAHCO
ID: 38496261
None of the other posts helped us in solving the problem at hand.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now