I'm testing query plan caching in SQL Anywhere 220.127.116.1154 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).
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?
There are a number of issues at play here. Indeed, some of these statements are eligible for query bypass. Secondly, client statement caching in 12.0.1 is enabled only for CMDSEQ connections; it is not supported for all TDS-based connections (which includes jConnect). That is documented; see the
Lastly, SQL Anywhere can inline SQL queries that are embedded in stored procedures, depending on how the procedure is called (you don't say how in your text above). This inlining can entirely avoid the overhead of a stored procedure invocation, which can be substantial.
answered 08 Mar '12, 14:23
This answer applies only to "1 How to get the query caching work? Please share your experience."
I don't do anything to get plan caching to work, but apparently it does work: All Foxhound queries run inside stored procedures, so it this result comes as no surprise after one Monitor session is started:
SELECT DB_PROPERTY ( 'QueryCachedPlans' ) DB_PROPERTY('QueryCachedPlans') '28'
Here's the result after a second Monitor session is started:
SELECT DB_PROPERTY ( 'QueryCachedPlans' ) DB_PROPERTY('QueryCachedPlans') '40'
It went to 51 after a third session was started. These numbers are FAR LOWER than the number of queries actually executed inside stored procedures, so it looks like plan caching isn't used for just any query.
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.
answered 08 Mar '12, 03:40