CFLOCK and Persistent Components

Posted on 2003-03-06
Medium Priority
Last Modified: 2013-12-24
I am working on a new CFMX coding strategy.  In order to put less strain on SQL Server 2000, i'm writing some components that essentially manage a section of my database, such as a Product component.  These components would be loaded into the Server or Application scope, and would keep queries loaded.  In this way, I won't have to hit the product database table everytime, I just have to call Server.ProductManager.getAllProducts() or whatever.  My question:  what sort of concerns does this situation raise in regards to CFLOCK.  When I'm writing the query, do I have to lock it?  When I'm reading?  What if I do an INSERT or UPDATE and the re-load the query.  Does that need a lock?  If so, what level of locking do I need?  Please note that this is CFMX i'm referring to, which seems to be a bit friendlier w/ locking.
Question by:curmudgeon42
LVL 15

Accepted Solution

danrosenthal earned 300 total points
ID: 8084412
You would need to put a CFLOCK around every call to set or read the variable.  I would recommend just having a cached query...

<cfquery name="products" datasource="#dsn#" cachedwithin="#CreateTimeSpan(1,0,0,0)#">
  SELECT * FROM Products

This particular query would only run once per day, unless you cycled the CF service or manually updated it.


Expert Comment

ID: 8085754
I agree with dan, let coldfusion cache it for you if you want to use the same query over and over.  Other ways to speed things up: stored procedures, cf's query of a query (for small result sets), make sure you have indexes built for the right fields, cfstructs can sometimes be used in place queries if you pre-populate it with query results and finally optimization of the sql code (using a query optimizer if it's available).

Expert Comment

ID: 8085793
I assume this is related to your other question concerning application caching of queries. The wording of your question this time sheds a bit more light on the issue, I might not have been understanding what you were doing before...

As you saying that your are executing a query from within your CFC and simply copying the result set into the application scope? And if so, are you then saying that when you want to refresh the data, you are re-running the query and then re-copying it into the application scope?

If so, you are not doing yourself any good at all. There is no benefit whatsoever to doing it this way. Using a natively cached query will not only be easier, but will be faster because it eliminates the locking issues.

I was under the impression that you were going to be using the CFC to update the cached data on a row by row basis. In other words, on an update to the db you would both update the cached data and the database at the same time. Keeping the cache "synchronized" so to speak. In that scenerio, then there is a benefit. But if you are not doing that, and are simply refreshing the entire cache when the data is stale, then there is no point in it. You should just use query caching.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question