Re: [ACFUG Discuss] Re: PLZ DISREGARD PREV MAIL..1.Query Caching & 2.Implementing search Feature

Mon, 16 Oct 2006 09:54:42 -0700

Query caching is one solution to alleviate the speed of the query execution.  There is the need to search page by page by a certain amount of records.  In an ad hoc solution, I have seen a second query that would perform a query on the original source based upon a start point.  The cfouput will typically have a maxrows based upon the query of a query.

For the search criteria form, storing the criteria in a shared scope other always helps. 

The goal would not to change the originating query in any way otherwise the cache will be reset for a different combination.  The search criteria would need to exists in the query of the query. 

Non-adhoc solutions include stored procedures that have increased amounts of complexity to perform conditional queries that can be either dynamically written or programatically built queries.

Object Realtional Mappers like Reactor for ColdFusion have a build in pagination scheme that makes that sort of nice.  I have not tested Reactor recently for the notation, but I saw the object set for it.

Teddy


On 10/16/06, Charlie Arehart <[EMAIL PROTECTED]> wrote:
Are folks reading my note below? Ajas, please clarify if I have understood your need correctly. I had just gone through the same dilemma on my UGTV site. I had done a similar next/prev link, but if the user had entered search criteria, I was challenged because I needed to carry that search criteria to the next screen. So this was about more than just simple query caching but indeed about carrying the search criteria (from a form value obtained on the first result page) to subsequent next/previous pages.
 
If this isn't the challenge you're trying to solve as well, my apologies. But if it is, I hope folks will see that there's more to it than just the general issues of query caching.
 
/charlie
 

From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED]] On Behalf Of Charlie Arehart
Sent: Monday, October 16, 2006 11:13 AM
To: discussion@acfug.org
Subject: RE: [ACFUG Discuss] Re: PLZ DISREGARD PREV MAIL..1.Query Caching & 2.Implementing search Feature

Ajas, I think you confused people with the subject saying "please disregard previous email".
 
As for your challenge, your challenge isn't with caching but simply that you need the "next" or "previous" pages to know that you are still working on the subset of searched data. You have a couple of choices. You could pass your search criteria from page to page in the query string, or you could store it in a session variable and use some indicator on the query string to tell the next/previous page that you mean to use the subset of data (which will then pull the search criteria from the session to run that in its SQL).
 
At that point, yes, you may want to use a cached result. Since the cache is per the SQL used, if you have the search string in the query and you'd previously run that same query within the cachedwithin time, then you will benefit from the cache.
 
As always, something to be careful with in caching is that your underlying data may change as the user is browsing the searched results. You need to pick a cachedwithin time that balances the cost of doing repeated queries against the cost of getting inaccurate data. Only you can know what's best. Some may know that you can flush the query cache, but only all cached results in the entire application, not just one, using CFOBJECTCACHE Action=""
 
BTW, query caching has nothing to do with the "save class files"  option, though yes the "max cached queries" does indeed limit how many cached query results (cachedwithin and cachedafter) you can have at one time (across the entire server).
 
/charlie
 

From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED]] On Behalf Of Ajas Mohammed
Sent: Monday, October 16, 2006 10:17 AM
To: discussion@acfug.org
Subject: [ACFUG Discuss] Re: PLZ DISREGARD PREV MAIL..1.Query Caching & 2.Implementing search Feature

Good Morning everyone,
                                             Just wondering if anyone has any ideas/suggestions for the 2 questions I had asked??? I didnt see any reply which makes me think if it is very simple or if its difficult / time consuming.

Thanks,

Ajas.

On 10/13/06, Ajas Mohammed <[EMAIL PROTECTED]> wrote:
Hello everyone, I apologize for the earlier mail sent.. I dont know what happened and the incomplete message got sent.....
                             I have 2 questions.
1) I have a search page which might return 1000's of records for a search/keyword. I have implemented Pagination i.e. showing NEXT / PREVIOUS (10) RECORDS at a time. My problem is that everytime I click on NEXT OR PREVIOUS, the query is getting executed all over again. I dont want the query to be run everytime NEXT/PREVIOUS link is clicked. I have this code to do the caching
<cfquery datasource="#client.dbname#" name="StateRegs" cachedwithin="#createTimeSpan
(0,2,13,0)#">

</cfquery>

<cfoutput query="StateRegs" group="Reg_Name" startrow="#start#" maxrows="#end#">
data shown here

<td><a href="" + disp")#">Next >>#next# records</a> </td>
(this link basically takes to the same query page, but I have a url parameter action, which if set to search will run the query and display search results)
</cfoutput>
Similarly a link for previous records. In the coldfusion Administrator, ONLY SAVE CLASS FILES IS CHECKED AND MAXIMUM NUMBER OF CACHED QUERIES IS SET AS 300.

2. The second question is also long. So plz visit this URL...http://www.sitepoint.com/forums/showthread.php?t=428803
Its about implementing multiple word search on a site and you have search for those words (maybe 3 or 5 or even more) in 4 tables.

Thanks in advance...

Ajas.


-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform

For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink
-------------------------------------------------------------
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform

For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink
-------------------------------------------------------------

-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform

For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink
-------------------------------------------------------------



--
<cf_payne />

Blog: http://cfpayne.wordpress.com/
Atlanta CFUG: http://www.acfug.org
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform

For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink
-------------------------------------------------------------

Reply via email to