Subscribe to RSS
There is a lot of discussion back and forth about reporting speeds in business and accounting applications. I recently did some benchmark testing as shown in this video below to highlight the difference.
MAS90 and MAS200 run on a flat file database. The way it is written, internal ‘hard coded’ reports run fairly quickly, but reports and external applications that access its data via ODBC (such as Crystal Reports) can be painfully slow.
This issue becomes even more obvious when reports use more than one ‘left outer join’ which means you are matching up data from two places where there isn’t a guarantee data exists to link in both tables. An example of this is an Invoice History report showing salesperson information from another table but yet MAS 90 doesn’t require a salesperson to be associated with an invoice.
Dynamics NAV (Navision) and many other applications (like SAP Business One) use a high performance relational database like Microsoft SQL Server
Everything about this test is stacked in MAS 90’s favor. The MAS 90 example was run on a native Core 2 DUO laptop. The SQL Server example wasted tested on a VMWare virtual machine running on that same laptop. That means the environment is about 30% slower than the native machine.
Yet the results of the test are staggering. The SQL version of the report runs in an order of magnitude faster.
Take a look here:
Tags: business intelligence, ca, Crystal Reports, ct, Florida (FL), New Hampshire (NH), new york city, nj, ny, pa, reporting performance, speed
Why didn’t you use the MAS200 AR InvoiceHistory.wrk file in the reports menu? The .wrk table is populated on the server, with just the results sent to the workstation. Many times faster than your example.
Should mention the SQL client and bandwidth issues that slow performance other than report printing, and require more network bandwidth.
Steve
Steve,
The .wrk is only populated when the code is executed by the live application. i.e. when you run an AR invoice history report from within MAS. Technically you could modify the template, and pull from that table (if it has all the fields you want, )but you would still likely be bringing in data from other tables.
Not sure what you mean about network bandwidth. When you run the crystal report through SQL, crystal requests a single query where the join work is done on the server and the resulting records are sent to Crystal. With MAS90, all of the joining is done on the client, and done so very slowly.
More bandwidth never hurts any any scenario.
Hi, Mark
Check out the table definition of the .wrk file – it has almost everything you need. I’ve only had to link another file once or twice in over two dozen reports. Even with a linked table(s) it’s still really fast. I think the .wrk file is generated before the link statement is executed. The link is executed on the server, too, and only the results are sent down the wire to the workstation.
My comment about bandwidth referred to data entry and workstation connections, rather than report speed.
Steve
I’m pretty sure even if it could be processed on the server, that wouldn’t be the case for MAS90 as there are no providex processes that run on the server. It is simply shared files.
This was just a random example I pulled because that particular file tends to be large. Plus if you have data from non Business Framework tables, there are no work tables.
I’m double checking the issue as to whether the .wrk is even regened.
I doubled checked with my crew over at http://www.90minds.com just to be sure.
Indeed, you can’t use the.wrk tables. The only time the data is populated and current is when you run a built in standard report in the 4.x version modules. So you could take an existing report, such as Invoice History, and create a version with what you want and could be faster. But if you fundamentally want something different, your going to need to depend on the base tables. If you are using anything other than Crystal, you’ll not even be able to use the .wrk tables.
The only real solution in the MAS90/200 family is to get a product like DSD SQL Mirror as described here:
http://www.clientsfirst-us.com/blog/sage/sage-mas-90-200-mas90-mas200/use-microsoft-sql-server-with-mas90-and-mas200/
[...] addition, I also created this video showing the speed comparison between reporting in MAS90 and MAS200 versus Microsoft Dynamics [...]