Jump to content

Table Caching Techniques?

This topic is 2856 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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)


Link to comment
Share on other sites

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?


Link to comment
Share on other sites

  • 4 weeks later...

This topic is 2856 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.