9 Replies Latest reply on Mar 30, 2016 2:17 PM by chivalry

    Table Caching Techniques?

    chivalry

      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

        • 1. Re: Table Caching Techniques?
          user19752

          As you don't have "last modified", you need upload all set of primary key and hash in FM table or download them from MSSQL.

          And you can't edit MSSQL schema, you may need download.

          So, I don't have any idea other than simple matching import on FM table.

          • 2. Re: Table Caching Techniques?
            beverly

            External SQL Sources can also be used without "live connect". You may make a standard Import script step and use a SQL query to get the data to import. I typically have a separate DSN (data source name) set up for Import and Execute SQL (script steps) from the ESS set up.

             

            beverly

            • 3. Re: Table Caching Techniques?
              chivalry

              Beverly,

               

              That's an interesting idea that I hadn't thought of. Thanks for the suggestion. If I may ask for clarification, why separate the DSNs for Import and Execute SQL? I'm not saying one shouldn't, you sound like you're more experienced in this than me. I'm just trying to understand the reasoning.

               

              Thanks,

              Chuck

              • 4. Re: Table Caching Techniques?
                beverly

                Hi, Chuck! mostly for my own sanity! you'd get different logging that may be easier to debug (if needed). Then when using the Import or ExecuteSQL and choose a source, they would appear in any DDR or errors or server logs. They are on the graph separately. Any Import is a table in FMP (not an external table, not "live") and you can create scripts and calculations to make UPDATE, INSERT, DELETE (with the Execute SQL script step) if needed.

                 

                beverly

                • 5. Re: Table Caching Techniques?
                  chivalry

                  Beverly,

                   

                  Well, I don't think that importing using an ODBC source will work after all, unless I'm missing something. Perhaps a relevant fact that I didn't mention is that this is a FMGo app, so the importing would need to take place on the server, but from what I'm reading FMS only supports importing files in specific directories.

                   

                  I think dealing with local data whenever possible is the right path however. The final solution only needs to edit two tables in the MSSQL database, everything else just needs to be viewed. So I'm looking at scripting the creation of records on the server instead of importing them.

                   

                  Thanks,

                  Chuck

                  • 6. Re: Table Caching Techniques?
                    beverly

                    I see. Well using FMGo will not allow this kind of connection (ODBC). How often does this data need to be "current" for the FMGo users to access, should someone on FMPro be able to trigger the actual import?

                     

                    Is it something you can make as CSV and have FMGo access for import (or the FMServer import and then FMGo would see a standard table of data?

                     

                    beverly

                    • 7. Re: Table Caching Techniques?
                      chivalry

                      Automatic is going to be the name of the game, so a manual import isn't really an option. The original problem that lead to all of this was that searching across relationships in MSSQL tables was abysmally slow, even for just 4,000 records or so, probably because we were searching for records where related data had a certain value.

                       

                      Ideally, when user's log in, the data will be updated quickly in the background, but we won't know if it's quick enough until we begin testing. If the update takes too long for the users' experience, I plan to schedule it to update every five minutes or so. The scripting of record creation is working fine (as opposed to importing). Sounds like a feature request to put here: Allow FMS to import from served files, or at least between tables within a single file.

                      • 8. Re: Table Caching Techniques?
                        beverly

                        trying again...

                        in addition, I think you said you do NOT have the ability to control the MySQL. Otherwise I'd suggest a "view" of data that would narrow down what you need to see (with ESS) and would be seen in the FMGo as well. But that's an admin setup process on MySQL.

                         

                        MySQL :: MySQL 5.7 Reference Manual :: 20.5 Using Views

                         

                        would you be able to get the admin do this if you cannot? it would certainly speed up what's shown!

                        beverly

                        • 9. Re: Table Caching Techniques?
                          chivalry

                          I'll check. The admin isn't an expert in MSSQL. The back-end database is part of a purchased manufacturing tracking app. They weren't happy with the clock-in/clock-out portion, especially its mobile version, which is why I'm building this. But I'll ask him to build views and we can see if that helps.