Hi, I am rebuilding my employer's bug tracking application; quite a job, but it's been fun (seriously, plus I get to be a hero to hundreds of developers who have been using the old, ugly, slow, and completely un-user-friendly app that this is replacing). I'm at the point where everything works well, and I'm doing some performance optimization.
First, the environment. DB is Oracle 8.0.4 (to be upgraded to 8.1.7 or even 9 in the near future, as soon as the old app is retired). Our primary bug table has ~90k records and there are a number of relationships, so there are joins in most queries. App server is CF5 Ent on Win2K Server. The primary search interface can be expected to return a relatively large number of results, so I have them paginated at 50 results per page. The search interface is really a "Query builder" that builds the SQL based on the paramaters the user has selected in the search form. It's a pretty complex operation, searching on a couple dozen parameters that have a large number of possible permutations. I got that part tuned and it's very fast, so no problem there. When the query is built, I store it as a session variable so that I can use it as the user pages through their results. However, that still means that as each page gets loaded, the query still needs to be run. It is my understanding of/experience with query caching that results are cached based on the name of the query, and not the actual "guts" of the query, so having multiple users each with their own dynamic queries but all run under the same query name will mean that only the first cached query gets run (at least that's what it looks like in my testing) -- is that the case? If so, what is the best way to "store" these results so that going to the next page doesn't mean the query needs to get run again? Should I do something like append the CFTOKEN for that user's session to the query name so that it is unique? Or should I store the query output as an array in a session variable? All I want is for the user to not have to wait for subsequent pages after the first page is loaded... Thanks for any tips or advice, Pete ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
