the select statement is taking more than about 30ms. In your example
below, the select statement would need to be a little more complex, or
the "tbl" table would need to have hundreds of thousands or millions of
records to make it worthwhile.
Cached queries are incredibly fast, but if you want the data to always
be up to date, you have to manually refresh the cache when you insert,
update or delete data. This is done by setting the cachedwithin timespan
to 0. It's rather difficult to keep track of when to refresh the
queries, but if you are anal enough to figure it out, you can
dramatically speed up your application. By doing this, you only hit the
database on the first query or after data has changed.
To give you a comparison of times....
Regular cfquery that averages 200milliseconds
1st time: 200 milliseconds
2nd time: 200 milliseconds
3rd time: 200 milliseconds
update data: 10 milliseconds
4th time: 200 milliseconds
etc
Same cfquery using cachedwithin
1st time: 200 milliseconds
2nd time: ~1 milliseconds
3rd time: ~1 milliseconds
update data: 10 milliseconds
4th time: 200 milliseconds << manual refresh
5th time: ~1 milliseconds
etc
Does that make sense?
One thing that i think is in BlueDragon but not in MM CFML is a way to
delete the cache as opposed to refreshing it. The difference being that
when you do a cachedwithin=#createtimespan(0,0,0,0)# to refresh a query,
you're effectively rerunning it. In BD you can just delete the query
from memory when you change data. This is a more elegant way of handling
it because when data changes in the db, you may not actually need to run
the query at the point in time. So you don't force the user to have to
wait for the query(ies) to run.
Steve
David Fafard wrote:
> So of I use:
>
> select x,y,z from tbl where id=1
> select x,y,z from tbl where id=2
> select x,y,z from tbl where id=3
>
> where id would be a dynamic variable,
> I would not get any benefit in using cached query
> but would see a benefit from cfqueryparam ?
>
> Thanks,
> Dave
>
> ----- Original Message -----
> From: Philip Arnold
> To: CF-Talk
> Sent: Monday, March 01, 2004 12:25 PM
> Subject: RE: SQL Multiple Inserts
>
> > From: Hugo Ahlenius
> >
> > Cached queries means storing the whole output query variable
> > in the cf server memory. CF does not touch the database. The
> > query can not have any dynamic variables (The SQL statement
> > has to be the same).
> >
> > Using cfqueryparam improves the caching on the db server side
> > (as described before). The cfquery will still need a
> > roundtrip to the database.
> >
> > So the first option is the fastest, but least flexible.
>
> The cached query is only useful if you're only getting one set of
> data,
> and it never changes, then the cached query is right
>
> If you've got loads of variation on the data, for example getting
> different records from the database, the CFQUERYPARAM is the way to
> go
>
> Of course, using SPs might be even faster than using CFQUERYPARAM,
> as
> long as your queries are never going to change
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
