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
Sent: Friday, July 30, 2004 8:11 AM
Subject: RE: The search is killing the server. Please help!
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
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
set within either application or session scopes.
I still fail to see where the complexity lies.
Dave
-Original Message-
From: Micha Schopman [mailto:[EMAIL PROTECTED]
Sent: 30 July 2004 14:39
To: CF-Talk
Subject: RE: The search is killing the server. Please help!
It could break existing
.
Andy
-Original Message-
From: Micha Schopman [mailto:[EMAIL PROTECTED]
Sent: Friday, July 30, 2004 8:48 AM
To: CF-Talk
Subject: RE: The search is killing the server. Please help!
This means if you have 10.000 users doing a search for dog the server
now creates 10.000 session entries each
-
From: Micha Schopman [mailto:[EMAIL PROTECTED]
Sent: Friday, July 30, 2004 8:48 AM
To: CF-Talk
Subject: RE: The search is killing the server. Please help!
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
to minimize the damage.enter caching.
- Original Message -
From: Adam Haskell
To: CF-Talk
Sent: Friday, July 30, 2004 11:28 AM
Subject: Re: The search is killing the server. Please help!
I don't disagree with you andy, 5000 results does sound completely
rediculous. But the fact
the server. Please help!
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
is killing the server. Please help!
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
the server. Please help!
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.
[Todays Threads]
[This Message]
[Subscription]
[Fast
is killing the server. Please help!
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.
[Todays Threads]
[This Message]
[Subscription
Hello everybody, I'm working on a logging system. The site search as you can imagine is very complicated and a lot depends on it.
I have written a stored procedure for the search to make the search quicker with a lot of inner and outer joins (the usual). The seach is very quick most of the times,
: The search is killing the server. Please help!
|
| Hello everybody, I'm working on a logging system. The site
| search as you can imagine is very complicated and a lot depends on it.
|
| I have written a stored procedure for the search to make the
| search quicker with a lot of inner and outer
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: CF
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
).
Aaron
- Original Message -
From: cf coder
To: CF-Talk
Sent: Thursday, July 29, 2004 8:25 PM
Subject: The search is killing the server. Please help!
Hello everybody, I'm working on a logging system. The site search as you can imagine is very complicated and a lot depends on it.
I have
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
, July 29, 2004 9:09 AM
Subject: RE: The search is killing the server. Please help!
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
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
| To: CF-Talk
| Subject: RE: The search is killing the server. Please help!
|
| 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
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
of both where necessary.
Andy
-Original Message-
From: cf coder [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 29, 2004 6:25 AM
To: CF-Talk
Subject: The search is killing the server. Please help!
Hello everybody, I'm working on a logging system. The site search as you can
imagine is very
it
in a number of different ways.Whether a query should be cached or not is
the more pertinent question.
Andy
-Original Message-
From: Micha Schopman [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 29, 2004 8:54 AM
To: CF-Talk
Subject: RE: The search is killing the server. Please help
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
is killing the server. Please help!
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
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
54 matches
Mail list logo
