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)