Summary speed of hosted files vs. local

[Quartekoen]

So, I've been doing some metrics of Filemaker's abilities, and I have some questions. Here's what I did:

I created a database with over 800,000 records. The database itself has only 2 fields: Price and Price_Summary.

Price is a number field with that was populated with a random number between 1 and 100 to 2 decimal places. The field is not indexed.

Price_Summary is a Summary(Total) of Price.

Record Count: 837,000

File Size: <14 MB

File was not accessed by more than 1 person (me) at a time.

Next, I allowed Price_Summary to calculate while the database was opened from my local machine, and again when I opened it from the hosted file on FM Server.

Time it took to sum 840,000 Records

Local Machine: ~5 sec.

Stored on network drive, but not hosted: ~11 sec.

(Hosted) Development Server on Mac Mini: ~46 sec.

(Hosted) Live FM Server: ~37 sec.

Specs:

Local Machine:

Core i7-2600 @ 3.4 GHz

Gigabit Ethernet

Windows 7

8 GB RAM

FM Pro 11

Dev Server:

Intel Core 2 Duo @ 2.5 GHz

Gigabit Ethernet

Mac OS 10.6.8

4 GB RAM, DDR3 1067

FM Pro 11 Server

Live Server:

Quad-Core Intel Xeon @ 2.26

Gigabit Ethernet

Mac OS 10.6.8

6 GB RAM, DDR3 1066

FM Pro 11 Server

*Note that this test was also performed on another Mac using FM Pro 11 with identical results.*

My question is: Why does it take so much longer to run the sum when the file is hosted on FM Server? Running the sum locally is relatively fast, and all the computers involved should be more than capable of calculating it this quickly. It can't be the network since the file is less than 14 MB and all the computers are on a Gigabit LAN. What is it that's slowing it down so much that it takes 8 - 10 times longer? I would really appreciate some insight into this. I've attached the file I used in hopes that you can replicate the results. Thanks.

test.zip

[Vaughan]

The speed is fastest on the local machine vs hosted because the network is relatively slow compared to the internal pipes in the computer.

Run the tests a couple of times and compare. FMP caches data and often the second and subsequent times are significantly faster.

Also run the test with the price field indexed and see what happens.

FMS can perform some calculations on the server and this can improve performance and make some process faster on hosted files, particularly if the network is slower.

[typewriter]

Hi Vaughan

Looks interesting!

Took me about 6 seconds (MacBook Pro 15", [email protected], FMPA11).

One comment: wouldn't it be nice if there was some kind of benchmark file. I would think of two FMP files actually

- one datafile containing a couple of simple tables and some relations between them. Like one for a automatic calculation, a summery field, etc. Nothing fancy but covering the basic FIleMaker operations while being compatible between FMP 8 and 11 (and FMGo as well).

- one interfacefile containing a couple of testscripts and a table with timing data, with a reference pointing to the datafile.

And a set of text-files with the actual data. So a testsuite would be: import of the text-files, and running a couple of standard scripts.

You could then do a sort of standardised test among various HW / network setups.

If more people would use the same reference file, it would be easier to get a good impression on FileMaker performance.

By the way: do you think runing FMS on a MacMini with an SSD would make a big difference?

Regards

Hans Erik