What do you lose with ESS?

[Rich K.]

Hello --

What exactly do you lose with ESS? In the FM Pro Bible 10 ... Ray Cologon seems to make a pretty big deal about "stored calculation fields". I have seen a few posts about slowness. Does anybody have any advice?

Thanks!

[mr_vodka]

That is a pretty broad question. Do you have any specific matters or concerns?

[Rich K.]

John,

Yes, thanks for the response ... I am embarking on an Inventory, Invoicing, and Purchasing project that generates a couple of gigs of data a year. It is pretty high-volume. Does an ESS architecture have severe ramifications over time (3-5 year window), without archiving?

Some of the ideas based around ESS revolved around creating Views in MySQL or SQL Server that act as the tables into the FM Pro Advanced Server. This way once a PO or Invoice is closed ... It could be allowed to fall of the View. I would make this happen at night when no-one else is on ... does this really screw with the internal indexes of FM Pro. I also saw a post about some wierdness about a Number fields getting truncated.

My arm is getting twisted to weigh-in on an ESS architecture and I need some guidance.

Regards!

[mr_vodka]

Actually I recommend using a viewing over the underlying table anyway. The number of rows in the External table does matter, but when you are dealing with ESS data, most of the time you should be viewing the records one at a time in form view anyway for efficiency. Using views will allow you to put the sorting, reduce the number of unneeded fields, etc back on the External side, not FileMaker.

[Jonathan Monroe]

The thing to keep in mind about ESS is that, in a nutshell, it does whatever it has to do to make a SQL table appear to the developer as if it were a FM table.

The implication of this is that FM will often break up a Find into multiple SQL SELECT statements in order to preserve the the FM search behavior. This can be perceived as "slow" because the developer is thinking that a FM find translates to a simple SELECT * , when it doesn't.

The most successful ESS solutions I've seen have used SQL "views" to help the SQL database help ESS by constraining the overall result set. Making sure an index is applied on each SQL field that might be searched on will also greatly improve performance.

I would also advise that you keep your initial ESS solutions simple and straightforward. You can then build a more complicated solution as you gain experience with exactly how ESS works.

ESS hides a lot of complexity from the developer, which means you can have a simple ESS-based solution (FM layout based on a SQL table) running in minutes. However, you may ultimately need to understand some of that complexity if you integrate ESS into a large solution. The Steve Lane whitepaper available from the FM website is good place to start.

Jonathan Monroe

Actual Technologies - ODBC for Mac OS X

http://www.actualtechnologies.com