Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Report Builder record limitations if any? Expand / Collapse
Author
Message
Posted Thursday, January 3, 2013 8:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 28, 2016 9:45 AM
Points: 13, Visits: 92
Hi,

I am new to reporting services but familiar with the basics. I have a stored procedure that queries tables and dumps the data in a single table on a separate database. I have created another stored procedure that has the parameters and queries the new table for data. This stored procedure is also linked in Report Builder and is a shared dataset.

I can query and capture records up to 600,000 (8 min to run) fine in report builder and/or SharePoint integration but any records above that the report timeout.

The report server takes about 20 min to run 700,000 records and anything more than that times-out at an hour.

Should a report take 20 min or more to run that many records? Is there another reporting tool that is recommended?

Please comment
.
Thank you for your time.
Post #1402423
Posted Thursday, January 3, 2013 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 14,439, Visits: 37,844
the better question is why you think you need a report with 600K or more records in it? who is going to use it? no-one can possibly manually review that many records accurately; they might be able to skip over it for trends or something, but it is too large for human review.

It's time to do a little business requirements gathering to address the underlying needs for the report instead.
I would strongly recommend changing the report to be multiple alternate reports, that identify the exceptions that people would look for in that 600/700K records instead;

So if people actually use that report to say, "hey this field is blank and it shouldn't be", then produce an alternate report for that; if someone else uses that report to see rows related in a certain way, create another report for that.


Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1402432
Posted Thursday, January 3, 2013 10:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 20, 2016 2:20 PM
Points: 2,831, Visits: 2,625
You are probably timing out because the rendering engine is not designed to build that many pages.

I have to agree with Lowell, this needs to be broken down into several reports. If you can cram 100 rows onto a page, the report will have 6000 pages, more likely 50 rows per page and 12000 pages. There is no human that is going to look at a report like that.....ever. At best they will scan for certain details. It will be better to simply give them what they really need.
Post #1402468
Posted Thursday, January 3, 2013 2:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, February 28, 2016 9:45 AM
Points: 13, Visits: 92
thank you very much for your reply.
Post #1402554
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse