0

I'm trying to cache a stored procedure. I have ColdFusion 10 downloaded and it is running on a Railo 4 server. I have the database connection setup in my Railo Admin ( a default cache ) and according to the Adobe docs I only need to use the cachedWithin attribute and it should get cached. However, it does not and I know this for sure because my pages take over 10 sec. to load!

I tried dynamically caching it using the cfcache tags around the stored procedures, but this kept caching the entire page which I do not want to cache. CachePut and CacheGet also work, but my code is way to complicated to effectively accommodate them.

Super confused.

Here is the relevant code:

<cfstoredproc datasource="#XXX#" procedure="XXX" cachedWithin="#CreateTimeSpan(0,3,0,0)#">
                <cfprocparam type="In" value="#IDate#" cfsqltype="CF_SQL_TIMESTAMP">
                <cfprocparam type="In" value="12" cfsqltype="CF_SQL_INTEGER">
                <cfprocparam type="In" value="1" cfsqltype="CF_SQL_BIT">
                <cfprocresult name="DeptQuery">
</cfstoredproc>

UPDATE: It turns out that the stored procedure is not the one taking all that time! And I've got it cache at last! But I'm confused about how it's doing it since I'm caching 1000s of variants of the same stored procedure over multiple pages (with each page alone having multiple stored procs) and all I've specified is a cachedWithin param. How does it know which procedure is which when it pulls them up to the view?

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
F Z
  • 21
  • 5
  • 1
    _my pages take over 10 sec. to load_ - Are you sure it is the stored procedure query that is taking all of this time or is it the processing of the query results taking the time? Turn on debugging and look at the output. This will not only show you if the query(ies) are being cached or not but also show you where ColdFusion is spending it's processing time. Are you using the `cfstoredproc` tag or `cfquery` tag? It would help if you showed us any relevant code. – Miguel-F Jun 13 '13 at 13:43
  • Also, put a cfabort tag after you call your SP. Since all you are interested in is seeing the execution time, the abort tag will get that to you quicker. – Dan Bracuk Jun 13 '13 at 13:51
  • I have used the cfdump tag, but it does not detail caching. – F Z Jun 13 '13 at 14:01
  • what does the value of #IDate# look like? – duncan Jun 13 '13 at 14:11
  • – F Z Jun 13 '13 at 14:15
  • For debugging see the [Debugging & Logging section of the docs](http://help.adobe.com/en_US/ColdFusion/10.0/Admin/WSc3ff6d0ea77859461172e0811cbf3638e6-7fe0.html) Basically, after you turn it on in the admin and add your ip address to the debug list it will show you all kinds of useful information in your browser. **Be sure that you add your ip address for debugging. If no ip addresses are defined _ALL_ users will see the debug info.** – Miguel-F Jun 13 '13 at 14:16

1 Answers1

1

You're passing in (what looks like) a date/time. Query caching is based on the arguments to the query (or more specifically, the SQL that would be used). I'm assuming you're passing in different date/time valus each time. Each different date/time value would have its own cached result, unless you're just passing in the same date value each time?

e.g. these would be treated as two different objects for caching:

<cfstoredproc datasource="#XXX#" procedure="XXX" cachedWithin="#CreateTimeSpan(0,3,0,0)#">
     <cfprocparam type="In" value="2013-06-13 09:00:00" cfsqltype="CF_SQL_TIMESTAMP">
</cfstoredproc>

<cfstoredproc datasource="#XXX#" procedure="XXX" cachedWithin="#CreateTimeSpan(0,3,0,0)#">
     <cfprocparam type="In" value="2013-06-13 10:00:00" cfsqltype="CF_SQL_TIMESTAMP">
</cfstoredproc>

From the documentation:

To use cached data, the current query must use same SQL statement, data source, query name, user name, and password.

duncan
  • 31,401
  • 13
  • 78
  • 99
  • The same date can get sent in multiple times but I also have a string param that I'm sending in that I had omitted from the example. Is that how it knows which cached query is which? – F Z Jun 13 '13 at 14:21
  • yes exactly, so even though your date is always identical, if that string parameter is different each time, they'll each be considered as separate SQL queries for caching – duncan Jun 13 '13 at 14:57
  • This _isn't_ a good solution, but you could run your stored procedure without the string and pull everything back and then use Query of Queries to pull out the specific results. This is not a good solution, but would help. – Matt Busche Jun 13 '13 at 17:31