6 Replies Latest reply on Oct 12, 2007 5:06 AM by CFMXPrGrmR

    Filtration of records on Coldfusion end.

    Coldfusion_Newbie
      Hi All,

      I am a newbie to Coldfusion. My experience is predominantly in Oracle. I am caught up in an issue and would like to discuss the same in this forum.

      Technical Specifications : Database - Oracle 8i, Front End - ColdfusionMX 6.1

      Requirement : I have five drop down's in a page. They are District, Property, Floor, Room Types and Room. The desire is to have them dynamically populated. By default based on user's login credentials the drop down would be populated with default values. When the user decides to change the district, the property drop down should contain properties corresponding to the chosen district, similarily, Floor depends on Property, Room Types depend on Floor and Room depend on Room Types.

      Technical Approach : As of now, i have developed a stored procedure that returns all the various combinations for the five drop downs. There are about 80,000 rows for these combinations. The backend is in Oracle and the query joining the tables takes about 3 seconds in the backend. The entire result set is sent across to the front end. The front end loads all the drop downs with the relevant details and does some amount of filtration based on user selection. The page load takes 93 seconds !!

      Alternative approaches : Have tried, cfquery. But the limiation with this, was we had to use a form within a form and hence, that was not possible. Either the form is not recognised or , when we try to submit the form, the page itself is submitted and the error thrown, is that not all the values are available. We have tried arrays on the coldfusion side, but not much has improved.I have even tried optimising the database query, which takes insignificant time at the backend itself.

      Requesting Suggestions : If you all have solved such an issue, please let me know alternative ways to tackle the same. The issue seems to take too much of time on the front end and the page load alone takes 93 seconds. Also, if there is a way in which a form can be called within a form, in a manner that submitting the child form does not submit the parent, or if there is any other out of the box solution, please share the same.


      Cheers,
      Jay

      PS: Apologies if the description was too verbose.
        • 1. Re: Filtration of records on Coldfusion end.
          TurboMini
          Use ajax to query the db for the necessary rows after they select an item in the dropdown. You will need a second coldfusion page that outputs only data to the ajax process.
          • 2. Re: Filtration of records on Coldfusion end.
            Level 7
            You are dealing with a fairly sophisticated user interface here. Most
            of these issues are language independent but rather deal with the nature
            of internet applications and the stateless nature of HTTP requests and
            responses. You are dealing with a basic "related selects" problem,
            where the choices of one select control are dependent on the selected
            value in another control. There are many solutions to this interface,
            but the size of your requirements limits some of your choices.

            The oldest, and thus the most backwards compatible, is to divide the
            steps into separate forms. But this is also the most primitive and
            least user friendly solution. To implement it you would just make a
            form for the first value, once the user has selected the value it is
            submitted to the server and a new interface is delivered with the second
            control and it's choices relative to the selected value of the first
            control.

            With the addition of some JavaScript these forms could be included into
            separate pop-up windows or divisions, but this does not really improve
            the interface much and could be impacted by users with pop-up blocker
            add-ons to their browser.

            You could deliver all the data to the client as JavaScript arrays and
            then build JS functions to access and filter the various controls
            depending on the choices of other controls. But the size of your
            dataset probably prohibits this. It would take a very long time to
            deliver that much data to the client every time.

            The next level would be to use modern AJAX techniques. This allows you
            to make behind the scenes requests to the server to get the required
            data to update select controls after the relevant value has been chosen
            in the related control. This would be one of the top choices if you can
            rely on the JavaScript that AJAX requires and have the capability to
            learn the various technologies involved.

            The final solution I can think of, is something Flex based. One of the
            strengths of Flex is that it is not stateless. It can maintain a
            connection with the server to send and receive data as the state of the
            interface changes with user interaction. But as with AJAX this is even
            more new technology to familiarize oneself with.

            Feel free to ask more questions about the various options or how to
            implement one or more of them.

            Ian
            • 3. Re: Filtration of records on Coldfusion end.
              Coldfusion_Newbie Level 1
              Hi Phil,

              Thanks for the reply. I havent used AJAX ane my team does not possess this skill set. Within the framework of Coldfusion and Oracle, would there be a solution to this problem?


              Cheers,
              Jay
              • 4. Re: Filtration of records on Coldfusion end.
                cf_dev2 Level 1
                The old way of doing it was to use a single form. When a selection in the district list changes, submit the form. When the form is submitted, run the appropriate queries to populate the other lists based on the selected district. When another list selection changes, submit the form... etc.
                • 5. Re: Filtration of records on Coldfusion end.
                  cf_dev2 Level 1
                  If you're using CF8 it has some built-in ajax functionality IIRC.
                  • 6. Re: Filtration of records on Coldfusion end.
                    CFMXPrGrmR Level 2
                    I would suggest using iFrames for your selections. I realize it's not on the level of AJAX or Flex for being robust but you could have your five selections each within an iFrame.

                    "The entire result set is sent across to the front end. The front end loads all the drop downs with the relevant details and does some amount of filtration based on user selection. The page load takes 93 seconds !!"

                    If your selections won't change often could you cache this "entire result set" in CF instead of using a SP? This could at least reduce your query time quite a bit. Maybe look at caching the top 20 or so drop-down choices as well?