Outsourcing to google spreadsheets for cpu-demanding calculations

[databaser]

I was wondering if anyone has experience with google spreadsheets.

I have a filemaker solution whose main purpose is to perform a lot of calculations. I feel like I have written my scripts, calculations, and custom functions to be as efficient as possible, but to calculate everything I'd like to, with one machine, would take 70-125 hours, every day (which means either having a whole lot of machines each doing part of the work (as it can be segregated), or doing only 10-20% as much as I would like to, and still having the cpu of one machine largely-consumed most of the time).

I was wondering if google spreadsheets might be able to run these calculations much quicker than doing it locally, with filemaker.

So my first question, is if anyone knows whether using google spreadsheets might be a good solution to this problem.

My second question is if anyone has basic guidance on converting filemaker calculations to something that works in google spreadsheets.

Right now, most of my critical recursive custom functions work with lists, and rely on getvalue, valuecount, middlevalues functions. In google spreadsheets, I don't see these functions, but also, when I scrape my data from the web (usually csv files, which is lucky, because they're not messy), whereas filemaker just gave me everything in one text field, which I would parse, Google spreadsheets sorts into fields. With filemaker, I had written recursive custom functions to isolate the columns, and then, if only wanted the first 500 values of that column, I could just use leftvalues($list; 500) (Though, it's typically done in the reverse order, because that would seem more efficient to calculate). I'm essentially wondering how I could do something similar in google spreadsheets.

I really appreciate any helpful responses.

[databaser]

I think I'm really wondering if google spreadsheets would be able to perform these calculations much quicker.

If yes, I think I would just seek to hire someone to help me do this.

[bruceR]

Let me paraphrase your question:

My database is slow. Will everything be better if I use something else?

Answer: we cannot know.

You will need to provide a much more meaningful and detailed description.

Right now, talking of Google is premature.

If you're going to hire somebody, you will probably want to hire them to look at the existing design.

You might want to talk to Honza at 24U who has a tool and an approach for analyzing and optimizing Filemaker databases.

[comment]

I think I'm really wondering if google spreadsheets would be able to perform these calculations much quicker.

Hard to say without having a clue what "these calculations" are. But you could test this by trying it out directly on Google Docs. My uninformed guess is that it will not be any faster than using a local platform - certainly not 5 to 10 times faster.

OTOH, Filemaker is no number-crunching machine, so perhaps you should look at alternatives. This is assuming that your processing needs are really that intensive. Somehow, I suspect you could with just some optimizing.

[databaser]

The overwhelming majority of time spent performing all my calculations is attributed to one thing, that ideally, I would like it to do 30,000 times per day (I may be able to make due with 3k, 7k, or 10k times per day, but 30k times per day would be ideal for my purposes).

This one thing, is to take a new list of 500 numbers, and determine a 'weighted' percentile. This is not how I compute it, but it's essentially like a percentile where you duplicate the numbers different amounts of times; the numbers are listed in a chronological order, and it would be like if the first one were duplicated 5 times (if 5 were the starting weight), then the next one 6 times... and the 500th one 504 times.

Even with the best optimization, would it be possible to do that 10,000 times in 4 hours? Could google spreadsheets do it much faster than filemaker?

###

Update:

I did notice that google spreadsheets has a percentile function - and it seems to come up with a percentile on 500 numbers much faster than filemaker can do an ascending sort of 500 numbers. I'm not sure about trying to weight it though, in google spreadsheets.

Hard to say without having a clue what "these calculations" are. But you could test this by trying it out directly on Google Docs. My uninformed guess is that it will not be any faster than using a local platform - certainly not 5 to 10 times faster.

OTOH, Filemaker is no number-crunching machine, so perhaps you should look at alternatives. This is assuming that your processing needs are really that intensive. Somehow, I suspect you could with just some optimizing.

[comment]

Even with the best optimization, would it be possible to do that 10,000 times in 4 hours?

I don't know, because you haven't described the method in sufficient detail. However, I suspect that if those 500 numbers were in records, and you were sorting the list using Filemaker's native sort, you would see a substantial speed improvement over any custom sorting method.

[databaser]

Thanks comment.

I might not have done it the best way, but your suggestion has doubled the speed. I created a new table with 500 records and a field ("order") where each record is numbered from 1 to 500. Now I have it put the list of 500 numbers into a global field, and have a calculation field ("value") of getvalue(globallistfield ; order). Then I just sort records by the value field. I also had it subsequently find the weighted percentile by using the value in "order" to determine the weight of each value and going through the records in ascending order, until the weights aggregate to the desired percentile. This seems to be 2-2.25 faster than what I was doing before.

On another note, I'm curious as to whether speed could be improved by using Filemaker Pro with Amazon EC2. Does anyone have any input on that?

I don't know, because you haven't described the method in sufficient detail. However, I suspect that if those 500 numbers were in records, and you were sorting the list using Filemaker's native sort, you would see a substantial speed improvement over any custom sorting method.

[Ron Cates]

I have a filemaker solution whose main purpose is to perform a lot of calculations. I feel like I have written my scripts, calculations, and custom functions to be as efficient as possible,

With one suggestion from comment you were able to double the speed of this process. That would suggest that you may not want to assume that you have written things as efficiently as possible. Maybe if you were to ask for help in that reguard you may ( or may not ) find that FileMaker will do the job.

[bruceR]

Agreed. Ask for help. BUT in doing so, expect to actually describe the problem in enough detail to actually enable anybody to help.