Long Sort Times

[rindin]

I'm currently working on a Filemaker 7 solution that contains about 6000

records. The solution keeps information on books and includes information

such as authors, title, description, and so on.

The title field is set to index all, and searches on the database occur very

quickly, both using Filemaker client, and via the web with CWP with XSLT.

The problem I'm having is when I attempt to sort the records. If I want to

sort all of the records in the database the sort can take up to about 30-40

seconds, even if I sort on the title field (which is set to index all.)

Why does it take so long to sort this many records even if I'm sorting on an

indexed field? Does anyone know how to make these sorts occur faster?

Another thing that's strange about this is that the sorts occured almost instantly using CDML with filemaker v6.

[Ender]

Sorting on local indexed fields with 6000 records should take just a few seconds.

My guess is you may be sorting on fields that are unstored calcs.

[rindin]

I do have a calculation field in this database, but it is stored and indexed.

This calculation field is a concatenation of all of the other 6 fields in the database, and is used for searching purposes via the web.

Any other ideas of what might be taking so long to sort?

[Ender]

Slow network?

[rindin]

I'm accessing the solution over a gigabit LAN, so I don't think this is it either.

I'm sorting on the title field which can often be very long. Could this be related to the long sort times?

[Ender]

I don't think that would be it.

Perhaps you can post a clone of your problem file, so we can look more closely at what's going on.

[rindin]

I've gone ahead and attached the FM file to this reply. There's currently only one record remaining in the database, and you can access it using just Admin for username.

Thanks!

One more thing... This FM file was 45MB with 6k records... Doesn't that sound kind of big for just containing text?

Edited July 18, 2005 by Guest

[Ender]

You'll need to zip or stuff your attachment so it can be transferred properly.

45MB is pretty big for just text. I might have an explanation for that too, once I see the file.

[rindin]

Try downloading it now.

Thanks for taking the time to help!

coa_small.zip

[Ender]

Well, your file is simple enough. I don't see any obvious reasons for slow sorting. My guess is it has to do with the length of the text in the field you're sorting by, or your indexes are bloated. I'd imagine that the indexes on the Abstract and BasicIndex fields would be quite large (this is probably part of the file size issue,) and sorting on these fields could be slow (I don't see why you would sort on these, but I could see why they might be slow if you tried.)

I have a file with text in a field as long as your Title, and mine sorts very quickly for 6000 records. If sorting on your Title is giving you problems then I'd suggest you try rebuilding the indexes. I'd also recommend compacting the file.

To rebuild the indexes, go into the field definitions' storage options and turn each index off. Close and reopen the file and turn the indexes back on for those fields.

To compact the file, choose File->Save a Copy As... and choose "compacted copy (smaller)" as the Type. This should cut out some megabytes.

Let us know how it goes.

[rindin]

I tried the things that you mentioned, and nothing seems to give a significat increase in the speed of the sort.

I even created a new stored calculation field that was the first 3 characters of the title field, and sorted on that. This still took the same amount of time.

One thing that I have noticed though is that the first sort that you do after opening the file, always takes the same amount of time no matter which field is sorted on. Then each sort following this first one occurs almost instantly.

It seems like the solution is building some kind of a "sort index" when the first sort is performed.

Another reason I'm pretty sure this is true is due to the fact that removing some of the larger fields from the database also speeds up sorts, even though they aren't specified in the sort.

Any ideas on how to force the solution to strictly sort on the specified field?