Hi all
I had a query regarding caching of data/index pages.The scenario is as follows:

For performance reasons I had to tune a porc where, within the code I found the bottle necked query which was consuming much more time than expected.
When I individually tried running the query for the first time the query took long time as expected but for consecutive runs(without doing any ammendments or changes) the query took comparitively less time than expected.
The conclusion that I could draw is--majority of the pages were present in the cache so the second run went for lesser number of physical reads.
(Please correct me if my understanding is incorrect)


So is there a way by which we can test our changes without having the impact/effect of cached pages which gives just an illusion of improved performance.



Thanking in advance for your time.


Prasad

If you have isolated the select statement that's taking all the time then just use showplan to see how the number of logical reads alters when using your new query:
Alternatively just run a large select to clear out the cache before running your query:

When you are optimizing...your goal should be to reduce logical reads. Because, as you stated, after caching, Sybase just do logical reads and not physical reads. See query plan to know whether Indexes are being used or not.

Thanks for the quick responce.
Yes I had seen the logical reads and they were greater than physical reads hence reached to the conclusion that the performance improvement was because of logial reads.

Now for me twiking the query would be secondary priority but before that I need to ensure that the changes that I would be doing and the performance improvemnt that I might see, should be the effect of the changes that I am doing and not because of the logical reads.

As mike suggested--
select * from BigTable(not included in my query) order by some_field

would be one approach of flushing the cache.

But is there any cleaner way/or any command to achieve the same

Thanks once again



Prasad

you can bind a table to a cache. If you do this, if this table is already existing in the cache then it will flush all the pages.

SyBooks Online

Try it and let us know.

As you stated you just need to reduce the number of logical reads - this is shown in the showplan output. Alternatively just provide the select statement and the index details of the tables involved and then we could either make some suggestions or point out any major issues.