Turning off cache for a single sql-query 
Author Message
 Turning off cache for a single sql-query
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



Sat, 15 May 2004 17:15:12 GMT
 Turning off cache for a single sql-query

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


Quote:
> 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



Sun, 16 May 2004 10:56:10 GMT
 Turning off cache for a single sql-query

Quote:

> 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
> Is there a way to avoid getting the result of a query into the cache?

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.



Mon, 17 May 2004 17:22:04 GMT
 Turning off cache for a single sql-query

Quote:

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

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



Tue, 18 May 2004 05:12:57 GMT
 Turning off cache for a single sql-query

Quote:

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

> 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
----------------------------------------------------------------------



Tue, 18 May 2004 06:47:28 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. SQL Server & turning RAID Cache Off

2. turn write-to-cache off

3. Turning Off the PTS Cache

4. Performance Testing ( Turn compile cache off ? )

5. Can I turn OFF read cache?

6. Possible to turn off file system cache?

7. Possible to turn off file system cache?

8. Possible to turn off file system cache?

9. Turning cache off on a Seagate drive under AIX - Progress/AIX/Seagate don't mix

10. Prob turning single user mode off

11. Query Analyser firing off multi connections on single query

12. Turn it off, turn it on


 
Powered by phpBB® Forum Software