CODEWITHSUNDEEP
sql-serverreporting-servicesdynamics-crm-2011ssrs-2012
Chanom First
Chanom First

Reputation: 1136

how to increase speed report in ssrs

I work with Reporting Service in MS CRM I have a problem about Report speed.

My report is slow when run on MS CRM For example in SSMS My Query run about 12 second but when I run on MS CRM it's run about 2 minutes

and some report It's run slow then I get error and report can not display I don't know how to fix this problem

So. I want to know some tips to increase report speed and I have 1 question what it's faster between 2 query below 

SELECT * FROM [A] LEFT JOIN [B] ON a = b

AND

SELECT * FROM [A] LEFT JOIN ( SELECT B1,B2 FROM [B]) b ON a = b.b1

Thank you.

Upvotes: 0

Views: 1012

Answers (1)

Shane
Shane

Reputation: 790

The MSCRM Report Viewer has a timeout setting and will timeout if the report is not successfully generated in that time (even if the report is still generating in the background - you can test this by running the same report using reporting services SSRS).

The supported way of generating reports in MSCRM is using the views and\or 'filteredviews' as they are called which has all the linked tables for security etc. build in & this makes it very slow. if the security/formatting etc. aspect is not required you can SELECT * FROM Base or ExtensionBase tables that will speed up the generation time (a lot) - solving the generating time issue you are having.

also your question: the second select should be faster (just) based on the select * part as the second join (B) will only use the B1, B2 on the join. it will do this piece first then join it to A accordingly.

Some good reading on the topic : Understanding Performance Mysteries

Upvotes: 1

Related Questions