Post by DanielWhen using RS to query the CRM database, I occasionally need the
FilteredActivityParty table to find records in which certain users are
participating. However, because of the JOIN with FilteredActivityPointer and
the Party table being a N:N table, performance of my reports goes down the
drain.
Does anyone have tips to use the Party table w/o losing too much performance?
Answers could probably fill a small book. But to start:
1) First thing is to analyze whether or not you can use parameters to
feed to your reports (show only one user's activities, e.g.) (Search
CRMAF)
2) You can't add stored procedures to Microsoft CRM databases. But
you can create another, shadow DB (on the same server), link that DB
to CRM, and put stored procedures in there, and execute them to return
the result set for your report (just another datasource to SSRS). I
expect you are just using a query, which isn't compiled. Moving from
a query to a compiled SQL stored procedure will also give you
performance gains, even across databases. (Note you need to use the
YourMSCRMDatabaseName.dbo.Table notation when referring to CRM tables
from within this shadow DB.)
2) Try writing views, (or UDFs, or CTEs) in your shadow DB, to combine
frequently used tables, using just the columns needed. Even across
databases, a small view on the base tables can outperform filtered
views.
3) All Filtered views have crazy joins that account for the 4 ways to
apply security. If you can get away with using the non-filtered
equivalent, that will speed things up dramatically. (I realize that
you may need to use the filtered views to only show those records the
user has access to, but often, if this is for your company and not a
production app, you can get by with using only one filtered view on
the core table. E.g., if you are writing an "opportunity activity"
report, and in your company opportunities are secured by user, but if
they can see the opportunity they can see all activities associated
with it, then you can use the FilteredOpportunity view, and then the
non-filtered other views (or even base tables), and achieve the same
result much faster. Be careful, some fields only exist in the
filtered views - but there are often workarounds for that, too, that
are faster than using Filtered views) Note: Please be careful with
this one, make sure you properly think about what these views do, and
when to use them...
From my perspective as a old DB guy, I try to use the power of SQL
Server whenever I can. HTH, use at your own risk, etc...
Dave
-------------------------------------------
David L. Carr, President
Visionary Software Consulting, Inc.
Cell: 503-351-4207
Email: ***@earthlink.net
http://www.vscrm.com