Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Tuesday, July 22, 2014 7:37 AM
Points: 13, Visits: 88
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: Yesterday @ 3:48 PM
Points: 12,908, Visits: 32,005
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1402432
Posted Thursday, January 3, 2013 10:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:02 PM
Points: 2,818, Visits: 2,560
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: Tuesday, July 22, 2014 7:37 AM
Points: 13, Visits: 88
thank you very much for your reply.
Post #1402554
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse