I was working with a customer today, troubleshooting a report reading MAS 90. The report had a subreport at the detail level and he said that the full report could take up to 24 hours to run. I tested the subreport and found that the selection formula was not being converted into a WHERE clause in the SQL of the subreport. So every instance of the subreport (thousands) had to do a serial read of the entire table. The subreport seemed pretty simple so my first approach was to recreate it and test it as a stand alone report. It took only a few seconds to run and I could see the WHERE clause in the SQL. But as soon as I inserted the new version to replace the existing subreport the SQL would no longer generate a WHERE clause.
So then I studied the relationship between the main and the subreport. I found it was possible to eliminate the subreport and link the subreport data directly to the main report, as long as I didn’t add any filters to the new table. This configuration would still require a serial read of the table, but it would only need to do one serial read, not thousands. The report ran in a few minutes.
But later I started to wonder if anyone else had run into this quirk. A little web sleuthing turned up a 2009 document listing MAS 500 Best Coding Practices. And this little nugget was in point #9:
Avoid subreports, most cases the constraint can’t be passed as the where clause of the subreport query.
So I wasn’t imagining things. I don’t know if this ‘feature’ is still part of the current product. But if you are a MAS user with slow subreports, you might want to check your WHERE clause. And if you need help finding an alternative configuration, give me a call.