3 Replies Latest reply on Feb 6, 2013 4:59 PM by tomoy

    SQL ESS Search Performance - best practices


      Hi - I'm recreating in FMP12 a solution I built a decade ago in FMP5. The old solution could import data from an MSSQL database and at that time the data was in FMP so it would auto-index and searching these fields became relatively quick.


      In the new solution I'm trying to find a place for this awesome ESS SQL power, have created a shadow table and dropped some of those fields into a different table's layout. I can search a single field, and it takes about as much time as I'd expect to having to reach through to an un-indexed field. Try to do any fancy searches though, and it becomes uselessly frozen.


      So, should I resolve myself to importing/updating locally into FMP fields for any field I want to search? It starts to diminish the power of working with shadow tables/fields. I'm guessing 75% of my fields will need to be indexed. Curious what people do in these types of cases. I'm basically using FileMaker to house 'metadata' that won't gracefully fit into our ERP's inventory control system.


      Please pardon any stupidity in advance, leaping from v5 to v12 is a big one, and I'm trying to dust off my cobwebs. Not a developer - just IT mgr in a small office trying to solve productivity problems.



        • 1. Re: SQL ESS Search Performance - best practices

          If you query the SQL DB directly, with a "fancy" search,  is it any faster ?


          check that an index exists for the search fields you use




          >  Try to do any fancy searches though, and it becomes uselessly frozen

          • 2. Re: SQL ESS Search Performance - best practices

            As mentioned, make sure that the ESS/SQL field(s) you are searching in are indexed on the SQL side.


            You can get potentially dramatic speed increases by using the "field contents match" operator in FM. By this I mean when in find mode in FM, put    ==searchValue   into the ESS field. In my case, the SQL table has 300,000+ records in it and the difference in searching one of the main fields is significant.


            If you can, create one or more views in the SQL dB that will cut down the amount of data that you are linking to via ESS. Use the view as the basis for your ESS table instead of the raw table. This is assuming that your raw tables have a lot of records. If the tables you need to use aren't too big (in record count and number of fields), then can probably skip creating special views.


            Interestingly, if you use GTRR in FM to get a found set of records in your ESS table, it works pretty fast as well. Apparently, GTRR uses the same kind of "field match" logic as searching with the "==" operator.


            Credit these tips to Steven Blackwell. He had an article about this quite some time ago.





            • 3. Re: SQL ESS Search Performance - best practices

              Thanks for both of your responses.  Digging around in SQL, it appears I'm hitting a non-indexed View, and cannot index it in its current form. (It's not schemabound and has too many joins.)  It has half a million records in it as it pulls product data (10K records), and multiple related records from another table (~50 per product).  It's the ERP's structure for allowing the addition of 'infinite' optional fields to the product.


              So to answer your follow-ups: no, querying in SQL isn't any faster. Either I do some fancy footwork with the view, or go straight to the tables which can be indexed and pull the data over to FMP, in which I can make the relationship and build from there.


              Thx for the pointers - I've got some work to do!