Chuck Posted March 29, 2016 Share Posted March 29, 2016 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 Link to comment Share on other sites More sharing options...
Wim Decorte Posted March 30, 2016 Share Posted March 30, 2016 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 More sharing options...
Chuck Posted April 27, 2016 Author Share Posted April 27, 2016 Wim, Thanks for the FTS suggestion. I already owned it, and I'll read that chapter. Chuck Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3062 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 accountSign in
Already have an account? Sign in here.
Sign In Now