Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443

    Unanswered: ASE Statement Caches

    Has anyone had any practical experience using Statement Caches in ASE (please dont post what is in the sybase website) and what you have seen in terms of pros and cons.

    The reason for me asking is I have come across a client who uses a lot of prepared statements thro jConnect but 75% of it is only 3 distinct SQLs.
    Last edited by trvishi; 06-03-09 at 18:42.

  2. #2
    Join Date
    Jun 2009
    Location
    South Africa
    Posts
    33
    trvishi,

    Enabling the statement cache should work well in most cases. I cannot quote exact figures for improvements, but we have it enabled on some of our servers where it seems to be working quite well. These fire off a lot of small repetitive ad-hoc queries against the server, and obviously a lot of time and CPU is saved by ASE not having to regenerate a new query plan for each of them.
    On one development server we clocked over 2.6 billion I/O operations against two tables in 2 separate databases in just under 12 Hours due to a buggy application where it fired the same query at the server looking for "events" in a table. The same query was repeatedly being executed at a rate of about 10-20 times a second each from each of 2 the application servers.


    We did have an issue a few months ago on another server where it actually broke some functionality of another application when we enabled it. This occurred mainly when executing some stored procedures which accepted quite a large number of parameters for facilitating updates. I cannot remember exact specifics such as error messages and the likes, but we suspect it was due to buggy ASE behaviour when the complete statement (exec sp_xxx @param1='value',...) exceeded roughly 16K (if memory serves me correctly). Disabling the statement cache resolved the problem right away.

    Your best bet would be to test it in a development environment first and check that no "funnies" occur.

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    thanks a lot for the feedback

Posting Permissions

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