4 Replies Latest reply on Nov 30, 2015 8:53 AM by jurgmay

    Building an advanced search module

    jurgmay

      Hello!

       

      I have a subscription database which I need to pull data from on a regular basis and I'm thinking about building a module to simplify the process.

       

      The two tables I'm interested in are the Customers table and the Subscriptions table. The Customers table contains information such as name, address and so on. The Subscriptions table contains data such as the subscription start date, end date, start issue number, end issue number, publication name. The two tables and joined using id fields (Customers::id = Subscriptions::id_Customer) and the subscriptions table has 50,000+ records.

       

      The data I need to pull can vary. Here are some examples -

       

      - All customers who are currently subscribed to publication A

      - All customers who are subscribed to publication A but have NEVER subscribed to publications B or C

      - All customers who have NEVER subscribed to publications A, C or D.

      - All customers who have subscription to ANY publication in the past but are not currently subscribed to ANY publication

       

      I've attached a screenshot of the UI that I have in mind to build the query.

       

      So my question is this... should I construct an SQL query or will I get better performance using native Finds and then Extending / Constraining the Found Set?

       

      I'd appreciate any thoughts or input on this.

       

      Many thanks,

       

      Juerg

        • 1. Re: Building an advanced search module
          wimdecorte

          Hard to say without knowing all the details of your design.  The simpler you can keep the SQL query the faster it will perform.  I would probably do both approaches and test them.

          • 2. Re: Building an advanced search module
            jurgmay

            Hello,

             

            Apologies for the slowness in replying - I didn't realise you'd replied to my question!

             

            I prefer ExecuteSQL as I figure it's easier to build the SQL based on the users selections so I'm tackling that approach at the moment but optimising the SQL is going to be the challenge here. It's taking 25-26 seconds to perform the query even using PSOS - acceptable but I'd like to improve it. Let's see how that goes...

             

            Cheers,

             

            Juerg

            • 3. Re: Building an advanced search module
              jormond

              How many records in the customer and subscriptions table are you talking about?

               

              The types of SQL queries you are talking about may be very difficult to optimize to make run fast in FM. Since FM is not a native SQL database.

               

              Couple of options:

               

              1. Use a Virtual List ( google it, you should find a lot of examples and explanations ), to pull in relevant data only. Then query that with your SQL. The kind approach like chopping off large chunk of rock that won't be in the final statue so you can work with as little stone as possible.

               

              2. Use native FM finds. You may find, as long as you are not searching on unstored calcs, that they are much faster and you can creatively combine them to generate the exact results you want.

              • 4. Re: Building an advanced search module
                jurgmay

                Hi Joshua,

                 

                There are 12,000 Customers and 57,000 Subscriptions.

                 

                I found an old discussion which suggested that instead of using JOINs it was best to run separate queries and then use IN to get the final list of IDs. That seems to be working really well so far but I'm going to run native finds as well and see which works the best in terms of performance.

                 

                I've heard of the Virtual List technique but have never used it so I'll be checking that out too.

                 

                Thanks for your help

                 

                Juerg