0

I am using Sybase Adaptive Server Enterprise (ASE) 16. I am using jConnect 4 in version 7.07.

I have a stored procedure that contains a rather simple query, equivalent to this one (I have changed the column names):

select o.item_id, o.option_uuid, c.product_description, o.option_size
from options_rm o
left join options_products_rm c on c.id = o.product_id
where o.item_id = @item_id

I have implemented metrics in Java using Micrometer.

At peak load, the procedure is called about 1200 times in 30 seconds. On average, the procedure takes about 1.31 ms to complete.

I have then replaced the stored procedure with a prepared statement using the exact same SQL query.

After measuring with Micrometer in Java, the prepared statement takes about 1.8 ms to complete.

Even though 1.8 ms is still fast, the prepared statement takes about 37 % longer than the stored procedure. The same phenomenon happens when applied to other stored procedures.

Here is a quick rundown of the Sybase ASE database setup:

  • Since I am using jConnect 4, DYNAMIC_PREPARE should be true by default.
  • The statement cache size is set to 2048 memory pages (2K).
  • The memory used of statement cache size is 4784 kilobytes.
  • streamlined dynamic SQL is enabled.

Why does the prepared statement take so much longer than the procedure, even though its execution plan etc. should already be cached?

How can I improve the performance of prepared statements on Sybase ASE?

Improve this question
13
  • a couple reasons the prepared statements could be taking longer ... a) ASE is generating a new query plan for each prepared statement ... b) there is contention on the query plan manager by a large volume of concurrent connections; at this point all I can do is throw out additional questions ... Commented Aug 28 at 13:54
  • are those 1200 executions performed across a single java connection to ASE, or are these executions spread across multiple java connections? if the latter, how many times is the query executed across a single java connection; if running multiple java processes/connections, and the query is executed many times by a single java process/connection, does enabling dynamic sql plan pinning provide any benefit; wondering if this is contention on the query plan mgr and if each connection can benefit from re-using its own query plan? Commented Aug 28 at 13:56
  • I don't work directly with prepared statements but I do a lot of work with statement caching so the following may not be 100% applicable ... if you switch from prepared statements to 'normal' statements, and ASE has been configured to support statement caching, do you see the same performance degradation? (objective is to separate parsing issues from potential compilation issues) Commented Aug 28 at 14:01
  • I'm leaning towards a configuration issue but which setting(s) is up in the air without more details; settings of interest ... enable literal autoparam, dynamic sql plan pinning, enable plan sharing; also, any chance you're submitting SQL comments with each query (ASE, unfortunately, treats comments as part of 'the code' such that different comments look to ASE like different code => each requiring a new plan) Commented Aug 28 at 14:12
  • @markp-fuso Thank you for your help. The application I'm working on serves thousands of users concurrently. It is a Java monolith hosted on ~ 5 different VMs. So multiple Java process on multiple VMs with different connections are executing the statement. Overall there are about 1200 executions in a give 30 seconds window. There are no comments inside the SQL statement. This is the current database configuration: dynamic SQL plan pinning is set to 0. enable literal autoparam is set to 1. enable plan sharing is set to 0. I will compare it to "normal" statements next week. Commented Aug 29 at 10:43

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.