|
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? |
|
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. Sorry for delay, I was just unable to continue with this issue before. I performed the same tests with iAnywhere JDBC driver and found that it performed better than jConnect, as expected. What I didn't expect is that the difference will be so large, actually iAnywhere JDBC driver appeared to be faster than jConnect by more than 3 times! However, some points are still not clear to me. Even with iAnywhere driver I see that db_property('QueryOptimized') increments after each submitted query, so it looks like plan caching still doesn't work. This happens for both PreparedStatement test (#2 in my original question) and CallableStatement (#3). Is it expected behavior for simple queries that bypass optimization? I call to my stored procedure as follows: call DBA.get_sessions_01a(?, ?); Is it possible to implement the call better? You mentioned "inlining" of the procedure call. I assume something like this: select * from DBA.get_sessions_01a(?, ?). I tried it with PreparedStatement and the performance appeared to be slightly worse. Maybe you meant something else? Thanks in advance
(13 Mar '12, 09:09)
Leonid Gvirtz
Replies hidden
To clarify: You don't have to make the inline call, it's the server who does the inlining. - However, the procedure's body will decide if inlining is possible, so that's your part... On the other hand, inlining seems only be done only when using the procedure as part of a FROM clause - so your question is still open for experts to answer (sorry!). AFAIK, that's a similar technique as used by C++ compilers with "inline" methods/functions: They replace the actual procedure call with the statements that build the procedure's body - thereby omitting a procedure call with its need to build a stack frame, pass arguments and so on. That's usually reasonable if the number of statements in the body is rather small compared to the expense of the call.
(13 Mar '12, 09:45)
Volker Barth
|
|
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. 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?
(08 Mar '12, 06:04)
Leonid Gvirtz
Replies hidden
Good questions, for sure, but way beyond my external knowledge (which is based on the docs) - I hope the experts like Glenn will tell:)
(08 Mar '12, 06:27)
Volker Barth
|
