cachedWithin issue

[GUJUm0deL]

Hmmm, I'm somewhat in a pickle here: I'm starting to use cfqueryparam in my queries more and more, and in some cases I need to use cachedWithin attribute in my cfquery.

Yet, that's not allowed. I get an error: Using "cachedWithin" or "cachedAfter" in CFQUERY with CFQUERYPARAM is not allowed

What does someone do in the predicament? I don't have to use cachedWithin attribute (thought would be nice if I could)

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[wrighterb]

Can you show sample of your code (Query)

 

[ecobb]

You can't use cachedWithin with cfqueryparam, unfortunately you have to pick one or the other. However, if it's used right (and your database supports it), cfqueryparam will allow the use of SQL bind parameters, which improves performance. It's a little bit like caching, but from the database's end instead of CF.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[GUJUm0deL]

ECAR, I use MS SQL Server (although the site is on a shared environment from the hosting company). I run some tests.

wrighterb, the code is a <cfquery> tag passing the cachedWithin attribute and using the cfqueryparam in the WHERE clause.



____________________________________
Just Imagine.

http://www.imajinarts.com

 

[Sarky78]

GUJUm0deL

Like Ecar says, using cfqueryparams will offer the performance advantage that you are looking for using the bind attributes. What I've seen done in the past is to have one query with no where clause that i then cache, then do a QofQ on the cached query using the queryparams for security and performance for the data that i want. something like this:

<cfquery datasource="abc" name="qryCache" cachewithin="#CreateTimeSpan(0,1,0,0)#">
select a,b,c,d,e,f
from tableA
</cfquery>

<cfquery dbtype="query" name="qryDate">
select a,b,c,d,e,f
from qryCache
where a = <cfqueryparam cfsqltype="cf_sql_varchar" value="#myValue#">
</cfquery>

Hope this helps!

Tony

 

[ecobb]

Honestly, if you're using SQL Server, I would recommend ditching cfquery and going with Stored Procedures. Especially if you're using more than one query on a page, you can just run it all in one Procedure and save multiple database calls (a performance saver in itself!). Let the database do the database work, and just use CF to send/receive info from the Procedures.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher