Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MSCRM Principal Object Access Table

Posted on 2010-09-07
20
2,358 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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