reporting speed

[happymac]

Wanted to get some opinions as to external sources to speed reporting...

 

We have an FM Server 12 running a few databases with roughly 5 - 7 fm clients (12) connecting.  We have a table with roughly 1 million records that is a transaction table.

 

We generate a number of reports that access the transaction table (via relationships), displaying calculated sums ... as an example ... show me all revenue for a certain product for months January through March 2014.  We are using a relationship to access the data.  so one field will be a "sum" calculation of a Amount field in the related table.  Users enter a date range and pk product in global fields in the parent table which creates the relationship ... and the results are displayed on a report (list layout).

 

Problem is the reports are incredibly slow and although they used to be decently rendered when we had fewer records, now each report takes between 1 and 10 minutes to display.

 

I am looking into options to speed up this ...

 

a few options I am considering ...

 

1) use FM Server as an ODBC source and have an outside program handle the reporting.  So instead of the user loading the report within FileMaker, they would open a web browser or excel to see it.  We are pondering keeping things simple by using SQL or PHP / MySQL or just Excel, or going more extreme and using Python.

 

We may need to export the data to a SQL table and then have this outside reporting database do its' magic unrelated to FM, OR if we can get the speeds we could keep the data in FM and just have an outside program access the data via FM Server (ODBC).

 

OR ... we are pondering simply calculating all the records via a scheduled script (into a new table) and then the report would have fewer calculations to run when generating the report.  So maybe there are 100 transactions in January, and we create a single record summarizing (not a summary field, but a number field) in this new "reporting" table and the report pulls from this table.  This way we have no calculations running when the user generates the report.

 

Speed is the goal here ... we want to get this ripping a long so that instead of minutes to generate the report, it only takes seconds to pull up.

 

If anyone can throw their two cents into the ring, of how best to display large sets of data quickly ...

 

thank you for your suggestions!!

 

 

 

 

[ggt667]

Will making a server side script that generates reports as PDF of flagged records every 10 mins and emails these reports solve your issue?

 

My experience is that such reports uses much less resources on the server than on the client.

 

Using this approach instead of calculations: http://wethecomputerabusersamongst.blogspot.com/2012/11/filemaker-syncing-data-across-timezones.html will speed up your solution. This is a method for only doing calculations when modid changes.

[happymac]

PDFs won't work, but Excel docs would.  and that may be the best solution??  That the server creates the reports periodically throughout the day and the users just access those ...

[comment]

We are using a relationship to access the data.

 

I would suggest you first try moving the reports to the "traditional" method of finding the relevant records and using summary fields to summarize the found set.

 

If that doesn't produce a satisfactory speed improvement, you will have to denormalize your data to some extent: for example, by creating a table where each product/month combination is a distinct record with stored data.

[happymac]

thanks for your reply.

 

We are currently using the method you suggest, of finding relevant records and using summary fields, but it is painfully slow to generate the report, users are waiting 5-45 minutes.

 

The path you suggest is what I was planning to do, but I thought maybe there is a better long term solution that is much quicker by using something faster than FM to generate the report.  Ideally I'd prefer to stay in FM, but it is pretty ridiculous how slow these reports are, frustrating for sure.

 

the problem will only get worse since we are getting more and more data also.

 

Also ... I just found out, the users don't need to see the reports in filemaker, they only need them in Excel.  Currently we generate the report on screen in filemaker and they click a button which then sends it to excel (xml export, then vbs converts the xml to xls).  SO ... since they are looking at the data outside of fm anyway, I thought maybe a generation tool outside of fm might make sense anyway.

 

thanks again for your help.  I need a solution that is quite robust with significant speed...

[comment]

We are currently using the method you suggest, of finding relevant records and using summary fields

 

That is not what you said earlier.

 

 

maybe there is a better long term solution that is much quicker by using something faster than FM to generate the report.

 

Anything is possible, although I don't see why would Filemaker be quicker in providing the data to another application than to itself.

 

 

I just found out, the users don't need to see the reports in filemaker, they only need them in Excel.

 

In such case, producing the reports server-side might be well-worth exploring. You should also take a look at what's on the report layout that might slow down the process (try it with an empty layout to see if it makes a difference).

[happymac]

thanks again for your reply.

 

You mentioned ... "In such case, producing the reports server-side might be well-worth exploring."  curious what you would suggest that is server side?  this sounds like the best approach at this stage

[Wim Decorte]

The key is in NOT generating the reports on the fly if the data that is being reported on is static.  In that case,  use a server-side nightly script to pre-aggregate the data so you can call on it at will without incurring the penalty of recalculating the same thing over and over again.  Calculate it once and store it.  Report on the stored data.

[comment]

You mentioned ... "In such case, producing the reports server-side might be well-worth exploring."  curious what you would suggest that is server side?  this sounds like the best approach at this stage

 

What I meant here is that a user would submit a request for a report to be produced, which the server would execute at is earliest convenience. The main difference is psychological - since users are not waiting for the report to be produced on-screen, the delay becomes acceptable. That is unless the main cause of the delay is network speed; in such case the server will be actually faster. Which brings me to the more important point here: IMHO, you may be rushing to embrace a solution before you have analyzed the cause of the problem. For example: have you tested the speed when the file is taken offline?