Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
> 
CRM 2011 SSRS 2008 - report performance

回答済み CRM 2011 SSRS 2008 - report performance

  • Friday, April 13, 2012 11:07 PM
     
     
    Sign In to Vote
    0

    Hello Everybody,

    We’re running SSRS reports in CRM 2011. The reports are a bit complex with many parameters, grouping, and drill-downs. It’s also brining substantial amount of data.

    We’re restricted to use stored procedures to use it for reports so rdl datasets are doing all work. So, we have some performance issue.

    Please, advise what would be an efficient way to improve our reports’ performance. If it’s the only store procedures then we could probably discuss to get the permission to use it for our reports. But, probably we could look at a combination of some approaches that could exist and help.

    Thanks

     

All Replies

  • Saturday, April 14, 2012 9:39 AM
     
     Proposed Answer
    Sign In to Vote
    0

    Hi,

    I have similar issues before with SSRS 2008 reports and I was using stored procedures (SP). The front end was not CRM. The problem was stored procedures.

    I have been using CRM 4.0 and CRM 2011 for last 4 years and have created several hundreds of reports, using "TSQL/filtered views" and "fetch xml". I never faced performance issues.

    In your case you said you are forced to use stored procedures. That is the problem. You report will run fine with less amount of data and direct use of filtered views. Your reports are complex and hence using complex queries in your SP. I am assuming you have lots of joins and group By. Also the data is too much.

    Try few things:

    1) Try breaking your reports into many reports. One report each for one area. This way you don't have to call all data in one report.

    2)  May be there are some badly written queries. When I say badly written means queries not optimized and takes too much time. The best way to find out is to the execution plan of these queries or also try SQL profiler.

    3) Try restricting your report from first parameter. Sometimes when you select first parameter, based on that you need to fill in a second parameter option set. If there are hundreds of records then also reports will be very slow. In that case remove those parameters, if possible.

    4) Run your reports in IE from reporting server and see whether it is running slow there. Try Googling caching techniques of SSRS and have those incorporated.

    5) If you can have CRM 2011 views instead of reports, then go for that.

    6) You can also go for middle data warehouse (another database). The drawback is you will have to refresh this data warehouse with your CRM data every few hours. The incorporate SSAS (analysis services) and de-normalize the data. Reporting on this data is very quick, but you will not get the fresh data (it will be few hours old).

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.

    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

     
  • Saturday, April 14, 2012 9:47 AM
     
     Answered
    Sign In to Vote
    0

    Please follow some tips and tricks to improve your reports performance.
    Here are the links that will help you.

    Improve performance of Reports

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.
    Mubasher Sharif
    Check out my about.me profile!
    http://mubashersharif.blogspot.com
    Linked-In Profile
    Follow me on Twitter!


    • Edited by MubasherSharif Saturday, April 14, 2012 9:47 AM
    • Proposed As Answer by MubasherSharif Saturday, April 14, 2012 2:21 PM
    • Marked As Answer by al-dol Monday, April 16, 2012 7:20 AM
    •  
     
  • Sunday, April 15, 2012 7:37 AM
     
     
    Sign In to Vote
    0

    Thank you so much, Ashish, for your response and your advices.

    Sorry, for some confusion we're not forced to use store procedures instead we're not allowed to use store procedures. This is why I thought the perfoprmance is not so good as all work is done on client and if we used store procedures it would be on a server that would probably bring better performance.

    As you might know in CRM we're also not using direct access to db but using Filtered entities such as FilteredAppointment, etc. Do you know any way we could do some indexing in CRM that would help to improve some performace?

    Thanks

     
  • Sunday, April 15, 2012 7:39 AM
     
     
    Sign In to Vote
    0

    Thank you MubasherSharif. That is definitely helpful link you provided.

     
  • Sunday, April 15, 2012 8:50 AM
     
     Answered
    Sign In to Vote
    0

    Hi,

    I agree in CRM we do everything through filtered views (in reports). Indexing could be a possibility. Since we don't directly do anything on a CRM database, I don't know what are the best practices for indexing on  CRM 2011 database. I would assume indexing would have already been part of the database. Read some Microsoft articles around custom entity tables indexing.

    Indexing requires careful planning and should be implemented properly, or else can have negative performance. I suggest google some article on SQL indexes and best ways to implement for CRM 2011. Meanwhile concentrate on the best ways to write efficient queries.

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.

    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

     
  • Monday, April 16, 2012 4:53 AM
     
     
    Sign In to Vote
    0
    Hi,

    If any of the responses answered your question and you are satisfied, please mark the response as an answer and vote as helpful. This will help others to search on similar problems.

    Thanks in advance.

    Ashish Mahajan, CRM Developer, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

     
  • Monday, April 16, 2012 7:21 AM
     
     
    Sign In to Vote
    0
    Thank you so much, Ashish. I really very much appreciate your help.