I have a project to build reports using Microsoft Dynamics CRM 2011 & SSRS. The recommended data source is filtered views. I have all queries for the reports using filtered views.
I have discovered that filtered views are very slow; for instance, it takes more than 10 seconds to select top 1 * from [FilteredContact].
What are the best alternatives to this solution?
The Filtered Views are slow usually because of all of the security rules that have to be applied. This leaves a couple things to look at and potentially tweak.
Abandon Filtered Views All Together (don't use if you need to limit viewable records via security) This is not the easiest thing to do usually because any joins you need have to be done explicitly. This is also unsupported being that the next rollup could break your queries. If you're willing to accept the risk, this is the fastest method.
Improve your Security Model You'll need a SQL DBA to confirm this, but I'm guessing that the main reason of slowness is the security rules that have to be applied. Check out the Scalable Security Modeling with Microsoft Dynamics CRM 2011 white paper to see if you can change any of your normal practices to improve performance: