Very Slow Layouts

[VFXdbGuy]

I have a fairly small database. Basically there is a Project table and a related Shots table. There is never more than a single project record and, during testing, I had no more than ten or so shot records.

But when the number of shot records gets higher (at this point it is still just 64 records) the graphic refresh of some of my layouts slows right down. This does not happen when the file is shared via FileMaker Server, only when opened directly.

I am using a pretty fast PC as my workstation (2.8 GHz, 16 MB AGP video) and I get the same speed issues when I run it directly from my desktop, so network issues aren't the problem. The real irony is that the PC being used as the FileMaker server is only a 733 MHz machine!

Any idea what could be happening here??

[Edward Newman]

So its faster on server?

[VFXdbGuy]

Yes, much faster on the server despite the server being under-powered.

There is still a touch of lag as the fields appear but nothing like what I see when opening it directly, either from the network or a local drive. The delay is as much as 2 seconds to 'paint' the fields.

One layout is by far the worst with one other being a bit slow. The slowest one is based on the single-record Project table and the other is based on the more populated Shot table.

I am beginning to think the complexity is getting too much. I have a fair number of fields that use Case calculations that change totals based on project-level settings.

Unfortunately I need to use peer to peer sharing as this setup is continualy copied during the bidding stage

[VFXdbGuy]

Is there really no one in this forum that can shed any light on this?

Please! I'm about to lose the thousands we've invested in version 7 if this can't be made to work!

signed,

Desperate

[stanley]

Grant:

There's nothing wrong with your server - it's got plenty of power for what you're doing (obviously.) It sounds to me like you may want to reconsider how your calculations are being done - perhaps you have calculations which are based on other calculations, or something along those lines?

Also, what exactly do you mean when you say "peer to peer sharing"?

-Stanley

[VFXdbGuy]

It sounds to me like you may want to reconsider how your calculations are being done - perhaps you have calculations which are based on other calculations, or something along those lines?

I am looking at that. But I really don't think I have a choice. The totals at the higher level are conditional. A number of services are quoted per shot record at prices set at the Project level. These are summed to determine the Project cost. But an "override" shot cost field at the shot level can be used and that is determined by a project-level field.

What I am trying now is scripting. Populating a set of number fields with the multi-calculation totals. I run the script each time someone access the slow layout. This delays the appearance of the layout in a minor way but then the totals are sttatic on that slow layout and it performa much better.

I guess the thing that bugs me most is: why does the redraw of the layout have to wait for the calculations? It would be far less annoying if the interface appeared immediately and the data filled in as it was calculated.

Also, what exactly do you mean when you say "peer to peer sharing"?

Bad choice of words I guess. I mean that the file is shared off the server.

[Oldfogey]

Grant,

It sounds as though you have multiple copies of your DB on the network. True? If so, FMP might be confused about where it is getting some of its data.

[stanley]

Agreed. The reason I asked was that DBs shared via OS-level file sharing will definitely get corrupted (which can slow them down) and can behave really strangely (which can also slow them down.) If you are sharing via FileMaker's hosting mechanism (or via FM Server) then this is not an issue.

-Stanley

[VFXdbGuy]

There are multiple copies but each has a unique file name and the sharing is done via filemaker sharing. But, even so, the problem was seen when a single copy was opened by a single user with sharing turned off.

I have a sufficiently quick system now. I used a script to populate plain number fields with the conditional data and reduced the number of calculations dependant on other calculations. I can't get rid of all such calculations due to the complicated nature of the system. But it works well enough...

[stanley]

Right, the slowness is almost certainly based on all the calculations, then. Although this involves a great deal of rewriting, if the speed issue is really killing you, you might want to move your calculations into scripts - you just have to ensure that the scripts are triggered in such a way that nobody ever sees any obsolete data.

-Stanley

[DykstrL]

Another issue that will cause slowdowns like you describe is with multi-file solutions. When the files are on the server, all of the relationships are looking for the files at the same address (either by IP or server name). When you move one or more files to your local machine and open it up, the file has to 'search' for the other files that are either still on the server or in another location. If a file still hosted on the machine is opened and has relationships to the file on your local machine that is supposed to be on the server, this will cause an even more pronounced slowdown.

I guess my big question is: why are you taking the file off the server to make revisions? With version 7, I don't think there is anything you can't do, developer-wise, requiring the file to be closed on the server. :

[VFXdbGuy]

The file is not off the server for revision but in order to perform one of the few things you cannot do with a server-hosted file: save a copy as...

This file is used in two phases: the first is bidding on the visual effects work. In this phase, many revisions are made as the cliet rejects some element of the bid. Management wants a copy kept of each revision and the only practical way to do that, is to save a copy that shows the state of things at that time. Exporting is a problem as their are a fairly large number of tables. It would be awesome if I could save a copy while the file is hosted.

Once the job is won, the file is hosted on FileMaker server.

[DykstrL]

I think a more efficient way to do this, assuming you're saving the data, is to create an archive file or table that has an identical structure of the main file. Then, via a script, when you revise a record export the current record to the archive file, then allow revisions. The different versions could be tracked with a serial number that includes date/time and revision number. You just would not allow editing of records in the archive file, preserving them.

In this way, you would be able to have a searchable/sortable database with all of the different revisions for each bid/client. It would also not require turning the file off the server.

We do this in several systems that require an audit trail. We "snapshot" a record to an audit file before allowing changes. That way every little edit is tracked by who made the change, when the change was made, and exactly what was changed.

[Brian C]

Filemaker Server has the ability to make a backup of the files it is hosting. You can have the server do this at specific scheduled times, or on demand. Would this solve your problem with the "Save A Copy As..." issue? You should always make sure the server is doing a daily dump of its files for backup purposes in any case so that the your backups will have whole files.

[bruceR]

It is not at all clear how how you have server set up and how you have peer to peer set up and this could be a big part of the problem. You should have file sharing turned OFF on the server, nobody should be able to see anything on the server except through the Open Remote menu choice.

[Søren Dyhr]

. But I really don't think I have a choice. The totals at the higher level are conditional. A number of services are quoted per shot record at prices set at the Project level.

Why don't you challenge the developers here, with these calc's?? ...I could mention more than a few roaming here, that loves such optimization puzzels, say you wish to make a Case( but looking up next higher/lower might just be the ticket to make fields stored, preventing backward evolving calc's based on the visible part of the layout - leaving the out of sights to thier own existence.

Some times is it a benefit for a solution that thing works this way at other times is it a drag, but say all calc's depending on globals is it going to be a strain on the network. Another issue is that relational structures refinements could often make more spreadsheetish behavoiurs and wishes go away...

--sd

[VFXdbGuy]

The file is shared off-server in the initial stage. I am giving some thought to DykstrL's idea about revision saving as a way to get ths phase on the server.

As for the file sharing in regards to FileMaker Server, only administrators can access the file system on the server so that new files can be added and old ones removed.

I do my best to enforce best practices but I am only the developer, not the boss, sounless I can find a way to provide the functionality within the parameters of best practices, I have to just do what I can...