Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    10

    Unanswered: Query regarding caching of data/index pages

    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

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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:
    Code:
    set showplan on
    Your select stement
    set showplan off
    Alternatively just run a large select to clear out the cache before running your query:
    Code:
    select * from BigTable order by some_field
    Your select statement

  3. #3
    Join Date
    Jun 2010
    Posts
    51
    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.

  4. #4
    Join Date
    Jul 2010
    Posts
    10
    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

  5. #5
    Join Date
    Jun 2010
    Posts
    51
    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.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •