We have a need to run the Production Detail Report for an insane amount of jobs. (15,000+)…we only need some of the data returned, so I have duplicated the report and the dataset queries are only grabbing the needed fields, I have removed anything un-necessary.
When I run the report for 50 jobs, it takes about 25 seconds. When I run it for 100 jobs, it takes about a minute. When I run it for 500 jobs it takes about…oh, well it’s been 2 hours and it still is not done.
Does anyone have any ideas on how this can be done w/o using up one of our task agents for 6 months to run a big report? I’d rather not have to break it down to 150 individual reports of 100 jobs each, but if that is what it ends up coming to…
I am outputting it to CSV, and the modified report only has 3 or 4 datasets (I removed everything that was not needed) and only 10 fields being queried and shown. But the RDD for it is HUGE…it has 15 data sources, 7 relationships and 77 calculated fields.
I have no idea as to what the fancy SQL queries being ran for this are, so I can’t recreate it without having to reverse engineer what is going on by staring at SQL trace files for weeks on end.
@jhecker Not to not answer you question, but what data are you looking for? SSRS is not efficient, I would try to make a BAQ / Dashboard and then only if needed a report from that.
You could try to grab the query from the SSMS Activity monitor and check the execution plan to see if has any suggestions.
As a test, I have a dashboard that calculates the productivity of every operation on a completed job. That dashboard ran 47,426 jobs in 10 minutes
What I have been asked to narrow the report down to is this:
JobNum
PartNum
ProdCode
QtyCompleted
Unit (this is always “EA” for each…so I can probably get rid of that)
Order / Line / Release
Order Value
Labor Costs
Burden Costs
Material Costs
Subcontracting Costs
Total of the previous 4 costs
The last 5 items (the costs) are recursive from the top level job through all sub-jobs that it took to get the top level complete. So if the top-level job we are running the report for required 25 other jobs to go through first (for the subcomponents)…well, it starts to add up quickly. Especially with the number of top-level jobs that are being thrown at this.
I think @Michael_Ramsey is on the right track. JobAsmbl holds all of that costing. Look in job tracker under assemblies costs and click retrieve. If those are the costs you need then this should be quick.
