Caching a query and using the blockfactor to employ a Get
next n records is a really easy thing to implement. Its easy
to code, easy to use, easy to maintain, easy to
understandi guess i dont see where the complexity comes in?
It's worth pointing out that the BLOCKFACTOR attribute
So because something is more difficult to do, we just take
the situation which causes the most overhead on the server
and stores duplicate data.
Session variables can get you faster into trouble then
application variables because the CMFX garbage collector is
only called once a time and
Sure, you can cache everything, but sometimes it is a little harder to
get it to work. If you have a lot of WHERE a = #b# AND b=#c# OR c IN
(cfloop list#d# index=e#e#/cfloop). You need to create
mechanisms to make sure the query is uniquely cached. You don't want to
end up returning a query, with
variableName is meaned to be created by you, dynamically based on the
SQL code executed. When you have WHERE, AND, OR statements (as well as
group by, distinct, order by, joins, count, max, top, etc.etc.) in your
query sql code you must make sure the variable it is cached in is not
the same as a
I tried applicaion variables, but as Hugo pointed out since the queries will be different for each user (using different keywords), and the refinement/paging would be per user, the session
scope would make sense?
I don't know where to go from here. Its getting all messy and confusing. All I want
During the days we have posted an enormous amount of tips, tricks, code
and guides on how to do the job. You still need more info?
Micha Schopman
Software Engineer
Modern Media, Databankweg 12 M, 3821 ALAmersfoort
Tel 033-4535377, Fax 033-4535388
KvK Amersfoort 39081679, Rabo 39.48.05.380
I don't know where to go from here. Its getting all messy and confusing.
All I want to do is, store the results returned by a query somewhere in
memory so that if the search returns more than 100 records (100 records are
displayed per page), when the 'next' button is clicked.. instead of running
RAM is one of the cheapest pieces of hardware which enables you t speed
up things a lot. Cached queries don't take very much of your RAM memory.
Especially not when you are only caching it for 5 minutes as your
example states.
Micha Schopman
Software Engineer
Modern Media, Databankweg 12 M,
Thank you all for your help. I really appreciate it. I've taken Hugo's advise (thank you Hugo) and gone with session scope route. I'm storing the results in a session variable. I'm not caching anything, it just adds more complexity to a already complex solution.
Here is the code for anyone who is
This means if you have 10.000 users doing a search for dog the server
now creates 10.000 session entries each with the exact same resultset
for dog ;)
That is why I suggested application variables. Everybody has instant
access to the cache if they search for the same thing.
Micha Schopman
using the application scope creates a lot more complexity...and for
what? A transparent change to the end user? That to me would make no
sense.
Adam H
On Fri, 30 Jul 2004 14:47:54 +0200, Micha Schopman
[EMAIL PROTECTED] wrote:
This means if you have 10.000 users doing a search for dog the
So because something is more difficult to do, we just take the situation
which causes the most overhead on the server and stores duplicate data.
Session variables can get you faster into trouble then application
variables because the CMFX garbage collector is only called once a time
and not on
Caching a query and using the blockfactor to employ a Get next n records is a really easy thing to implement. Its easy to code, easy to use, easy to maintain, easy to understandi guess i dont see where the complexity comes in?
- Original Message -
From: Micha Schopman
To: CF-Talk
It could break existing caches :-) We talked about that in earlier
postings.
Micha Schopman
Software Engineer
Modern Media, Databankweg 12 M, 3821 ALAmersfoort
Tel 033-4535377, Fax 033-4535388
KvK Amersfoort 39081679, Rabo 39.48.05.380
[Todays Threads]
[This Message]
[Subscription]
My point is that more overhead on a server is, to me, an acceptable
trade offto more maintainable code, under the assumption it does not
affect the end user's view of the system. Once you see a noticeable
impact in doing it a simpler way thats when the situation should be
reassessed.
Adam
On
So? This simply means the broken cached query will just be run again
(minor performance bottleneck) and re-enter the cache. Increase the number
of cached queries in cf administrator accordingly. This is by far much more
efficient (memory usage) than storing multiple instances of the same result
I fail to see why a lot of these ideas are even necessary in this case.The
records returned number about 5000.NO ONE is going to page through all
5000 records.This is what the search filters are for.Just return the top
20, 50, 100 records ordered by whatever your business rules require ONLY IF
I don't disagree with you andy, 5000 results does sound completely
rediculous. But the fact of the matter is if someone wants to do this,
then I'll give my input on how I think it should be done. I am not one
to refuse to give helpful insight on how to accomplish a task just
becuase I think it is
To that end, when you work for the Government as I do, the ridiculous quickly becomes the ordinary.A query returning 5000 records, and a report to display all those records, is not only a possibility, its LIKELY.
All you can do as a designer is get your objections on record, then do your best to
Adam,
I am not saying that your input is worthless, but I think it is a good
developer's duty to make a case for more efficient ways to do things.From
what I gathered of the initial post, nothing was set in stone.Given that,
I can't in good conscience agree with most of the methods put forth
The subject didn't have anything to do with reports, but a list returned to
the user in it's entirety.The discussion was about caching, and in this
case there is no need to return all 5000 records to the user because the
user will not page through them anyway without entering some sort of
I worked for a DOD lab for years and I disagree. It's not LIKELY, it's a
CERTAINTY. Some FOIA sites have to do this sort of thing regardless of
whether or not anyone will ever use it. I had to write a report for a
clean-up project that often returned several thousand rows of complex
chemical data
This works pretty well - but on some RDBMS's you can get a buffer overrun on your IN () list if it grows very large.
I've had that happen at 10,000 or so. Actually, it is more likely when using Cfqueryparam because the array allocated
for the bind may have a maximum value. If you pass in an actual
I think you can do it in sql, but I'm no wizard there -- I am sure there
is one of the SQL Aces that can answer you there.
But what you can do -- store the result query in the session scope, then
you can QoQ it to get the results that you want, without having to go to
the database. By the way --
Your message is a reply to anothers post and is more than 100 lines of
text. Unless you are a major writer, your probably adding a lot of the
previous replies. Please trim your posts when replying. Thank you.
I think you can do it in sql, but I'm no
wizard there -- I am sure there
is one of
Try using full text search, which gives far more superior speeds, and
superior search results. Why do you need 5000 results btw, do you really
think someone is going to browse them all?
Micha Schopman
Software Engineer
Modern Media, Databankweg 12 M, 3821 ALAmersfoort
Tel 033-4535377, Fax
And try using cached queries, for 1 minute or some.. :-)
Micha Schopman
Software Engineer
Modern Media, Databankweg 12 M, 3821 ALAmersfoort
Tel 033-4535377, Fax 033-4535388
KvK Amersfoort 39081679, Rabo 39.48.05.380
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
You can use cfquery in lieu of cfstored proc and cache the results.
That should solve the speed for records 101-200, etc.
- Original Message -
From: Micha Schopman [EMAIL PROTECTED]
Date: Thu, 29 Jul 2004 13:33:48 +0200
Subject: RE: The search is killing the server. Please help!
To:
I like Hugo's idea of putting the query result in a session variable.
But you shouldn't have to do a QofQ you should jsut be able to use
your current code. If you want to change your SQL as far as I know the
only way to do this is something like this:
SELECT Top 100 Partnumber From Products
WHERE
Have a look at the help for CFQUERY and CFOUTPUT.
In particular:
CFOUTPUT - StartRow and Maxrows attribues.
CFQUERY - CachedWithin attribute
I would either cache the query or store it in a scope. Then output the records you require using startrow (= (page num -1) * 100) and maxrows (= 100).
Caching queries in application scope is a gray area, sometimes it can be
implemented very soon, and sometimes it is very hard to implement. It
all depends on the sql code being executed.
Micha Schopman
Software Engineer
Modern Media, Databankweg 12 M, 3821 ALAmersfoort
Tel 033-4535377, Fax
a lot of people have suggested that I cache the results. To do this I must use the cfquery tag, but I'm using the cfstoredproc tag. Is there any cachedwithin equivalent?
someone else suggested that I store the results in session variables. Won't there be a performance hit by storing for ex 5000
You have 2 options,
Use cfquery to execute the stored procedure (I prefer this method, much
better readable instead of that ugly cfstoredproc tags) and then cache
it. Or define a sql code based variablename based on the parameters in
the sql to store the data into an application variable.
Micha
I've always used CFQUERY as well when executing stored procedures.but you've got me wondering, is there a performance hit that occurs here? Or are there other distinct advantages to using CFSTOREDPROC tag?
Brian
- Original Message -
From: Micha Schopman
To: CF-Talk
Sent: Thursday,
I always have used cfquery, according to Macromedia document
cfstoredproc should be faster, but I've only seen the opposite being
true. Like it is important though if one function is 0.1 ms faster
than the other function ;)
I prefer cfquery because it makes my code more readable, and I see
Same here. CFQUERY is such a flexible tag, i abuse the heck out of it.
- Original Message -
From: Micha Schopman
To: CF-Talk
Sent: Thursday, July 29, 2004 9:21 AM
Subject: RE: The search is killing the server. Please help!
I always have used cfquery, according to Macromedia document
Micha,
Why are you talking about application variables? Are you suggesting a
struct of different keywords?
Since the queries will be different for each user (using different
keywords), and the refinement/paging would be per user, the session
scope would make sense, right? No need for caching
I don't know how to user cfquery to execute the stored procedure. I thought they are tags that compliemnt each other (either or).
I might be wrong but are you suggesting to wrap the cfstoredproc code inside a cfquery tag? Sorry if this a silly question to ask
[Todays Threads]
[This Message]
Yes cfstoredproc is supposed to be faster...I beleive I read/heard
somewhere that cfstoredproc only takes 3 steps to exectute everything
and cfquery requires something like 10 steps(might have been a
dream)...I have not seen a difference generally.
Adam H
On Thu, 29 Jul 2004 16:21:16 +0200,
The situation is a bit more complicated, with cachedwithin you have the
power of caching + defining the expiration. The negative effect of
cachedwithin is that it uses a first in first out system, because
ColdFusion has only has a maximum amount of cached queries it can hold
(defined in the cf
On my last project I had to present a large amount of items to the user
using a next/prev style interface.Since there were a lot of filters to
narrow down the returned data, I just ended up presenting the top 20 items
to the user and basically forced them to use the filter to narrow their
search.
I don't agree with this.You can cache pretty much any query as long as the
server has the resources, but that doesn't mean you should cache everything.
In my experience caching in general takes a bit of forethought to implement
in the most efficient way.As long as you return a query, you can cache
You simply put the procedure call inside the cfquery tag, where you would normally write your SQL.
cfquery
EXECUTE procedureName
/cfquery
- Original Message -
From: cf coder
To: CF-Talk
Sent: Thursday, July 29, 2004 9:52 AM
Subject: Re: The search is killing the server. Please
The negative effect of cachedwithin is that it uses a first
in first out system, because ColdFusion has only has a maximum
amount of cached queries it can hold (defined in the cf
administrator). This means if you have cached queries in a
website not related to a search, those cached
Yes cfstoredproc is supposed to be faster...I beleive I
read/heard somewhere that cfstoredproc only takes 3 steps to
exectute everything and cfquery requires something like 10
steps(might have been a dream)...I have not seen a difference
generally.
Unfortunately, things are just not that
I don't quite understand the code you posted.
cfset tmp = structNew()
cfset tmp [expiration] = CreateTimeSpan(0,0,5,0)
cfset tmp [variableName] = variableName
cfset ArrayAppend(application.cachedQueries, tmp)
I keep getting an error when I try to dump application.cachedQueries that its not
I don't know how to user cfquery to execute the stored
procedure. I thought they are tags that compliemnt each other
(either or).
You can invoke stored procedures using CFQUERY with some database servers,
such as SQL Server. Typically, you can do this by invoking a stored
procedure using
The advantages of doing this instead of using the
CFSTOREDPROC tag are that you can cache recordsets using
CACHEDWITHIN/CACHEDAFTER, and that you can write it a little
more easily in some cases.
Is there anyway to reference more than one recordset? We have a lot of
stored procs that return
I always have used cfquery, according to Macromedia document
cfstoredproc should be faster, but I've only seen the
opposite being true. Like it is important though if one
function is 0.1 ms faster than the other function ;)
If you're just calling stored procedures from CFQUERY, I doubt
Mik is me, Mike, my e key has some burrito in it.:-)
The advantages of doing this instead of using the
CFSTOREDPROC tag are
that you can cache recordsets using
CACHEDWITHIN/CACHEDAFTER, and that
you can write it a little more easily in some cases.
Is there anyway to reference more
Yeah, it's a documented tag that is nested inside the cfstoredproc tag.
It's called cfprocresult and it allows you to return multiple recordsets.
-Original Message-
From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 29, 2004 12:33 PM
To: CF-Talk
Subject: RE: The search
Yeah, I know about that.. I was referring to the ability to reference
multiple record sets from a stored procedure when you call the procedure
within cfquery tags.
Mike
Yeah, it's a documented tag that is nested inside the
cfstoredproc tag.
It's called cfprocresult and it allows you to
Is there anyway to reference more than one recordset? We have
a lot of stored procs that return 2 or 3 so we find ourselves
using cfstoredproc in those instances and cfquery with EXEC
PRON_NAME in other instances.. Mainly select queries where we
want to cache the results.
To the best of
53 matches
Mail list logo
