Skip navigation
Currently Being Moderated

Query of Search Results

Apr 16, 2013 11:29 AM

I need some best practices advice.  I'm not sure if there is a better way to do what I'm trying to accomplish.  The execution time is very high which is why I'm looking for alternatives.  I welcome any tips to help reduce this execution time.  Hopefully, I can explain this clearly because I'm not seeing it clearly.  I'm going to just use one form field for this example, however, there are about 10 search field options.

 

Multiple items (checkboxes) can be selected for one field.  I am going to use CODES for my example.

 

The cfquery on the results page uses a view that has been created in the database.  This view joins a primary table (company) to several other tables (i.e., codes) that possibly have many records for that one company.  Within the cfquery, I use IN for the joined tables just to see if that company has any of those records.  It appears to be working and not taking too long.  In order to output the results, a  distinct company is being used because multiple rows could be returned for one company, and I only want to output each company one time. 

 

Then, I want to output the company information.  I also want to output the multiple codes for returned for each company.  So, I have to do a little query within the main query output to get those records.  Because there are so many search field options, this is where I believe the execution time is slowing down because is has to loop very every company returned (800+) and multiple search fields means different queries.  Below is a sample piece of the code.

 

I am thankful and open to any ideas.  Sincerely.

 

<cfquery datasource="xxx" name="Company">

Select distinct company

from vwCompany

where active='true'

cfif isdefined("code") and code neq ''> and code IN (<cfqueryparam value="#form.code#" list="yes">)</cfif>

</cfquery> 

 

<cfoutput query="company">

 

<CFQUERY DATASOURCE="xxx" NAME="getCodes">

    SELECT     *

    FROM     tblCodes

    WHERE code IN (<cfqueryparam value="#form.code#">)

</CFQUERY>

 

 

#company#

codes: #valuelist(getcodes.code)#

 

</cfquery>

 
Replies
  • Currently Being Moderated
    Apr 16, 2013 9:17 PM   in reply to Balddog4

    You can try to join the queries and get the result as one query.

     

    Using queries of query, you can display the result for each company.

     

    Your first query will return all the results. Second query will use the first query(queries of query) and get the results for each company.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 17, 2013 2:41 PM   in reply to Balddog4

    The best answer, as stated earlier, is to figure out a way to get all your data with a single query by joining tables.  However, if that is not appropriate for the situation at hand, something else to look at the the sql keyword "in" along with the ColdFusion valuelist.  I'd give you an example but for some reason I can't use carraige returns on this site right now.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 17, 2013 4:38 PM   in reply to Dan Bracuk

    To give you a short n sweet on QoQ, lets say I have a master query:

     

    <cfquery name="allTheData" datasource="myDSN" cachewithin="#createTimeSpan( 0, 1, 0, 0 )#">

         SELECT

              p.FirstName,

              p.LastName

         FROM Persons AS P

         ORDER BY P.LastName ASC,

              P.FirstName ASC

    </cfquery>

     

    This queries the database for all the records, and then stores it into a server-controlled query cache for 1 hour (cachewithin attribute).  From there, I can perform a query on that cached query which runs at BLAZINGLY fast speeds, like

     

    <cfquery name="Johns" dbtype="query">

         SELECT * FROM allTheData WHERE FirstName = 'John'

    </cfquery>

     

    <cfquery name="Js" dbtype="query">

         SELECT * FROM allTheData WHERE LastName LIKE 'J%'

    </cfquery>

     

    These 2 queries are run against the cached first query (allTheData) and come back VERY quickly (cause they don't hit the original database).  Just be careful!  You don't want to cache all the data if there is MASSIVE amounts of data or if that data changes frequently (as it'll take up lots of processing time, server resources and can become outdated)

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 17, 2013 6:17 PM   in reply to Aegis Kleais

    You don't have to cache a query to make it available for query of queries.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 17, 2013 6:35 PM   in reply to Dan Bracuk

    Yeah, you don't.  You cache a query because in the event the query pulls a lot of data that doesn't change often, you can increase the performance of the retrieval by pulling from the ColdFusion query cache rather than hitting the RDBMS again for the same data.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 18, 2013 10:33 AM   in reply to Balddog4

    The answer might lie in your getcompanies query.  It could be as simple as doing a select distinct.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 19, 2013 1:20 AM   in reply to Balddog4

    <cfquery datasource="xxx" name="Company">

    Select distinct company, codes

    from vwCompany

    where active='true'

    cfif isdefined("code") and code neq ''> and code IN (<cfqueryparam value="#form.code#" list="yes">)</cfif>

    </cfquery> 

     

    from this you can take distinct compnay and get the codes accordingly.

     

    <cfquery dbtype="query" name = "getDisCompany">

         select distinct company from company

    </cfquery>

     

    <cfloop from =1 to =#getDiscompany.recordcount# index = i>

        

    <cfquery dbtype="query" name = "getDisCodes">

         select distinct codes from company where company = #getDisCompany.Company[i]#

    </cfquery>

    </cfloop>

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points