6 Replies Latest reply on Sep 28, 2014 12:59 PM by philmodjunk

    Speeding up Search

    mr.b.

      Title

      Speeding up Search

      Post

       

      HI All,

       

       

      Hoping you can help. I have a file for my Wife's Osteopathic Practice.

      It's nothing too fancy, deals with patient information, billing, invoicing and appointments etc.

      About a year ago we moved from hosting locally (actually on the same machine as the user was logged onto but on a separate admin account) to a hosted remote service provided by a 3rd party company. Generally it all works well and makes remote access slightly easier but the main issue is that searches for Patient names is very slow.

      I'm no master programmer but think I've done everything correctly. The name fields are indexed as is a full name calculation field which is where the search for a patient is normally performed. (It's calculations is - Case ( IsEmpty ( NameFirst ) ; NameTitle & " " & NameLast; IsEmpty ( NameLast ) ; NameFirst ; NameFirst & " "  & NameLast)

      The initial search seems to take 5 to 10 seconds but each time you then search for the same person, it's instant. However, if you log out and back in again, searching for the same person, you're back to 10 seconds for the initial search.

      Is there a way I can speed this whole initial search up as I really like having the file hosted but am getting grief of my dearly beloved about the slow search.

      The patients table has roughly 800 Records so it's not hues at all. Obviously the bookings and invoicing /payment tables are far in excess of that, currently approximately 15000 but we always start with a patient search.

       

      Hope that's enough info for someone to suggest something but please let me know if you need anything else.

       

      Kind Regards

       

      Mark Ballard

       

       

        • 1. Re: Speeding up Search
          dejosejimpaul

          I think why the search is slow is because you are trying to perform a find on a calculation field.

          Try to change the field type to a simple Text Field and set its option to Auto-Enter -> Calculatedvalue and put your formula there then uncheck the tick box that says: "Do not replace existing value of field..."

          • 2. Re: Speeding up Search
            philmodjunk

            I don't think the fact that this is a calculation field has any affect on the speed. As it's a stored, indexed field, it will have an index like any other field and the fields do not need to be evaluated prior to the search (as would be the case with an Unstored calculation field.

            Best guess is that the delay is due to the remote host being "remote". A certain amount of data is pulled over the wire during the initial query that causes the delay but it then "cached" on your local computer--making subsequent queries faster.

            • 3. Re: Speeding up Search
              mr.b.

               

              Hi Jim & Phil,

              Thanks for your interest and help.

              Jim, I've just tried you r suggestion but sadly seems to still be exactly the same so I think that, Phil, you may be correct and it's simply the fact that it's now hosted.

              I've contacted the hoster and they've replied with the following but I have a feeling that reposting locally may be the only answer:

              There is lots of bandwidth, spare processor, RAM etc at our end. I don’t have access to your system but would suspect either you are searching on unindexed fields or more likely the search process is loading lots of data and lots of data takes time to arrive over the internet. After the initial search the data is cached locally hence is faster. 

               

              He's then suggested a sync style solution with something like Easysync. Do yourselves or anyone else have any experience of this kind of addition?

              Thanks in advance...

              Mark.

               

              • 4. Re: Speeding up Search
                philmodjunk

                What he's suggesting is that you keep a copy of the database on each client and then periodically "synch" to the hosted copy. That brings its own level of complexity and possible issues to your solution and may or may not be practical for what you need to do.

                I'd first make absolutely sure that its actually the find on the calculation field that's the main source of delay. I'd run a find using a layout that is absolutely devoid of any other fields, conditional formatting, portals, etc to see if there is a noticeable difference in response times just to make sure that it's the find and not the screen update that follows that's the source of delay.

                I also just saw some very interesting and eye-popping performance differences with remote hosts (The host was in Switzerland and the Client in California bay area), where they used the perform script on server script step introduced in FileMaker 13. I have to wonder if there'd be an improvement by passing your search criteria as a script parameter to the script via Perform Script on Server and then returned the primary key to the found record via the "Result" clause of an Exit Script step. That single value could be used in a match field to display the found record. I really don't know if that will be any faster but if it works it would be less of a challenge to implement that a "synched" pair of database files.

                • 5. Re: Speeding up Search
                  mr.b.

                  Hi Phil,

                  Good call. I have returned the calculated field back to how it was and created a layout with only that on it and you're right, it now searches almost instantly. So seems like it's just the quantity of info on the first screen that may be causing an issue.

                  To be fair I guess there's quite a lot to load as there is 4 tabs each has a portal on so I'm guessing it's got to preload all that before it returns the result and getting all that remotely is the issue. I've attached a screen shot so you get the idea.

                  Is there a way that I can force it to just load one of the portals initially as pretty much always a search for a patient is going to involve a booking being made rather than an invoice or payment. I guess I could just make up some different layouts that just find the person first and then goes to the main screen but was just trying to keep it simple!

                   

                   

                  • 6. Re: Speeding up Search
                    philmodjunk

                    You might try putting each portal on a different layout to see what happens. A button can change layouts and if you get cute with the layout design, it would look much the same as though you changed panels in your tab control.