Jump to content
Salesforce and other SMB Solutions are coming soon. ×

To Portal Or Not To Portal?


frigante

Recommended Posts

This is the question.

And I'm sure there are lots of answers.

 

Here's my situation: I've got a "frankenstein" of a solution going here at work. My sister started it back in '94 with Filemaker (version 3, maybe - the last non-relational version, anyway) and it's grown into an all-encompassing system to run the business. I started working on this last year and about 6 months ago was able to convert the entire solution to Filemaker 7 (it was last in fp5 format) as well as add other modules and functionality to it.

 

Part of the functionality I added was a dispatching system that uses dynamic portals instead of find & sort scripts.

As you might imagine, the portal method is a bit more impressive and a lot more user-friendly (in theory - you'll see why it might not be in practice). It's not a simple situation - we have orders, actions, trucks, drivers and contacts to deal with, so there are lots of calcs, table occurances, relationships, etc. to make the whole thing work.

 

The problem: the solution is hosted on a dual 2GHz G5 with 4 gigs of RAM and even on this machine, performance can be kinda clunky. I realize that part of this might be due to the "frankenstein" nature of the solution (there were parts that I didn't want to touch at all, so I just added - the idea is that this will all be freshly re-done with FM7) and possibly the fact that there is a user using the shared solution, but there are other problems, particularly the big problem:

 

When our dispatcher is using the dispatch windows I've created (with the complex portals), he occasionally is presented with the dreaded "spinning wheel" from which it never recovers. It's happend with and without other users connected to the shared solution and there doesn't seem to be a pattern other than the fact that it only happens in the file and layout with all the complex portals. It seems to be getting worse as the number of records involved increases, which, I would imagine, is to be expected.

 

The strange thing is that when the wheel is spinning, anyone connected to the database is not affected at all.

 

When this occurs, I ask everyone to exit, then force-quit Filemaker and re-open. I know this leaves me open to a lot of potential problems, but I am militant about backups. So far, I've expereinced very little in the way of data corruption (there was one occurance with my gps data) so I'm not that worried.

 

So this brings me back to my big question - should I abandon the portal idea and go back to find/sort scripts or should I try to find and correct the problem?

 

I'm going to be re-designing the entire solution from scratch, so I'm just looking for your experienced opinions about all this before I try stuff out.

 

FWIW, the portal-laden dispatch system is well-liked by the people using it when it's not suffering from "spinning-wheel-itis" ...

 

Oh, also, it doesn't happen that often - probably once or twice a week at most.

Link to comment
Share on other sites

Hi frigante,

 

I haven't seen the "spinning-wheel-itis" you seem to have contracted, but I know a few optimization tricks that have helped improve the speed of layouts with our slow WAN connections.

 

First, if you can limit the number of records in the portal by adding another filter criteria to the relationship, this can help. Second, if you can limit the number of relationships that are used on the layout, this can help. If you need to have a lot of records in the list, I'd go with a List View instead.

 

Hmm, maybe we need a bigger picture of your setup, to see if there could be an issue there. Are you hosting this with FM7 Client? How many clients are connecting to it? And what kind of network do you have?

 

Or there could be some latent corruption from prior versions. When I had stability problems with my FM7 deployment, the FMI tech said to run recover on each file after converting and importing the data, then run the "Compact" File Maintenance option (in the Developer version.) He said this would repair any latent corruption that may not have affected the FM6 file operation, but could affect the FM7 operation.

Link to comment
Share on other sites

Hi Ender, thanks for the response.

 

First, I'll describe the problem layout a little more.

There are two main portals that show records from similar relationships with many criteria for filtering and two main criteria - Action and ActionDate. Orders::DispatchDate is a global that allow the user to change the day's deliveries and pickups that are to show up in the portal. It's related to Dispatch::ActionDate and similarly, Orders::Action is a global that is related to Dispatch::Action which separates three main types of actions - Dispatch is a separate file - this is an example of where I had to do some workarounds. In an ideal solution, these two tables would be in the same file.

 

Aside from the two main portals, there are two more - An "Assigned Actions" portal to show items that have been "checked off" from either of the main portals (when you're assigning trucks to actions, they disappear from the main portals) and another that sits in the corner to show available trucks and their latest GPS position data.

 

As for the number of records shown in the portals, they're already highly filtered (there are up to 8 filters on all of them) and I'll mention that there are usually no more than 150 records being displayed in any portal on the problem layout.

 

Am I trying to fit in too much on one layout?

It is doing a hell of a lot of stuff..

 

The file set is served from a G5 running FM Pro 7.0v3 and that copy is used on that machine as a workstation. There are up to 4 others accessing the shared solution, all with various Macs running FM Pro 7.0v3 except for my station which is running FM Developer 7.0v3. I've also connected with a WinXP machine running a trial version of FM Pro - it seems to work much faster but a little 'choppier' on this machine.

 

As for what kind of network, I'm not sure what you mean but I'll tell you it's a simple ethernet LAN using a nice Asante switch and an Airport Extreme base station as a router for the internet and DHCP. All but one of the Macs connecting to the server have Gigabit ethernet.

 

As for the "Compact" feature, I am a bit confused as to what the proper process might be. The help says this:

 

"When you save a compacted copy, FileMaker Pro rewrites the entire database, fitting as much data into each block as possible. This reclaims unused space in the file and rebuilds the file's structure. Compacting can be time-consuming if a file is large, and might be best accomplished as an overnight task."

 

So what do you do once this file is created?

 

I've already decided to do a full recover - any advice on how to best approach this?

As far as I know, it would involve the following:

 

1) Create clone (no records) copies of each file

2) Recover data on each suspicious file

3) Import data from recovered files to cloned files

 

Is this a good starting point?

If so, how does compacted copy fit into this, if at all?

Link to comment
Share on other sites

Okay, new info: The spinning wheel problem just happened in a different file altogether!

My colleague now thinks the problem is the operator who likes to do data entry fast, without looking.

 

I'm not so sure I can accept that as the source of this problem, so I'm going to do some maintenance on OSX (10.3.9) and then try the full maintenance routine on my database files.

Link to comment
Share on other sites

Some other thoughts:

 

[ QUOTE ]

First, I'll describe the problem layout a little more.

There are two main portals that show records from similar relationships with many criteria for filtering and two main criteria - Action and ActionDate. Orders::DispatchDate is a global that allow the user to change the day's deliveries and pickups that are to show up in the portal. It's related to Dispatch::ActionDate and similarly, Orders::Action is a global that is related to Dispatch::Action which separates three main types of actions - Dispatch is a separate file - this is an example of where I had to do some workarounds. In an ideal solution, these two tables would be in the same file.

 

[/ QUOTE ]

 

Are the date relationships regular "=" relationships or are there ranges? I don't know that this would matter.

 

[ QUOTE ]

As for the number of records shown in the portals, they're already highly filtered (there are up to 8 filters on all of them) and I'll mention that there are usually no more than 150 records being displayed in any portal on the problem layout.

 

[/ QUOTE ]

 

I don't know for sure, not having tested this many criteria in a large file, but I'd suspect there may be some performance loss with having many match fields involved in the relationship. My reasoning is that each match field must be indexed, and that index information must be passed on the network, where the client picks out which records match all of the criteria. This may be a case when concatonated match fields would perform better, where only one index would need to be passed for the relationship. Then again this could all be fiction, having nothing to do with your performance problems (though it may be interesting to test.) tongue.gif

 

I'm a little suspicious of the 150 records in the portal. If the portal is trying to sort by more than one criteria (especially if the criteria is unstored,) then even with these 150 records, it would be a performance drag on a slower network.

 

However, you said the person that was mostly experiencing this problem was the one running as host. Maybe the workstation is hanging because of the work it's doing for the connected clients? Perhaps you've already eliminated this possibility by seeing the problem when no other users are connected. If this is still a suspect after eliminating other possibilities, I'd recommend using a dedicated computer and putting Server 7 on it to host the files. Ideally, the FileMaker host should be the only thing running on that computer (it's kind of a bandwidth hog.) The use of a client machine as a host can be problematic because that user may want to access more than boring databases; like email, printers, internet radio, and their favorite online forums.

 

The "Compact File" option I was referring to is a good utility to use on troublesome files, or after importing or deleting lots of records. This is only available in Developer 7, and is located under File->File Maintenance. I don't know if this does the same thing as the "compacted file" option under "Save a Copy As". Anyway, before going through the trouble of recoveries and imports, you may just try this Compact File utility.

Link to comment
Share on other sites

[ QUOTE ]

Are the date relationships regular "=" relationships or are there ranges? I don't know that this would matter.

 

[/ QUOTE ]

They're all "=" and through experience it really matters! Date range relationships are extremely clunky! I found a solution to that on this forum about 6 months ago - I forget who turned me on to it, but it worked well. I've already forgotten the technique - huge calc fields, but it speeds things up nicely.

 

For the record, I was wrong - I'm only using two sort criteria and performance is really not that bad - it takes a while (under a minute) to load the layout, but once it's there, filters via popup menus, date changes, etc, are quick - commit records and refresh (w/flush) take about the same amount of time as loading the layout, but that's kind of understandable.

 

Really, the big problem is the spinning wheel and I've basically eliminated the layout with the portals as mentioned in my previous post.

I've also eliminated the "doing work for connected clients" possibility as the problem has occured while no clients were connected.

 

I agree that the solution should be hosted on a dedicated server with FM Server - we're getting to that, but that'll require us to get another mac, etc. I'm sure I don't have to mention what my boss thinks of that....

I'm not even sure how the whole thing will work once it's hosted by FM Server - I have yet to try it!

 

This is the first I've heard of the File Maintenance stuff in FM Developer! I've never even noticed it in the File menu... Being a pretty experienced db programmer, I tend to just jump into things like this head first - Filemaker has certainly taught me to pay attention to the tool you are using!

Anyway, after reading up on the two File Maintenance functions (Compact File and Optimize File) I've decided not to use the Compact File utility because the Developer's Guide (not much of a guide, I must say) suggests that if your file will have data added to it, it might actually end up creating more fragmentation.

 

On the other hand, the "Optimize File utility defragments the file to make the physical arrangement of data match the logical arrangement." That's right from the guide.

 

So I'll try that before anything else.

 

I'm still curious - the "Save a Copy As" "Compacted File" seems like a mystery to me - you save the file as that, then use the compacted file from then on? I just can't figure out how and for what this option is used.

 

Thanks for your help though, Ender.

It is much appreciated!

Link to comment
Share on other sites

Here's a little update:

 

After optimizing the files, I've had only one 'spinning wheel' "crash" (not really a crash since the db is still fully functional to any and all clients) which came when the user was scrolling a portal.

 

I looked at some things this time - looks like Filemaker was accessing some of Cocoa's or Carbon's frameworks to perform its tasks.

 

So this makes me think that perhaps the OSX account that the user is logged in as and hosting the Filemaker solution as is the problem. Maybe its accessing libraries that it does not have access to - after all, the account is set up with very limited access.

 

So, for now, this user will be logging in as the administrator and running FMP from there. I'll post results as they occur.

Link to comment
Share on other sites

  • 2 months later...

It's been a while, but here's an update to anyone (Ender?) who's interested.

 

After going through hoops to narrow down the exact cause of this problem with the help of Filemaker engineers, it seems as though our Dual G5 is the culprit. But only when both processors are active!

 

An engineer had me install the developer tools (CHUD, specifically) which include a System Preferences panel called CPU which allows you to toggle your caches and, if you have a dual CPU machine, run with one or two processors.

 

Well, the problem never happens as long as the G5 is running on one processor. When running with both active, the problem occurs frequently.

 

Looks like Filemaker knows about other dual-CPU issues with FMP7.

Link to comment
Share on other sites

Hmm, do tell...

 

I've got a couple dual processor servers myself, and I'm having some stability problems with random machines. Though my setup is using Server 7, I wonder if this could be related.

 

Perhaps you can say more about this. Is there a way to easily disable a processor?

Link to comment
Share on other sites

Ender, you simply need the CHUD Tools, available at the Apple Developer Connection site:

 

http://developer.apple.com/tools/download/

 

If you're not already a developer, it's free to become one at the basic level.

 

Once you download the tools and install you'll have a new System Preferences item in the Extras section called "CPU" which will allow you to tweak the various features of your Mac's processor hardware.

 

I'm not sure if Server 7 is affected by the dual processor issues. It seems to me like they are very rare problems. I've obviously stumbled upon one that Filemaker didn't know about yet - in fact, I haven't yet closed the case with the engineers there.

 

Ender, was there any other info you were looking for?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use