Hello,

I'm trying to write a small application that measures the time a
sql-statement needs to be processed. Because I want to run this
statement several times I disagree with the server putting the results
into the cache. As I found out the datebase must be openend with at
least about 300k of caching memory.

Is there a way to avoid getting the result of a query into the cache?

Thanks in advance,

M. Manthe

I am not aware of such command to flush the db cache. It would be nice to
have one though.

The best you can do is bounce the server before you run the query and
compare the result. If you are making changes to your query, you can 'set
prefetch off' to make sure the optimize doesn't do large I/O for your query.

If bouncing the server is not an option, try to run the query several times
and get an average.

benny

You can use MRU hint in your queries or fill the cache with other data
before each query.

But I don't see any reason to measure query processing time without
caches. Caches is one of main part of database functionality.
And bisides this, what about operating system file cache ?
RAID cache ? I think you should measure queries in the real
processing environment.

There's a common trick to flush one table's data
out of cache: bind the table and its indices to
a different cache. As a minimum, this requires
having a named cache besides the default data
cache to bind the table back and forth.

Regards,
Mariano Corral

Actually, it works also when you have no named caches: just binding a
table explicitly to the default data cache works just as well.
Unbinding has the same effect again.
You could also use dbcc cacheremove if you're fond of undocumented
dbcc commands, of course.

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

WWW  http://www.sypron.nl
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------