3 Replies Latest reply on Aug 16, 2015 9:39 PM by philmodjunk

    More efficient way to match all found records

    BenT

      Title

      More efficient way to match all found records

      Post

      Hey all, so for the purposes of understanding, I have two tables in my Filemaker database:

       

             
      • Products
      •      
      • Inventory

       

      Our "Products" table has over 50,000 items, these are SKU's. Not all of this 50,000 SKU's are in stock, so we have another table for "Inventory". When an a product gets in stock, it is added to the inventory table, and daily, "0 inventory" items (aka, items that have sold out), are purged. 

       

      Having said that, my Inventory table usually floats around 3000 items. 

       

      So I'm working in the Products table and I'm working on a script that lets me find items that have been marked "High Priority", but I only want to find items that are in-stock. 

       

      So originally, I setup the script a little like this:

       

             
      • Enter Find Mode
      •      
      • Input text "High Priority" into "Products::Priority" field
      •      
      • Input value ">0" into "Inventory::Stock" field
      •      
      • Find
      •      
      • Goto Layout - List view

       

      So, essentially this script starts with all 50K products, then matches ones that are "high priority", which lets say is 10K of them, then it goes and finds ones that in are in stock (based upon the calculation of that stock value being greater than 0). But that calculation has to go through all 50K products. Then the two results are referenced, and I get my output.

       

      As you may have noticed, this seems incredibly inefficient, especially since I know that there are only about 3K products actually in stock. 

       

      Any recommendations to limit to the query to only these items in stock (by using a value from the "Inventory" table) without having to do a calculation for all 50K?

       

      Regards, Ben

        • 1. Re: More efficient way to match all found records
          philmodjunk

          Is the inventory::stock field an indexed number field or an unstored calculation field?

          • 2. Re: More efficient way to match all found records
            BenT

            Phil,

            So slight change here... I grossly oversimplified that actual "Inventory::Stock" field. It's actually a "Products::QtyInventoryTotal" field, which is an unstored calculation which is simply "Sum( Inventory::Qty. Fed Out)".

             

            Wherein the "Inventory::Qty. Fed Out" field is also an unstored calculation. This probably helps explain why the original query I proposed takes so long to process.

             

            Hopefully that doesn't too overly complicate the situation.

            • 3. Re: More efficient way to match all found records
              philmodjunk

              The fact that it's an unstored calculation field is what makes it so slow. There's no index in place for the find to use to produce quick results.

              If you can put a number field in place that is updated with each inventory transaction that modifies the inventory total for a given product, you then have a number field that can be used in this find to produce far faster results.

              A less complex solution--but also slower would be to perform a find for the "high priority" records, then return to find mode, specify the inventory level and then constrain the found set instead of performing a find. This limits the unstored calculation field evaluations to just those records that are High Priority.