Filemaker 4.0 v3 Stressing Server?

[proton]

Hello People,

I did a database for a company for them to record transactions and track marketing data. The transactions database has a couple hundred thousand records. I did a number of reports for them and these work fine. The database was also functioning properly. It is running on Filemaker Pro 4.0 v3 on a Dell PowerEdge Server. The server had a Pentium III 450 Mhz processor, with 128 Mb of RAM (Windows NT 4.0). They also use the server as a general File/Printer Server and the Peachtree Accounting is hosted on it.

Everything was fine, but then they said they needed some 'special' reports. The transactions are recorded with date, product, customer, price, etc. Now in the original reports, the data is represented going down the page.

So for example, if the product report is generated the product is listed as a subsummary and then below in the details section you get the date sold, customer sold to, price etc.

In some cases they wanted summaries, so instead of seeing the details they would see the product (subsummary part), then in the details part they would see the month and amount sold and price, etc.

They decided that they wanted the months going across the page at the top (like a heading) so you have Product, Customer, Year, January, February, Mar., etc going across at the top.

Then in the details you have the year (1999, 2000, etc.) and under each month heading you have the quantity sold and the price. They had a number of 'management reports' that they wanted like this. One by customers, one by product, one by salesman, etc.

Now this is not an easy thing to do. What I did was I created a couple of self-relationships that link the product and the month and the year (PMY), and one that linked the customer, product, month, year (CPMY). Then I created calculated fields that summarised the total amount (quantity) sold based on the relationship (e.g. PMY). I also created calculated fields that summarised the total cost based on the relationship. These fields used the IF function, as in - If month = January then the quantity sold is equal to the value in the quantity field, and similar for cost.

So for each record I had two fields for each month. One to hold the quantity and one to hold the cost. Then I had to create calculated fields that would summarise the above created fields. So one would summarise the January Quantity field based on the relationship, one would summarise the January Cost field based on the relationship, one would summarise the February Quantity field based on the relationship, one would summarise the February Cost field based on the relationshop, and so on.

The goal of this was to get the quantity and cost of a product sold based on month and year. So for each record four calculation fields were created for each month, and there are twelve months so that is 48 calculation fields per record.

For the reports now, they wanted the reports summarised - some by Customer and some by Salesman and etc. So I had to create a number of subsummary parts. Each report has about three subsummaries, as well as twenty-four of the calculated fields (the summary calculated fields) - twelve for quantity, and twelve for cost).

I tested the changes with about 10 records and it worked, but very slowly. When I imported the actual data (a hundred and something thousand records) and tried to run the report, the database crashed. None of the reports would work.

My view was that maybe it was too much stress for the server dealing with so many calculations and so much data. I checked the NT server resources and when the report was being run, the memory was depleted and the CPU was maxed out (100%). The CPU just flatlined at 100% and stayed there, and Filemaker stopped responding.

I told them that they would have to buy more memory and another processor (either another 450Mhz to run in dual cpu mode or a Ghz to replace the 450). They bought the memory (they now have 384 Mb SDRAM). One of the reports is working with two months data, but all the rest are still crashing.

I am just wondering if I am right in assuming that the CPU is remaining problem. Can any of you experts point me in the right direction? Thanks in advance.

[Kurt Knippel]

The problems that you are experiencing are not due to the server, or are they directly related to Filemaker. It is actually the needs of the customer and your implementation of those needs that are the problem.

Basically databases are not really good at those matrix kind of reports. There is no database that will do that kind of thing well, even the dedicated reporting engines will likely have a problem.

I have two suggestions for you.

One is to actually Export the summaries (of the lowest level) to a clone of the file, and print the reports from there. Depending upon how many records are being summarized this can have a HUGE impact on the preformance. Imagine that each month has 1,000 records, by summarizing you reduce the number of records by a factor of 1,000 for running the reports.

As an example of the time reduction, I had a database of 11,000 records which took like 3 hours to run a complex summarized matrix report similiar to what you are describing. By exporting the summaries, which took about 2 minutes, I was able to reduce the report generation time down to about 3-5 minutes. Still not "fast", but far better than the other way.

The second option is to export the data to Excel and develop the reports there. Excel is VERY FAST with these kinds of reports, since it is designed as a matrix. Although it is possible that Excel cannot handle the number of records here.

Something else that you can combine with option 1 is to reduce the scale of the reports. Perhaps they do not really need to see entire years represented on the reports, but might be able to restrict each printout to a single year, or quarter or month. You can then work on a smaller set of data. Although you might need to now generate several runs of the report each showing a different set, still this should be quicker.

Bottom-line: Do not run matrix summarization reports out of Filemaker on more than a couple thousand records max.

[proton]

quote:

---

Originally posted by CaptKurt:

The problems that you are experiencing are not due to the server, or are they directly related to Filemaker. It is actually the needs of the customer and your implementation of those needs that are the problem.

Basically databases are not really good at those matrix kind of reports. There is no database that will do that kind of thing well, even the dedicated reporting engines will likely have a problem.

I have two suggestions for you.

One is to actually Export the summaries (of the lowest level) to a clone of the file, and print the reports from there. Depending upon how many records are being summarized this can have a HUGE impact on the preformance. Imagine that each month has 1,000 records, by summarizing you reduce the number of records by a factor of 1,000 for running the reports.

As an example of the time reduction, I had a database of 11,000 records which took like 3 hours to run a complex summarized matrix report similiar to what you are describing. By exporting the summaries, which took about 2 minutes, I was able to reduce the report generation time down to about 3-5 minutes. Still not "fast", but far better than the other way.

The second option is to export the data to Excel and develop the reports there. Excel is VERY FAST with these kinds of reports, since it is designed as a matrix. Although it is possible that Excel cannot handle the number of records here.

Something else that you can combine with option 1 is to reduce the scale of the reports. Perhaps they do not really need to see entire years represented on the reports, but might be able to restrict each printout to a single year, or quarter or month. You can then work on a smaller set of data. Although you might need to now generate several runs of the report each showing a different set, still this should be quicker.

Bottom-line: Do not run matrix summarization reports out of Filemaker on more than a couple thousand records max.

---

Believe me CaptKurt, I HEAR YOU!

Here's where the non-Filemaker aspect of the explanation comes in. Initially when they asked for management reports, I designed the database to just find the records and export to excel where I generated a pivot table to layout the reports. This worked extremely well, and they used those reports for awhile.

The reports a bit more complex than the regular filemaker reports, but still not too difficult. I could even get the dates to go across in Excel.

Then management said that they wanted the months going across and under each month they wanted the quantity and the price (going across as well). So you would have two headings. The top heading row has the months going across, and the bottom heading row has a quantity heading and a price heading under each month, and the values and listed in the cells under that. You understand? And that is when the problem started. Could you tell me how to do this in Excel? I've been busting my head to get it down but can't seem to figure it out!

That is why I went back to Filemaker to do it, because initially I wanted to do these new ones in Excel as well. If you could help me with this I would be ever most grateful!!!

Oh, also, I tried exporting the summaries to a clone but that presents it's own set of problems. My best bet is to figure out how to do this in Excel. Management is getting the reports that contain all the data and info that they need, but they want it to look a 'certain way' and that is what is causing the problems!

HELP ME!!

[Kurt Knippel]

Another way to accomplish this, in addition to exporting ONLY the summarized data is to run this process overnight on an offline copy of Filemaker Pro running in a RAM disk.

Setup the Filemaker application as well as the databases in a RAM disk and start the process to run overnight, do not preview the reports simply print them. This way the reports are available in the morning.

Running out of RAM disk should show a HUGE speed improvment, although it will still task the CPU pretty hard. Also make sure that the CPU that you are using has a Math Co-Processor, either built-in or as an add-on. Most Intel Pentiums and Motorola PowerPC/G3/G4 chips have them built-in, other brands are a mixed bag. This should also help to speed the processing.

[proton]

quote:

---

Originally posted by CaptKurt:

Another way to accomplish this, in addition to exporting ONLY the summarized data is to run this process overnight on an offline copy of Filemaker Pro running in a RAM disk.

Setup the Filemaker application as well as the databases in a RAM disk and start the process to run overnight, do not preview the reports simply print them. This way the reports are available in the morning.

Running out of RAM disk should show a HUGE speed improvment, although it will still task the CPU pretty hard. Also make sure that the CPU that you are using has a Math Co-Processor, either built-in or as an add-on. Most Intel Pentiums and Motorola PowerPC/G3/G4 chips have them built-in, other brands are a mixed bag. This should also help to speed the processing.

---

They are looking at maybe generating these reports using Crystal Reports for Peachtree. In other words, they are thinking about getting the data from Peachtree and using Crystal Reports to make this report. Is that possibly a solution?

[Kurt Knippel]

I doubt that it will be any better of a solution. Honestly they might be better off with normal reports and having a data entry clerk had enter that data into a spreadsheet in the format in which they want to see it.

Although I am sure that they will find the format useful, I am not sure that I have seen any solution that will print in that kind of format.

This could not take more than an hour of data entry time to accomplish.

[proton]

quote:

---

Originally posted by CaptKurt:

I doubt that it will be any better of a solution. Honestly they might be better off with normal reports and having a data entry clerk had enter that data into a spreadsheet in the format in which they want to see it.

Although I am sure that they will find the format useful, I am not sure that I have seen any solution that will print in that kind of format.

This could not take more than an hour of data entry time to accomplish.

---

Thanks a lot for your input CaptKurt. It is greatly appreciated. You've helped me a lot. I will advise them accordingly.

[Steven H. Blackwell]

As stated:

I tested the changes with about 10 records and it worked, but very slowly. When I imported the actual data (a hundred and something thousand records) and tried to run the report, the database crashed. None of the reports would work.

My view was that maybe it was too much stress for the server dealing with so many calculations and so much data. I checked the NT server resources and when the report was being run, the memory was depleted and the CPU was maxed

out (100%). The CPU just flatlined at 100% and stayed there, and Filemaker stopped responding.

I told them that they would have to buy more memory and another processor (either another 450Mhz to run in dual cpu mode or a Ghz to replace the 450). They bought the memory (they now have 384 Mb SDRAM). One of the reports is working with two months data, but all the rest are still crashing.

I am just wondering if I am right in assuming that the CPU is remaining problem. Can any of you experts point me in the right direction? Thanks in advance.

Well. This crashing can alos be symptomatic of file corruption, and the set up you describe for this server is very likely to have induced corruption.

FMI recommends that FileMAker Server run on a dedicated CPU with NO file sharing enabled for a number of reasons. And these results are one of those reasons.

Consult the new FileMaker Server Best Practices White Paper on the FMI web site.

HTH

Old Advance Man