Extremely slow page turning

[RoadrunnerRay]

I have a 6-file database that was converted to FMP-7 without merging the files. I use the Web Publishing Engine on a Mac G5 and Filemaker Server on another Mac G5.

My web pages are turning 10 to 15 times slower than they were under FMP-6 using CDML. Does anyone have any thoughts on improving the speed?

I have searched the forum archives for similar questions regarding this problem and was unable to locate any discussions.

Thanks, roadrunner(Ray)

[RoadrunnerRay]

Hi, thought I would ask if anyone has had experience under FMP 7.0 merging mulltiple files into a single file and if this had improved the page turning performance. Anythoughtswill be greatly appreciated........Thanks, Ray

[Martin Brändle]

According to my experience, in CWP it's the FM Publishing Engine process which is slow. This part communicates with the database and creates the XML result tree. The XSLT part (XML parsing + XSL transformation) in the java process is fast.

On the software side, you can increase speed as follows:

- did you remove open (undefined) file references in the converted solution? They slow down the whole database.

- just put the fields on the layout that are needed, create different layouts for different views (list, detail, ...)

- use low values for the -max query parameter (say not higher than 100).

- design the web page so that it does not contain to many nested tables

- find out which is faster: -sort or xsl:sort

On the hardware side:

- a fast CPU

- a fast network connection

- a lot of RAM. In our case, the FM Web Publishing process consumes about 130 MB, depending on the how the jvm is startet, the java process might consume up to 1 GB

[RoadrunnerRay]

Hi Martin, many thanks for taking time to respond to my query. I will look into your suggestions on the software side of the equation tommorow and let you know what I discover. I think the hardware side is adequate for this application.

Ray

[RoadrunnerRay]

Martin, I am reviewing my file references and they are all defined with relative paths. Would it help to add full paths and/or network paths since I have my database files on one computer and the web server on another computer. How do you determine if a file reference is "open"??

I also reviewed my hardware again and have the databases on a Power Mac G5 dual 1.8 Ghz machine with 2 GB of memory (will upgrade this to 4 GB, the max). The web server (with the WPE) is on a Power Mac G4 with a single 1.25 Ghz processor with 1.25GB of memory. I will upgrade this to 2GB.

Thanks again for your input, I am reviewing each comment and will return feedback as I progress.

Ray

[RoadrunnerRay]

I put a stop watch on the same page in both FMP 6.0 and FMP 7.0. Nothing was changed in the function of the page. The CDML page turned in 1 second and the XSL page takes 12 seconds.

I have checked the file references (3) and they are all used. I have eliminated all unnecessary fields from the layout that is being referenced. In all cases it still takes 12 seconds for the XSL page to turn. I will check out the rest of the suggestions and report back.

If I merged the six files into a single file with 6 tables would this reduce the time to turn pages? I am also upgrading memory in both the web server and the database server and will post the results here.

Thanks again, Ray

Edited July 20, 2005 by Guest

[RoadrunnerRay]

Update......When I converted my system to FMP 7.0 each of the six files converted were assigned a number of file references to one or more of the other 5 files. After reading more on the function of these file references they are for files "exrternal" to Filemaker.

I deleted all of the file references in the 6 files and discovered that page turning for the test page went from 12 seconds to 3 seconds. I have a problem with data displaying on a couple of pages but I should be able to resolve this in my relationship definitions (I hope).

Am I correct in understanding that these file references are only for files external to Filemaker??

Thanks, Ray

[Martin Brändle]

Hey, you are too fast for me - 4 posts in a row ... and time zone shift.

Yes, the file references are for the FM and other files external to a DB file, e.g. each relation to another table in another file needs a file reference, each call of a script in another FM file as well. If during time the FM 6 files were moved to different locations (or e.g. the server was not available), FM 6 tries to find them in the local file system and adds a file reference if it has found a file that matches. This might happen if you have say a local version for development and a production version on the server. In old solutions you can see the whole history of where the files have been.

After conversion to FM 7, FM 7 tries to find all those files where a file reference exists. If it doesn't find the file, it may run into a timeout (this would be a "open" reference). Therefore it's best to clean up the file references to only the files that are needed for the relations, keep the files of a project together in the same path, and use relative paths. For large projects, there are tools like New Millenium MetaDataMagic that can do this analysis and cleanup for you (we used MDM for pre-conversion of a 50+ file project).

I cannot tell if it's better to combine all tables into one file or not to improve performance. According to what I have heard from other developers, it depends on the complexity of the solution (e.g. the relationship graph, the number and type of relations, the number of table occurences etc.). In our case I still have a multifile solution because of different reasons, but reduced the number of files by combining several tables according to the logic of the processes covered.

Just another question: Do you have lookup fields that depend on self-join relations where also calculated fields are involved? Inspect them carefully and try to avoid them. Depending on the way the lookup is done this might be really a "slow-downer". We had several cases where we had to redesign our solution where in FM 6 a trick was used for automatic counters based on such a scheme. In a large FM 7 file this trick had as side-effect that for every creation of a record 40 MB (probably index data) was sent over the network from the server to the client, and the spinning wheel turned for >30 seconds. When the files were not loaded from the server, but from the file system of the client, the solution behaved normally.

[RoadrunnerRay]

Hi Martin, I discovered that all my file references for Filemaker files are needed. Just finished restoring them. I too, used MDM for the conversion along with their "File Reference Fixer" which worked quite well.

I do use 2 look-ups and will investigate this further. Can you tell me what kind of time increase, if any, you experienced after conversion. I am hoping that increasing the memory will improve my page turning times. Our users are accustomed to the pages turning quickly, so any increase in time will not be welcomed.

Thanks for your continued support...... Ray

================================

[Martin Brändle]

Well, very difficult to say. Before we had the following config (old):

G4/733 OS 9.2.1 as DB server, FMS 5.5

2 G4/400 OS X 10.3.9 as web servers (FMU6)

1 G4/1.4 GHz 2-CPU OS X 10.2.8 Server as web server, FMU6 and WSC,

the webservers being in a RAIC

Now we have (new):

1 Xserve/2 GHz 2-CPU, OS X 10.3.9 Server as DB server (FMSA7)

1 Xserve/2 GHz 2-CPU, OS X 10.4.2 Server as WPE/web server (FMSA7)

Network in both cases 100 MBit/s or 1 GBit/s.

I have also changed a lot of the web functionality, so it's difficult to compare.

Web response:

Case 1 (with long answer times):

old (1.4 GHz): search in main table, about 400 records returned and displayed, sorted

new: search in related table, the same 400 records returned and displayed, sorted

take about the same time (about 4-10 seconds).

In the new system, about 800 KB XML tree are generated and 400 KB are sent as HTML to the browser.

Case 2:

old: search in main table, about 250 records returned, 10 displayed, sorted

new: federated search, 1 record creation, 3 searches in 2 main tables, of one search the same 250 records returned, 10 displayed, sorted

Both systems take about 2-3 seconds (but the new one has much more to do).

Case 3:

Sort records of case 2 in another order.

The new system is much faster (<1 second), the old takes the same time as before.

We can assume that with the same hardware FMS7A web publishing is a little slower than with FMU6, but not 10-15 times as in your case.

As I mentioned before, it does not depend so much on how many records are found (except you sort them), but on how many are displayed (with -max). The more are displayed, the longer the XML tree that has to be generated by the FM Web Publishing process.

You can test that yourself on http://www.clicaps.ethz.ch/en/

Search with the following 3 queries:

physical chemistry

phys chem

p

Change the number of records displayed with the selection box at the lower end (or -max) with each of the queries.

There is a little trick with the last query why the system is so fast (at least in the beginning).

A different thing is the response of FM Server - FM Pro client (not web):

Search in a related table, indexed field, about 50'000 records returned

Old: 60 seconds

New: 5 seconds

This is due to two reasons:

- FMS7A can hold the whole DB (130 MB) in cache, in FMS5.5 this was not possible (max. 40 MB)

- our server hardware is faster in the new system

[RoadrunnerRay]

Hi Martin, many thanks for this exhaustive explanation. I was delayed in getting back because of other assignments.

In one case a user enters their "username" and demographics for the user are returned (only 1500 records in the searched file). This took 1-second under FMP 6.0 and now takes 12-seconds.

I will continue to look into your suggestions and report back soon.

Here is a link to our test system:

http://www.teaching2.nmsu.edu/

Click on "Registration" and enter testcase11 for the username and see how long the data retrival takes. You can then click the "update button" to see how long it takes to simply update a record.

Thanks again, Ray

[Martin Brändle]

[RoadrunnerRay]

Hi Martin, I had that code commented out in the verify_data.xsl file so just deleted the comment stuff.

You should get the XML data when you click on the log-In button and also when you click the "update button on the verify_data.xsl page.

Thanks again for your assistance.Ray

[Martin Brändle]

Do you have field validation on certain fields turned on?

FirstName

LastName

ParticipantID

Status

Department

MailStop

Campus

This might also slow down (because the data has to be checked by the FM Publishing Engine).

Then I see that the field Department occurs twice on the layout.

[RoadrunnerRay]

Hi Martin, I tried removing the field validations but did not notice any improved performance. There is a department and department2 field, but I don't believe this is creating a problem.

I am going to thoroughly analyze the file references (as you initially suggested) since there was a marked improvement when they were removed. It may be a combination of things that are creating this problem.

If anyone is familiar with a technique to display a "processing request" page I would appreciate your thoughts on how to implement this. I would like to implement this new system as soon as possible and would use this on an interim basis.

Thanks again for your assistance and I will report back soon........ Ray

[Martin Brändle]

Ray, I just cannot believe that fetching a single record takes several seconds (if you compare with our databases, which are probably more complicated). There must be something moot with the DB.

Check further: Unstored calculation fields? Unindexed fields?

Maybe rebuild the layout and just place the fields that you need on this layout.

[RoadrunnerRay]

Hi Martin, I am doing just what you suggested regarding creating a layout with only the required fields. I then removed each of the six fields, one at a time to see if a problem exists with any of them. I will review the calculation fields of which there are many. We also have many reports that are produced from the system on a batch basis.

I believe it is as you say, a very moot point we are dealing with, hopefully not as moot as that dummy web site that is required on the OSX Server!!

My main problem is that I have many interruptions that tend to hamper my concentration and prolong the solution. I will persevere though.

Thanks again Martin and I will let you know what the resolution is...Ray