Re: svn commit: r739123 [1/3] - in /openjpa/trunk: openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/conf/ openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/ openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/kernel/exps/ openjpa-jdbc/src/ma

[Pinaki Poddar <pp...@apache.org>]

Hi Jeremy,
  Thank you for reviewing the work. Really helps.
   
   1. Few tests are included that performs a performance comparison
with/without cache and asserts any performance regression in terms of
execution time. Details are in:
    org.apache.openjpa.persistence.jdbc.sqlcache.TestPreparedQueryCache

   2. A quick snapshot of logs from the above test at the end of this post
shows impact on execution time.

   What can be inferred from those numbers and is commensurate with the
purpose is that the performance advantage is geared towards queries that
involves joins. That is because what the technique saves is (a) parsing of
JPQL + (b) construction of Select on repeated execution. So when cost(b) is
high, we get better savings by bypassing it. For example, a simple query 
          SELECT x FROM PObject x
 shows ~+5% while a more complex one shows +22%
  SELECT e FROM Employee e WHERE e.name = :emp AND e.department.name = :dept
AND e.department.company.name LIKE 'IBM' AND e.department.company.name =
:company AND e.address.zip = :zip
  
  3. It does not support find(), yet. I am looking into that but given the
previous observation that simple queries result in small gains -- I am
debating whether this is the appropriate technique for it. Also certain
central techniques followed to make the implementation somewhat
'non-intrusive' (which was also an explicit design goal) may not apply for
find().   

  4. I plan to continue work on the Docs and performance-oriented tests
w.r.t this issue. So they should be considered incomplete at this stage. The
status of this work is ongoing -- the commit should be considered as a
savepoint commit prompted by the fact that i) the unit-of-work was getting
larger and ii) some changes will impact code that other developers are
working on. I will communicate those changes in a separate post. Moreover,
if I decide to use similar technique for find() -- which implementation-wise
takes a different route than query -- more development or design change is
foreseen.

  5. Getting any 'independent observer' to evaluate performance differential
(positive or negative) will be useful. 

  6. I would have preferred to keep this work separate and give it a
different name than QuerySQLCache. As you can see PreparedQuery is defined
at a level *without* exact nature of datastore, meaning SQL happens to be
one instance. And we can consider QuerySQLCache (as it existed before this
change) to coexist with this new Prepared Query Cache. It will land up
double caching -- but that is a user's  choice.

  Thank you again for your interest --

Pinaki
 

==============================================================================
Performance result with/without Prepared Query Cache
==============================================================================
188  test  INFO   [main] openjpa.jdbc.JDBC - Using dictionary class
"org.apache.openjpa.jdbc.sql.MySQLDictionary".

Execution time in nanos for 100 query execution with and without SQL
cache:4931633 5370794 (8%)
JPQL: select p from Company p
SQL : SELECT t0.id, t0.name, t0.startYear FROM Company t0
change in execution time = +8%


Execution time in nanos for 100 query execution with and without SQL
cache:4933588 5344534 (7%)
JPQL: select p from Company p where p.name = 'PObject'
SQL : SELECT t0.id, t0.name, t0.startYear FROM Company t0 WHERE (t0.name =
?)
change in execution time = +7%

Execution time in nanos for 100 query execution with and without SQL
cache:4827709 5140038 (6%)
JPQL: select p from Company p where p.name = :param
SQL : SELECT t0.id, t0.name, t0.startYear FROM Company t0 WHERE (t0.name =
?)
change in execution time = +6%

Execution time in nanos for 100 query execution with and without SQL
cache:4820445 6201626 (22%)
JPQL: select e from Employee e where e.name = :emp and e.department.name =
:dept and e.department.company.name LIKE 'IBM' and e.department.company.name
= :company and e.address.zip = :zip
SQL : SELECT t0.id, t3.id, t3.city, t3.state, t3.street, t3.zip, t1.id,
t2.id, t2.name, t2.startYear, t1.name, t0.name FROM Employee t0 INNER JOIN
Department t1 ON t0.DEPARTMENT_ID = t1.id INNER JOIN Address t3 ON
t0.ADDRESS_ID = t3.id INNER JOIN Company t2 ON t1.COMPANY_ID = t2.id WHERE
(t0.name = ? AND t1.name = ? AND t2.name LIKE ? ESCAPE '\\' AND t2.name = ?
AND t3.zip = ?)
change in execution time = +22%

Execution time in nanos for 100 query execution with and without SQL
cache:4583264 4745017 (3%)
JPQL: select x from Company x
SQL : SELECT t0.id, t0.name, t0.startYear FROM Company t0
change in execution time = +3%

Execution time in nanos for 100 query execution with and without SQL
cache:4688305 4890007 (4%)
JPQL: select x from Company x where x.name='X' and x.startYear=1960
SQL : SELECT t0.id, t0.name, t0.startYear FROM Company t0 WHERE (t0.name = ?
AND t0.startYear = ?)
change in execution time = +4%

Execution time in nanos for 100 query execution with and without SQL
cache:4786363 4882464 (1%)
JPQL: select x from Company x where x.name=?1 and x.startYear=?2
SQL : SELECT t0.id, t0.name, t0.startYear FROM Company t0 WHERE (t0.name = ?
AND t0.startYear = ?)
change in execution time = +1%

Execution time in nanos for 100 query execution with and without SQL
cache:4624890 4803124 (3%)
JPQL: select x from Company x where x.name=:name and x.startYear=:startYear
SQL : SELECT t0.id, t0.name, t0.startYear FROM Company t0 WHERE (t0.name = ?
AND t0.startYear = ?)
change in execution time = +3%

-- 
View this message in context: http://n2.nabble.com/Re%3A-svn-commit%3A-r739123--1-3----in--openjpa-trunk%3A--openjpa-jdbc-src-main-java-org-apache-openjpa-jdbc-conf--openjpa-jdbc-src-main-java-org-apache-openjpa-jdbc-kernel---openjpa-jdbc-src-main-jav-tp2246101p2246458.html
Sent from the OpenJPA Developers mailing list archive at Nabble.com.