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:
Your options are very limited. CRM will only allow you to use the filtered views however you can query information from external databases by creating a linked server and using 4 part naming or having the other database on the same SQL instance. You could for example hold CRM data in a data warehouse and report off that more example
I would however be more worried about the performance of your CRM server. On one of the CRM instances I look after I tried the same query and it returned in <3 seconds on a table size of 21,971 rows.
I would also say that that your query is also not a normal one you would run as to do the top 1 it will have to query a lot of tables. A more normal query like showing the contacts for a company would be a lot quicker.
