cachedwithin with a query that accepts varirables

[bnymk]

Hello all;


I am using Cachedwithin with a createtimespan(0,0,30,0) in my query and the performance has been very satisfying. But unfortunately, my query takes in argument that will be passed in by users. The query looks like this.
SELECT DISTINCT clinic
FROM aim_clerk, aim_clinic_access
WHERE dmis = '#Getdmis.dmis#'
AND aim_clinic_access.MEPRS = left(aim_clerk.meprs ,3)
AND provider is not null
Order by Clinic

When I'm not using the cachedwithin, then the time it takes for the page to load is very slow. But with the cachedwithin, after the first load, since it's stored on the server's ram, the response time is very fast. I would like to keep these feature but how can I implement it in a query that takes in passed arguments in it's where clause???

Thanks.

"Behind every great fortune there lies a great crime", Honore De Balzac

 

[r937]

remove the ORDER BY from your query to speed it up

consider changing the name of the query to match the variable passed in

(note: i've never done this so i don't know for sure it's possible)

<CFQUERY name="dmis#Getdmis.dmis#query" ... >

rudy

http://r937.com/

SQL Consulting

 

[webmigit]

Though I've never done it either, I've considered Rudy's suggestion before and it would work... One change... because of potential special characters..

<cfset qname=hash("dmis" & getdmis.dmis & "query")>

And then your opening cfquery name:

<cfquery name="#qname#"...>

Because of the way cfoutput and cfloop work, you can also use #qname# in the query attributes for those tags..

Hashing returns a (supposedly unique) compilation of characters based on the string being hashed. Theoretically two strings cannot hash to the same value so uniqe strings will always form different names so you have your differing query name.

Though I doubt how two strings can never hash to the same value, there are multiple efforts using mass amounts of computing power to find two strings that do hash to the same value and they've failed so far. Its definitely secure enough to use here. It will do what you want.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[bnymk]

thanks guys for your advice. Will try your suggestions.

"Behind every great fortune there lies a great crime", Honore De Balzac