Reusing of query plans

[Former Member]

I'm testing query plan caching in SQL Anywhere 12.0.1.3554 and it doesn't work for me. I compare following approaches to execute a simple query:

1 Execute the query as is, with actual values in the WHERE clause:

select * from DBA.sessions_01 where key_lc = 2 and session_id = 308

2 Execute the query with bind variables, I use JDBC PreparedStatement for this purpose:

select * from DBA.sessions_01 where key_lc = ? and session_id = ?

3 Put the query into a simple stored procedure and pass parameters to the stored procedure:

create or replace procedure get_sessions_01a ( p_key_lc integer, p_session_id integer ) begin select * from DBA.sessions_01 where key_lc = p_key_lc and session_id = p_session_id; end

call get_sessions_01a(?, ?);

I have built a benchmark with JMeter submits the same query 10000 times with different values using a single connection. The table contains 16000000 records and there is an index on (key_lc, session_id). The query always return 2 records. I'm the only user in the database; it is a test system.

I expected that SQLA would cache the query plan for prepared statement and stored procedure at some point, which would avoid full optimization of the statement each time it is submitted. The goal, of course, is to increase throughput.

Unfortunately, I see the opposite effect. In fact, the simple, non-parametrized statement (#1 in my list above) executes slightly faster than the parametrized options (33 sec. vs. 35 sec.) and I assume that the difference represents the overhead of parameters handling, which is expected if query caching doesn't work. Also, the value of select db_property('QueryOptimized') constantly climbs up, no matter which test case I run. Select db_property('QueryCachedPlans') constantly returns 1, but I have no idea how to check what plan is cached, I suppose that it is not the plan of my query. Max_plans_cached option is set to 20 (the default).

Questions:

1 How to get the query caching work? Please share your experience.

2 I performed all my tests with JConnect driver that comes with SQLA. I tried really hard to run the same tests with iAnywhere JDBC driver - with no success so far, I just receive "No suitable driver" error. Does anyone have experience with iAnywhere JDBC driver in JMeter or something similar? JMeter requires copying of the driver's jar file to its own lib directory.

3 Is there any way to know what query plans are currently cached?

[VolkerBarth]

I would simply guess that this query is too simple to get optimized - cf. the following doc page on "bypass queries".

In case you are confronted with this situation, the page give hints how to force "normal optimization" and plan cashing.

[Former Member]

Volker, you guess is quite right, my query is indeed eligible for bypass optimization, I could verify it by checking the query in Plan Viewer. I tried to add OPTION(FORCE OPTIMIZATION) and saw that this causes the query to be optimized. But, unfortunately, it didn't improve the throughput of my test case with PreparedStatement and db_property('QueryOptimized') still increments at the same rate.

From documentation, I see that db_property('QueryOptimized') returns the number of fully optimized requests and I definitely don't expect this number to raise when I use bind variables.

If I understand you correctly, you mean that plan caching may be available only for optimized queries. I don't see where the documentation tells us about it in a clear way. For bypass queries the plan is still generated, even though cost-based optimization is not used. If a query plan is generated, why SQLA can't cache it?

[VolkerBarth]

Good questions, for sure, but way beyond my external knowledge (which is based on the docs) - I hope the experts like Glenn will tell:)