I'm working with a solution that accesses MSSQL tables using ESS. Some of the scripted searching that's in place needs to search across multiple tables and relationships. For example, when the user logs in, the database is supposed to show them a list of jobs that have uncompleted tasks, with jobs and tasks each residing in their own table in a one-to-many relationship.

The solution works, but it's slow, even over the local network, and that's only going to get worse over time as the number of jobs and tasks increases. So I'm looking at creating a cache table that stores the data that we need to search on in indexed fields. But before I begin designing this, I'm pretty sure this is a problem that's been solved before, and thought I would check out there for any tips or articles that might exist that Google has failed to find for me.

Just a few FYIs:

- I cannot edit the schema of the MSSQL system
- The existing MSSQL system does *not* have fields that record when a record was last modified
- I've created hash functions to allow me to determine if a record has changed (i.e., store the hash of the record in the cache table, compare the cache hash to the original record's hash, if they differ, the data needs to be updated)

Thanks
Chuck

The FileMaker Training Series - Advanced book ($19 on FMI's website) has an excellent section in chapter 9 that deals with a lot of gotchas with ESS.  If you have not reviewed it yet, then I would suggest starting there.

Using non-native tables (ESS) is always going to be slower than native tables, no way around it.

You mention that you can not edit the MSSQL schema, but can you at least create views?  Anything to produce subsets of data there that would have narrower tables (fewer fields) and less records?

 

Wim,

Thanks for the FTS suggestion. I already owned it, and I'll read that chapter.

Chuck