Hi Jeremy,
Yes, you can very easily cache queries using the cachedwithin=
attribute of cfquery
Like so:
cfquery datasource=mydata name=myquery
cachedwithin=#createTimeSpan( 1, 0, 0, 0 )#
SELECT COUNT( * ) FROM myTable WHERE myDate '1900-01-01'
/cfquery
This will cache your query for
The only problem with this is the query can change due to the dynamic
building of the where clause. Could do a cached query that selects
everything that could be in the where clause then do QoQ on that for dynamic
where clause needs. I'd just be a little fearful of how much would need to
be
On 3/17/06, Jeremy Bunton [EMAIL PROTECTED] wrote:
I have a query counting records in a 25 million plus database. Even with
indexing and what not it takes about 25 seconds to run this query. (count *
from table where date 19000101) Is there a way I could cache the query so
that if it is ran
know I
can't fit the whole record set in ram.
Jeremy
-Original Message-
From: Aaron Rouse [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 9:44 AM
To: CF-Talk
Subject: Re: Large count query caching
The only problem with this is the query can change due to the dynamic
building
If you were taking the route of a cached query then yes/no that you are
wrong. If the count() is going to change based upon the where clause then
you would have to have a single master cached query that pulled out
everything possibly needed so that you could then do QoQ on that for the
dynamic
You should use a field name in the count instead of the *. It should
speed it up.
SELECT COUNT( fieldname ) FROM myTable
With the * it has to return all records and all fields.
-Original Message-
From: Jeremy Bunton [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 9:28 AM
To:
PROTECTED]
Sent: Friday, March 17, 2006 10:08 AM
To: CF-Talk
Subject: Re: Large count query caching
If you were taking the route of a cached query then yes/no that you are
wrong. If the count() is going to change based upon the where clause then
you would have to have a single master cached query
index seems to go. I need oracle's bitmap indexes I think to do it
right. I'm pretty sure my indexes are ok.
Jeremy
-Original Message-
From: mark [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 10:43 AM
To: CF-Talk
Subject: RE: Large count query caching
Is there a finite number
:43 AM
To: CF-Talk
Subject: RE: Large count query caching
Is there a finite number of options for a count? Even if the number is
50,
you could run the queries late at night and cache the results for 24
hours?
It wouldn't take any shorter time, but to the user it would be much
better
oddly enough the more ands in the where clause
the faster the
clustered index seems to go.
That does seem odd, but that's the way it usually works. The 'ands' are
cutting off chunks of data from the whole, and making the search cover
smaller areas. Also, if your 'ands' are part of your
If you cannot cache on the web server - make sure and bind the data using
CFQUERYPARAM. This will enable the RDMBS to use a cached execution plan on
it's end. I have a blog on why this is important.
http://mkruger.cfwebtools.com/index.cfm?mode=aliasalias=cfqueryparam
Secondly, change the * to
I'll check that out, I am not using them now.
Jeremy
-Original Message-
From: Mark A Kruger [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 11:09 AM
To: CF-Talk
Subject: RE: Large count query caching
If you cannot cache on the web server - make sure and bind the data using
Hi Aaron,
Are you sure about that?
I just tried three different queries (count(*), count(field) and
count(0)) on two joined tables with a date selection which return a
count of about 50 records.
all three queries run in exactly the same time (1.5sec).
I think I read somewhere that it should
: Friday, March 17, 2006 10:29 AM
To: CF-Talk
Subject: Re: Large count query caching
Hi Aaron,
Are you sure about that?
I just tried three different queries (count(*), count(field) and
count(0)) on two joined tables with a date selection which return a
count of about 50 records.
all three queries
Wolfe, Aaron wrote:
You should use a field name in the count instead of the *. It should
speed it up.
SELECT COUNT( fieldname ) FROM myTable
With the * it has to return all records and all fields.
COUNT(fieldname) has a different meaning then COUNT(*).
Jochem
Aaron Rouse wrote:
What is a bitmap index?
The short explanation:
Most types of database index contain the value of the indexed
field plus a pointer to the rest of the row. So if you are
indexing a bigint, that is 8 bytes for the value, another 8 bytes
for the pointer and some room for
: Large count query caching
Aaron Rouse wrote:
What is a bitmap index?
The short explanation:
Most types of database index contain the value of the indexed
field plus a pointer to the rest of the row. So if you are
indexing a bigint, that is 8 bytes for the value, another 8 bytes
for the pointer
17 matches
Mail list logo
