Matrix report slow but query fast

Question

Hi,

   Recently, I am doing a matrix report. This is my query.

 

with cte
as
(
select issuedCommonName,MAX(CertExpirationDate) as CertExpirationDate ,MAX(CertEffectiveDate) AS CertEffectiveDate
from dbo.Certification
group by issuedCommonName
)

select  u.GUID, u.FirstName,u.LastName,u.Email,u.Territory, c.CertExpirationDate, c.CertEffectiveDate
from dbo.Base_Users u
left join cte c
on u.GUID = c.IssuedCommonName

This query roughly return 260K records in 4-5 seconds.

And for the matrix report, it shows like this

Territory, UserGUID, First Name, Last Name, Email, Cert Effective Date, Cert Expiration Date

The parent group is Territory, and the child group is UserGUID, the display User's firstname, lastname, email.certEffDate, certExpirationDate,

My question is the matrix report is taking forever to load . How could this happen? And is there anyway to speed up the loading in a reasonable time? For example, reporting snapshot?

Thank you

---

Rui

Answer 1

Hello,

Is it that it takes long for the first time only? and then from the next time it is fast? Please let us know

Answer 2

260k is too big.... What id you filter your query to return let me say 100 rows , does it work faster?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

Hi,

  It always takes long time, I mean forever to run... I tried to leave it for a night and its just stuck at the loading... But if I choose to display top 100 then it is pretty fast.

---

Rui

Answer 4

Well, then return small portion of the data to report and drill down if users require addition info.

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

Hi There

How many column matrix reports is generating

Have you tried to use tablix report instead? Would that issue be the same for tablix report?

Please update

Many thanks

Syed

Answer 6

One point which I am keen to understand is that from a visualization perspective how would this report help enduser with 260K records getting loaded into it. This would be a HUGE data as Uri mentioned. Is this an excel dump kind of report which would be fed to downstream systems (In that case you may render the report directly to Excel in a share folder from where user can access the report.)?

Please let me know.

Regards,

---

Phani Note: Please vote/mark the post as answered if it answers your question/helps to solve your problem.

Answer 7

Hello,

Here are some articles about processing large reports and troubleshooting report performance for your reference, please see:
Process Large Reports: http://msdn.microsoft.com/en-us/library/ms159638.aspx
Performance, Snapshots, Caching (Reporting Services): http://msdn.microsoft.com/en-us/library/bb522786.aspx
Troubleshooting Report Performance: http://msdn.microsoft.com/en-us/library/bb522806.aspx

Regards,
Bin Long

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Bin Long

TechNet Community Support

Answer 8

Thank you all for the help.

Like Phaneendra and Uri said, this kind of large data should not be presented to the end user. BA didnt release this kind of large data when she talked to the client. We already update the report by showing the count of users for each country. and if the user would like to see the details, they can click through the country name and lead them to another detail report.

Thank you.

---

Rui