tag:blogger.com,1999:blog-68776294289513987312017-12-02T05:46:08.428-08:00Java Persistence PerformanceA blog on Java, performance, scalability, concurrency, object-relational mapping (ORM), Java Persistence API (JPA), persistence, databases, caching, Oracle, MySQL, NoSQL, XML, JSON, EclipseLink, TopLink, and other fun stuff.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.comBlogger18125tag:blogger.com,1999:blog-6877629428951398731.post-60022752532247489902013-08-13T08:52:00.002-07:002013-08-27T05:52:00.243-07:00Optimizing Java Serialization - Java vs XML vs JSON vs Kryo vs POFPerhaps I'm naive, but I always thought Java serialization must surely be the fastest and most efficient way to serialize Java objects into binary form. After all, Java is on it's 7th major release, so this is not new technology, and since every JDK seems to be faster than the last, I incorrectly assumed serialization must be very fast and efficient by now. I thought, since Java serialization is binary, and language dependent, it must be much faster and more efficient than XML or JSON. Unfortunately, I was wrong, if you concerned about performance, I would recommend avoiding Java serialization. <p>Now, don't get me wrong, I'm not trying to dis Java. Java serialization has many requirements, the main one being able to serialize anything (or at least anything that implements <code>Serializable</code>), into any other JVM (even a different JVM version/implementation), even running a different version of the classes being serialized (as long as you set a <code>serialVersionUID</code>). The main thing is it just works, and that is really great. Performance is not the main requirement, and the format is standard and must be backward compatible, so optimization is very difficult. Also, for many types of use cases, Java serialization performs very well. <p>I got started on this journey into the bowels of serialization while working on a three tier concurrency benchmark. I noticed a lot of the CPU time being spent inside Java serialization, so I decided to investigate. I started by serializing a simple <code>Order</code> object that had a couple of fields. I serialized the object and output the bytes. Although the Order object only had a few bytes of data, I was not that naive to think it would serialize to only a few bytes, I knew enough about serialization that it would at least need to write out the full class name, so it knew what it had serialized, so it could read it back. So I expected, maybe 50 bytes or so. The result was over 600 bytes, that's when I realized Java serialization was not as simple as I had imagined. <h4>Java serialization bytes for Order object</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>----sr--model.Order----h#-----J--idL--customert--Lmodel/Customer;L--descriptiont--Ljava/lang/String;L--orderLinest--Ljava/util/List;L--totalCostt--Ljava/math/BigDecimal;xp--------ppsr--java.util.ArrayListx-----a----I--sizexp----w-----sr--model.OrderLine--&-1-S----I--lineNumberL--costq-~--L--descriptionq-~--L--ordert--Lmodel/Order;xp----sr--java.math.BigDecimalT--W--(O---I--scaleL--intValt--Ljava/math/BigInteger;xr--java.lang.Number-----------xp----sr--java.math.BigInteger-----;-----I--bitCountI--bitLengthI--firstNonzeroByteNumI--lowestSetBitI--signum[--magnitudet--[Bxq-~----------------------ur--[B------T----xp----xxpq-~--xq-~--<br /></code></pre>(note "-" means an unprintable character) <p>As you may have noticed, Java serialization writes out not only the full class name of the object being serialized, but also the entire class definition of the class being serialized, and all of the referenced classes. The class definition can be quite large, and seems to be the main performance and efficiency issue, especially when writing out a single object. If you are writing out a large number of objects of the same class, then the class definition overhead is not normally a big issue. One other thing that I noticed, is that if your object has a reference to a class (such as a meta-data object), then Java serialization will write the entire class definition, not just the class name, so using Java serialization to write out meta-data is very expensive. <p><h3>Externalizable</h3>It is possible to optimize Java serialization through implementing the Externalizable interface. Implementing this interface avoids writing out the entire class definition, just the class name is written. It requires that you implement the <code>readExternal</code> and <code>writeExternal</code> methods, so requires some work and maintenance on your part, but is faster and more efficient than just implementing Serializable. <p>One interesting note on the results for Externalizable is that it was much more efficient for a small number of objects, but actually output slightly more bytes than Serializable for a large number of objects. I assume the Externalizable format is slightly less efficient for repeated objects. <p><h4>Externalizable class</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>public class Order implements Externalizable {<br /> private long id;<br /> private String description;<br /> private BigDecimal totalCost = BigDecimal.valueOf(0);<br /> private List<OrderLine> orderLines = new ArrayList<OrderLine>();<br /> private Customer customer;<br /><br /> public Order() {<br /> }<br /><br /> public void readExternal(ObjectInput stream) throws IOException, ClassNotFoundException {<br /> this.id = stream.readLong();<br /> this.description = (String)stream.readObject();<br /> this.totalCost = (BigDecimal)stream.readObject();<br /> this.customer = (Customer)stream.readObject();<br /> this.orderLines = (List)stream.readObject();<br /> }<br /><br /> public void writeExternal(ObjectOutput stream) throws IOException {<br /> stream.writeLong(this.id);<br /> stream.writeObject(this.description);<br /> stream.writeObject(this.totalCost);<br /> stream.writeObject(this.customer);<br /> stream.writeObject(this.orderLines);<br /> }<br />}</code></pre> <h4>Externalizable serialization bytes for Order object</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>----sr--model.Order---*3--^---xpw---------psr--java.math.BigDecimalT--W--(O---I--scaleL--intValt--Ljava/math/BigInteger;xr--java.lang.Number-----------xp----sr--java.math.BigInteger-----;-----I--bitCountI--bitLengthI--firstNonzeroByteNumI--lowestSetBitI--signum[--magnitudet--[Bxq-~----------------------ur--[B------T----xp----xxpsr--java.util.ArrayListx-----a----I--sizexp----w-----sr--model.OrderLine-!!|---S---xpw-----pq-~--q-~--xxx<br /></code></pre> <p><h3>Other Serialization Options</h3>I started to investigate what other serialization options there were in Java. I started with EclipseLink MOXy, which supports serializing objects to XML or JSON through the JAXB API. I was not expecting XML serialization to outperform Java serialization, so was quite surprised when it did for certain use cases. I also found a product Kryo, which is an open source project for optimized serialization. I also investigated the Oracle Coherence POF serialization format. There are pros and cons with each product, but my main focus was on how their performance compared, and how efficient they were. <p><h3>EclipseLink MOXy - XML and JSON</h3>The main advantage of using EclipseLink MOXy to serialize to XML or JSON is that both are standard, portable formats. You can access the data from any client using any language, so are not restricted to Java, as with Java serialization. You can also integrate your data with web services and REST services. Both formats are also text based, so human readable. No coding or special interfaces are required, only meta-data. The performance is quite acceptable, and outperforms Java serialization for small data-sets. <p>The draw backs are that the text formats are less efficient than optimized binary formats, and JAXB requires meta-data. So you need to annotate your classes with JAXB annotations, or provide an XML configuration file. Also, circular references are not handled by default, you need to use an <ocde>@XmlIDREF</code> to handle cycles. <h4>JAXB annotated classes</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>@XmlRootElement<br />public class Order {<br /> @XmlID<br /> @XmlAttribute<br /> private long id;<br /> @XmlAttribute<br /> private String description;<br /> @XmlAttribute<br /> private BigDecimal totalCost = BigDecimal.valueOf(0);<br /> private List<OrderLine> orderLines = new ArrayList<OrderLine>();<br /> private Customer customer;<br />}<br /><br />public class OrderLine {<br /> @XmlIDREF<br /> private Order order;<br /> @XmlAttribute<br /> private int lineNumber;<br /> @XmlAttribute<br /> private String description;<br /> @XmlAttribute<br /> private BigDecimal cost = BigDecimal.valueOf(0);<br />}<br /></code></pre> <h4>EclipseLink MOXy serialization XML for Order object</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>&lt;order id="0" totalCost="0"&gt;&lt;orderLines lineNumber="1" cost="0"&gt;&lt;order&gt;0&lt;/order&gt;&lt;/orderLines&gt;&lt;/order&gt;<br /></code></pre> <h4>EclipseLink MOXy serialization JSON for Order object</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>{"order":{"id":0,"totalCost":0,"orderLines":[{"lineNumber":1,"cost":0,"order":0}]}}<br /></code></pre> <p><h3>Kryo</h3><a href="http://code.google.com/p/kryo/">Kryo</a> is a fast, efficient serialization framework for Java. Kryo is an open source project on Google code that is provided under the New BSD license. It is a small project, with only 3 members, it first shipped in 2009 and last shipped the 2.21 release in Feb 2013, so is still actively being developed. <p>Kryo works similar to Java serialization, and respects transient fields, but does not require a class be Serializable. I found Kryo to have some limitations, such as requiring classes to have a default constructor, and encountered some issues in serializing java.sql.Time, java.sql.Date and java.sql.Timestamp classes. <h4>Kryo serialization bytes for Order object</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>------java-util-ArrayLis-----model-OrderLin----java-math-BigDecima---------model-Orde-----<br /></code></pre> <p><h3>Oracle Coherence POF</h3>The <a href="http://www.oracle.com/technetwork/middleware/coherence/overview/index.html">Oracle Coherence</a> product provides its own optimized binary format called POF (portable object format). Oracle Coherence is an in-memory data grid solution (distributed cache). Coherence is a commercial product, and requires a license. EclipseLink supports an integration with Oracle Coherence through the <a href="http://www.oracle.com/technetwork/middleware/ias/tl-grid-097210.html">Oracle TopLink Grid</a> product that uses Coherence as the EclipseLink shared cache. <p>POF provides a serialization framework, and can be used independently of Coherence (if you already have Coherence licensed). POF requires that your class implement a <code>PortableObject</code> interface and read/write methods. You can also implement a separate Serializer class, or use annotations in the latest Coherence release. POF requires that each class be assigned an constant id ahead of time, so you need some way determine this id. The POF format is a binary format, very compact, efficient, and fast, but does require some work on your part. <p>The total bytes for POF was 32 bytes for a single Order/OrderLine object, and 1593 bytes for 100 OrderLines. I'm not going to give the results, as POF is part of a commercially licensed product, but is was very fast. <h4>POF PortableObject</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>public class Order implements PortableObject {<br /> private long id;<br /> private String description;<br /> private BigDecimal totalCost = BigDecimal.valueOf(0);<br /> private List<OrderLine> orderLines = new ArrayList<OrderLine>();<br /> private Customer customer;<br /><br /> public Order() {<br /> }<br /> <br /> public void readExternal(PofReader in) throws IOException {<br /> this.id = in.readLong(0);<br /> this.description = in.readString(1);<br /> this.totalCost = in.readBigDecimal(2);<br /> this.customer = (Customer)in.readObject(3);<br /> this.orderLines = (List)in.readCollection(4, new ArrayList());<br /> }<br /><br /> public void writeExternal(PofWriter out) throws IOException {<br /> out.writeLong(0, this.id);<br /> out.writeString(1, this.description);<br /> out.writeBigDecimal(2, this.totalCost);<br /> out.writeObject(3, this.customer);<br /> out.writeCollection(4, this.orderLines);<br /> }<br />}</code></pre> <h4>POF serialization bytes for Order object</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>-----B--G---d-U------A--G-------<br /></code></pre> <h3>Results</h3>So how does each perform? I made a simple benchmark to compare the different serialization mechanisms. I compared the serialization of two different use cases. The first is a single Order object with a single OrderLine object. The second is a single Order object with 100 OrderLine objects. I compared the average serialization operations per second, and measure the size in bytes of the serialized data. Different object models, use cases, and environments will give different results, but this gives you a general idea of the performance differences in different serializers. <p>The results show that Java serialization is slow for a small number of objects, but good for a large number of objects. Conversely, XML and JSON can outperform Java serialization for a small number of objects, but Java serialization is faster for a large number of objects. Kryo and other optimized binary serializers outperform Java serialization with both types of data. <p>You may wonder why it is relevant that something that takes less than a millisecond is of any relevance to performance, and you may be right. In general you would only have a real performance problem if you wrote out a very large number of objects, and then Java serialization performs quite well, so is the fact that it performs very poorly for a small number of object relevant? For a single operation, this is probably true, but if you execute many small serialization operations, then the cost <i>is</i> relevant. A typical server servicing many clients will typically send out many small requests, so while the cost of serialization is not great enough to make any of these single requests take a long time, it will dramatically effects the scalability of the server. <p><h4>Order with 1 OrderLine</h4><table cellspacing=0 cellpadding=4 border=1><tr><th>Serializer</th><th>Size (bytes)</th><th>Serialize (operations/second)</th><th>Deserialize (operations/second)</th><th>% Difference (from Java serialize)</th><th>% Difference (deserialize)</th></tr><tr><td>Java Serializable</td><td align=right>636</td><td align=right>128,634</td><td align=right>19,180</td><td align=right>0%</td><td align=right>0%</td></tr><tr><td>Java Externalizable</td><td align=right>435</td><td align=right>160,549</td><td align=right>26,678</td><td align=right>24%</td><td align=right>39%</td></tr><tr><td>EclipseLink MOXy XML</td><td align=right>101</td><td align=right>348,056</td><td align=right>47,334</td><td align=right>170%</td><td align=right>146%</td></tr><tr><td>Kryo</td><td align=right>90</td><td align=right>359,368</td><td align=right>346,984</td><td align=right>179%</td><td align=right>1709%</td></tr></table><p><h4>Order with 100 OrderLines</h4><table cellspacing=0 cellpadding=4 border=1><tr><th>Serializer</th><th>Size (bytes)</th><th>Serialize (operations/second)</th><th>Deserialize (operations/second)</th><th>% Difference (from Java serialize)</th><th>% Difference (deserialize)</th></tr><tr><td>Java Serializable</td><td align=right>2,715</td><td align=right>16,470</td><td align=right>10,215</td><td align=right>0%</td><td align=right>0%</td></tr><tr><td>Java Externalizable</td><td align=right>2,811</td><td align=right>16,206</td><td align=right>11,483</td><td align=right>-1%</td><td align=right>12%</td></tr><tr><td>EclipseLink MOXy XML</td><td align=right>6,628</td><td align=right>7,304</td><td align=right>2,731</td><td align=right>-55%</td><td align=right>-73%</td></tr><tr><td>Kryo</td><td align=right>1216</td><td align=right>22,862</td><td align=right>31,499</td><td align=right>38%</td><td align=right>208%</td></tr></table><p><h3>EclipseLink JPA</h3>In EclipseLink 2.6 development builds, and to some degree 2.5, we have added the ability to choose your serializer anywhere that EclipseLink does serialization. <p>One such place is in serialized @Lob mappings. You can now use the @Convert annotation to specify a serializer such as @Convert(XML), @Convert(JSON), @Convert(Kryo). In addition to optimizing performance, this provides an easy mechanism to write XML and JSON data to your database. <p>Also for EclipseLink cache coordination you can choose your serializer using the "eclipselink.cache.coordination.serializer" property. <p>The source code for the benchmarks used in this post can be found <a href="http://git.eclipse.org/c/eclipselink/examples/performance.git/tree/serialization">here</a>, or downloaded <a href="http://git.eclipse.org/c/eclipselink/examples/performance.git/plain/serialization/serialization.zip">here</a>.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com16tag:blogger.com,1999:blog-6877629428951398731.post-70373132742991815572013-06-20T07:53:00.000-07:002013-06-24T10:12:19.364-07:00EclipseLink supports HQL and several advanced new JPQL featuresEclipseLink 2.5 added several new JPQL features and supported syntax.<br>These include: <ul><li>HQL compatibility <li>Array expressions <li>Hierarchical selects <li>Historical queries </ul><p><h3>HQL Compatibility</h3>A common issue for users migrating from Hibernate to EclipseLink is that the HQL syntax of not having a SELECT clause is not supported, as it is not standard JPQL syntax. EclipseLink now supports this syntax. For queries that will return the entire object, this allows you to omit the SELECT clause, and start the query with the FROM clause. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>from Employee e where e.salary > 50000<br /></code></pre><p><h3>Array Expressions</h3>Previously in JPQL you could use the IN operation and sub-selects to compare a single value, but what if you wanted to compare multiple values, such as composite ids? You would then have to dynamically generate very long AND/OR expression trees to compare each value one by one, which is difficult and cumbersome. Many databases provide a much better solution to this problem with array expressions, this allows for arrays within the SQL in sub-selects comparisons, or nested within an IN comparison. <p>EclipseLink now supports array expressions with JPQL. EclipseLink also uses array expressions internally when object comparisons are done in JPQL and objects with composite ids. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select e from Employee e where (e.firstName, e.lastName) IN :names<br /><br />Select e from Employee e where (e.firstName, e.lastName) IN (Select m.firstName, m.lastName from Managers m)<br /></code></pre><p><h3>Hierarchical Selects</h3>Traditionally it has been very difficult to query hierarchical trees in relational data. For example querying all employees who work under a particular manager. Querying one level is simple, two levels is possible, but querying the entire depth of the tree, when you don't know how deep the tree is, is very difficult. <p>Some databases support a special syntax for this type of query. In Oracle the CONNECT BY clause allows for hierarchical queries to be expressed. EclipseLink now supports a CONNECT BY clause in JPQL, to support hierarchical queries on databases that support CONNECT BY. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select e from Employee e START WITH e.id = :id CONNECT BY e.managedEmployees<br /><br />Select e from Employee e START WITH e.id = :id CONNECT BY e.managedEmployees ORDER SIBLINGS BY e.salary where e.salary > 100000<br /></code></pre> <p><h3>Historical Queries</h3>Historical queries allow you to query back in time. This requires that you use EclipseLink's history support, or use a database that supports historical queries such as Oracle's flashback support. Historical queries use the AS OF clause to query an entity as of a point in time (or an Oracle SCN for flashback). This provides the ability to do some pretty cool queries and analytic on your data. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select e from Employee e AS OF :date where e.id = :id<br /><br />Select e from Employee e, Employee e2 AS OF :date where e = e2 and e.salary > e2.salary<br /></code></pre>James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com8tag:blogger.com,1999:blog-6877629428951398731.post-77477548385185321152013-06-10T06:07:00.002-07:002013-06-24T10:10:37.184-07:00Cool performance features of EclipseLink 2.5The main goal of the EclipseLink 2.5 release was the support of the JPA 2.1 specification, as EclipseLink 2.5 was the reference implementation for JPA 2.1. For a list of JPA 2.1 features look <a href="http://wiki.eclipse.org/EclipseLink/Release/2.5/JPA21">here</a>, or <a href="http://en.wikibooks.org/wiki/Java_Persistence/What_is_new_in_JPA_2.1%3F">here</a>. <p>Most of the features that went into the release were to support JPA 2.1 features, so there was not a lot of development time for other features. However, I was still able to sneak in a few cool new performance features. The features are not well documented yet, so I thought I would outline them here. <p><h3>Indexing Foreign Keys</h3>The first feature is auto indexing of foreign keys. Most people incorrectly assume that databases index foreign keys by default. Well, they don't. Primary keys are auto indexed, but foreign keys are not. This means any query based on the foreign key will be doing full table scans. This is any OneToMany, ManyToMany or ElementCollection relationship, as well as many OneToOne relationships, and most queries on any relationship involving joins or object comparisons. This can be a major perform issue, and you should always index your foreign keys fields. <p>EclipseLink 2.5 makes indexing foreign key fields easy with a new persistence unit property: <pre><code>"eclipselink.ddl-generation.index-foreign-keys"="true"</code></pre> This will have EclipseLink create an index for all mapped foreign keys if EclipseLink is used to generate the persistence unit's DDL. Note that DDL generation is now standard in JPA 2.1, so to enable DDL generation in EclipseLink 2.5 you can now use: <pre><code>"javax.persistence.schema-generation.database.action"="create"</code></pre> EclipseLink 2.5 and JPA 2.1 also support several new DDL generation features, including allowing user scripts to be executed. See, <a href="http://wiki.eclipse.org/EclipseLink/Release/2.5/JPA21#DDL_generation">DDL generation</a> for more information. <p><h3>Query Cache Invalidation</h3>EclipseLink has always supported a query cache. Unlike the object cache, the query cache is not enabled by default, but must be enabled through the query hint <code>"eclipselink.query-results-cache"</code>. The main issue with the query cache, is that the results of queries can change when objects are modified, so the query cache could become out of date. Previously the query cache did support time-to-live and daily invalidation through the query hint <code>"eclipselink.query-results-cache.expiry"</code>, but would not be kept in synch with changes as they were made. <p>In EclipseLink 2.5 automatic invalidation of the query cache was added. So if you had a query <code>"Select e from Employee e"</code> and had enabled query caching, every execution of this query would hit the cache and avoid accessing the database. Then if you inserted a new Employee, in EclipseLink 2.5 the query cache for all queries for Employee will automatically get invalidated. The next query will access the database, and get the correct result, and update the cache so all subsequent queries will once again obtain cache hits. Since the query cache is now kept in synch, the new persistence unit property <code>"eclipselink.cache.query-results"="true"</code> was added to enable the query cache on all named queries. If, for some reason, you want to allow stale data in your query cache, you can disable invalidation using the <code>QueryResultsCachePolicy.setInvalidateOnChange()</code> API. <p>Query cache invalidation is also integrated with cache coordination, so even if you modify an Employee on another server in your cluster, the query cache will still be invalidated. The query cache invalidation is also integrated with EclipseLink's support for Oracle Database Change Notification. If you have other applications accessing your database, you can keep the EclipseLink cache in synch with an Oracle database using the persistence unit property <code>"eclipselink.cache.database-event-listener"="DCN"</code>. This support was added in EclipseLink 2.4, but in EclipseLink 2.5 it will also invalidate the query cache. <p><h3>Tuners</h3>EclipseLink 2.5 added an API to make it easier to provide tuning configuration for a persistence unit. The <code>SessionTuner</code> API allows a set of tuning properties to be configured in one place, and provides deployment time access to the EclipseLink Session and persistence unit properties. This makes it easy to have a development, debug, and production configuration of your persistence unit, or provide different configurations for different hardware. The <code>SessionTuner</code> is set through the persistence unit property <code>"eclipselink.tuning"</code>. <p><h3>Concurrent Processing</h3>The most interesting performance feature provided in EclipseLink 2.5 is still in a somewhat experimental stage. The feature allows for a session to make use of concurrent processing. <p>There is no public API to configure it as of yet, but if you are interested in experimenting it is easy to set through a <code>SessionCustomizer</code> or <code>SessionTuner</code>. <pre><code><br />public class MyCustomizer implements SessionCustomizer {<br /> public void customize(Session session) {<br /> ((AbstractSession)session).setIsConcurrent(true);<br /> }<br />}<br /></code></pre><p>Currently this enables two main features, one is the concurrent processing of result sets. The other is the concurrent loading of load groups. <p>In any JPA object query there are three parts. The first is the execution of the query, the second is the fetching of the data, and the third is the building of the objects. Normally the query is executed, all of the data is fetched, then the objects are built from the data. With concurrency enabled two threads will be used instead, one to fetch the data, and one to build the objects. This allows two things to be done at the same time, allowing less overall time (but the same amount of CPU). This can provide a benefit if you have a multi-CPU machine, or even if you don't, it allows the client to be doing processing at the same time as the database machine. <p>The second feature allows all of the relationships for all of the resulting objects to be queried and built concurrently (only when using a shared cache). So, if you queried 32 Employees and also wanted each Employee's address, the address queries could all be executed and built concurrently, resulting in significant less response time. This requires the usage of a <code>LoadGroup</code> to be set on the query. <code>LoadGroup</code> defines a new API <code>setIsConcurrent()</code> to allow concurrency to be enabled (this defaults to true when a session is set to be concurrent). <p>A <code>LoadGroup</code> can be configured on a query using the query hint <code>"eclipselink.load-group"</code>, <code>"eclipselink.load-group.attribute"</code>, or through the JPA 2.1 <code>EntityGraph</code> query hint <code>"javax.persistence.loadgraph"</code>. <p>Note that for concurrency to improve your application's performance you need to have spare CPU time. So, to benefit the most you need multiple-CPUs. Also, concurrency will not help you scale an application server that is already under load from multiple client requests. Concurrency does not use less CPU time, it just allows for the CPUs to be used more efficiently to improve response times. James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com4tag:blogger.com,1999:blog-6877629428951398731.post-85818122002972470032013-05-28T10:43:00.004-07:002013-06-04T07:00:29.142-07:00Batch Writing, and Dynamic vs Parametrized SQL, how well does your database perform?One of the most effective database optimizations is batch writing. Batch writing is supported by most modern databases and part of the JDBC standard and is supported by most JPA providers. <p>Normal database access consists of sending each DML (insert, update, delete) statement to the database in a separate database/network access. Each database access has a certain amount of overhead to it, and the database must process each statement independently. Batch writing has two forms, dynamic and parametrized. Parametrized is the most common, and normally provides the best benefit, as dynamic can have parsing issues. <p>To understand batch writing, you must first understand parametrized SQL. SQL execution is composed of two parts, the parse and the execute. The parse consists of turning the string SQL representation to the database representation. The execute consists of executing the parsed SQL on the database. Databases and JDBC support bind parameters, so the the arguments to the SQL (the data) does not have to be embedded in the SQL. This avoids the cost of converting the data into text, and allows for the same SQL statement to be reused, with multiple executions. This allows for a single parse and multiple executes, aka "parametrized SQL". Most JDBC DataSource implementations and JPA providers support parametrized SQL and statement caching, this effectively avoids ever having a parse in a running application. <h4>Example dynamic SQL</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><br />INSERT INTO EMPLOYEE (ID, NAME) VALUES (34567, "Bob Smith")<br /></pre><h4>Example parametrized SQL</h4><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><br />INSERT INTO EMPLOYEE (ID, NAME) VALUES (?, ?)<br /></pre><p>Parametrized batch writing involves executing a single DML statement, but with a set of bind parameters for multiple homogenous statements, instead of bind parameters for a single statement. This effectively allows for a large batch of homogenous inserts, updates, or deletes, to be processed by the database and network as a single operation, instead of n operations. The database only needs to perform the minimal amount of work, as there is only a single statement, so at most only a single parse. It is also compatible with statement caching, so no statement parsing needs to occur at all. The limitation is that all of the statement's SQL must be identical. So, it works really good for say inserting 1,000 Orders, as the insert SQL is the same for each Order, only the bind parameters differ. But it does not help for inserting 1 Order, or for inserting 1 Order, 1 OrderLine, and 1 Customer. Also, all of the statements must be part of the same database transaction. <p>Dynamic batch writing involves chaining a bunch of heterogeneous dynamic SQL statements into a single block, and sending the entire block to the database in a single database/network access. This is beneficial in that there is only a single network access, so if the database is remote or across a slow network, this can make a big difference. The drawback is that parameter binding is not allowed, and the database must parse this huge block of SQL when it receive it. It some cases the parsing costs can outweigh the network benefits. Also, dynamic SQL is not compatible with statement caching, as each SQL is different. <p>JDBC standardizes batch writing through its Statement and PrepareStatement batch APIs (as of JDBC 2.0, which was JDK 1.2, aka a long time ago). The JDBC batch API requires different JDBC code, so if you are using raw JDBC, you need to rewrite your code to switch between batching and non-batching APIs. Most JDBC drivers now support these APIs, but some do not actually send the DML to the database as a batch, they just emulate the APIs. So how do you know if you are really getting batch writing? The only real way is to test it, and measure the performance difference. <p>The JPA specification does not standardize batch writing configuration, but most JPA providers support it. Normally batch writing is enabled in JPA through persistence unit properties, so turning it on or off is a simple matter of configuration, and requires no coding changes. Some JPA providers may not support batch writing when using optimistic locking, and may not re-order SQL to enable it to be batched, so even with batch writing enabled, you may still not be getting batching writing. Always test your application with batch writing on and off, and measure the difference to ensure it is actually functioning. <p>EclipseLink supports both parametrized and dynamic batch writing (since EclipseLink 1.0). In EclipseLink, batch writing is enabled through the <code>"eclipselink.jdbc.batch-writing"</code> persistence unit property. EclipseLink provides three options, <code>"JDBC"</code>, <code>"Buffered"</code>, and <code>"Oracle-JDBC"</code>. The <code>"JDBC"</code> option should always be used. <p><code>"Buffered"</code> is for JDBC drivers that do not support batch writing, and chains dynamic SQL statements into a single block itself. <code>"Buffered"</code> does not support parametrized SQL, and is not recommended. <p><code>"Oracle-JDBC"</code> uses the Oracle database JDBC API that predates the JDBC standard API, and is now obsolete. Previous to EclipseLink 2.5, this option allowed batch writing when using optimistic locking, but now the regular <code>"JDBC"</code> option supports optimistic locking. <p>EclipseLink 2.5 supports batch writing with optimistic locking on all (compliant) database platforms, where as previously it was only supported on selected database platforms. EclipseLink 2.5 also provides a <code>"eclipselink.jdbc.batch-writing"</code> query hint to disable batch writing for native queries that cannot be batched (such as DDL or stored procedures on some database platforms). <p>EclipseLink supports parametrized SQL through the <code>"eclipselink.jdbc.bind-parameters"</code>, and <code>"eclipselink.jdbc.cache-statements"</code> persistence unit properties. However, these don't normally need to be set, as parameter binding is the default, so you would only set the property to disable binding. Statement caching is not on by default, but only relevant to EclipseLink if using EclipseLink's connection pooling, if you are using a JDBC or Java EE DataSource, then you must configure statement caching in your DataSource config. <p>When batch writing is enabled in EclipseLink, by default it is parametrized batch writing. To enable dynamic batch writing, you must disable parameter binding. This is the same to enable buffered batch writing. <p>Supporting batch writing is not incredibly difficult, most JPA providers support this, ordering the SQL such that it can be batched is the difficult part. During a commit or flush operation, EclipseLink automatically groups SQL by table to ensure homogenous SQL statements can be batched (and at the same time still maintains referential integrity constraints and avoids dead locks). Most JPA providers do not do this, so even if they support batch writing, a lot of the time the SQL does not benefit from batching. <p>To enabled batch writing in EclipseLink, add the following to persistence unit property; <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><br />"eclipselink.jdbc.batch-writing"="JDBC"<br /></pre>You can also configure the batch size using the <code>"eclipselink.jdbc.batch-writing.size"</code> persistence unit property. The default size is 100. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><br />"eclipselink.jdbc.batch-writing.size"="1000"<br /></pre><p>Batch writing is very database, and JDBC driver dependent. So I was interested in which databases, drivers it worked with, and what the benefit was. I made two tests, one does a batch of 50 inserts, and one does a batch of 100 updates (using optimistic locking). I tried all of the batch writing options, as well as not using any batching. <p><b><i>Note, this is not a database benchmark, I am not comparing the databases between each other, only to themselves</i></b>. <p>Each database is running on different hardware, some are local, and some are across a network, so do not compare one database to another. The data of interest is the percentage benefit enabling batch writing has over not using batch writing. For the insert test I also measured the difference between using parametrized versus dynamic SQL, and parametrized SQL without statement caching. The result is the number of transactions processed in 10 seconds (run 5 times, and averaged), so a bigger number is a better result. <h3>Database: MySQL Version: 5.5.16<br>Driver: MySQL-AB JDBC Driver Version: mysql-connector-java-5.1.22 </h3>Insert Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql, no batch</td><td align=right>483</td><td align=right>0%</td></tr><tr><td>dynamic-sql, no batch</td><td align=right>499</td><td align=right>3%</td></tr><tr><td>parametrized-sql, no statement caching</td><td align=right>478</td><td align=right>-1%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>499</td><td align=right>3%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>509</td><td align=right>5%</td></tr></table><p>Update Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql</td><td align=right>245</td><td align=right>0%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>244</td><td align=right>0%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>248</td><td align=right>1%</td></tr></table><p>So the results seem to indicating batch writing has no affect whatsoever (5% is within the variance). What this really means, is that the MySQL JDBC driver does not actually use batch processing, it just emulates the JDBC batch APIs and executes statements one by one underneath. <p>MySQL does have batch processing support though, it just requires different SQL. The MySQL JDBC driver does support this, but requires the <code>rewriteBatchedStatements=true</code> JDBC connect property to be set. This can easily be set by modifying your connect URL, such as; <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><br />jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true<br /></pre><h3>MySQL: rewriteBatchedStatements=true</h3>Insert Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql, no batch</td><td align=right>504</td><td align=right>0%</td></tr><tr><td>dynamic-sql, no batch</td><td align=right>508</td><td align=right>0%</td></tr><tr><td>parametrized-sql, no statement caching</td><td align=right>483</td><td align=right>-4%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>1292</td><td align=right>156%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>2181</td><td align=right>332%</td></tr></table><p>Update Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql</td><td align=right>250</td><td align=right>0%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>669</td><td align=right>167%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>699</td><td align=right>179%</td></tr></table><p>So, it appears batch writing does make a big difference in MySQL, if configured correctly (why the JDBC driver does not do this by default, I have no idea). Parametrized batch writing does the best, being 332% faster for inserts, and 179% faster for updates. Dynamic batch writing also performs quite well. Interestingly there appears to be little difference between dynamic and parametrized SQL on MySQL (my guess is either MySQL is really faster at parsing, or does little optimization for prepared statements). <h3>PostgreSQL Version: 9.1.1<br>PostgreSQL 8.4 JDBC4 </h3>Insert Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql, no batch</td><td align=right>479</td><td align=right>0%</td></tr><tr><td>dynamic-sql, no batch</td><td align=right>418</td><td align=right>-12%</td></tr><tr><td>parametrized-sql, no statement caching</td><td align=right>428</td><td align=right>-10%</td></tr><tr><td>dynamic-sql, buffered</td><td align=right>1127</td><td align=right>135%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>1127</td><td align=right>135%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>2037</td><td align=right>325%</td></tr></table><p>Update Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql</td><td align=right>233</td><td align=right>0%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>395</td><td align=right>69%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>707</td><td align=right>203%</td></tr></table><p>The results show batch writing makes a big difference on PostgreSQL. Parametrized batch writing performs the best, being 325% faster for inserts, and 203% faster for updates. Dynamic batch writing also performs quite well. For PostgreSQL I also measure the performance of EclipseLink's buffered batch writing, which performs the same as dynamic JDBC batch writing, so I assume the driver is doing the same thing. Parametrized SQL outperforms dynamic SQL by about 10%, but parametrized SQL without statement caching performs similar to dynamic SQL. <h3>Oracle Database 11g Enterprise Edition Release 11.1.0.7.0<br>Oracle JDBC driver Version: 11.2.0.2.0 </h3>Insert Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql, no batch</td><td align=right>548</td><td align=right>0%</td></tr><tr><td>dynamic-sql, no batch</td><td align=right>494</td><td align=right>-9%</td></tr><tr><td>parametrized-sql, no statement caching</td><td align=right>452</td><td align=right>-17%</td></tr><tr><td>dynamic-sql, buffered</td><td align=right>383</td><td align=right>-30%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>489</td><td align=right>-10%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>3308</td><td align=right>503%</td></tr></table><p>Update Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql</td><td align=right>282</td><td align=right>0%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>258</td><td align=right>-8%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>1672</td><td align=right>492%</td></tr></table><p>The results show parametrized batch writing makes a big difference on Oracle, being 503% faster for inserts, and 492% faster for updates. Dynamic batch writing does not provide any benefit, this is because Oracle's JDBC driver just emulates dynamic batch writing and executes statements one by one, so it has the same performance as dynamic SQL. Buffered batch writing actually has worse performance than not batching at all. This is because of the parsing cost for the huge block of dynamic SQL, this may vary in different configurations, if the database is remote or across a slow network, I have seen this provide a benefit. <p>Parametrized SQL with statement caching provides about a 10% benefit over dynamic SQL, and points out that to benefit from parametrized you need to use statement caching, otherwise the performance can be worse than dynamic SQL. Of coarse there are other benefits to parametrized SQL, as it removes CPU processing from the server, which may not help much in this single threaded case, but can make a huge difference in a multi-threaded case where the database is a bottleneck. <h3>Apache Derby Version: 10.9.1.0 - (1344872)<br>Apache Derby Embedded JDBC Driver Version: 10.9.1.0 - (1344872)<br>(local) </h3>Insert Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql, no batch</td><td align=right>3027</td><td align=right>0%</td></tr><tr><td>dynamic-sql, no batch</td><td align=right>24</td><td align=right>-99%</td></tr><tr><td>parametrized-sql, no statement caching</td><td align=right>50</td><td align=right>-98%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>24</td><td align=right>-99%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>3252</td><td align=right>7%</td></tr></table><p>Update Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql</td><td align=right>1437</td><td align=right>0%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>6</td><td align=right>-99%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>2172</td><td align=right>51%</td></tr></table><p>The results show parametrized batch writing makes a difference on Derby, being 7% faster for inserts, and 51% faster for updates. This result difference is not as much as other database because my database was local. For a networked database, it would be a bigger difference, but this does show that batch writing can provide a benefit even for local databases, so it is not just a network optimization. The really interesting results from Derby are the horrible performance of the dynamic and non-cached statements. This shows the Derby has a huge parsing cost, so if you are using Derby, using parametrized SQL with statement caching is really important. <p> <h3>DB2/NT64 Version: SQL09070<br>IBM Data Server Driver for JDBC and SQLJ Version: 4.0.100 </h3>The results are basically similar to Oracle, in that parametrized batch writing gives a big performance benefit. Dynamic batch writing has worse performance then no batching with parametrized SQL, and dynamic SQL and parametrized SQL without statement caching result in worse performance. <p> <h3>Microsoft SQL Server Version: 10.50.1617<br>Microsoft SQL Server JDBC Driver 2.0 Version: 2.0.1803.100 </h3>The results were similar to PostgreSQL, showing both parametrized and dynamic batch writing providing a significant benefit. Parametrized batch writing performed the best, and parametrized SQL outperformed dynamic SQL, and no statement caching. <p><h3>** UPDATE **</h3>It was requested that I also test H2 and HSQL, so here are the results. <h3>Database: H2 Version: 1.3.167 (2012-05-23)<br>Driver: H2 JDBC Driver Version: 1.3.167 (2012-05-23)<br>(local) </h3>Insert Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql, no batch</td><td align=right>4757</td><td align=right>0%</td></tr><tr><td>dynamic-sql, no batch</td><td align=right>3210</td><td align=right>-32%</td></tr><tr><td>parametrized-sql, no statement caching</td><td align=right>4757</td><td align=right>0%</td></tr><tr><td>dynamic-sql, buffered</td><td align=right>1935</td><td align=right>-59%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>3293</td><td align=right>-30%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>5753</td><td align=right>20%</td></tr></table><p>The results show H2 performs 20% faster with parametrized batch writing. H2 is an in-memory database (backed by a persistent log file), so is not expected to benefit as much as there is no network involved. Dynamic batch writing, and dynamic SQL perform worse the parametrized SQL. Interestingly using statement caching with parametrized SQL makes no difference. My assumption is that H2 is always caching prepared statements in its connection, so the user does not need to do their own statement caching. <p> <h3>Database: HSQL Database Engine Version: 1.8.1<br>Driver: HSQL Database Engine Driver Version: 1.8.1<br>(local) </h3>Insert Test <table cellspacing=0 cellpadding=4 border=1><tr><th>Option</th><th>Average Result</th><th>% Difference from non batched</th></tr><tr><td>parametrized-sql, no batch</td><td align=right>7319</td><td align=right>0%</td></tr><tr><td>dynamic-sql, no batch</td><td align=right>5054</td><td align=right>-30%</td></tr><tr><td>parametrized-sql, no statement caching</td><td align=right>6776</td><td align=right>-7%</td></tr><tr><td>dynamic-sql, batch</td><td align=right>5500</td><td align=right>-24%</td></tr><tr><td>parametrized-sql, batch</td><td align=right>9176</td><td align=right>25%</td></tr></table><p>The results show HSQL performs 25% faster with parametrized batch writing. HSQL is an in-memory database (backed by a persistent log file), so is not expected to benefit as much as there is no network involved. Dynamic batch writing, and dynamic SQL perform worse the parametrized SQL. <p> James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com4tag:blogger.com,1999:blog-6877629428951398731.post-963984416618032462013-03-07T11:18:00.000-08:002013-04-10T12:18:53.590-07:00But what if I'm not querying by id? (database and cache indexes)Most data models define a sequence generated numeric primary key. This is the most efficient key to use as it is a single efficient guaranteed unique value. Some applications also use a UUID, which is a little less efficient in terms of time and space, but has its advantages in distributed systems and databases. <p>In JPA the primary key is defined as the JPA Id, and since JPA's object cache is indexed by Id, any queries by this Id obtain cache hits, and avoid database access. This is great when querying by id, and for traversing relationships across foreign keys based on the id, but what about queries not using the id? <p>Sequence generated ids are great for computers and databases, but are not very useful to people. How many times how you been to a store or website, and to look up your record they asked your for your sequence generated id? <p>Probably not very often, you are more likely to be asked for your phone number, email address, ssn or other such key that is easy to remember. These data keys can be considered alternative or secondary keys, and are very common in databases. Querying using alternative keys are very common, so it is important that they perform optimally. <p>The most important thing to ensure is that the columns are properly indexed in the database, otherwise each query will require a full table scan. The second thing to ensure is that the columns are indexed in the JPA object cache. <h3>EclipseLink Database Indexes</h3>To create a <i>database</i> index on a column you can use your own DDL script, or use the <code>@Index</code> annotation in EclipseLink (since EclipseLink 2.2). JPA 2.1 will also defined its own <code>@Index</code> annotation. For the EclipseLink index annotation you can just put this on the attributes you would like to index. <p><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><br />@Entity<br />@Index(columnNames={"F_NAME", "L_NAME"})<br />public class Employee {<br /> @Id<br /> private long id;<br /> @Index<br /> @Column(name="F_NAME")<br /> private String firstName;<br /> @Index<br /> @Column(name="L_NAME")<br /> private String lastName;<br /> @Index(unique=true)<br /> private String ssn;<br /> @Index<br /> private String phoneNumber;<br /> @Index<br /> private String emailAddress;<br /> ...<br />}<br /></pre> <h3>JPA 2.1 Database Indexes</h3>The upcoming JPA 2.1 spec (draft) also defines support for database indexes. EclipseLink 2.5 is the reference implementation for JPA 2.1, so in EclipseLink 2.5 (dev builds) you can also create database indexes using the JPA 2.1 annotations. This is a little bit more complex, as you cannot define the <code>@Index</code> on attributes, only inside table annotations. <p><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><br />@Entity<br />@Table(indexes={<br /> @Index(name="EMP_SSN_INDEX", unique=true, columnList={"SSN"}),<br /> @Index(name="EMP_PHONE_INDEX", columnList="PHONENUMBER"),<br /> @Index(name="EMP_EMAIL_INDEX", columnList="EMAILADDRESS"),<br /> @Index(name="EMP_F_NAME_INDEX", columnList="F_NAME"),<br /> @Index(name="EMP_L_NAME_INDEX", columnList="L_NAME"),<br /> @Index(name="EMP_NAME_INDEX", columnList={"F_NAME", "L_NAME"}) })<br />public class Employee {<br /> @Id<br /> private long id;<br /> @Column(name="F_NAME")<br /> private String firstName;<br /> @Column(name="L_NAME")<br /> private String lastName;<br /> private String ssn;<br /> private String phoneNumber;<br /> private String emailAddress;<br /> ...<br />}<br /></pre> <h3>EclipseLink Cache Indexes</h3>EclipseLink also supports indexes on the object cache (since EclipseLink 2.4). This allows JPQL and Criteria queries on indexed fields to obtain cache hits, and avoid all database access. The <code>@CacheIndex</code> annotation is used to index an attribute, or set of columns. When a set of columns are indexed, any queries using the attributes that map to those columns will use the index. <p>Cache indexes only provide a benefit to queries that expect a single result. Indexing a field such as <code>firstName</code> would provide no benefit, as there are many results with the same first name, and EclipseLink could never be certain is has them all loaded in the cache, so must access the database. <p><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><br />@Entity<br />@CacheIndex(columnNames={"F_NAME", "L_NAME"})<br />public class Employee {<br /> @Id<br /> private long id;<br /> @Column(name="F_NAME")<br /> private String firstName;<br /> @Column(name="L_NAME")<br /> private String lastName;<br /> @CacheIndex<br /> private String ssn;<br /> @CacheIndex<br /> private String phoneNumber;<br /> @CacheIndex<br /> private String emailAddress;<br /> ...<br />}<br /></pre> <p>So, is indexing worth it? I created an example benchmark that shows the difference between indexed and non-indexed queries. These results were obtained accessing an Oracle database across a LAN from a desktop machine. Results are queries per second, so a bigger number is better. The test consisted of querying a Customer object by name, with a database size of 1,000 customers. <table cellspacing=0 cellpadding=4 border=1><tr><th>Config</th><th>Average Result (q/s)</th><th>% Difference</th></tr><tr><td>No index, no cache</td><td align=right>7,155</td><td align=right>0%</td></tr><tr><td>No index, cache</td><td align=right>8,095</td><td align=right>13%</td></tr><tr><td>Database index, cache</td><td align=right>9,900</td><td align=right>38%</td></tr><tr><td>Database index, cache index</td><td align=right>137,120</td><td align=right>1,816%</td></tr></table><p>The results show that the object cache indexed by id provides a little benefit (13%), as it avoids having to rebuild the object, but still has to access the database. Note the test did not access any relationships on Customer, if it had this object cache would still provide a major benefit in avoiding relationship queries. The database index provides a better benefit, (38%), this is a factor of the size of the table, the bigger the table the bigger the benefit. The object cache index provides the best benefit, almost a 20x improvement. <p>This shows it is important to properly index your database and your cache. Avoid indexing everything, as there is a cost in maintaining indexes. For the database, index any commonly queried columns, for the cache, index any secondary key fields. <p>See the <a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Caching">EclipseLink UserGuide</a> for more info on caching. <p>The source code for the benchmarks used in this post can be found <a href="http://git.eclipse.org/c/eclipselink/examples/performance.git/tree/cache">here</a>, or downloaded <a href="http://git.eclipse.org/c/eclipselink/examples/performance.git/plain/cache/benchmark.zip">here</a>. James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com6tag:blogger.com,1999:blog-6877629428951398731.post-78915484068995661572013-01-03T07:23:00.000-08:002013-05-28T12:19:08.351-07:00Got Cache? improve data access by 97,322%Caching is the most valuable optimization one can make in software development. Making something run faster is nice, but it can never beat not having to run anything at all, because you already have the result cached. <p/>JPA caches many things. The most important thing to cache is the <code>EntityManagerFactory</code>, this is generally done for you in JavaEE, but in JavaSE you need to do this yourself, such as storing it in a static variable. If you don't cache your <code>EntityManagerFactory</code>, then your persistence unit will be redeployed on every call, which will really suck. <p/>Other caches in JPA include the cache of JDBC connections, the cache of JDBC statements, the result set cache, and most importantly the object cache, which is what I would like to discuss today. <p/>JPA 1.0 did not define caching, although most JPA providers did support a cache in some form or another. JPA 2.0 defined caching through the <code>@Cacheable</code> annotation and the <code>&lt;shared-cache-mode&gt;</code> persistence.xml element. Some describe caching in JPA as two levels. Conceptually there is the L1 cache on an <code>EntityManager</code>, and the L2 cache on the <code>EntityManagerFactory</code>. <p/>The <code>EntityManager</code> cache is an isolated, transactional cache, that only caches the objects read by that <code>EntityManager</code>, and shares nothing with other <code>EntityManagers</code>. The main purpose of the L1 cache is to maintain object identity (i.e. <code>person == person.getSpouse().getSpouse()</code>), and maintain transaction consistency. The L1 cache will also improve performance by avoiding querying the same object multiple times. The only way to avoid the L1 cache is to refresh, create a new <code>EntityManager</code>, or call <code>clear()</code>. <p/>The <code>EntityManagerFactory</code> cache is a shared cache across all <code>EntityManagers</code>, and reflects the current committed state of the database (stale data can be possible depending on your configuration and if you have other applications accessing the database). The main purpose of the L2 cache is to improve performance by avoiding queries for objects that have already been read. The L2 cahe is normally what is referred to when caching is discussed in JPA, and what the JPA <code>&lt;shared-cache-mode&gt;</code> and <code>@Cacheable</code> refer to. <p/>There are many types of caches provided by the various JPA providers. Some provide data caches, some provide object caches, some have relationship caches, some have query caches, some have distributed caches, or coordinated caches. <p/>EclipseLink provides an object cache, what I would call a "live" object cache. I believe most other JPA providers provide a data cache. The difference between a data cache, and an object cache, is that a data cache just caches the object's row, where as an object cache caches the entire object, including its relationships. <p/>Caching relationships is normally more important than caching the object's data, as each relationship normally represent a database query, so saving <i>n</i> database queries to build an object's relationships is more important than saving the <i>1</i> query for the object itself. Some JPA providers augment their data cache with a relationship cache, or a query cache. If a data cache caches relationships at all, it is normally in the form of caching only the ids of the related objects. This can be a major issue, consider caching a OneToMany relationship, if you only have a set of ids, then you need to query the database for each id that is not in the cache, causing <i>n</i> database queries. With an object cache, you have the related objects, so never need to query the database. <p/>The other advantage to caching objects is that you also save the cost of building the objects from the data. If the object or query is read-only, the cached object can be used directly, otherwise it only needs to be copied, not rebuilt from data. <p/>EclipseLink also supports not caching relationships through the <code>@Noncacheable</code> annotation. Also the <code>@Cache(isolation=PROTECTED)</code> option can be used to ensure read-only entities and queries always copy the cached objects. So you can simulate a data cache with EclipseLink. <p/>One should not underestimate the performance benefits of caching. Where as other JPA optimization may improve performance by 10-20%, or 2-5x for the major ones, caching has the potential to improve performance by factors of 100x even 1000x. <p/>So what are the numbers? In this simple benchmark I compare reading a simple Order object, and it relationships (orderLines, customer). I compared the various caching options.<br/>(result is queries per second, so bigger number is better, test was single threaded, randomly querying an order from a data set of 1000 orders, tests were run 5 times and averaged, database was an Oracle database over a local area network, low end hardware was used). <p/><table cellspacing=0 cellpadding=4 border=1><tr><th>Cache Option</th><th>Cache Config</th><th>Average Result (q/s)</th><th>% Difference</th></tr><tr><td>No Cache</td><td>@Cacheable(false)</td><td align=right>965</td><td align=right>0%</td></tr><tr><td>Object Cache</td><td>@Cacheable(true)</td><td align=right>36,544</td><td align=right>3,686%</td></tr><tr><td>Object Cache</td><td>@Cache(isolation=PROTECTED)</td><td align=right>35,107</td><td align=right>3,538%</td></tr><tr><td>Data Cache</td><td>@Cache(isolation=PROTECTED) + @Noncacheable(true)</td align=right><td align=right>1,889</td><td align=right>95%</td></tr><tr><td>Read Only Cache</td><td>@ReadOnly</td><td align=right>940,123</td><td align=right>97,322%</td></tr><tr><td>Protected Read Only Cache</td><td>@ReadOnly + @Cache(isolation=PROTECTED)</td><td align=right>625,602</td><td align=right>64,729%</td></tr></table><p/>The results show that although a data cache provides a significant benefit (~2x), it does not compare with an object cache (~100x). Marking the objects as @ReadOnly provides a significant additional benefit (~1000x). <p/>The object cache, caches objects by their Id. This is great for find() or merge() operations, but does not help as much with queries. In EclipseLink any query by Id will also hit the object cache, but queries not by Id will have to hit the database. For each database result the object cache will still be checked, so the cost of building the objects and most importantly their relationships can still be avoided. <p/>EclipseLink also supports a query cache. The query cache is configured independently of the object cache, and is configured per query, and not enabled by default. The query cache caches query results by query name and query parameters. This allows any query to obtain a cache hit. The query cache is configured through the "eclipselink.query-results-cache" query hint. <p/>EclipseLink can execute queries in-memory against the object cache. This is not used by default, but can be configured on any query. Since the object cache does not normally contain the entire database, this works best with a FULL cache type, that has been preloaded. This is configured on the query through the "eclipselink.cache-usage" query hint. <p/>This next benchmark compares the various caching options with a query. Each query is for the orders for a customer id, this will result in 10 Order objecs per query. Random customer ids are used. <p/><table cellspacing=0 cellpadding=4 border=1><tr><th>Cache Option</th><th>Cache Config</th><th>Average Result (q/s)</th><th>% Difference</th></tr><tr><td>No Cache</td><td>@Cacheable(false)</td><td align=right>186</td><td align=right>0%</td></tr><tr><td>Object Cache</td><td>@Cacheable(true)</td><td align=right>1,021</td><td align=right>448%</td></tr><tr><td>Object Cache</td><td>@Cache(isolation=PROTECTED)</td><td align=right>1,085</td><td align=right>483%</td></tr><tr><td>Data Cache</td><td>@Cache(isolation=PROTECTED) + @Noncacheable(true)</td align=right><td align=right>198</td><td align=right>6%</td></tr><tr><td>Read Only Query</td><td>"eclipselink.read-only"="true"</td><td align=right>1,391</td><td align=right>647%</td></tr><tr><td>Read Only Query - Protected Cache</td><td>"eclipselink.read-only"="true" + @Cache(isolation=PROTECTED)</td><td align=right>1,351</td><td align=right>626%</td></tr><tr><td>Query Cache</td><td>"eclipselink.query-results-cache"="true"</td><td align=right>5,114</td><td align=right>2,649%</td></tr><tr><td>In-memory Query</td><td>"eclipselink.cache-usage"="CheckCacheOnly"</td><td align=right>2,397</td><td align=right>1,188%</td></tr></table><p/>This shows that the object cache can still provide a significant benefit to queries through the benefit of caching the relationships (~5x). The query cache performs the best with ~25x benefit, and in-memory querying also performing well with a ~10x benefit. A data cache provide little benefit to queries. <p/>I have measured the performance of several caching options in this post, but by no means have detailed all of the caching options in EclipseLink.<br/>Other caching options available in EclipseLink include: <ul><li>@Cache - type : FULL, WEAK, SOFT, SOFT_CACHE, HARD_CACHE <li>@Cache - size : size of cache in number of objects <li>@Cache - expiry : millisecond time to live expiry <li>@Cache - expiryTimeOfDay : daily expiry <li>@CacheIndex : non-id cache indexing <li>"eclipselink.cache.coordination" : clustered cache synchronization or invalidation <li>"eclipselink.cache.database-event-listener" : database event driven cache invalidation (Oracle DCN) <li>"eclipselink.query-results-cache.expiry" : query cache time to live expiry <li>"eclipselink.query-results-cache.expiry-time-of-day" : query cache daily expiry <li>TopLink Grid : integration with Oracle Coherence distributed cache </ul>See the <a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Caching">EclipseLink UserGuide</a> for more info on caching. <p/>The source code for the benchmarks used in this post can be found <a href="http://git.eclipse.org/c/eclipselink/examples/performance.git/tree/cache">here</a>, or download <a href="http://git.eclipse.org/c/eclipselink/examples/performance.git/plain/cache/benchmark.zip">here</a>. <p/>What caching options to use depends on the application and its data. Caching may not be suitable to all types of applications or data, but for those in which it is applicable, it will normally provide the biggest performance benefit that is attainable. <p/> James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com1tag:blogger.com,1999:blog-6877629428951398731.post-77346738087352978332012-11-13T12:01:00.000-08:002013-04-10T12:20:28.071-07:00EclipseLink 15x faster than other JPA providersI'm sorry to disappoint those of you looking to see some canned benchmark showing how EclipseLink dominates over all competitors in performance and scalability. <p>It is not that this would be difficult to do, or that I don't have such a benchmark that shows this, but that I give the JPA community credit for not attributing much weight to a benchmark produced by a single JPA provider that shows their JPA product excels against their competitors. If you really want such a benchmark, scroll to the bottom of this post. <p>There are many such provider produced benchmarks out there. There are websites, blog posts, and public forum posts from these vendors marketing their claims. If you believe these claims, and wish to migrate your application to some previously unheard of JPA provider, then I have an email that I would like to send to you from a Nigerian friend who needs help transferring money into your country. <p>The only respectable benchmark that I know of that heavily utilizes JPA is the <a href="http://www.spec.org/jEnterprise2010/">SPECjEnterprise2010</a> benchmark from the Standard Performance Evaluation Corporation, a respected, independent standards body that produces many industry standard benchmarks. SpecJ is more than a JPA benchmark, as it measures the performance of the entire JavaEE stack, but JPA is a major component in it. <p>One could argue that SpecJ is too broad of a benchmark, and defining the performance of an entire JavaEE stack with a single number is not very meaningful. I have helped in producing Oracle's SpecJ results for several years, both with TopLink/EclipseLink JPA on WebLogic and previously with TopLink CMP on OC4J. I can honestly tell you that producing good numbers on the benchmark is not easy, and does require you to significantly optimize your code, and in particular optimize your concurrency and scalability. Having good JPA performance will not guarantee you good results on SpecJ, as there are other components involved, but having poor performance, concurrency or scalability in <i>any</i> component in your stack will prevent you from having good results. SpecJ emulates a real multi-user application, with a separate driver machine (or many) that simulate a large number of concurrent users hitting the system. It is very good at finding any performance, concurrency, or scalability bottlenecks in your stack. <p>One could also argue that it is very difficult for small JPA providers to publish a <a href="http://www.spec.org/jEnterprise2010/results/jEnterprise2010.html">SpecJ result</a>. Publishing a result requires having a full JavaEE stack and being integrated with it, and having access to <i>real</i> hardware, and investing the time and having the expertise to analyze and optimize the implementation on the <i>real</i> hardware. Of course, one could also argue if you don't have access to <i>real</i> hardware and <i>real</i> expertise, can you really expect to produce a performant and scalable product? <p>Ideally if you are looking for the most performant JPA provider for your application, you would benchmark it on your own application, and on your production hardware. Writing a good performance benchmark and tests can be a difficult task. It can be difficult to get consistent results, and easy to misinterpret things. In general, if a result doesn't make sense, there is probably something fishy going on. I have seen many such invalid performance comparisons from our users over the years (and from competitors as well). One that I remember was a user had a test showing TopLink was 100x slower than their JDBC code for a simple query. After looking at the test, I found their JDBC code executed a statement, but did not actually fetch any rows, or even close the statement. Once their JDBC code was fixed to be valid, TopLink was actually faster than their non-optimized JDBC code. <p>In EclipseLink and Oracle TopLink we take performance and scalability very seriously. In EclipseLink we run a large suite of performance tests every week, and every release to ensure our performance is better than our previous release, and better than any of our main competitors. Although our tests show we have the best performance today, this has not always been the case. We did not write the tests to showcase our performance, but to find problem areas where we were slower than our competitors. We then optimized those areas to ensure we were then faster than our competitors. I'm not claiming EclipseLink, or Oracle TopLink are faster than every other JPA implementation, in every use case. There will be some use cases where we are not faster, and some where our default configuration differs from another JPA provider that makes us slower until the configuration is fixed. <p>For example, EclipseLink does not automatically join fetch EAGER relationships, we view this as wrong, as EAGER does not mean JOIN FETCH, we have a separate @JoinFetch annotation to specify this. So there are some benchmarks, such as one from an object database that supports JPA that shows EclipseLink slower for ElementCollection mappings, this is solely because of the configuration, and we would be faster with the simple addition of a @JoinFetch. <p>EclipseLink also does not enable batch writing by default. You can configure this easily in the persistence.xml, but some other JPA providers use batch writing by default on some databases, so this could show a difference. EclipseLink also requires the usage of an agent to enable LAZY and other optimizations, some "benchmarks" fail to use this agent, which will seriously affect EclipseLink's performance. <p>The raw throughput of JPA is rarely ever the main factor in an application's performance. How optimized the database access is, what features the JPA provider supports, and how well the application makes use of JPA, do have a big impact on an applications performance. EclipseLink and Oracle TopLink have a large feature set of performance and scalability features. I'm not going to go over all of these today, but you can browse my blog for other posts on some of these. <p>Finally, if you really want a benchmark showing EclipseLink is 15x faster than any other JPA implementation, (even ones that claim this fact), you can find it <a href="http://git.eclipse.org/c/eclipselink/examples/performance.git/tree/canned-benchmark">here</a>. When running this benchmark on my desktop, accessing a local Derby database, it does 1,016,884 queries per second. This is over 15x (88x in fact) faster than other JPA providers, even 145x faster than the self claimed "fastest JPA implementation on the face of the Earth". <p>Granted EclipseLink contains an integrated, feature rich, object cache, where as other JPA providers do not. So, I also ran the "benchmark" with caching disabled. Even then EclipseLink was 2.7x faster. Of course EclipseLink also supports statement caching, other JPA providers do not, so I also disabled this. Again EclipseLink was 2.2x faster than the self claimed "fastest JPA implementation on the face of the Earth". <p>Was it a fair, objective benchmark that emulates a real application environment? Probably not... but don't take my, or anyone else's word for it. James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com4tag:blogger.com,1999:blog-6877629428951398731.post-81147967144540936482012-05-15T12:29:00.003-07:002013-04-10T12:21:06.042-07:00JPQL vs SQL, why not have bothOne of the most common questions I see on JPA, is users wanting to know how to write some specific SQL query as a JPQL query. Some of the time, they just need to learn JPQL, and their SQL can easily be converted to JPQL. Other times their SQL is using one of the many features on SQL that are not provided in JPQL, and their only option is to use a native SQL query. <p/>In EclipseLink 2.4, we have greatly enhanced our JPQL support to support most features of SQL. I refer to EclipseLink's JPQL extensions as the EclipseLink Query Language, or EQL. <p/>The JPQL support in EclipseLink 1.0 followed the JPA 1.0 BNF. It was not until the 2.1 release that we started adding some extensions, and removing restrictions. We introduced FUNC and TREAT in 2.1. FUNC allows calling any database function, and TREAT allows casting to a subclass for entities with inheritance. <p/>In the upcoming 2.4 release several new features have been added: (available for download today <a href="http://www.eclipse.org/eclipselink/downloads/milestones.php">here</a>) <ul><li>ON</li><li>UNION</li><li>INTERSECT</li><li>EXCEPT</li><li>NULLS FIRST/LAST</li><li>CAST</li><li>EXTRACT</li><li>REGEXP</li><li>FUNCTION</li><li>OPERATOR</li><li>SQL</li><li>COLUMN</li><li>TABLE</li></ul> <p/>EQL not only allows usage of more of the SQL syntax and functionality, but also allows the mixing of SQL, and SQL constructs within JPQL. EQL provides a hybrid query language that is object-oriented and database platform independent, but can still access raw data and database specific functionality when required. <p/><h3>ON</h3>SQL defines an ON clause to joins, but JPA 2.0 JPQL does not. JPQL does not require an ON clause because when a relationship is joined, the ON clause comes from the join columns already defined in the mapping. Sometimes however it is desirable to append additional conditions to the join condition, normally in the case of outer joins. <p/>EclipseLink supports the ON clause, both for relationships joins, and to define joins between two independent objects. <p/>The JPA 2.1 draft defines an ON clause, but only on relationship joins, not on joins between independent objects. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT e FROM Employee e LEFT JOIN e.address ON a.city = :city<br /><br />SELECT e FROM Employee e LEFT JOIN MailingAddress a ON e.address = a.address</code></pre> <p/><h3>UNION, INTERSECT, EXCEPT</h3>SQL supports UNION, INTERSECT and EXCEPT, but JPA 2.0 JPQL does not. Most unions can be done in terms of joins, but some can not, and some are more difficult to express using joins. <p/>EclipseLink supports UNION, INTERSECT and EXCEPT, including the ALL option to include duplicates. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT MAX(e.salary) from Employee e where e.address.city = :city1<br />UNION SELECT MAX(e.salary) from Employee e where e.address.city = :city2<br /><br />SELECT e from Employee e join e.phones p where p.areaCode = :areaCode1<br />INTERSECT SELECT e from Employee e join e.phones p where p.areaCode = :areaCode2<br /><br />SELECT e from Employee e<br />EXCEPT SELECT e from Employee e WHERE e.salary > e.manager.salary<br /></code></pre> <p/><h3>NULLS FIRST</h3>SQL supports NULLS FIRST, and NULLS LAST ordering options but JPA 2.0 JPQL does not. <p/>EclipseLink supports NULLS FIRST, and NULLS LAST in the ORDER BY clause. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT e FROM Employee e LEFT JOIN e.manager m ORDER BY m.lastName NULLS FIRST<br /></code></pre> <p/><h3>CAST</h3>SQL supports a CAST function to convert between datatypes, JPA 2.0 JPQL does not support this function. <p/>EclipseLink supports CAST allowing any value to be cast to any database type supported by the database. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT CAST(e.salary NUMERIC(10,2)) FROM Employee e<br /></code></pre> <p/><h3>EXTRACT</h3>SQL supports an EXTRACT function for accessing date/time values, JPA 2.0 JPQL does not support any functions for accessing date/time values. <p/>EclipseLink supports EXTRACT allowing any database supported date/time part value to be extracted from the date/time. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT EXTRACT(YEAR, e.startDate) FROM Employee e<br /></code></pre> <p/><h3>REGEXP</h3>Regular expression comparisons are supported by many databases, although there is no standard SQL syntax that has been adopted by major databases yet. JPA 2.0 JPQL does not support regular expressions. <p/>EclipseLink supports REGEXP on Oracle, PostgreSQL, MySQL, MongoDB, and other supporting databases. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT e FROM Employee e WHERE e.lastName REGEXP '^Dr\.*'<br /></code></pre> <p/><h3>FUNC and FUNCTION</h3>SQL supports a lot more database functions than JPQL. Specific database vendors also provide their own set of functions. Users and libraries can also define their own database functions. JPA 2.0 JPQL provides no mechanism to call database specific functions. The JPA 2.1 draft defines a special FUNCTION operator in JPQL to allow calling a specific database function. <p/>EclipseLink 2.1 provided this support using the FUNC operator. EclipseLink 2.4 will also provide the same functionality using the JPA 2.1 FUNCTION operator. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT FUNC('YEAR', e.startDate) AS year, COUNT(e) FROM Employee e GROUP BY year<br /></code></pre> <p/><h3>OPERATOR</h3>Some SQL functions have special syntax such as EXTRACT that takes a date part keyword (YEAR, MONTH, DAY), or CAST that takes a type name (NUMBER(10,2)). The FUNCTION operator cannot be used to call these special functions because of their special syntax. FUNCTION is also database specific, and does not allow the JPA provider to map the function to a different name on a different database platform. <p/>EclipseLink has support for over 80 database functions through defined EclipseLink ExpressionOperators. EclipseLink has always supported these operator using EclipseLink Expression queries, and now supports these operators using the JPQL OPERATOR keyword. OPERATOR allows calling any EclipseLink ExpressionOperator. EclipseLink ExpressionOperators are database independent, in that if a database provides a equivalent function it is used. ExpressionOperators support generating any syntax to allow calling database functions that require special syntax. The list of supported EclipseLink ExpressionOperators is <a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#OPERATOR">here</a>. Users can also define their own ExpressionOperators. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT e FROM Employee e WHERE OPERATOR('ExtractXml', e.resume, '@years-experience') > 10<br /></code></pre> <p/><h3>SQL</h3>The SQL operator allows for any SQL to be embedded in the JPQL query. This allows a hybridization of JPQL and SQL, giving the advantages of both in the same query. Previously if any part of the query required something not supported by JPQL, the entire query would need to be rewritten as a native SQL query. Now JPQL can still be used, and the SQL operator can be used just for the parts that require SQL. The SQL operator accepts a variable number of arguments, which are translated into the SQL string using the <code>?</code> marker. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT p FROM Phone p WHERE SQL('CAST(? AS CHAR(3))', e.areaCode) = '613'<br /><br />SELECT SQL('EXTRACT(YEAR FROM ?)', e.startDate) AS year, COUNT(e) FROM Employee e GROUP BY year<br /><br />SELECT e FROM Employee e ORDER BY SQL('? NULLS FIRST', e.startDate)<br /><br />SELECT e FROM Employee e WHERE e.startDate = SQL('(SELECT SYSDATE FROM DUAL)')<br /></code></pre> <p/><h3>COLUMN</h3>The COLUMN operator allows for any unmapped column to be referenced in JQPL. This can be used to access unmapped columns such as foreign key columns, inheritance discriminators, or system columns such as ROWID. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT e FROM Employee e WHERE COLUMN('MANAGER_ID', e) = :id<br /><br />SELECT e FROM Employee e WHERE COLUMN('ROWID', e) = :id<br /></code></pre> <p/><h3>TABLE</h3>The TABLE operator allows for any unmapped table to be referenced in JQPL. This can be used to access join, collection, history, auditing, or system tables for use in JPQL queries. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT e, a.LAST_UPDATE_USER FROM Employee e, TABLE('AUDIT') a WHERE a.TABLE = 'EMPLOYEE' AND a.ROWID = COLUMN('ROWID', e)<br /></code></pre> <p/><h3>JOIN FETCH</h3>JPQL does not allow using an alias on a JOIN FETCH, and does not allow nested join fetches. EclipseLink allows these. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>SELECT e FROM Employee e JOIN FETCH e.address a ORDER BY a.city<br /><br />SELECT e FROM Employee e JOIN FETCH e.manager m JOIN FETCH m.manager<br /></code></pre> <p/><h3>Criteria API</h3>You may be asking yourself, this is all great, but what about the Criteria API, can these extensions be used with that? <p/>EclipseLink 2.4 will provide a JpaCriteriaBuilder interface that allows access to EclipseLink specific functionality. Both Criteria queries and JPQL queries get translated to EclipseLink Expression queries, before being translated to SQL. EclipseLink Expressions support all of the above functionality through their API. JpaCriteriaBuilder defines two API toExpression() and fromExpression() to create Criteria Expression objects from EclipseLink Expression objects. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>JpaCriteriaBuilder cb = (JpaCriteriaBuilder)em.getCriteriaBuilder();<br />CriteriaQuery<Employee> query = cb.createQuery(Employee.class);<br />Root<Employee> emp = query.from(Employee.class);<br />query.where(cb.fromExpression(cb.toExpression(emp).get("firstName").regexp("^Dr\.*")));<br /></code></pre> <p/><h3>Summary</h3>EQL offers a lot of functionality to make querying easier and more powerful. There are still a few features of SQL that would be difficult to expression with EQL, but the majority of SQL is feasible. Of coarse, there is nothing in JPA that forces you to use JPQL, and if you love SQL, you are still free to use native SQL queries.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com14tag:blogger.com,1999:blog-6877629428951398731.post-70209609576075835282012-04-23T11:43:00.001-07:002013-04-10T12:22:09.690-07:00Objects vs Data, and Filtering a JOIN FETCHJPA deals with objects, and SQL deals with rows. Some developers love JPA because it allows them to use objects in an object-oriented fashion. However, other developers have trouble understanding JPA precisely because it is object-oriented.<br /><br />Even though object-oriented programming has been popular for several decades, there are still a lot of procedural languages out their. Developers used to procedural programming have a different mindset than object-oriented developers. This is also true of developers with a lot of experience with SQL. JPA and JPQL can be difficult to understand for developers used to SQL and JDBC.<br /><br />This can lead to some odd usages and misunderstandings of JPA. In this blog post, I would like to highlight a couple of these misunderstandings, and provide a solution to my favorite, which I call <a href="#Filtering"><b><i>Filtering a JOIN FETCH</i></b></a>.<br /><br /> <h3>Dobject Model</h3>One JPA usage that I find aggravating is what I call the Dobject model. This is a data model that has been made into an object model. Sometimes this comes across as a class that has the same name as the database table, with all of the same field names, and no relationships. Sometimes there are relationships, but they have the same name as the foreign key columns.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>@Entity<br />public class EMP {<br /> @Id<br /> long EMP_ID;<br /> String F_NAME;<br /> String L_NAME;<br /> long MGR_ID;<br /> long ADDR_ID;<br />}<br /></code></pre><br />The above is an unusual class, and not very object-oriented. It is probably not as useful as it could if it had relationships instead of foreign keys.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>@Entity<br />public class employees {<br /> @Id<br /> long empId;<br /> String fName;<br /> String lName;<br /> @ManyToOne<br /> Employee mgrId;<br /> @OneToOne<br /> Address addrId;<br />}<br /></code></pre><br />This one is very confused. First of all, it seems to be named after its table, where the name <code>employees</code> might make sense, but an object is a single entity, so should not be pluralized. Also, classes in Java should start with an upper case letter, as classes are proper names of the real world entity that they represent.<br /><br />This class at least has relationships, which I suppose is an improvement, but they are named like they are foreign keys, not objects. This normally leads the user to try to query them as values instead of as objects.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select e from employees e where e.mgrId = 4<br /></code></pre><br />Which does not work, because <code>mgrId</code> is an Employee object, not an Id value. The relationship should be named after what it represents, i.e. <code>manager</code> not the foreign key.<br /><br />A more object-oriented way to define the class would be:<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>@Entity<br />public class Employee {<br /> @Id<br /> long id;<br /> String firstName;<br /> String lastName;<br /> @ManyToOne(fetch=FetchType.LAZY)<br /> Employee manager;<br /> @OneToMany(mappedBy="manager")<br /> List&lt;Employee&gt; managedEmployees;<br /> @OneToOne<br /> Address address;<br />}<br /></code></pre><br /> <h3>How not to write a DAO</h3>In JPA you do not normally execute queries to insert, update or delete objects. To update and object you just find or query it, change it through its set methods, and commit the transaction. JPA automatically keeps track of what changed and updates what is required to be updated, in the order that it is required to be updated. <p/>To insert an object you call persit() on the EntityManager. To delete an object you call remove() on the EntityManager. This is different than SQL or JDBC that requires you to execute a query to perform any modification. <p/>JPA does allow UPDATE and DELETE queries through JPQL. These are for batch updates and deletes, not for the deletion or updating or single objects. This can lead to the very confused Data Access Object below: <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>public class EmployeeDOA {<br /> public void insert(Employee employee) {<br /> em.persist(employee);<br /> }<br /> public void update(Employee employee) {<br /> Query query = em.createQuery("Update Employee e set e.firstName = :firstName, e.lastName = :lastName where e.id = :id");<br /> query.setParameter("id", employee.getId());<br /> query.setParameter("firstName", employee.getFirstName());<br /> query.setParameter("lastName", employee.getLastName());<br /> query.executeUpdate();<br /> }<br /> public void delete(Employee employee) {<br /> Query query = em.createQuery("Delete from Employee e where e.id = :id");<br /> query.setParameter("id", employee.getId());<br /> query.executeUpdate();<br /> }<br />}<br /></code></pre><br /> This is wrong. You do not execute queries to update or delete objects. This will leave the objects in your persistence context in an invalid state, as they are not aware of the query updates. In is also not using JPA correctly, or as it was intended, and not benefiting from its full functionality. A better Data Access Object would be: <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>public class EmployeeDOA {<br /> public void persist(Employee employee) {<br /> em.persist(employee);<br /> }<br /> public Object merge(Employee employee) {<br /> return em.merge(employee);<br /> }<br /> public void remove(Employee employee) {<br /> em.remove(employee);<br /> }<br />}<br /></code></pre><br />Note that there is no update(). JPA does not have or require and update(), merge() can be used for detached objects, but is not the equivalent of update(), you do not need to call update in JPA, this is one of its benefits. <p/><h3>JPQL vs SQL</h3>JPQL is not SQL. It looks a lot like SQL, has similar syntax and uses the same standard naming for operators and functions, but it is not SQL. This can be very confusing for someone experienced with SQL. When they try to use their SQL in place of JPQL it does not work.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select * from Employee e join Address a on e.addressId = a.id where a.city = 'Ottawa'<br /></code></pre><br />This is SQL, not JPQL.<br />The equivalent JPQL would be:<br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select e from Employee e where e.address.city = 'Ottawa'<br /></code></pre><br />Of coarse if you prefer SQL, JPA fully allows you to use SQL, you just need to call <code>createNativeQuery</code> instead of <code>createQuery</code>. However, most users prefer to use JPQL. I suppose this is because JPQL lets them deal with objects, and even if they don't quite understand objects, they do understand there is some benefit there.<br /><br />JPQL also defines the JOIN syntax, but it does not have a ON clause, and JOIN is based on relationships, not foreign keys.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select e from Employee e join e.address a where a.city = 'Ottawa'<br /></code></pre><br />The JOIN syntax in JPQL allows you do query collection relationships, and use OUTER joins and FETCH. A join FETCH allows you read an object an its relationship in a single query (as appose to a possible dreaded N+1 queries).<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select e from Employee e left join fetch e.address where e.address.city = 'Ottawa'<br /></code></pre><br />Notice that I did not use an alias on this JOIN FETCH. This is because JPQL does not allow this, which I will get into later. There is also no ON clause in JPQL because the relationship is always joined by the foreign keys defined in the relationship's join columns in its mapping. <p/>Sometimes it is desirable to place additional conditions in a JOIN ON clause. This is normally in the case of OUTER joins, where placing the condition in the WHERE clause would result in empty joins being filtered. A ON clause is something that is part of the JPA 2.1 draft, so it is coming. EclipseLink already supports the ON clause, as well as aliasing JOIN FETCH in its 2.4 development milestones, see:<br /><br /><a href="http://www.eclipse.org/eclipselink/downloads/milestones.php">www.eclipse.org/eclipselink/downloads/milestones</a><br /><br /> <h3 id="Filtering">Filtering a JOIN FETCH</h3>A common misunderstanding I see users make occurs when querying a OneToMany relationship.<br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select d from Department d join d.employees e where e.address.city = 'Ottawa'<br /></code></pre><br />This query results in all department objects that have any employee living in Ottawa. The confusion comes when they access the department's employees. Each department contains all of its employees, however they were expecting just the employees that live in Ottawa.<br /><br />This is because JPA deals with objects, and a Department object represents a specific department, and has a specific identity. If I issue two queries for a particular department, I get back the same identical (==) instance (provided both queries use the same EntityManager). A specific department always has the same employees, it represent the real world department, and does not change, just because you queried it differently. This is important for caching, but also within the same persistence context, if you query the same department, two different ways, you should also get back the same exact department.<br /><br />If you really want the department, and only the employees of the department that live in Ottawa, you can use the following query:<br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Select d, e from Department d join d.employees e where e.address.city = 'Ottawa'<br /></code></pre><br /> This will give you an List of Object[] that contain the Department and the Employee. For each employee you will get back n Object[] (rows), where n is the number of departments in the employee. The same employee will be duplicated n times, each with its different department. If you access the departments any of the employees they will contain all of the employee's departments, not just the ones in Ottawa. <p/>But, if you really, really want the department to only have the employees that live it Ottawa, you can do this. I'm not sure I would recommend it, but it is possible, at least in EclipseLink 2.4 it will be. EclipseLink allows you to use an alias on a JOIN FETCH. This support was intended for OneToOne and ManyToOne relationships, to avoid having to join it twice just to get an alias, as well as to allow using it in an ORDER BY or in other ways that would not filter the results and alter how the objects are built. But, there is nothing stopping you from using it with a OneToMany to filter the contents of the fetched OneToMany results. <p/>If you are going to do this, you should be careful, and at least ensure you set the "javax.persistence.cache.storeMode" and "javax.persistence.cache.retrieveMode" to BYPASS, to avoid corrupting the shared cache. <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Query query = em.createQuery("Select d from Department d join fetch d.employees e where e.address.city = 'Ottawa'");<br />query.setHint("javax.persistence.cache.storeMode", "BYPASS");<br />query.setHint("javax.persistence.cache.retrieveMode", "BYPASS");<br />List<Department> departmentsWithFilteredEmployees = query.getResultList();<br /></code></pre><br /> EclipseLink 2.4 is currently under development, but its milestone builds already contain this functionality. The EclipseLink 2.4 milestone builds can be download from:<br /><a href="http://www.eclipse.org/eclipselink/downloads/milestones.php">www.eclipse.org/eclipselink/downloads/milestones</a><br /><br />For more information of the many JPQL extensions and enhancements in EclipseLink 2.4 see:<br /><a href="http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL">wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL</a>James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com1tag:blogger.com,1999:blog-6877629428951398731.post-31218112191077005162012-04-02T12:13:00.000-07:002013-08-27T05:52:21.114-07:00EclipseLink JPA supports MongoDBEclipseLink 2.4 will support JPA access to NoSQL databases. This support is already part of the EclipseLink development trunk and can be tried out using the <a href="http://www.eclipse.org/eclipselink/downloads/milestones.php">milestone </a>or <a href="http://www.eclipse.org/eclipselink/downloads/nightly.php">nightly </a>builds.&nbsp; Initial support is provided for MongoDB and Oracle NoSQL. A plug-able platform and adapter layer allows for other databases to be supported.<br /><br />NoSQL is a classification of database systems that do not conform to the relational database or SQL standard. They have various roots, from distributed internet databases, to object databases, XML databases and even legacy databases. They have become recently popular because of their use in large scale distributed databases in Google, Amazon, and Facebook.<br /><br />There are various NoSQL databases including:<br /><ul><li>Mongo DB</li><li>Oracle NoSQL</li><li>Cassandra</li><li>Google BigTable</li><li>Couch DB</li></ul><br />EclipseLink's NoSQL support allows the JPA API and JPA annotations/xml to be used with NoSQL data. EclipseLink also supports several NoSQL specific annotations/xml including @NoSQL that defines a class to map NoSQL data.<br /><br />EclipseLink's NoSQL support is based on previous EIS support offered since EclipseLink 1.0. EclipseLink's EIS support allowed persisting objects to legacy and non-relational databases. EclipseLink's EIS and NoSQL support uses the Java Connector Architecture (JCA) to access the data-source similar to how EclipseLink's relational support uses JDBC. EclipseLink's NoSQL support is extendable to other NoSQL databases, through the creation of an EclipseLink EISPlatform class and a JCA adapter.<br /><br />Let's walk through an example of using EclipseLink's NoSQL support to persist an ordering system's object model to a MongoDB database.<br /><br />The source for the example can be found <a href="http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.nosql.mongo/org.eclipse.persistence.example.jpa.nosql.mongo.zip">here</a>, or from the EclipseLink SVN <a href="http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.nosql.mongo/">repository</a>.<br /><br /><h4>Ordering object model</h4>The ordering system consists of four classes, Order, OrderLine, Address and Customer. The Order has a billing and shipping address, many order lines, and a customer.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>public class Order implements Serializable {<br />private String id;<br />private String description;<br />private double totalCost = 0;<br />private Address billingAddress;<br />private Address shippingAddress;<br />private List<orderline> orderLines = new ArrayList<orderline>();<br />private Customer customer;<br />...<br />}<br />public class OrderLine implements Serializable {<br />private int lineNumber;<br />private String description;<br />private double cost = 0;<br />...<br />}<br />public class Address implements Serializable {<br />private String street;<br />private String city;<br />private String province;<br />private String country;<br />private String postalCode;<br />....<br />}<br />public class Customer implements Serializable {<br />private String id;<br />private String name;<br />...<br />}<br /></code></pre><br /><h4>Step 1 : Decide how to store the data</h4>There is no standard on how NoSQL databases store their data. Some NoSQL databases only support key/value pairs, others support structured hierarchical data such as JSON or XML.<br /><br />MongoDB stores data as BSON (binary JSON) documents. The first decision that must be made is how to store the objects. Normally each independent object would compose a single document, so a single document could contain Order, OrderLine and Address. Since customers can be shared amongst multiple orders, Customer would be its own document.<br /><br /><h4>Step 2 : Map the data</h4>The next step is to map the objects. Each root object in the document will be mapped as an @Entity in JPA. The objects that are stored by being embedded within their parent's document are mapped as @Embeddable. This is similar to how JPA maps relational data, but in NoSQL embedded data is much more common because of the hierarchical nature of the data format. In summary, Order and Customer are mapped as @Entity, OrderLine and Address are mapped as @Embeddable.<br /><br />The @NoSQL annotation is used to map NoSQL data. This tags the classes as mapping to NoSQL data instead of traditional relational data. It is required in each persistence class, both entities and embeddables. The @NoSQL annotation allows the dataType and the dataFormat to be set.<br /><br />The dataType is the equivalent of the table in relational data, its meaning can differ depending on the NoSQL data-source being used. With MongoDB the dataType refers to the collection used to store the data. The dataType is defaulted to the entity name (as upper case), which is the simple class name.<br /><br />The dataFormat depends on the type of data being stored. Three formats are supported by EclipseLink, XML, Mapped, and Indexed. XML is the default, but since MongoDB uses BSON, which is similar to a Map in structure, Mapped is used. In summary, each class requires the @NoSql(dataFormat=DataFormatType.MAPPED) annotation.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>@Entity<br />@NoSql(dataFormat=DataFormatType.MAPPED)<br />public class Order<br /><br />@Embeddable<br />@NoSql(dataFormat=DataFormatType.MAPPED)<br />public class OrderLine<br /></code></pre><br /><h4>Step 3 : Define the Id</h4>JPA requires that each Entity define an Id. The Id can either be a natural id (application assign id) or a generated id (id is assign by EclipseLink). MongoDB also requires an _id field in every document. If no _id field is present, then Mongo will auto generate and assign the _id field using an OID (object identifier) which is similar to a UUID (universally unique identifier).<br /><br />You are free to use any field or set of fields as your Id in EclipseLink with NoSQL, the same as a relational Entity. To use an application assigned id as the Mongo id, simply name its field as "_id". This can be done through the @Field annotation, which is similar to the @Column annotation (which will also work), but without all of the relational details, it has just a name. So, to define the field Mongo will use for the id include @Field(name="_id") in your mapping.<br /><br />To use the generated Mongo OID as your JPA Id, simply include @Id, @GeneratedValue, and @Field(name="_id") in your object's id field mapping. The @GeneratedValue tells EclipseLink to use the Mongo OID to generate this id value. @SequenceGenerator and @TableGenerator are not supported in MongoDB, so these cannot be used. Also the generation types of IDENTITY, TABLE and SEQUENCE are not supported. You can use the EclipseLink @UUIDGenerator if you wish to use a UUID instead of the Mongo OID. You can also use your own custom generator. The id value for a Mongo OID or a UUID is not a numeric value, it can only be mapped as String or byte[].<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>@Id<br />@GeneratedValue<br />@Field(name="_id")<br />private String id;<br /></code></pre><br /><h4>Step 4 : Define the mappings</h4>Each attribute in your object has too be mapped. If no annotation/xml is defined for the attribute, then it mapping will be defaulted. Defaulting rules for NoSQL data, follow the JPA defaulting rules, so most simple mappings do not require any configuration if defaults are used. The field names used in the Mongo BSON document will mirror the object attribute names (as uppercase). To provide a different BSON field name, the @Field annotation is used.<br /><br />Any embedded value stored in the document is persisted using the @Embedded JPA annotation. An embedded collection will use the JPA @ElementCollection annotation. The @CollectionTable of the @ElementCollection is not used or supported in NoSQL, as the data is stored within the document, no separate table is required. The @AttributeOverride is also not required nor supported with NoSQL, as the embedded objects are nested in the document, and do not require unique field names. The @Embedded annoation/xml is normally not required, as it is defaulted, the @ElementCollection is required, as defaulting does not currently work for @ElementCollection in EclipseLink.<br /><br />The relationship annotations/xml @OneToOne, @ManyToOne, @OneToMany, and @ManyToMany are only to be used with external relationships in NoSQL. Relationships within the document use the embedded annotations/xml. External relationships are supported to other documents. To define an external relationship a foreign key is used. The id of the target object is stored in the source object's document. In the case of a collection, a collection of ids is stored. To define the name of the foreign key field in the BSON document the @JoinField annotation/xml is used.<br /><br />The mappedBy option on relationships is not supported for NoSQL data, for bi-directional relationships, the foreign keys would need to be stored on both sides. It is also possible to define a relationship mapping using a query, but this is not currently supported through annotations/xml, only through a DescriptorCustomizer.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>@Basic<br />private String description;<br />@Basic<br />private double totalCost = 0;<br />@Embedded<br />private Address billingAddress;<br />@Embedded<br />private Address shippingAddress;<br />@ElementCollection<br />private List<orderline> orderLines = new ArrayList<orderline>();<br />@ManyToOne(fetch=FetchType.LAZY)<br />private Customer customer;<br /></code></pre><br /><h4>Step 5 : Optimistic locking</h4>Optimistic locking is supported with MongoDB. It is not required, but if locking is desired, the @Version annotation can be used.<br /><br />Note that MongoDB does not support transactions, so if a lock error occurs during a transaction, any objects that have been previously written will not be rolled back.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>@Version<br />private long version;<br /></code></pre><br /><h4>Step 6 : Querying</h4>MongoDB has is own JSON based query by example language. It does not support SQL (i.e. NoSQL), so querying has limitations.<br /><br />EclipseLink supports both JPQL and the Criteria API on MongoDB. Not all aspects of JPQL are supported. Most basic operations are supported, but joins are not supported, nor sub-selects, group bys, or certain database functions. Querying to embedded values, and element collections are supported, as well as ordering, like, and selecting attribute values.<br /><br />Not all NoSQL database support querying, so EclipseLink's NoSQL support only supports querying if the NoSQL platform supports it.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Query query = em.createQuery("Select o from Order o where o.totalCost &gt; 1000");<br />List<order> orders = query.getResultList();<br /><br />Query query = em.createQuery("Select o from Order o where o.description like 'Pinball%'");<br />List<order> orders = query.getResultList();<br /><br />Query query = em.createQuery("Select o from Order o join o.orderLines l where l.description = :desc");<br />query.setParameter("desc", "shipping");<br />List<order> orders = query.getResultList();<br /></code></pre><br />Native queries are also supported in EclipseLink NoSQL. For MongoDB the native query is in MongoDB's command language.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>Query query = em.createNativeQuery("db.ORDER.findOne({\"_id\":\"" + oid + "\"})", Order.class);<br />Order order = (Order)query.getSingleResult();<br /></code></pre><br /><h4>Step 7 : Connecting</h4>The connection to a Mongo database is done through the JPA persistence.xml properties. The "eclipselink.target-database" property must define the Mongo platform "org.eclipse.persistence.nosql.adapters.mongo.MongoPlatform". A connection spec must also be defined through "eclipselink.nosql.connection-spec" to be "org.eclipse.persistence.nosql.adapters.mongo.MongoConnectionSpec". Other properties can also be set such as the "eclipselink.nosql.property.mongo.db", "eclipselink.nosql.property.mongo.host" and "eclipselink.nosql.property.mongo.port". The host and port can accept a comma separated list of values to connect to a cluster of Mongo databases.<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>&lt;persistence-unit name="mongo-example" transaction-type="RESOURCE_LOCAL"&gt;<br />&lt;class&gt;model.Order&lt;/class&gt;<br />&lt;class&gt;model.OrderLine&lt;/class&gt;<br />&lt;class&gt;model.Address&lt;/class&gt;<br />&lt;class&gt;model.Customer&lt;/class&gt;<br />&lt;properties&gt;<br />&lt;property name="eclipselink.target-database" value="org.eclipse.persistence.nosql.adapters.mongo.MongoPlatform"&gt;<br />&lt;property name="eclipselink.nosql.connection-spec" value="org.eclipse.persistence.nosql.adapters.mongo.MongoConnectionSpec"&gt;<br />&lt;property name="eclipselink.nosql.property.mongo.port" value="27017"&gt;<br />&lt;property name="eclipselink.nosql.property.mongo.host" value="localhost"&gt;<br />&lt;property name="eclipselink.nosql.property.mongo.db" value="mydb"&gt;<br />&lt;property name="eclipselink.logging.level" value="FINEST"&gt;<br />&lt;/property&gt;<br />&lt;/property&gt;<br /></code></pre><br /><h4>Summary</h4>The full source code to this demo is available from <a href="http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.nosql.mongo/">SVN</a>.<br /><br />To run the example you will need a Mongo database, which can be downloaded from, <a href="http://www.mongodb.org/downloads">http://www.mongodb.org/downloads</a>.<br /><br />EclipseLink also support NoSQL access to other data-sources including:<br /><ul><li>Oracle NoSQL<br /><li>XML files<br /><li>JMS<br /><li>Oracle AQ<br /></ul>James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com5tag:blogger.com,1999:blog-6877629428951398731.post-54481680273070481502012-03-29T07:41:00.002-07:002013-08-27T05:52:47.410-07:00NoSQLIn the beginning data was free and wild. It was not confined to rows and columns and not bounded to standardization. Data access was unruly and proprietary. These were the first "NoSQL" databases. They consisted of flat file, hierarchical and network databases such as VSAM, IMS and ADABASE.<br /><br />Then there was SQL, and things were good.<br /><br />SQL was developed during the golden age of data in the 1970s. Database access became standardized through the SQL language and the relational model. The 1970s saw the birth of relational database products such as RDBMS, Ingres, Oracle and DB2. The 1980s saw ANSI standardization of the SQL language, and the adoption of client-server computing.<br /><br />However, the legacy databases still existed, as well as the legacy applications that accessed them. New applications needed to access the old data, and this was in general a very painful experience.<br /><br />Back in the good old Smalltalk days during the 1990s, Smalltalk was unofficially adopted as the programming language of choice for large corporate projects. It was the beginning of the commercial adoption of object-oriented programming, both Smalltalk, C++ and other OO languages. Things were great, but there was a dark side. All of the data was stored in relational databases, or worse legacy mainframe databases. Fitting round objects into square relational tables was difficult and cumbersome. Two solutions emerged, object-oriented databases, and object-relational mapping.<br /><br />New commercial object-oriented database management systems (OODBMS) emerged in the 1990s including Versant, Gemstone and ObjectStore. They were integrated with their respective languages, Smalltalk and C++, and stored data as it was represented in memory, instead of in the relational model. These were the 2nd generation of "NoSQL" databases. There was little standardization and solutions were mainly proprietary. Access to the data from non object-oriented languages was difficult. The world did not adopt this new model, as it had previously adopted the relational model. The worlds data remained in the trusted, standardized and universally accessible relational model.<br /><br />Object-relational mapping allowed objects to be used in the programming model, but have them converted to relational rows and SQL when persisted. A lot of OR mapping frameworks were built, including many corporate in-house solutions. TopLink, a product from The Object People became the leading OR mapper in the Smalltalk language. In C++ there was Persistence, as well as various other products in various languages.<br /><br />Although the relational model was the industry standard for any new applications, much of the worlds data remained in mainframe databases. The data was slowly being migrated, but most corporations still had mainframe data. Consulting at TopLink clients in the 90s I found most clients were building applications on relational database, but still had to get some data from the mainframe. This is when we created the first version of TopLink's "NoSQL" support. Of coarse NoSQL was not a buzz word at the time, so the offering was called TopLink for the Mainframe. The main problem was that everyone's mainframe data and access was different, so the product involved lots of consulting.<br /><br />When Java came along, TopLink moved from Smalltalk to Java. OR mapping became very popular in Java and many new products came to market. The first real OR standard came in the form on EJB CMP. It had is "issues" to say the least, and was coupled with the J2EE platform. A new competing standard of JDO was created in retaliation to CMP. To reconcile the issue of having two competing Java standards, JPA was created to replace them both, and was adopted by most OR mapping products.<br /><br />In response to the popularity of object-oriented computing, the relational database vendors created the object-relational paradigm. This allowed storage of structured object types and collections in relational tables. SQL3 (SQL 1999) defined new query syntax to access this data. Despite some initial hype, the object-relational paradigm was not successful, and although the features remain in Oracle, DB2 and Postgres, the world stayed with the trusted relational model.<br /><br />The panic around Y2K had the good fortune of getting most corporations and governments off mainframe databases, and into relational databases. Some legacy data still remained, so we also offered TopLink for the Mainframe in Java. At that time the Internet was taking off, and XML was becoming popular. Since XML is hierarchical data that you could convert any mainframe data to, it became part of our solution for accessing legacy data and the TopLink SDK was born.<br /><br />With the explosion of the Internet, XML was becoming increasingly popular. This lead to once again the questioning of the relational model, and the creation of XML databases (the 3rd generation of NoSQL). There were several XML databases that achieved much hype, but limited market success. The relational database vendors responded by adding XML support for storage of XML in relational tables.<br /><br />Again the world stayed with the relational model.<br /><br />The TopLink SDK also provided simple object to XML mapping, perhaps the first such product to do so. As XML usage in Java became mainstream, the TopLink SDK was split into two products. TopLink Moxy become TopLink's object to XML mapping solution. TopLink EIS became TopLink's legacy data persistence solution.<br /><br />Around 2009 the term NoSQL was used to categories the new distributed databases being used at Google, Amazon and Facebook. The databases categorized themselves as <br />being highly scalable, not adhering to ACID transaction semantics, and having limited querying. The NoSQL term grew to include the various other non-relational databases that have emerged throughout the ages.<br /><br />Is the relational model dead? Will the world switch to the NoSQL model, and will data once again be free? Only time will tell. If history teaches us anything, one would expect the relational model to persist. NoSQL has already been renamed in some circles to "Not Only SQL", to leave room for the NoSQL databases to support the SQL standard. In fact, some NoSQL databases already have support for JDBC drivers. My intuition is a union of the two models, perhaps this has already begun with some NoSQL databases adding SQL support, and some relational databases extended their clustering support such as MySQL cluster.<br /><br />EclipseLink 2.4 will contain JPA support for NoSQL databases. Initial support with include MongoDB and Oracle NoSQL. This support is already available in the EclipseLink nightly builds. Technically, this is not new functionality, as EclipseLink (formerly TopLink) has been supporting non-relational data for over a decade, but the JPA support is new.<br /><br />In the upcoming months I will be blogging about some of the new features in EclipseLink to support NoSQL. This blog post is solely an introduction, so sorry to those expecting hard content.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com4tag:blogger.com,1999:blog-6877629428951398731.post-29597318377221202632011-06-09T06:37:00.000-07:002013-04-10T12:28:20.742-07:00How to improve JPA performance by 1,825%The Java Persistence API (JPA) provides a rich persistence architecture. JPA hides much of the low level dull-drum of database access, freeing the application developer from worrying about the database, and allowing them to concentrate on developing the application. However, this abstraction can lead to poor performance, if the application programmer does not consider how their implementation affects database usage.<br /><br />JPA provides several optimization features and techniques, and some pitfalls waiting to snag the unwary developer. Most JPA providers also provide a plethora of additional optimization features and options. In this blog entry I will explore the various optimizations options and techniques, and a few of the common pitfalls.<br /><br />The application is a simulated database migration from a MySQL database to an Oracle database. Perhaps there are more optimal ways to migrate a database, but it is surprising how good JPA's performance can be, even in processing hundreds of thousand or even millions of records. Perhaps it is not a straight forward migration, or the application's business logic is required, or perhaps the application has already been persisted through JPA, so using JPA to migrate the database is just easiest. Regardless, this fictitious use case is a useful demonstration of how to achieve good performance with JPA.<br /><br />The application consists of an Order processing database. The model contains a Customer, Order and OrderLine. The application reads all of the Orders from one database, and persists them to the second database. The source code for the example can be found <a href="http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.performance/">here</a>.<br /><br />The initial code for the migration is pretty simple:<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>EntityManagerFactory emf = Persistence.createEntityManagerFactory("order");<br />EntityManager em = emf.createEntityManager();<br />EntityManagerFactory emfOld = Persistence.createEntityManagerFactory("order-old");<br />EntityManager emOld = emfOld.createEntityManager();<br />Query query = emOld.createQuery("Select o from Order o");<br />List<order> orders = query.getResultList();<br />em.getTransaction().begin();<br />// Reset old Ids, so they are assigned from the new database.<br />for (Order order : orders) {<br />order.setId(0);<br />order.getCustomer().setId(0);<br />}<br />for (Order order : orders) {<br />em.persist(order);<br />for (OrderLine orderLine : order.getOrderLines()) {<br />em.persist(orderLine);<br />}<br />}<br />em.getTransaction().commit();<br />em.close();<br />emOld.close();<br />emf.close(); <br />emfOld.close();<br /></code></pre><br />The example test runs this migration using 3 variables for the number of Customers, Orders per Customer, and OrderLines per Order. So, 1000 customers, each with 10 orders, and each with 10 order lines, would be 111,000 objects.<br /><br />The test was run on a virtualized 64 bit Oracle Sun server with 4 virtual cores and 8 gigs of RAM. The databases run on similar machines. The test is single threaded, running in Oracle Sun JDK 1.6. The tests are run using EclipseLink JPA 2.3, and migrating from a MySQL database to an Oracle database.<br /><br />This code functions fine for a small database migration. But as the database size grows, some issues become apparent. It actually handles 100,000 objects surprisingly well, taking about 2 minutes. This is surprisingly well, given it is thoroughly unoptimized and persisting all 100,000 objects in a single persistence context and transaction.<br /><br /><h4>Optimization #1 - Agent</h4>EclipseLink implements LAZY for OneToOne and ManyToOne relationships using byte code weaving. EclipseLink also uses weaving to perform many other optimizations, such as change tracking and fetch groups. The JPA specification provides the hooks for weaving in EJB 3 compliant application servers, but in Java SE or other application servers weaving is not performed by default. To enable EclipseLink weaving in Java SE for this example the EclipseLink agent is used. This is done using the Java <code>-javaagent:eclipselink.jar</code> option. If dynamic weaving is unavailable in your environment, another option is to use static weaving, for which EclipseLink provides an ant task and command line utility.<br /><br /><h4>Optimization #2 - Pagination</h4>In theory at some point you should run out of memory by bringing the entire database into memory in a single persistence context. So next I increased the size to 1 million objects, and this gave the expect out of memory error. Interestingly this was with only using a heap size of 512 meg. If I had used the entire 8 gigs of RAM, I could, in theory, have persisted around 16 million objects in a single persistence context. If I gave the virtualized machine the full 98 gigs of RAM available on the server, perhaps it would even be possible to persist 100 millions objects. Perhaps we are beyond the day when it does not make sense to pull an entire database into RAM, and perhaps this is no longer such as crazy thing to do. But, for now, lets assume it is an idiotic thing to do, so how can we avoid this?<br /><br />JPA provides a pagination feature that allows a subset of a query to be read. This is supported in JPA in the <code>Query setFirstResult,setMaxResults</code> API. So instead of reading the entire database in one query, the objects will be read page by page, and each page will be persisted in its own persistence context and transaction. This avoids ever having to read the entire database, and also should, in theory, make the persistence context more optimized by reducing the number of objects it needs to process together.<br /><br />Switching to using pagination is relatively easy to do for the original orders query, but some issues crop up with the relationship to Customer. Since orders can share the same customer, it is important that each order does not insert a new customer, but uses the existing customer. If the customer for the order was already persisted on a previous page, then the existing one must be used. This requires the usage of a query to find the matching customer in the new database, which introduces some performance issues we will discuss later.<br /><br />The updated code for the migration using pagination is:<br /><br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>EntityManagerFactory emf = Persistence.createEntityManagerFactory("order");<br />EntityManagerFactory emfOld = Persistence.createEntityManagerFactory("order-old");<br />EntityManager emOld = emfOld.createEntityManager();<br />Query query = emOld.createQuery("Select o from Order o order by o.id");<br />int pageSize = 500;<br />int firstResult = 0;<br />query.setFirstResult(firstResult);<br />query.setMaxResults(pageSize);<br />List<order> orders = query.getResultList();<br />boolean done = false;<br />while (!done) {<br />if (orders.size() < pageSize) {<br /> done = true;<br /> }<br /> EntityManager em = emf.createEntityManager();<br /> em.getTransaction().begin();<br /> Query customerQuery = em.createNamedQuery("findCustomByName");<br /> // Reset old Ids, so they are assigned from the new database.<br /> for (Order order : orders) {<br /> order.setId(0);<br /> customerQuery.setParameter("name", order.getCustomer().getName());<br /> try {<br /> Customer customer = (Customer)customerQuery.getSingleResult();<br /> order.setCustomer(customer);<br /> } catch (NoResultException notPersistedYet) {<br /> // Customer does not yet exist, so null out id to have it persisted.<br /> order.getCustomer().setId(0);<br /> }<br /> }<br /> for (Order order : orders) {<br /> em.persist(order);<br /> for (OrderLine orderLine : order.getOrderLines()) {<br /> em.persist(orderLine);<br /> }<br /> }<br /> em.getTransaction().commit();<br /> em.close();<br /> firstResult = firstResult + pageSize;<br /> query.setFirstResult(firstResult);<br /> if (!done) {<br /> orders = query.getResultList();<br /> }<br />}<br />emOld.close();<br />emf.close(); <br />emfOld.close();<br /></code></pre><br /><h4>Optimization #3 - Query Cache</h4>This will introduce a lot of queries for customer by name (10,000 to be exact), one for each order. This is not very efficient, and can be improved through caching. In EclipseLink there is both an object cache and a query cache. The object cache is enabled by default, but objects are only cached by Id, so this does not help us on the query using the customer's name. So, we can enable a query cache for this query. A query cache is specific to the query, and caches the query results keyed on the query name and its parameters. A query cache is enabled in EclipseLink through using the query hint <code>"eclipselink.query-results-cache"="true"</code>. This should be set where the query is defined, in this case in the orm.xml. This will reduce the number of queries for customer to 1,000, which is much better.<br /><br />There are other solutions to using the query cache. EclipseLink also supports in-memory querying. In-memory querying means evaluating the query on all of the objects in the object cache, instead of accessing the database. In-memory querying is enabled through the query hint <code>"eclipselink.cache-usage"="CheckCacheOnly"</code>. If you enabled a full cache on customer, then as you persisted the orders all of the existing customers would be in the cache, and you would never need to access the database. Another manual solution is to maintain a Map in the migration code keying the new customer's by name. For all of the above solutions if the cache is made fixed sized (query cache defaults to a size of 100), you would never need all of the customers in memory at the same time, so there would be no memory issues.<br /><br /><h4>Optimization #4 - Batch Fetch</h4>The most common performance issue in JPA is in the fetch of relationships. If you query n orders, and access their order-lines, you get n queries for order-line. This can be optimized through join fetching and batch fetching. Join fetching, joins the relationship in the original query and selects from both tables. Batch fetch executes a second query for the related objects, but fetches them all at once, instead of one by one. Because we are using pagination, this make optimizing the fetch a little more tricky. Join fetch which still work, but since order-lines is join fetched, and there are 10 order-lines per order, the page size that was 500 orders, in now only 50 orders (and their 500 order-lines). We can resolve this by increasing the page size to 5000, but given in a real application the number of order-lines in not fixed, this becomes a bit of a guess. But the page size was just a heuristic number anyway, so no real issue. Another issue with join fetching with pagination is the last and first object may not have all of its related objects, if it falls in-between a page. Fortunately EclipseLink is smart enough to handle this, but it does require 2 extra queries for the first and last order of each page. Join fetching also has the draw back that it is selecting more data when a OneToMany is join fetched. Join fetching is enable in JPQL using <code>join fetch o.orderLine</code>.<br /><br />Batch fetching normally works by joining the original query with the relationship query, but because the original query used pagination, this will not work. EclipseLink supports three types of batch fetching, JOIN, EXISTS, and IN. IN works with pagination, so we can use IN batch fetching. Batch fetch is enabled through the query hint <code>"eclipselink.batch"="o.orderLines"</code>, and <code>"eclipselink.batch.type"="IN"</code>. This will reduce the n queries for order-line to 1. So for each batch/page of 500 orders, there will be 1 query for the page of orders, and 1 query for the order-lines, and 50 queries for customer.<br /><br /><h4>Optimization #5 - Read Only</h4>The application is migrating from the MySQL database to the Oracle database. So is only reading from MySQL. When you execute a query in JPA, all of the resulting objects become managed as part of the current persistence context. This is wasteful in JPA, as managed objects are tracked for changes and registered with the persistence context. EclipseLink provides a <code>"eclipselink.read-only"="true"</code> query hint that allows the persistence context to be bypassed. This can be used for the migration, as the objects from MySQL will not be written back to MySQL.<br /><br /><h4>Optimization #6 - Sequence Pre-allocation</h4>We have optimized the first part of the application, reading from the MySQL database. The second part is to optimize the writing to Oracle.<br /><br />The biggest issue with the writing process is that the Id generation is using an allocation size of 1. This means that for every insert there will be an update and a select for the next sequence number. This is a major issue, as it is effectively doubling the amount of database access. By default JPA uses a pre-allocation size of 50 for TABLE and SEQUENCE Id generation, and 1 for IDENTITY Id generation (a very good reason to never use IDENTITY Id generation). But frequently applications are unnecessarily paranoid of holes in their Id values and set the pre-allocaiton value to 1. By changing the pre-allocation size from 1 to 500, we reduce about 1000 database accesses per page.<br /><br /><h4>Optimization #7 - Cascade Persist</h4>I must admit I intentionally added the next issue to the original code. Notice in the for loop persisting the orders, I also loop over the order-lines and persist them. This would be required if the order did not cascade the persist operation to order-line. However, I also made the <code>orderLines</code> relationship cascade, as well as order-line's <code>order</code> relationship. The JPA spec defines somewhat unusual semantics to its persist operation, requiring that the cascade persist be called every time persist is called, even if the object is an existing object. This makes cascading persist a potentially dangerous thing to do, as it could trigger a traversal of your entire object model on every persist call. This is an important point, and I added this issue purposefully to highlight this point, as it is a common mistake made in JPA applications. The cascade persists causes each persist call to order-line to persist its order, and <i>every</i> order-line of the order again. This results in an n^2 number of persist calls. Fortunately there are only 10 order-lines per order, so this only results in 100 extra persist calls per order. It could have been much worse if the customer defined a relationship back to its orders, then you would have 1000 extra calls per order. The persist does not need to do anything, as the objects are already persisted, but the traversal can be expensive. So, in JPA you should either mark your relationships cascade persist, or call persist in your code, but not both. In general I would recommend only cascading persist for logically dependent relationships (i.e. things that would also cascade remove).<br /><br /><h4>Optimization #8 - Batch Writing</h4>Many databases provide an optimization that allows a batch of write operations to be performed as a single database access. There is both parametrized and dynamic batch writing. For parametrized batch writing a single parametrized SQL statement can be executed with a batch of parameter vales instead of a single set of parameter values. This is very optimal as the SQL only needs to be executed once, and all of the data can be passed optimally to the database.<br /><br />Dynamic batch writing requires dynamic (non-parametrized) SQL that is batched into a single big statement and sent to the database all at once. The database then needs to process this huge string and execute each statement. This requires the database do a lot of work parsing the statement, so is no always optimal. It does reduce the database access, so if the database is remote or poorly connected with the application, this can result in an improvement.<br /><br />In general parametrized batch writing is much more optimal, and on Oracle it provides a huge benefit, where as dynamic does not. JDBC defines the API for batch writing, but not all JDBC drivers support it, some support the API but then execute the statements one by one, so it is important to test that your database supports the optimization before using it. In EclipseLink batch writing is enabled using the persistence unit property <code>"eclipselink.jdbc.batch-writing"="JDBC"</code>.<br /><br />Another important aspect of using batch writing is that you must have the same SQL (DML actually) statement being executed in a grouped fashion in a single transaction. Some JPA providers do not order their DML, so you can end up ping-ponging between two statements such as the order insert and the order-line insert, making batch writing in-effective. Fortunately EclipseLink orders and groups its DML, so usage of batch writing reduces the database access from 500 order inserts and 5000 order-line inserts to 55 (default batch size is 100). We could increase the batch size using <code>"eclipselink.jdbc.batch-writing.size"</code>, so increasing the batch size to 1000 reduces the database accesses to 6 per page.<br /><br /><h4>Optimization #9 - Statement caching</h4>Every time you execute an SQL statement, the database must parse that statement and execute it. Most of the time application executes the same set of SQL statements over and over. By using parametrized SQL and caching the prepared statement you can avoid the cost of having the database parse the statement.<br /><br />There are two levels of statement caching. One done on the database, and one done on the JDBC client. Most databases maintain a parse cache automatically, so you only need to use parametrized SQL to make use of it. Caching the statement on the JDBC client normally provides the bigger benefit, but requires some work. If your JPA provider is providing you with your JDBC connections, then it is responsible for statement caching. If you are using a DataSource, such as in an application server, then the DataSource is responsible for statement caching, and you must enable it in your DataSource config. In EclipseLink, when using EclipseLink's connection pooling, you can enable statement caching using the persistence unit property <code>"eclipselink.jdbc.cache-statements"="true"</code>. EclipseLink uses parametrized SQL by default, so this does not need to be configured.<br /><br /><h4>Optimization #10 - Disabling Caching</h4>By default EclipseLink maintains a shared 2nd level object cache. This normally is a good thing, and improves read performance significantly. However, in our application we are only inserting into Oracle, and never reading, so there is no point to maintaining a shared cache. We can disable this using the EclipseLink persistence unit property <code>"eclipselink.cache.shared.default"="false"</code>. However, we are reading customer, so we can enable caching for customer using, <code>"eclipselink.cache.shared.Customer"="true"</code>.<br /><br /><h4>Optimization #11 - Other Optimizations</h4>EclipseLink provides several other more specific optimizations. I would not really recommend all of these in general as they are fairly minor, and have certain caveats, but they are useful in use cases such as migration where the process is well defined.<br /><br />These include the following persistence unit properties:<br /><ul><li><code>"eclipselink.persistence-context.flush-mode"="commit"</code> - Avoids the cost of flushing on every query execution.<br /><li><code>"eclipselink.persistence-context.close-on-commit"="true"</code> - Avoids the cost of resuming the persistence context after the commit.<br /><li><code>"eclipselink.persistence-context.persist-on-commit"="false"</code> - Avoids the cost of traversing and persisting all objects on commit.<br /><li><code>"eclipselink.logging.level"="off"</code> - Avoids some logging overhead.<br /></ul>The fully optimized code: <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>EntityManagerFactory emf = Persistence.createEntityManagerFactory("order-opt");<br />EntityManagerFactory emfOld = Persistence.createEntityManagerFactory("order-old");<br />EntityManager emOld = emfOld.createEntityManager();<br />System.out.println("Migrating database.");<br />Query query = emOld.createQuery("Select o from Order o order by o.id");<br />// Optimization #2 - batch fetch<br />// #2 - a - join fetch<br />//Query query = emOld.createQuery("Select o from Order o join fetch o.orderLines"); // #2 - b - batch fetch (batch fetch is more optimal as avoids duplication of Order data)<br />query.setHint("eclipselink.batch", "o.orderLines"); query.setHint("eclipselink.batch.type", "IN");<br />// Optimization #3 - read-only<br />query.setHint("eclipselink.read-only", "true");<br />// Optimization #4 - pagination int pageSize = 500; int firstResult = 0; query.setFirstResult(firstResult);<br />query.setMaxResults(pageSize); <br />List<order> orders = query.getResultList();<br />boolean done = false;<br />while (!done) {<br /> if (orders.size() < pageSize) {<br /> done = true;<br /> }<br /> EntityManager em = emf.createEntityManager();<br /> em.getTransaction().begin();<br /> Query customerQuery = em.createNamedQuery("findCustomByName");<br /> // Reset old Ids, so they are assigned from the new database.<br /> for (Order order : orders) {<br /> order.setId(0);<br /> customerQuery.setParameter("name", order.getCustomer().getName());<br /> try {<br /> Customer customer = (Customer)customerQuery.getSingleResult();<br /> order.setCustomer(customer);<br /> } catch (NoResultException notPersistedYet) {<br /> // Customer does not yet exist, so null out id to have it persisted.<br /> order.getCustomer().setId(0);<br /> }<br /> }<br /> for (Order order : orders) {<br /> em.persist(order);<br /> // Optimization #5 - avoid n^2 persist calls<br /> //for (OrderLine orderLine : order.getOrderLines()) {<br /> // em.persist(orderLine);<br /> //}<br /> }<br /> em.getTransaction().commit();<br /> em.close();<br /> firstResult = firstResult + pageSize;<br /> query.setFirstResult(firstResult);<br /> if (!done) {<br /> orders = query.getResultList();<br /> }<br />}<br />emOld.close();<br />emf.close(); <br />emfOld.close();<br /></code></pre>The optimized persistence.xml: <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>&lt;persistence-unit name="order-opt" transaction-type="RESOURCE_LOCAL"&gt;<br /> &lt;!-- Optimization #7, 8 - sequence preallocation, query result cache --&gt;<br /> &lt;mapping-file&gt;META-INF/order-orm.xml&lt;/mapping-file&gt;<br /> &lt;class&gt;model.Order&lt;/class&gt;<br /> &lt;class&gt;model.OrderLine&lt;/class&gt;<br /> &lt;class&gt;model.Customer&lt;/class&gt;<br /> &lt;properties&gt;<br /> &lt;!-- Change this to access your own database. --&gt;<br /> &lt;property name="javax.persistence.jdbc.driver" value="oracle.jdbc.OracleDriver" /&gt;<br /> &lt;property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@ottvm028.ca.oracle.com:1521:TOPLINK" /&gt;<br /> &lt;property name="javax.persistence.jdbc.user" value="jsutherl" /&gt;<br /> &lt;property name="javax.persistence.jdbc.password" value="password" /&gt;<br /> &lt;property name="eclipselink.ddl-generation" value="create-tables" /&gt;<br /> &lt;!-- Optimization #9 - statement caching --&gt;<br /> &lt;property name="eclipselink.jdbc.cache-statements" value="true" /&gt;<br /> &lt;!-- Optimization #10 - batch writing --&gt;<br /> &lt;property name="eclipselink.jdbc.batch-writing" value="JDBC" /&gt;<br /> &lt;property name="eclipselink.jdbc.batch-writing.size" value="1000" /&gt;<br /> &lt;!-- Optimization #11 - disable caching for batch insert (caching only improves reads, so only adds overhead for inserts) --&gt;<br /> &lt;property name="eclipselink.cache.shared.default" value="false" /&gt;<br /> &lt;!-- Except for Customer which is shared by orders --&gt;<br /> &lt;property name="eclipselink.cache.shared.Customer" value="true" /&gt;<br /> &lt;!-- Optimization #12 - turn logging off --&gt;<br /> &lt;!-- property name="eclipselink.logging.level" value="FINE" /--&gt;<br /> &lt;property name="eclipselink.logging.level" value="off" /&gt;<br /> &lt;!-- Optimization #13 - close EntityManager on commit, to avoid cost of resume --&gt;<br /> &lt;property name="eclipselink.persistence-context.close-on-commit" value="true" /&gt;<br /> &lt;!-- Optimization #14 - avoid auto flush cost on query execution --&gt;<br /> &lt;property name="eclipselink.persistence-context.flush-mode" value="commit" /&gt;<br /> &lt;!-- Optimization #15 - avoid cost of persist on commit --&gt;<br /> &lt;property name="eclipselink.persistence-context.persist-on-commit" value="false" /&gt;<br /> &lt;/properties&gt;<br />&lt;/persistence-unit&gt;<br /></code></pre>The optimized orm.xml: <pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;<br />&lt;entity-mappings version="2.1"<br /> xmlns="http://www.eclipse.org/eclipselink/xsds/persistence/orm"<br /> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;<br /><br /> &lt;named-query name="findCustomByName"&gt;<br /> &lt;query&gt;Select c from Customer c where c.name = :name&lt;/query&gt;<br /> &lt;hint name="eclipselink.query-results-cache" value="true"/&gt;<br /> &lt;/named-query&gt;<br /> &lt;entity class="model.Order"&gt;<br /> &lt;table-generator name="ORD_SEQ" allocation-size="500"/&gt;<br /> &lt;/entity&gt;<br /> &lt;entity class="model.Customer"&gt;<br /> &lt;table-generator name="CUST_SEQ" allocation-size="500"/&gt;<br /> &lt;/entity&gt;<br /><br />&lt;/entity-mappings&gt;<br /></code></pre>So, what is the result? The original un-optimized code took on average 133,496 milliseconds (~2 minutes) to process ~100,000 objects. The fully optimized code took only 6,933 milliseconds (6 seconds). This is very good, and means it could process 1 million objects in about 1 minute. The optimized code is an 1,825% improvement on the original code. <p/>But, how much did each optimization affect this final result? To answer this question I ran the test 3 times with the fully optimized version, but with each optimization missing. This worked out better than starting with the unoptimized version and only adding each operation separately, as some optimizations get masked by the lack of others. So, in the table below the bigger the % difference, the better the optimization (that was removed) was. <table cellspacing=0 cellpadding=4 border=1><tr><th>Optimization</th><th>Average Result (ms)</th><th>% Difference</th></tr><tr><td>None</td><td align=right>133,496</td><td align=right>1,825%</td></tr><tr><td>All</td><td align=right>6,933</td><td align=right>0%</td></tr><tr><td>1 - no agent</td><td align=right>7,906</td><td align=right>14%</td></tr><tr><td>2 - no pagination</td align=right><td align=right>8,679</td><td align=right>25%</td></tr><tr><td>3 - no read-only</td><td align=right>8,323</td><td align=right>20%</td></tr><tr><td>4a - join fetch</td><td align=right>11,836</td><td align=right>71%</td></tr><tr><td>4b - no batch fetch</td><td align=right>17,344</td><td align=right>150%</td></tr><tr><td>5 - no sequence pre-allocation</td><td align=right>30,396</td><td align=right>338%</td></tr><tr><td>6 - no persist loop</td><td align=right>7,947</td><td align=right>14%</td></tr><tr><td>7 - no batch writing</td><td align=right>75,751</td><td align=right>992%</td></tr><tr><td>8 - no statement cache</td><td align=right>7,233</td><td align=right>4%</td></tr><tr><td>9 - with cache</td><td align=right>7,925</td><td align=right>14%</td></tr><tr><td>10 - other</td><td align=right>7,332</td><td align=right>6%</td></tr></table><br />This shows that batch writing was the best optimization, followed by sequence pre-allocation, then batch fetching.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com37tag:blogger.com,1999:blog-6877629428951398731.post-91570652034016704392011-05-04T07:03:00.000-07:002013-04-10T12:29:26.970-07:00Data Partitioning - Scaling the DatabaseIn Enterprise Java most of the effort is normally done to scale the mid-tier application and its server.&nbsp; Pretty much every Java application server supports clustering and scaling out the application to several mid-tier machines.&nbsp; Even if the application server does not officially support clustering, it is normally pretty easy to have a "cluster" of application servers fronted by a round-robin load-balancer.&nbsp; This would even work with something as simple as Tomcat.<br /><br />The mid-tier normally scales very well to a cluster, as it does not have any shared in-memory data, as this data is normally stored in a database.&nbsp; All of the mid-tier cluster members access the same database, and life is good.&nbsp; The application can have unlimited performance, simply by adding more mid-tier machines.&nbsp; But what happens when the poor database machine suddenly can't take any more requests?<br /><br />The most common solution to scaling the database seems to be to buy a bigger and badder database machine.&nbsp; If 8 cores is not cutting it, then perhaps 16 cores will.&nbsp; This solution in general works pretty good, assuming hardware vendors can keep stuffing more cores into their machines.&nbsp; This solution seems to be used in Enterprise Java performance benchmarks such as SpecJ, if you look at the nodes column of the SpecJ 2010 results, all the results have a single database node, some with as many as 40 cores, even though some have 8 mid-tier nodes.<br /><br /><a href="http://www.spec.org/jEnterprise2010/results/jEnterprise2010.html">http://www.spec.org/jEnterprise2010/results/jEnterprise2010.html</a><br /><br /><br />But what happens when you can't stuff any more cores into a machine, or the cost of an insanely multi-core machine greatly outweighs the cost of multiple lower end hardware machines?&nbsp; Perhaps this is just a hardware problem, and you just need to wait for the hardware vendors to make a bigger and badder machine, but there are other solutions from the wonderful world of software.<br /><br />The first solution to look into is to optimize your own application (as always).&nbsp; Generally the application's code is not so efficient in its database access, and by optimizing the number and types of queries hitting the database, using parametrized SQL, using batch writing, using lazy, join and batch fetching, a significant load can be removed from the database.&nbsp; But perhaps you already did that, or don't have the expertise, or just don't feel like it.<br /><br />The second solution is to optimize your database.&nbsp; By ensuring your database is configured optimally, has the correct indexes, queries are using the optimal query plan, and the disk access optimally, its performance, and thus scalability can be improved.&nbsp; &nbsp; But perhaps you already did that, or don't have the expertise, or just don't feel like it.<br /><br />The third solution is to investigate caching in the mid-tier.&nbsp; By caching objects and data in the mid-tier, you can offload a lot of the queries hitting the database, and improve your application's performance to boot.&nbsp; Most <a href="http://en.wikipedia.org/wiki/Java_Persistence_API">JPA </a>providers support caching, and some such as <a href="http://www.eclipse.org/eclipselink/">EclipseLink </a>offer quite advanced caching functionality including invalidation, and coordinated clustered caches.&nbsp; JPA 2.0 defines some basic caching annotations to enable and access the cache.<br /><br />Caching mainly benefits reads, but some caching solutions such as <a href="http://www.oracle.com/technetwork/middleware/coherence/overview/index.html">Oracle Coherence</a> offer the ability to offload writes as well.&nbsp; <a href="http://www.oracle.com/technetwork/middleware/toplink/overview/index.html">Oracle TopLink Grid</a> provides JPA support for Coherence.<br /><br />Caching can be a good solution, but there can be issues with stale data, clustering, and mid-tier contention.&nbsp; Some caching solution are very good, but not always as good at managing concurrent access to data as relational databases that have been doing it for decades.&nbsp; Also if your database has become a bottleneck because of writes, then caching reads may not be a solution.<br /><br />The best solution is to scale the database through clustering the database across multiple machines.&nbsp; This could be a real clustered database, such as <a href="http://www.oracle.com/technetwork/database/clustering/overview/index.html">Oracle RAC</a>, or just multiple regular database instances.&nbsp; Clustered database are good, and can improve your scalability without much work, but depending on your application you may also have to partition your data across the database nodes for optimal scalability. Without partitioning, if you write a row on one node, then access it on another, the other node must request the latest copy of the data from the other node, this can potentially make performance worse.<br /><br />Partitioning splits your data across each of the database nodes.&nbsp; There is horizontal partitioning, and vertical partitioning.&nbsp; Vertical partitioning is normally the easiest to implement.&nbsp; You can just put half of your classes on one database, and the other half on another.&nbsp; Ideally the two sets would be isolated from each other and not have any cross database relationships.<br /><br />For horizontal partitioning&nbsp; you need to split your data across multiple database nodes.&nbsp; Each database node will have the same tables, but each node's table will only store part of the data.&nbsp; You can partition the data by the data values, such as range partitioning, value partitioning, hash partitioning, or even round robin.<br /><br />To enable data partitioning you require your persistence solution to be aware of how to partition the data.&nbsp;&nbsp; <a href="http://wiki.eclipse.org/EclipseLink/Release/2.2.0">EclipseLink 2.2</a> added support for partitioning data.&nbsp; Both vertical and horizontal partitioning is supported.&nbsp; Several partitioning options are provided at the Session, Entity and Query level,<br /><ul><li>Range partitioning - each partition maps a range of field values.</li><li>Value partitioning - each field value maps to a partition.</li><li>Hash partitioning - the field value is hashed to determine its partition.</li><li>Pinned partitioning - allows an Entity or query to be vertically partitioned.</li><li>Round robin - allows load balancing of requests across multiple database nodes.</li><li>Replication - allows data to be replicated across multiple database nodes.</li></ul>EclipseLink supports partitioning across any database, including both clustered databases such as Oracle RAC, and&nbsp; standard databases such as MySQL. Relationships and queries across database partitions are supported, but joins across partitions are only supported for clustered databases.<br /><br />So how does data partitioning with JPA and EclipseLink scale?&nbsp; To determine the answer, I developed a simple order processing example.&nbsp; The example defines an Order, OrderLine and Customer.&nbsp; The example client processes orders for a Customer using 16 client threads.&nbsp; The application is primarily insert oriented, so heavily uses the database.&nbsp; I first ran the application without partitioning on a single MySQL database instance.&nbsp; To give the poor database no chance of keeping up to the mid-tier client, I ran the mid-tier on a virtualized 8 core machine with 16g of ram (Oracle Sun hardware, Oracle Linux OS).&nbsp; I ran the MySQL database on a similar machine, but only gave it 1 virtual core and 8g or RAM.&nbsp; So, I was pretty sure the application would be bottlenecking on the database.&nbsp; This was the goal, to simulate a cluster of mid-tier machines accessing a single database machine.<br /><br />Next, I enable partitioning of the Order and OrderLine by the ORDER_ID using hash partitioning across two database nodes.&nbsp; I also hash partitioned Customer by its ID.&nbsp; This resulted in about half of the transactions going to one database, and half to the other.&nbsp; Because the Order and the OrderLine shared the same ORDER_ID, they were partitioned to the same database node, so I did not need to worry about transactions spanning multiple nodes.&nbsp; The read for the Customer could go to either node, but because it was a non-transactional read, this was just routed separately by EclipseLink, which has support for using different connections for non-transactional reads versus transactional writes.&nbsp; Having writes span multiple nodes in a single transaction is normally not desirable.&nbsp; EclipseLink allows this and can be integrated with JTA to give 2-phase commit across the nodes.&nbsp; If JTA is not used EclipseLink still does a 2-stage commit, but there are no gaurentees if all of the writes succeed, but the commit transaction fails.<br /><br />The resulting order was mapped as,<br /><pre style="background:lightyellow none repeat scroll 0 0; border:1px inset orange; margin:10px; overflow:auto; padding:6px; text-align:left;"><code>@Entity<br />@Table(name="PART_ORDER")<br />@HashPartitioning(<br /> name="HashPartitionByOrderId",<br /> partitionColumn=@Column(name="ORDER_ID"),<br /> connectionPools={"default","node2"})<br />@Partitioned("HashPartitionByOrderId")<br />public class Order implements Serializable {<br />&nbsp;&nbsp;&nbsp; @Id<br />&nbsp;&nbsp;&nbsp; @GeneratedValue(strategy=GenerationType.TABLE)<br />&nbsp;&nbsp;&nbsp; @Column(name="ORDER_ID")<br />&nbsp;&nbsp;&nbsp; private long id;<br />&nbsp;&nbsp;&nbsp; @Basic<br />&nbsp;&nbsp;&nbsp; private String description;<br />&nbsp;&nbsp;&nbsp; @Basic<br />&nbsp;&nbsp;&nbsp; private BigDecimal totalCost = BigDecimal.valueOf(0);<br />&nbsp;&nbsp;&nbsp; @OneToMany(mappedBy="order", cascade=CascadeType.ALL, orphanRemoval=true)<br />&nbsp;&nbsp;&nbsp; @OrderBy("lineNumber")<br />&nbsp;&nbsp;&nbsp; private List<orderline> orderLines = new ArrayList<orderline>();<br />&nbsp;&nbsp;&nbsp; @ManyToOne(fetch=FetchType.LAZY)<br />&nbsp;&nbsp;&nbsp; private Customer customer;</orderline></orderline><br />}<br /></code></pre><br />For the full source code for the example see <a href="http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.partitioned/">here</a>.<br /><br />For the second run a 2nd MySQL database was added running on a separate 1 core machine.&nbsp; The result showed a 66% increase in scalability for the application, processing close to 2x as many orders. The test application was run for 1 minute and the total number of processed orders for all 16 client threads totaled. This was run 5 times and the results averaged for each configuration.<br /><br />The results:<br /><table><tbody><tr><td>Configuration</td><td>Threads</td><td>Average processed orders</td><td>%STD</td><td>%DIF </td></tr><tr><td>Single database</td><td>16</td><td>11,150</td><td>0.4%</td><td>0%</td></tr><tr><td>2 node partition</td><td>16</td><td>18,583</td><td>2.2%</td><td>66%</td></tr></tbody></table><br />The results show that through effective partitioning the database can be scaled out to multiple machines as well as the mid-tier.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com8tag:blogger.com,1999:blog-6877629428951398731.post-38010764426792450452011-03-07T08:33:00.000-08:002013-04-11T10:54:16.903-07:00JVM Performance - Part III - Concurrent Maps<h2>Concurent Maps</h2>The main difference between Hashtable and HashMap is that Hashtable is synchronized. For this reason Hashtable is still used in a lot of concurrent code because it is, in theory, thread safe.&nbsp; This theory is however normally just a theory, because if you don't write concurrent code correctly, it will still not be thread safe no matter how many synchronized methods you have.<br /><br />For example you could call get() on the Hashtable, then if it is not there call put(), both operations are synchronized and thread-safe, but in between your get() and put() another thread could have done the same thing and put something there already, in which case your code may be incorrect and have thrown away some other thread's data.&nbsp; With a Hashtable the solution to this is to synchronize the whole operation on the map.<br /><br /><span style="font-family: arial;"><code></code><pre>Object value = map.get(key);<br />if (value == null) {<br /> synchronized (map) {<br /> value = map.get(key);<br /> if (value == null) {<br /> value = buildValue(key);<br /> map.put(key, value);<br /> }<br /> }<br />}<br /></pre></span><br />JDK 1.5 added the ConcurrentMap implementation that is thread safe, and designed and optimized for concurrent access. It basically has pages inside the map, to avoid locks on concurrent access to different pages. It also provides useful API such as putIfAbsent() to allow something to be put in the map unless it is already there, in a thread safe manner.&nbsp; Using putIfAbsent() is more efficient than using a synchronized get() and put() in both concurrency and performance.<br /><br /><span style="font-family: arial;"><code></code><pre>Object value = map.get(key);<br />if (value == null) {<br /> Object newValue = buildValue(key);<br /> value = map.putIfAbsent(key, value);<br /> if (value == null) {<br /> value = newValue;<br /> }<br />}<br /></pre></span><br />So, how does the performance and concurrency of HashMap, Hashtable and ConcurrentMap stack up?&nbsp; This test compare the performance for gets and puts in various Map implementations using 1 to 32 threads. It does 100 gets or puts in a Map of size 100.&nbsp; Two machines were tested.&nbsp; The first machines is my Windows XP desktop, that has two cores. The second machine is an 8 core Linux server.&nbsp; All tests were run 5 times and averaged, Oracle Sun JDK 1.6.23 was used.<br /><br />Threads, is the number of thread running the test.&nbsp; The average is the total number of operations performed in the time period by all threads in total.&nbsp; The %STD is the percentage standard deviation in the results.&nbsp; The %DIF is the percentage difference between the run and the single threaded run (for the same Map type). <br /><br /><h3>Concurrent Map Performance Comparison (desktop, 2cpu)</h3><table><tbody><tr><td>Map</td><td>Opperation</td><td>Threads</td><td>Average</td><td>%STD</td><td>%DIF (with 1 thread)</td></tr><tr><td>HashMap</td><td>get</td><td>1</td><td>3551306</td><td>0.06%</td><td>0%</td></tr><tr><td>HashMap</td><td>get</td><td>2</td><td>4121102</td><td>0.03%</td><td>16%</td></tr><tr><td>HashMap</td><td>get</td><td>4</td><td>4132506</td><td>0.15%</td><td>16%</td></tr><tr><td>HashMap</td><td>get</td><td>8</td><td>4227485</td><td>0.68%</td><td>19%</td></tr><tr><td>HashMap</td><td>get</td><td>16</td><td>4402532</td><td>1.36%</td><td>23%</td></tr><tr><td>HashMap</td><td>get</td><td>32</td><td>4426514</td><td>1.61%</td><td>24%</td></tr><tr><td>Hashtable</td><td>get</td><td>1</td><td>1132956</td><td>0.06%</td><td>0%</td></tr><tr><td>Hashtable</td><td>get</td><td>2</td><td>364236</td><td>0.08%</td><td>-211%</td></tr><tr><td>Hashtable</td><td>get</td><td>4</td><td>274603</td><td>0.14%</td><td>-312%</td></tr><tr><td>Hashtable</td><td>get</td><td>8</td><td>277188</td><td>1.08%</td><td>-308%</td></tr><tr><td>Hashtable</td><td>get</td><td>16</td><td>277881</td><td>0.78%</td><td>-307%</td></tr><tr><td>Hashtable</td><td>get</td><td>32</td><td>296779</td><td>2.51%</td><td>-281%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>1</td><td>2771098</td><td>0.04%</td><td>0%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>2</td><td>3466451</td><td>2.30%</td><td>25%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>4</td><td>3458492</td><td>0.33%</td><td>24%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>8</td><td>3510282</td><td>0.31%</td><td>26%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>16</td><td>3613182</td><td>2.36%</td><td>30%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>32</td><td>3599489</td><td>2.23%</td><td>29%</td></tr><tr><td>HashMap</td><td>put</td><td>1</td><td>3897925</td><td>0.07%</td><td>0%</td></tr><tr><td>HashMap</td><td>put</td><td>2</td><td>2614784</td><td>0.01%</td><td>-49%</td></tr><tr><td>HashMap</td><td>put</td><td>4</td><td>2473011</td><td>0.21%</td><td>-57%</td></tr><tr><td>HashMap</td><td>put</td><td>8</td><td>2482743</td><td>0.30%</td><td>-57%</td></tr><tr><td>HashMap</td><td>put</td><td>16</td><td>2506519</td><td>0.53%</td><td>-55%</td></tr><tr><td>HashMap</td><td>put</td><td>32</td><td>2579715</td><td>0.30%</td><td>-51%</td></tr><tr><td>Hashtable</td><td>put</td><td>1</td><td>1042076</td><td>0.33%</td><td>0%</td></tr><tr><td>Hashtable</td><td>put</td><td>2</td><td>474199</td><td>3.06%</td><td>-119%</td></tr><tr><td>Hashtable</td><td>put</td><td>4</td><td>179550</td><td>6.71%</td><td>-480%</td></tr><tr><td>Hashtable</td><td>put</td><td>8</td><td>183102</td><td>1.63%</td><td>-469%</td></tr><tr><td>Hashtable</td><td>put</td><td>16</td><td>393085</td><td>0.68%</td><td>-165%</td></tr><tr><td>Hashtable</td><td>put</td><td>32</td><td>398277</td><td>1.10%</td><td>-161%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>1</td><td>1336292</td><td>0.21%</td><td>0%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>2</td><td>557880</td><td>3.71%</td><td>-139%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>4</td><td>390736</td><td>1.64%</td><td>-241%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>8</td><td>362653</td><td>1.21%</td><td>-268%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>16</td><td>1492123</td><td>0.20%</td><td>11%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>32</td><td>1564926</td><td>0.10%</td><td>17%</td></tr></tbody></table><br /><h3>Concurrent Map Performance Comparison (server, 8cpu)</h3><table><tbody><tr><td>Map</td><td>Opperation</td><td>Threads</td><td>Average</td><td>%STD</td><td>%DIF (with 1 thread)</td></tr><tr><td>HashMap</td><td>get</td><td>1</td><td>3047533</td><td>0.0%</td><td>0%</td></tr><tr><td>HashMap</td><td>get</td><td>2</td><td>7500603</td><td>0.1%</td><td>146%</td></tr><tr><td>HashMap</td><td>get</td><td>4</td><td>14080828</td><td>0.01%</td><td>362%</td></tr><tr><td>HashMap</td><td>get</td><td>8</td><td>25160569</td><td>0.01%</td><td>769%</td></tr><tr><td>HashMap</td><td>get</td><td>16</td><td>17215757</td><td>1.2%</td><td>464%</td></tr><tr><td>HashMap</td><td>get</td><td>32</td><td>11797330</td><td>7.7%</td><td>287%</td></tr><tr><td>Hashtable</td><td>get</td><td>1</td><td>1165834</td><td>0.06%</td><td>0%</td></tr><tr><td>Hashtable</td><td>get</td><td>2</td><td>434485</td><td>16.9%</td><td>-168%</td></tr><tr><td>Hashtable</td><td>get</td><td>4</td><td>203231</td><td>2.7%</td><td>-473%</td></tr><tr><td>Hashtable</td><td>get</td><td>8</td><td>201290</td><td>2.1%</td><td>-479%</td></tr><tr><td>Hashtable</td><td>get</td><td>16</td><td>358459</td><td>2.3%</td><td>-225%</td></tr><tr><td>Hashtable</td><td>get</td><td>32</td><td>303975</td><td>4.7%</td><td>-283%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>1</td><td>2119602</td><td>0.0%</td><td>0%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>2</td><td>5044317</td><td>0.1%</td><td>137%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>4</td><td>9422460</td><td>0.09%</td><td>344%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>8</td><td>10195480</td><td>0.0%</td><td>381%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>16</td><td>9799273</td><td>1.2%</td><td>362%</td></tr><tr><td>ConcurrentHashMap</td><td>get</td><td>32</td><td>9557975</td><td>0.1%</td><td>350%</td></tr><tr><td>HashMap</td><td>put</td><td>1</td><td>1729801</td><td>0.02%</td><td>0%</td></tr><tr><td>HashMap</td><td>put</td><td>2</td><td>1347323</td><td>0.1%</td><td>-28%</td></tr><tr><td>HashMap</td><td>put</td><td>4</td><td>1267770</td><td>0.02%</td><td>-36%</td></tr><tr><td>HashMap</td><td>put</td><td>8</td><td>1056226</td><td>0.0%</td><td>-63%</td></tr><tr><td>HashMap</td><td>put</td><td>16</td><td>1055462</td><td>0.01%</td><td>-63%</td></tr><tr><td>HashMap</td><td>put</td><td>32</td><td>1055139</td><td>0.01%</td><td>-63%</td></tr><tr><td>Hashtable</td><td>put</td><td>1</td><td>1391458</td><td>0.08%</td><td>0%</td></tr><tr><td>Hashtable</td><td>put</td><td>2</td><td>211793</td><td>13.1%</td><td>-556%</td></tr><tr><td>Hashtable</td><td>put</td><td>4</td><td>191052</td><td>2.8%</td><td>-628%</td></tr><tr><td>Hashtable</td><td>put</td><td>8</td><td>200480</td><td>3.4%</td><td>-594%</td></tr><tr><td>Hashtable</td><td>put</td><td>16</td><td>399748</td><td>2.3%</td><td>-248%</td></tr><tr><td>Hashtable</td><td>put</td><td>32</td><td>400840</td><td>3.2%</td><td>-247%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>1</td><td>1503588</td><td>0.2%</td><td>0%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>2</td><td>441143</td><td>0.8%</td><td>-240%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>4</td><td>380565</td><td>1.1%</td><td>-295%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>8</td><td>354054</td><td>11.8%</td><td>-324%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>16</td><td>1736618</td><td>2.8%</td><td>15%</td></tr><tr><td>ConcurrentHashMap</td><td>put</td><td>32</td><td>1699647</td><td>5.7%</td><td>13%</td></tr></tbody></table><br />Very interesting results. Given the desktop machine has 2 CPUs, I would have expected the 2+ threaded tests to have at most 2x the single threaded test.&nbsp; For the server results with 8 CPUs, I would expect the results to double until 8 threads, then flatten out.<br /><br />Given Hashtable is synchronized, HashMap is not, and ConcurrentHashMap is partially synchronized, I would have expect HashMap to be about 2x, Hashtable to be about 1x, and ConcurrentHashMap to be somewhere in between. The thing I like best about running performance tests, is that you rarely get what you expect, and these results are not what I would have expected.<br /><br />My basic premise holds, in that for get()<b> </b>HashMap had the best concurrency, then ConcurrentHashMap, and then Hashtable.&nbsp; I would have not expected Hashtable to do so bad though.&nbsp; Given it is synchronized, only one thread can perform a get() at one time, so naively one would expect the same results as a single thread.&nbsp; The reality is that it had 5x worse performance than a single thread.&nbsp; The reasons for this include that synchronization has a certain overhead, which modern JVMs optimize out when only a single thread is accessing the object, but with multiple threads this overhead becomes very apparent.&nbsp; Also, contention in general has a huge performance cost, as the threads are busy waiting for the lock to become available.&nbsp; In addition just having multiple threads adds some overhead with context switching and such, so in general the more threads, the worse the peformance unless the threads are doing something useful.<br /><br />The desktop results for get() are worse than I would have expected with 2 CPUs, but perhaps the second CPU was busy with other things, such as the OS and garbage collection, etc. <br /><br />The put()<i> </i>results are much more perplexing.&nbsp; First of all, I know that running concurrent puts on a HashMap does not make much sense, as HashMap does not support concurrent puts.&nbsp; I ran the test anyway, just to see what would happen, and the result is quite surprising.&nbsp; I would expect similar results to the get() test.&nbsp; However, HashMap had much worse results, similar to Hashtable, as if it were having some contention.&nbsp; How could this be given that HashMap has no synchronized methods?&nbsp; My only explanation is that the puts required modifying the same memory locations, so were experiencing contention on the memory access.&nbsp; If you have a better explanation, please comment.<br /><br />I would have also expected the put() concurrency for ConcurrentHashMap to be better.&nbsp; I think the primary reason for this was that my tests looped over the same set of keys in the same order, so each thread was trying to put the same key at the same time.&nbsp; Since ConcurrentHashMap works by having multiple pages and only having to lock a page on put() instead of the entire Map, it still had contention because for the most part the threads were accessing the same keys at the same time.&nbsp; I think this is also why the &gt; 8 threads fared better.&nbsp; With more threads they got more out of synch, and had less page conflicts.&nbsp; This is an important point, ConcurrentHashMap will only perform well when it has a large enough size to have many pages, and when the access to it is random.&nbsp; If all the threads are accessing a single page, it is really no better than a Hashtable.<br /><br />So, what does all this mean?&nbsp; In general, if you have static meta-data that is read-only and requires concurrent access, then use HashMap (it is only not thread safe with puts, gets are fine).&nbsp; If you require concurrent read-write access, then use ConcurrentHashMap.&nbsp; If you just like being old school, then use Hashtable, but beware the hidden costs of concurrency.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com7tag:blogger.com,1999:blog-6877629428951398731.post-28788335556958284212011-01-26T11:48:00.000-08:002013-04-11T10:56:07.702-07:00JVM Performance, Part II - JVM BazaarAs promised, I have results from running some of the tests in other JVMs. The most interesting results were from the Map test and the Method execution test, so I have re-run those on various JVMs and environments.<br /><br />The first result comes from running with Oracle (Sun) JDK 1.6.23 on the same Windows XP machine. Next I ran on Oracle (Sun) JDK 1.5.5, and then Oracle JRockit 1.6. I then ran the tests on another machine, an old 4 cpu 8 core AMD machine running Linux. The results are very surprising, each different machine and environment seemed to have very different behavior.<br /><br /><b>Note</b>: These tests are not in any way attempting to compare Windows vs Linux performance, or Oracle Sun JDK vs Oracle JRockit, completely different hardware, JVMs and environments are used.&nbsp; The goal is to see how different operations compare on the same JVM, and how one JVM and environment can differ with another.&nbsp; Some of the Windows results are faster most likely because the machine is newer, and a faster CPU, some of the Linux results are faster possibly because the machine has 8 CPUs instead of 2, so can process garbage collection on other threads. <br /><br />The test uses a Map of size 100 testing instantiation of the Map, 100 puts and 100 gets. The average result is included. The %DIF is between the Map and the Hashtable results for that same JVM.<br /><br /><h3>Map Operation Performance Comparison</h3><table><tbody><tr><td>Map</td><td>WXP-JDK1.6.7</td><td>%DIF</td><td>WXP-JDK1.6.23</td><td>%DIF</td><td>WXP-JDK1.5.5</td><td>%DIF</td></tr><tr><td>Hashtable</td><td>357229</td><td>0%</td><td>379673</td><td>0%</td><td>219961</td><td>0%</td></tr><tr><td>HashMap</td><td>274587</td><td>-30%</td><td>403501</td><td>+6.2%</td><td>238916</td><td>+8.6%</td></tr><tr><td>LinkedHashMap</td><td>269840</td><td>-32%</td><td>356207</td><td>-6.5%</td><td>228787</td><td>+4.0%</td></tr><tr><td>IdentityHashMap</td><td>110801</td><td>-222%</td><td>121691</td><td>-211%</td><td>60670</td><td>-262%</td></tr><tr><td>ConcurrentHashMap</td><td>119068</td><td>-200%</td><td>183159</td><td>-107%</td><td>109912</td><td>-100%</td></tr><tr><td>HashSet</td><td>281960</td><td>-26%</td><td>407709</td><td>+7.3%</td><td>228936</td><td>4.0%</td></tr></tbody></table><br /><table><tbody><tr><td>Map</td><td>LX-JDK1.6.20</td><td>%DIF</td><td>LX-JDK1.5.5</td><td>%DIF</td><td>LX-JRK1.6.5</td><td>%DIF</td></tr><tr><td>Hashtable</td><td>343250</td><td>0%</td><td>307227</td><td>0%</td><td>474378</td><td>0%</td></tr><tr><td>HashMap</td><td>496258</td><td>+44%</td><td>375805</td><td>+22%</td><td>642267</td><td>+35%</td></tr><tr><td>LinkedHashMap</td><td>476535</td><td>+38%</td><td>417273</td><td>+35%</td><td>595331</td><td>+25%</td></tr><tr><td>IdentityHashMap</td><td>552437</td><td>+60%</td><td>478350</td><td>+55%</td><td>716368</td><td>+51%</td></tr><tr><td>ConcurrentHashMap</td><td>293529</td><td>-16%</td><td>299675</td><td>-2.5%</td><td>331730</td><td>-43%</td></tr><tr><td>HashSet</td><td>451615</td><td>+31%</td><td>381843</td><td>+24%</td><td>633824</td><td>+33%</td></tr></tbody></table><br />The first good thing to notice is that for the same machine, the results get better with newer JVM's.&nbsp; That is very nice, and something that most Java developers are familiar with, each new JVM version having better performance than the previous.<br /><br />The second things to notice is that the Map types have huge variations based on the JVM.&nbsp; HashMap was faster than Hashtable in 1.6.23 and even 1.5.5, but somehow slower in 1.6.7.&nbsp; I found this very perplexing, but no matter how many times, or in which order I ran the tests the results were the same.&nbsp; I also compared the code between the releases, and did not find any code changes that could account for the change in performance.&nbsp; I assume it has to do with how the JVM manages memory and synchronized methods.&nbsp; One would think that the code for Hashtable, HashMap, and IdentityHashMap was all the same, since the functionality is the essentially identical, but in fact they share none of the same code, and use quite different data structures.&nbsp; I assume this accounts for the difference in performance under the different JVMs, so that none of them is inferior, they are just different.<br /><br />Note that these tests are still single threaded tests.&nbsp; So although they are comparing the raw performance difference between Hashtable and HashMap, the far bigger issue is the affect Hashtable's synchronized methods have on concurrency.&nbsp; This is also true for ConcurrentHashMap, altough its raw throughput seems to be worse, it concurrency is far better, and in a mutli-threaded environment it will fair much better.&nbsp; I will hopefully explore this in another blog post. <br /><br /><h3>Method Execution Performance Comparison</h3><table><tbody><tr><td>Execution type</td><td>WXP-JDK1.6.7</td><td>%DIF</td><td>WXP-JDK1.6.23</td><td>%DIF</td><td>WXP-JDK1.5.5</td><td>%DIF</td></tr><tr><td>Normal</td><td>25533130</td><td>0%</td><td>29608720</td><td>0%</td><td>18557941</td><td>0%</td></tr><tr><td>synchronized</td><td>13383707</td><td>-93%</td><td>14373780</td><td>-105%</td><td>1254183</td><td>-1379%</td></tr><tr><td>Block synchronized</td><td>8244087</td><td>-203%</td><td>8812159</td><td>-235%</td><td>1253624</td><td>-1380%</td></tr><tr><td>final</td><td>26873873</td><td>+6.1%</td><td>30812911</td><td>+4.0%</td><td>18853714</td><td>+1.5%</td></tr><tr><td>In-lined</td><td>26816109</td><td>+5.2%</td><td>30815433</td><td>+4.0%</td><td>19734119</td><td>+6.3%</td></tr><tr><td>volatile</td><td>1503727</td><td>-1539%</td><td>3290550</td><td>-799%</td><td>1448561</td><td>-1181%</td></tr><tr><td>Reflection</td><td>159069</td><td>-1564%</td><td>145585</td><td>-20237%</td><td>82713</td><td>-22336%</td></tr></tbody></table><br /><table><tbody><tr><td>Execution type</td><td>LX-JDK1.5.5</td><td>%DIF</td><td>LX-JDK1.6.20</td><td>%DIF</td><td>LX-JRK1.6.5</td><td>%DIF</td></tr><tr><td>Normal</td><td>4666753</td><td>0%</td><td>4840361</td><td>0%</td><td>4257995</td><td>0%</td></tr><tr><td>synchronized</td><td>2250871</td><td>-107%</td><td>4413458</td><td>-9.6%</td><td>3465398</td><td>-22%</td></tr><tr><td>Block synchronized</td><td>2252474</td><td>-107%</td><td>4410721</td><td>-9.7%</td><td>3672440</td><td>-15%</td></tr><tr><td>final</td><td>4666384</td><td>0.0%</td><td>4911130</td><td>+1.4%</td><td>4392700</td><td>+3.1%</td></tr><tr><td>In-lined</td><td>4668538</td><td>0.0%</td><td>4874539</td><td>0.7%</td><td>4042412</td><td>-5.3%</td></tr><tr><td>volatile</td><td>3236491</td><td>-44%</td><td>3231105</td><td>-49%</td><td>3032121</td><td>-40%</td></tr><tr><td>Reflection</td><td>2645911</td><td>-76%</td><td>2568412</td><td>-88%</td><td>132232</td><td>-3120%</td></tr></tbody></table><br />The first good thing to note again is that newer JVM have for the most part better performance.&nbsp; This is one of the great aspects of developing using the Java platform.<br /><br />The second thing to notice is once again the behavior for the various JVMs is very different.&nbsp; They all seem to be consistent in that synchronized methods, volatile and reflection are slower, but the degree of the difference is pretty major.&nbsp; Synchronized methods seem to be much better in JDK 1.6 vs 1.5, but in the latest Linux JVM almost have no overhead at all.&nbsp; Reflection also improved a lot from 1.5 to 1.6, but again the Linux overhead is less than a tenth of the Windows JVM results (except for JRockit).&nbsp; Volatile has similar differences.&nbsp; This could have more to do with the hardware than the OS or JVM, as the Linux machine has 8 CPUs, so may be doing some memory managment using its other CPUs (but this does not explain the JRockit result...).<br /><br /><h3>Summary</h3>So what have we learned?&nbsp; I think the most important thing is that different JVMs and environment can have very different behavior, so it is important to test in the environment that you will go into production in.&nbsp; If you do not have that luxury, then it is important to test in a variety of different environments to ensure you are not making trade offs in one environment that could hurt you in another.<br /><br />I would not get too obsessed with worrying about how your applications performance may differ in different environments.&nbsp; By in large, the main performance optimizations of reducing CPU usages, reducing message sends, reducing memory and garbage, improving concurrency will improve your application's performance not matter what the environment is.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com2tag:blogger.com,1999:blog-6877629428951398731.post-50299137524136567762010-12-17T08:03:00.000-08:002013-04-11T10:56:27.755-07:00What is faster? JVM PerformanceJava performance optimization is part analysis, and part superstition and witch craft.<br /><br />In deciding how to optimize their applications a lot of developers search the web for what people say is faster, or go by what they have heard from their co-workers sister's boyfriend's cousin, or by what seems to be the "public opinion". Sometimes the public opinion is correct, and their application benefits, and sometimes it is not, and they waste their time and effort and make little performance improvement, or make things worse.<br /><br />True performance optimization involves measuring the current performance. Profiling the application and determining the bottlenecks. Investigating and implementing optimizations to avoid the bottlenecks.<br /><br />But how should one code on a day to day basis for optimal performance? Are synchronized methods slow? Are final methods fast? What is faster Vector, ArrayList or LinkedList? What is the optimal way to iterate a List? Is refection slow? I will attempt to answer these questions in this post.<br /><br />In the EclipseLink project we are generally very concerned about performance, as we are a persistence library used by other applications, so like the JDK we are part of the plumbing and need to be as optimized as possible.<br /><br />We have a number of performance test suites in EclipseLink, mainly to measure our persistence performance, but we have one test suite that has nothing to do with persistence. Our Java performance test suite just measures the performance of different operations in Java. These tests help us determine how to best optimize our code.<br /><br />The tests function by running a certain operation for a set amount of time and measuring the number of operations in the time period. Next the next operation is run and measured the same way. This is then repeated 5 times, and the max/min values are rejected, the average of the middle 3 is computed, the standard deviation is computed, and the averages of the two operations are compared. Tests are single threaded.<br /><br />The tests were run on Oracle Sun JDK 1.6.0_07 on 32 bit Windows.<br /><br /><h2>Maps</h2>There are several different Map implementations in Java. This test compares the performance for various sizes of Maps. The test instantiates the Map, does n (size) puts, then n gets and n removes.<br /><h3>Map Operation Performance Comparison</h3><table><tbody><tr><td>Map</td><td>Size</td><td>Average (operations/10 seconds)</td><td>%STD</td><td>%DIF (with Hashtable)</td></tr><tr><td>Hashtable</td><td>10</td><td>3605235</td><td>0.03%</td><td>0%</td></tr><tr><td>HashMap</td><td>10</td><td>2908854</td><td>0.02</td><td>-23%</td></tr><tr><td>LinkedHashMap</td><td>10</td><td>2594492</td><td>0.03%</td><td>-38%</td></tr><tr><td>IdentityHashMap</td><td>10</td><td>1346278</td><td>0.01%</td><td>-167%</td></tr><tr><td>ConcurrentHashMap</td><td>10</td><td>1009259</td><td>0.0%</td><td>-257%</td></tr><tr><td>HashSet</td><td>10</td><td>2927254</td><td>0.02%</td><td>-23%</td></tr><tr><td>Hashtable</td><td>100</td><td>357229</td><td>0.01%</td><td>0%</td></tr><tr><td>HashMap</td><td>100</td><td>274587</td><td>0.03%</td><td>-30%</td></tr><tr><td>LinkedHashMap</td><td>100</td><td>269840</td><td>0.03%</td><td>-32%</td></tr><tr><td>IdentityHashMap</td><td>100</td><td>110801</td><td>0.02%</td><td>-222%</td></tr><tr><td>ConcurrentHashMap</td><td>100</td><td>119068</td><td>0.01%</td><td>-200%</td></tr><tr><td>HashSet</td><td>100</td><td>281960</td><td>0.04%</td><td>-26%</td></tr><tr><td>Hashtable</td><td>1000</td><td>34034</td><td>6.2%</td><td>0%</td></tr><tr><td>HashMap</td><td>1000</td><td>27818</td><td>0.06%</td><td>-22%</td></tr><tr><td>LinkedHashMap</td><td>1000</td><td>25514</td><td>0.03%</td><td>-33%</td></tr><tr><td>IdentityHashMap</td><td>1000</td><td>11650</td><td>0.04%</td><td>-192%</td></tr><tr><td>ConcurrentHashMap</td><td>1000</td><td>12420</td><td>2.9%</td><td>-174%</td></tr><tr><td>HashSet</td><td>1000</td><td>26888</td><td>0.04%</td><td>-26%</td></tr></tbody></table><br />These results are quite interesting, I never would have expected such a big difference in performance between classes doing basically the same well defined thing, that has been around for quite some time. It is surprising that Hashtable performs better than HashMap, when HashMap is suppose to be the replacement for Hashtable, and does not suffer from its limitation of using synchronized methods, which are suppose to be slow.&nbsp; <b>Note</b>: After exploring other JVMs in my next post, it seems that this anomaly only exists in JDK 1.6.7, in the latest JVM, and most other JVM, HashMap seems to be faster than Hashtable.<br /><br />I would expect LinkedHashMap and ConcurrentHashMap to be somewhat slower, as they have additional overhead and perform better in specific use cases, but it is odd that IdentityHashMap is so much slower, given my test object did not define a hashCode(), so was using its identity, so the map was doing the identical thing as HashMap and Hashtable. Also interesting that HashSet has the same performance as HashMap, given it in theory could be a simpler data structure (in reality it is a subclass of HashMap, so performs the same).<br /><br />Note that these are single threaded results. Although Hashtable outperformed HashMap in this test, in a multi-threaded (and multi-CPU) environment, Hashtable would perform much worse because of the method synchronization. Assuming read-only access of coarse, as concurrent access to a HashMap would blow up. ConcurrentHashMap does perform the best in a concurrent read-write environment.<br /><br /><h2>Lists</h2>For Maps, Hashtable turned out to have the best performance. Lets now investigate Lists, will the old Vector implementation have better performance than ArrayList? The list test instantiates a list, then does n (size) adds followed by n gets by index.<br /><br /><h3>List Operation Performance Comparison</h3><table><tbody><tr><td>List</td><td>Size</td><td>Average (operations/10 seconds)</td><td>%STD</td><td>%DIF (with Vector)</td></tr><tr><td>Vector</td><td>10</td><td>11625453</td><td>0.006%</td><td>0%</td></tr><tr><td>ArrayList</td><td>10</td><td>18448453</td><td>0.08%</td><td>+56%</td></tr><tr><td>LinkedList</td><td>10</td><td>12362290</td><td>0.03%</td><td>+6.0%</td></tr><tr><td>Vector</td><td>100</td><td>1241420</td><td>0.2%</td><td>0%</td></tr><tr><td>ArrayList</td><td>100</td><td>1893581</td><td>0.1%</td><td>+52%</td></tr><tr><td>LinkedList</td><td>100</td><td>1012740</td><td>0.01%</td><td>-22%</td></tr><tr><td>Vector</td><td>1000</td><td>132182</td><td>0.2%</td><td>0%</td></tr><tr><td>ArrayList</td><td>1000</td><td>223969</td><td>0.1%</td><td>+69%</td></tr><tr><td>LinkedList</td><td>1000</td><td>12689</td><td>0.02%</td><td>-941%</td></tr></tbody></table><br />So, it seems that ArrayList is much faster than Vector. Given both Vector and Hashtable are synchronized, I assume it is not the synchronized methods, just the implementation. LinkedList surprisingly performs as good as Vector for small sizes, but then performs much worse on larger sizes because of the indexed get. This is expected as LinkedList performs good in specific use cases, such as removing from the head or middle, but this was not tested.<br /><br /><h2>Iteration</h2>There are many way to iterate a List in Java. For a Vector a Enumeration can be used, or an Iterator in any List. A simple for loop with an index can also be used for any List. Java 5 also defines a simplified <code>for</code> syntax for iterating any Collection. This test compares the performance of iteration, the List has already been pre-populated.<br /><br /><h3>Iteration Performance Comparison</h3><table><tbody><tr><td>List</td><td>Average (operations/10 seconds)</td><td>%STD</td><td>%DIF (with Vector)</td></tr><tr><td>for index (Vector)</td><td>6079272</td><td>0.06%</td><td>0%</td></tr><tr><td>for index (ArrayList)</td><td>6048324</td><td>0.03%</td><td>-0.5%</td></tr><tr><td>Enumeration (Vector)</td><td>4736049</td><td>0.02%</td><td>-28%</td></tr><tr><td>Iterator (Vector)</td><td>2840094</td><td>0.05%</td><td>-114%</td></tr><tr><td>Iterator (ArrayList)</td><td>1935122</td><td>0.008%</td><td>-214%</td></tr><tr><td>for (Vector)</td><td>2841567</td><td>0.05%</td><td>-113%</td></tr><tr><td>for (ArrayList)</td><td>1933576</td><td>0.04%</td><td>-214%</td></tr></tbody></table><br />So using a for loop with an index is faster than an Enumerator or Iterator. This is expected, as it avoids the cost of the iteration object instance. It is interesting that a Enumerator is faster than an Iterator, and a Vector Iterator is faster than an ArrayList Iterator. Unfortunately there is no magic in the Java 5 <code>for</code> syntax, it just calls iterator().<br /><br />So the optimal way to iterate a List is:<br /><pre><code><br />int size = list.size();<br />for (int index = 0; index &lt; size; index++) {<br /> Object object = list.get(index);<br /> ...<br />}<br /></code></pre><br />However, the Java 5 syntax is simpler, so I must admit I would use it in any non performance critical code, and maybe some day the JVM will do some magic and <code>for</code> will have better performance.<br /><br /><h2>Method Execution</h2>A method in Java can be defined in several different ways. It can be synchronized, final, called reflectively, or not called at all (in-lined). This next test tries to determine the performance overhead to a method call. The test executes a simple method that just increments an index. The test executes the method 100 times to avoid the test call overhead. For the reflective usage the Method object and arguments array are cached.<br /><br /><h3>Method Execution Performance Comparison</h3><table><tbody><tr><td>Method</td><td>Average (100 operations/10 seconds)</td><td>%STD</td><td>%DIF (with normal)</td></tr><tr><td>Normal</td><td>25533130</td><td>0.7%</td><td>0%</td></tr><tr><td>synchronized</td><td>13383707</td><td>4.3%</td><td>-93%</td></tr><tr><td>Block synchronized</td><td>8244087</td><td>4.7%</td><td>-203%</td></tr><tr><td>final</td><td>26873873</td><td>1.2%</td><td>+6.1%</td></tr><tr><td>In-lined</td><td>26816109</td><td>1.5%</td><td>+5.2%</td></tr><tr><td>volatile</td><td>1503727</td><td>0.1%</td><td>-1539%</td></tr><tr><td>Reflection</td><td>159069</td><td>3.2%</td><td>-15646%</td></tr></tbody></table><br />Interesting results. Synchronized methods are slower, and using a synchronized block inside a method seems to be slower than just making the method synchronized. Final methods seem to be slightly faster, but very minor, and seem to have the same improvement as in-lining the method, so I assume that is what the JVM is doing. <br /><br />Calling a method through reflection is significantly slower. Reflection has improved much since 1.5 added byte-code generation, but if you profile a reflective call you will see several isAssignable() checks before the method is invoked to ensure the object and arguments are of the correct type, it is odd that these cannot be handled through casts in the generated code, or typing errors just trapped.<br /><br /><b>CORRECTION</b><br />Originally volatile was showing an improvement in performance because of a bug in the volatile test. After correcting the bug the test now shows a huge overhead in performance. The usage of volatile on the int field that is being incremented in the test method is causing a 15x performance overhead. This is surprising, and much larger than the synchronized overhead, but still smaller than the reflection overhead. This is especially odd as the field is an int which one would think is atomic anyway. I imaging the affect the volatile has on the JVM memory usage is somehow causing the overhead. In spite of the overhead, I would still use volatile when required, in concurrent pieces of code where the same variable is concurrently modified. I have done multi-threaded tests comparing its concurrency with synchronizing the method, and using volatile is better than using synchronization for concurrency.<br /><br />Note that this difference in cost of execution is on a method that does basically nothing. Generally, performance bottlenecks in applications are in places that do <i>something</i>, not nothing, and it is the <i>something</i> that is the bottleneck, not how the method is called. Even in the reflective call, the cost was less than 1 millionth of a second, so on a method that toke 1 millisecond the overhead would be irrelevant. Also, I assume these results are very JVM specific, other JVMs, older JVMs, and the JVMs of the future may behave much differently.<br /><br /><h3>Reflection</h3>There are two types of reflection, field reflection and method reflection. In JPA this normally corresponds to using FIELD access of PROPERTY access (annotating the fields or the get methods). Which is faster? This test sets a single variable, either directly, by calling a set method, or through field or set method reflection.<br /><br /><h3>Reflection Performance Comparison</h3><table><tbody><tr><td>Type</td><td>Average (operations/10 seconds)</td><td>%STD</td><td>%DIF (with in-lined)</td></tr><tr><td>In-lined</td><td>44105730</td><td>0.7%</td><td>0%</td></tr><tr><td>Set method</td><td>46189638</td><td>1.2%</td><td>+4.7%</td></tr><tr><td>Field Reflection</td><td>23488987</td><td>1.4%</td><td>-87%</td></tr><tr><td>Method Reflection</td><td>10862684</td><td>0.8%</td><td>-306%</td></tr></tbody></table><br />So its appears that in JDK 1.6 field reflection is faster than set method reflection. Part of this difference is that for method reflection an Object array must be created to call the set method, where as field reflection does not require this. Method reflection improved a lot in JDK 1.5, but it still seems slower than field reflection, at least for set methods. Both have an overhead over a compiled method execution or direct variable access.<br /><br />If you look at the results the difference between reflection and normal execution is much less than in the previous test. This is because in the previous test the method was executed 100 times inside the test, so the overhead of calling the test method was reduced, where as this test only called it once, so had the test method call overhead in it. This highlights an important fact, that how you call the method that sets the field has a big impact on the performance. If you use a complex layering of interfaces and generated code to call a set method directly, versus calling it reflectively, the overhead of the layering may be worse than the reflective call.<br /><br />In EclipseLink field reflection is used by default, but it depends if you annotate your fields or get methods. If you annotate your get methods, then method reflection is used. If you use weaving (enabled through either, the EclipseLink agent, static weaving, JEE, or Spring) and use FIELD access, then EclipseLink will weave in a generic get and set method into your classes to avoid reflection. This amounts to a minor optimization in the context of database persistence, and can be disabled using the persistence unit property "eclipselink.weaving.internal"="false".<br /><br /><h3>Summary</h3>So this post has presented a lot of data on how different things perform in Oracle Sun JDK 1.6 on Windows. It would be interesting to see how the same tests perform in different JVMs in different environments. Perhaps I will investigate that next.<br /><br />The source code to the above tests is in the EclipseLink SVN repository,<br /><a href="http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/foundation/eclipselink.core.test/src/org/eclipse/persistence/testing/tests/performance/java/">here</a>James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com24tag:blogger.com,1999:blog-6877629428951398731.post-75479670766731279022010-11-18T11:17:00.000-08:002013-04-11T10:57:12.709-07:00Batch Fetching, part II - Journey to Big DataIn my last post I investigated different ways to optimize the loading of objects and their relationships. I presented some performance results, but from a very small database with small query result sets. In this post I will investigate how those same query optimization strategies scale to larger data sets.<br /><br />The previous post only had a database of 12 rows. So for this run I will increase the database size by 5,000 to 60,000 rows. Still not a huge database, but should be big enough to highlight any performance differences in the results. I will also increase the size of the query result set from 6 employees, to 5,000 employees.<br /><br />The first thing that I noticed in this run was that Oracle has a limit of 1,000 parameters per statement. Since the IN batch fetching binds a large array, and I'm reading 5,000 objects, this limit was exceeded and the run blew up with a database error. The <code>BatchFetchPolicy</code> in EclipseLink accounts for this and defines a <code>size</code> for the max number of ids to include the an IN. The default size limit in EclipseLink was suppose to be 500, but I think I remember increasing it to 100,000 to test something when I was developing the feature, and, well..., I guess never set it back, oops, I will fix this...<br /><br />EclipseLink defines a JPA Query hint <code>"eclipselink.batch.size"</code> that allows the size to be set. So I will set this to 500 for the test. This means that to read in all of the 5,000 objects, the IN batch fetch will need to execute 10 queries per batch fetched relationship. It will be interesting to see how it compares to the other query optimization techniques.<br /><br />The run time was also increased to 10 minutes from 1 minute because reading 5,000 objects obviously takes longer than reading 6. Also, since the last run our lab got a new database machine. The old database was running on Linux on an old server machine, and the new database is running on a new Oracle Sun server machine running Linux on a virtualized environment. The client machine is the same, my old desktop running Oracle Sun JDK 1.6 on Windows XP. Both databases were Oracle 11g.<br /><br /><h3>Big Data Results, run 1, simple (fetch address, phoneNumbers)</h3><table><tbody><tr><td>Query</td><td>Average (queries/10 minutes)</td><td>%STD</td><td>%DIF (of standard)</td></tr><tr><td>standard</td><td>27</td><td>4.5%</td><td>0%</td></tr><tr><td>join fetch</td><td>307</td><td>0.1%</td><td>+1037%</td></tr><tr><td>batch fetch (JOIN)</td><td>310</td><td>0.2%</td><td>+1048%</td></tr><tr><td>batch fetch (EXISTS)</td><td>309</td><td>0.1%</td><td>+1044%</td></tr><tr><td>batch fetch (IN)</td><td>261</td><td>0.1%</td><td>+866%</td></tr></tbody></table><br />The results show that join fetching and batch fetching have basically equivalent performance, and about 10x better performance than the non-optimize query. IN batch fetching does not perform as well as the others with this larger result set. It performs better than I expected, given it has huge IN statement and has to execute 10 queries per relationship. Note that these results differ from the previous post that showed IN batch fetching performing the best for queries with small result sets.<br /><br />The second run uses the complex query which fetches 9 different relationships.<br /><br /><h3>Big Data Results, run 2, complex (fetch address, phoneNumbers, projects, manager, managedEmployees, emailAddresses, responsibilities, jobTitle, degrees)</h3><table><tbody><tr><td>Query</td><td>Average (queries/10 minutes)</td><td>%STD</td><td>%DIF (of standard)</td></tr><tr><td>standard</td><td>6</td><td>0.0%</td><td>0%</td></tr><tr><td>join fetch</td><td>59</td><td>1.5%</td><td>+383%</td></tr><tr><td>batch fetch (JOIN)</td><td>125</td><td>0.3%</td><td>+1983%</td></tr><tr><td>batch fetch (EXISTS)</td><td>124</td><td>0.3%</td><td>+1966%</td></tr><tr><td>batch fetch (IN)</td><td>80</td><td>3.2%</td><td>+1455%</td></tr></tbody></table><br />The results show batch fetching having about 2x the performance of join fetching, and 20x the performance of the non-optimize query. Join fetching still performs 10x faster than the non-optimize case, which is different than the small result set run which gave it worse performance than the non-optimized query. IN batch fetching again did not perform as well as JOIN and EXISTS batch fetching, but still out performed join fetching and was 15x faster than the non-optimized query.<br /><br />Note that these results are not universal. Expect that every database, every machine, every environment, every query, and every object model will give different results. The basics should be the same though, batch fetch should have better performance than non-optimized queries, and better performance than join fetching for objects with complex relationships. IN batch fetching will perform worse for large result sets, but have similar performance for small result sets. Join fetching will perform well for objects with a small number of relationships.<br /><br />To see how the results differ in different environments, I did a few more runs on different databases. The next run is for a local Oracle 10g database installed on my desktop. This database is slower than the new server, but will not require a network trip since it is on the same machine as the Java client.<br /><br /><h3>Big Data Results, run 3, simple (fetch address, phoneNumbers)</h3><table><tbody><tr><td>Query</td><td>Average (queries/10 minutes)</td><td>%STD</td><td>%DIF (of standard)</td></tr><tr><td>standard</td><td>11</td><td>0.0%</td><td>0%</td></tr><tr><td>join fetch</td><td>346</td><td>0.7%</td><td>+2718%</td></tr><tr><td>batch fetch (JOIN)</td><td>310</td><td>0.0%</td><td>+2718%</td></tr><tr><td>batch fetch (EXISTS)</td><td>343</td><td>0.6%</td><td>+3018%</td></tr><tr><td>batch fetch (IN)</td><td>260</td><td>0.1%</td><td>+2263%</td></tr></tbody></table><br /><h3>Big Data Results, run 4, complex (fetch address, phoneNumbers, projects, manager, managedEmployees, emailAddresses, responsibilities, jobTitle, degrees)</h3><table><tbody><tr><td>Query</td><td>Average (queries/10 minutes)</td><td>%STD</td><td>%DIF (of standard)</td></tr><tr><td>standard</td><td>1</td><td>0.0%</td><td>0%</td></tr><tr><td>join fetch</td><td>39</td><td>1.1%</td><td>+3800%</td></tr><tr><td>batch fetch (JOIN)</td><td>106</td><td>0.8%</td><td>+10500%</td></tr><tr><td>batch fetch (EXISTS)</td><td>113</td><td>0.0%</td><td>+11200%</td></tr><tr><td>batch fetch (IN)</td><td>36</td><td>1.2%</td><td>+3500%</td></tr></tbody></table><br />These results show similar results for the previous simple run, but about a 30x improvement over the non-optimize query, which is a bigger difference. The JOIN batch fetch did not seem to perform as well as the EXISTS or join fetch.<br /><br />The complex run only completed a single run in the 10 minutes for the non-optimized query. The JOIN and EXISTS batch fetching performed the best, over 100x faster than the non-optimized query. Join fetching and IN batch fetching did not perform as well, but were both still over 30x faster than the non-optimized query.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com2tag:blogger.com,1999:blog-6877629428951398731.post-20863210935057002792010-08-09T12:03:00.000-07:002013-04-11T10:57:28.009-07:00Batch fetching - optimizing object graph loadingProbably the biggest impedance mismatch between <span style="font-style: italic;">object-oriented</span> object models and <span style="font-style: italic;">relational</span> <span style="font-style: italic;">database </span>data models, is the way that data is accessed.<br /><br />In a relational model, generally a single <span style="font-weight: bold;">big database query</span> is constructed to join all of the desired data for a particular use case or service request. The more <span style="font-style: italic;">complex </span>the data, the more <span style="font-style: italic;">complex </span>the SQL. Optimization is done by avoiding <span style="font-style: italic;">unnecessary </span>joins and avoiding fetching <span style="font-style: italic;">duplicate </span>data.<br /><br />In an object model an object or set of objects are obtained and the desired data is collected by <span style="font-style: italic;">traversing </span>the object's <span style="font-style: italic;">relationships</span>.<br /><br />With object relation mapping (<span style="font-weight: bold;">ORM</span>/<span style="font-weight: bold;">JPA</span>) this typically leads to multiple queries being executed and typically the dreaded <span style="font-weight: bold;">"N queries"</span> problem, or executing a separate query per object in the original result set.<br /><br />Consider an example relational model <span style="font-weight: bold;">EMPLOYEE</span>, <span style="font-weight: bold;">ADDRESS</span>, <span style="font-weight: bold;">PHONE </span>tables. <span style="font-weight: bold;"> EMPLOYEE</span> has a foreign key <span style="font-weight: bold;">ADDR_ID</span> to <span style="font-weight: bold;">ADDRESS ADDRESS_ID</span>, and <span style="font-weight: bold;">PHONE </span>has a foreign key <span style="font-weight: bold;">EMP_ID</span> to <span style="font-weight: bold;">EMPLOYEE EMP_ID</span>.<br /><br />To display employee data including address and phone for all part-time employees you would have to following SQL,<br /><br /><h3>Big database query</h3><span style="font-family:arial;"><code>SELECT E.*, A.*, P.* FROM EMPLOYEE E, ADDRESS A, PHONE P WHERE E.ADDR_ID = A.ADDRESS_ID AND E.EMP_ID = P.OWNER_ID AND E.STATUS = 'Part-time'</code></span><br /><br />You would then need to group and format this data to get all the phone numbers for each employee to display.<br /><br /><table><tbody><tr><td>ID</td><td>Name</td><td>Address</td><td>Phone</td></tr><tr><td>6578</td><td>Bob Jones</td><td>17 Mountainview Dr., Ottawa</td><td>519-456-1111, 613-798-2222</td></tr><tr><td>7890</td><td>Jill Betty</td><td>606 Hurdman Ave., Ottawa</td><td>613-711-4566, 613-223-5678</td></tr><tr>...</tr></tbody></table><br />The corresponding object model defines <span style="font-weight: bold;">Employee</span>, <span style="font-weight: bold;">Address</span>, <span style="font-weight: bold;">Phone </span>classes.<br />In JPA, Employee has a <span style="font-style: italic;">OneToOne </span>to Address, and a <span style="font-style: italic;">OneToMany </span>to Phone, Phone has a <span style="font-style: italic;">ManyToOne </span>to Employee.<br /><br />To display employee data including address and phone for all part-time employees you would have the following JPQL,<br /><br /><h3>Simple JPQL</h3><span style="font-family:arial;"><code>Select e from Employee e where e.status = 'Part-time'</code></span><br /><br />To display this data you would write the Employee data, get and write the Address from the Employee, and get and write each of the Phones. The displayed result is of coarse the same as using SQL, but the SQL generated is quite different.<br /><br /><br /><table><tbody><tr><td>ID</td><td>Name</td><td>Address</td><td>Phone</td></tr><tr><td>6578</td><td>Bob Jones</td><td>17 Mountainview Dr., Ottawa</td><td>519-456-1111, 613-798-2222</td></tr><tr><td>7890</td><td>Jill Betty</td><td>606 Hurdman Ave., Ottawa</td><td>613-711-4566, 613-223-5678</td></tr><tr>...</tr></tbody></table><br /><h3>N+1 queries problem</h3><span style="font-family:arial;"><code>SELECT E.* FROM EMPLOYEE E WHERE E.STATUS = 'Part-time'</code></span><br />... followed by N selects to ADDRESS<br /><span style="font-family:arial;"><code>SELECT A.* FROM ADDRESS A WHERE A.ADDRESS_ID = 123</code></span><br /><span style="font-family:arial;"><code>SELECT A.* FROM ADDRESS A WHERE A.ADDRESS_ID = 456</code></span><br />...<br />... followed by N selects to PHONE<br /><span style="font-family:arial;"><code>SELECT P.* FROM PHONE P WHERE P.OWNER_ID = 789</code></span><span style="font-family: monospace;"><br /></span><span style="font-family:arial;"><code>SELECT P.* FROM PHONE P WHERE P.OWNER_ID = 135</code></span><br />...<br /><br />This will of coarse have very pathetic performance (unless all of the objects were already in the cache). There are a few ways to optimize this in JPA. The most common method is to use<span style="font-style: italic;"> join fetching</span>. A join fetch is where an object, and its related objects are fetched in a single query. This is quite easy to define in JPQL, and is similar to defining a join.<br /><br /><h3>JPQL with join fetch</h3><span style="font-family:arial;"><code>Select e from Employee e join fetch e.address, join fetch e.phones where e.status = 'Part-time'<code></code></code></span><br /><br />This produces the same SQL as in the SQL case,<br /><br /><h3>SQL for JPQL with join fetch</h3><span style="font-family:arial;"><code>SELECT E.*, A.*, P.* FROM EMPLOYEE E, ADDRESS A, PHONE P WHERE E.ADDR_ID = A.ADDRESS_ID AND E.EMP_ID = P.OWNER_ID AND E.STATUS = 'Part-time'</code></span><br /><br />The code to display the Employee results is the same, the objects are just loaded more efficiently.<br /><br />JPA only defines join fetches using JPQL, with EclipseLink you can also use the <span style="font-style: italic;">@JoinFetch</span> annotation to have a relationship always be join fetched. Some JPA providers will always join fetch any <span style="font-style: italic;">EAGER </span>relationship, this may seem like a good idea, but is generally a very bad idea. EAGER defines if the relationship should be loaded, not how it should be accessed from the database. A user may want every relationship loaded, but join fetching every relationship, in particular every <span style="font-style: italic;">ToMany</span> relationships will lead to a huge join (outer joins at that), fetching a huge amount of duplicate data. Also for <span style="font-style: italic;">ManyToOne </span>relationships such as <span style="font-style: italic;">parent</span>, <span style="font-style: italic;">owner</span>, <span style="font-style: italic;">manager</span> where there is a shared reference (that is probably already in the cache), join fetching this duplicate parent for every child will perform much worse than a separate select (or cache hit).<br /><br />JPQL does not allow the aliasing of the join fetch, so if you wish to also query on the relationship, you have to join it twice. This is optimized out to a single join for ToOne relationships, and for ToMany relationships you really need the second join to avoid filtering the object's related objects. Some JPA providers do support using an alias for a join fetch, but the JPA spec does not allow it, and EclipseLink does not support this as of yet (but there is a <a href="https://bugs.eclipse.org/bugs/show_bug.cgi?id=293775">bug </a>logged).<br /><br />Nesting join fetches are not directly supported by JPQL either (there is also a <a href="https://bugs.eclipse.org/bugs/show_bug.cgi?id=321722">bug </a>for this). EclipseLink supports nested join fetches through the Query hints "eclipselink.join-fetch" and <code>"eclipselink.left-join-fetch"</code>.<br /><br /><h3>Nested join fetch query hint</h3><span style="font-family:arial;"><code>query.setHint("eclipselink.join-fetch", "e.projects.milestones");</code></span><br /><br />Join fetching is fine, and the best solution in many use cases, but it is a very relational database centric approach. Another, more creative and object-oriented solution is to use <span style="font-style: italic;">batch fetching</span>. Batch fetching is much harder for the traditional relational mindset to comprehend, but once understood is quite powerful.<br /><br />JPA only defines join fetches, not batch fetches. To enable batch fetching in EclipseLink the Query hint <code>"eclipselink.batch"</code> is used, in our example this would be,<br /><br /><h3>Batch fetch query hint</h3><span style="font-family:arial;"><code>query.setHint("eclipselink.batch", "e.address");</code></span><br /><span style="font-family:arial;"><code>query.setHint("eclipselink.batch", "e.phones");</code></span><br /><br />In a batch fetch the original query is executed normally, the difference is how the related objects are fetched. Once the employees are retrieved and their first address is accessed, <span style="font-weight: bold;">ALL </span>of the addresses for <span style="font-weight: bold;"></span><span style="font-weight: bold;">ONLY </span>the <span style="font-weight: bold;">SELECTED </span>employees are fetched. There are several different forms of batch fetching, for a <span style="font-style: italic;">JOIN </span>batch fetch the SQL will be,<br /><br /><h3>SQL for batch fetch (JOIN)</h3><span style="font-family:arial;"><code>SELECT E.* FROM EMPLOYEE E WHERE E.STATUS = 'Part-time'</code></span><br /><span style="font-family:arial;"><code>SELECT A.* FROM EMPLOYEE E, ADDRESS A WHERE E.ADDR_ID = A.ADDRESS_ID AND</code></span><span style="font-family:arial;"><code>E.STATUS = 'Part-time'</code></span><br /><span style="font-family:arial;"><code>SELECT P.* FROM EMPLOYEE E, PHONE P WHERE E.EMP_ID = P.OWNER_ID AND E.STATUS = 'Part-time'</code></span><br /><br />The first observation is that 3 SQL statements occurred instead of 1 with a join fetch. This may lead one to think that this is less efficient, but it actually is more efficient in most cases. The difference between 1 and 3 selects is pretty minimal, the main issue with the unoptimized case was that N selects were executed, which could be 100s or even 1000s.<br /><br />The main benefit to batch fetching is that only the desired data is selected. In the join fetch case, the EMPLOYEE and ADDRESS data were duplicated in the result for <span style="font-style: italic;">every </span>PHONE. If each employee had 5 phones numbers, 5 times as much data would be selected. This is true for any ToMany relationship and becomes exasperated if you join fetch multiple or nested ToMany relationships. For example if an employee's projects were join fetched, and the project's milestones, for say 5 projects per employee and 10 milestones per project, you get the employee data duplicated 50 times (and project data duplicated 10 times). For a complex object model, this can be a major issue.<br /><br />Join fetching typically needs to use an <span style="font-style: italic;">outer </span>join to handle the case where an employee does not have an address or phone. Outer joins are general much less efficient in the database, and add a row of nulls to the result. With batch fetching if an employee does not have an address or phone, it is simply not in the batch result, so less data is selected. Batch fetching also allows for a <span style="font-style: italic;">distinct </span>to be used for ManyToOne relationships. For example if the employee's manager was batch fetched, then the distinct would ensure that only the unique managers were selected, avoiding the selecting of any duplicate data.<br /><br />The draw backs to <span style="font-style: italic;">JOIN </span>batch fetching is that the original query is executed multiple times, so if it is an expensive query, join fetching could be more efficient. Also if only a single result is selected, then batch fetching does not provide any benefit, where as join fetching can still reduce the number of SQL statements executed.<br /><br />There are a few other forms of batch fetching. EclipseLink 2.1 supports three different batch fetching types, <span style="font-style: italic;">JOIN</span>, <span style="font-style: italic;">EXISTS</span>, <span style="font-style: italic;">IN </span>(defined in the BatchFetchType enum). The batch fetch type is set using the Query hint "eclipselink.batch.type". Batch fetching can also be always enabled for a relationship using the <span style="font-style: italic;">@BatchFetch</span> annotation.<br /><br /><h3>Batch fetch query hints and annotations</h3><code>query.setHint("eclipselink.batch.type", "EXISTS");</code><br /><br /><code>@BatchFetch(type=BatchFetchType.EXISTS)</code><br /><br />The <span style="font-style: italic;">EXISTS </span>option is similar to the <span style="font-style: italic;">JOIN </span>option, but the batch fetch uses an exists and a sub-select instead of a join. The advantage of this is that no distinct is required, which can be an issue with lobs or complex queries.<br /><br /><h3>SQL for batch fetch (EXISTS)</h3><span style="font-family:arial;"><code>SELECT E.* FROM EMPLOYEE E WHERE E.STATUS = 'Part-time'</code></span><br /><span style="font-family:arial;"><code>SELECT A.* FROM ADDRESS A WHERE EXISTS (SELECT E.EMP_ID FROM EMPLOYEE E WHERE E.ADDR_ID = A.ADDRESS_ID AND </code></span><span style="font-family:arial;">E.STATUS = 'Part-time'</span><span style="font-family:arial;">)</span><br /><span style="font-family:arial;"><code>SELECT P.* FROM PHONE P WHERE EXISTS (SELECT E.EMP_ID FROM EMPLOYEE E, WHERE E.EMP_ID = P.OWNER_ID AND E.STATUS = 'Part-time')</code></span><br /><br />The <span style="font-style: italic;">IN </span>option is quite different than the <span style="font-style: italic;">JOIN </span>and <span style="font-style: italic;">EXISTS </span>options. For the IN option the original select is not included, instead an IN clause is used to filter the related objects just for the original object's id. The advantage of the IN option is that the original query does not need to be re-executed, which can be more efficient if the original query is complex. The IN option also supports pagination and usage of cursors, where as the other options do not work effectively as they must select all of the related objects, not just the page. In EclipseLink IN also makes use of the cache, so that if the related object can be retrieved from the cache, it is, and is not included in the IN, so only the minimal required data is selected.<br /><br />The issues with the IN option is that the set of ids in the IN can be very big, and inefficient for the database to process. If the set of ids is too big, it much be split up into multiple queries. Also composite primary keys can be an issue. EclipseLink supports nested INs for composite ids on databases such as Oracle that support it, but some databases do not support this. IN also requires the IN part of SQL to be dynamically generated, and if the IN batch sizes are not always the same, can lead to dynamic SQL on the database.<br /><br /><h3>SQL for batch fetch (IN)</h3><span style="font-family:arial;"><code>SELECT E.* FROM EMPLOYEE E WHERE E.STATUS = 'Part-time'</code></span><br /><span style="font-family:arial;"><code>SELECT A.* FROM ADDRESS A WHERE A.ADDRESS_ID IN (1, 2, 5, ...)</code></span><br /><span style="font-family:arial;"><code>SELECT P.* FROM PHONE P WHERE P.OWNER_ID IN (12, 10, 30, ...)</code></span><br /><br />Batch fetching can also be nested, by using the dot notation in the hint.<br /><br /><h3>Nested batch fetch query hint</h3><span style="font-family:arial;"><code>query.setHint("eclipselink.batch", "e.projects.milestones");</code></span><br /><br />Something that batch fetching allows that join fetching does not is the optimal loading of a tree. If you set the <span style="font-style: italic;">@BatchFetch</span> annotation on a <span style="font-style: italic;">children </span>relationship in a tree structure, then a single SQL statement will be used for each level.<br /><br />So, what does all of this mean? Well, every environment and use case is different, so there is no perfect solution to use in all cases. Different types of query optimization will work better in different situations. The following results are provided as an example of the potential performance improvements from following these approaches.<br /><br />The results were obtained running in a single thread in JSE accessing an Oracle database over a local network on low end hardware. Each test was run for 60 seconds, and number of operations recorded. Each test run was run 5 times. The high/low results were rejected and the middle 3 results average, the % standard deviation between the runs in included. The numbers themselves are not important, only the % difference between the results.<br /><br />The source code for this example and comparison run can be found <a href="http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.batch-fetch/">here</a>.<br /><br />The example performs a simple query for Employees using an unoptimized query, join fetching and each type of batch fetching. After the query each employee's address and phone numbers are accessed. The JPQL NamedQuery queries a small result set of 6 employees by salary from a tiny database of 12 employees.<br /><br /><h3>Simple run (fetch address, phoneNumbers)</h3><table><tbody><tr><td>Query</td><td>Average (queries/minute)</td><td>%STD</td><td>%DIF (of standard)</td></tr><tr><td>standard</td><td>5897</td><td>0.5%</td><td>0</td></tr><tr><td>join fetch</td><td>14024</td><td>1.1%</td><td>+137%</td></tr><tr><td>batch fetch (JOIN)</td><td>11190</td><td>4.5%</td><td>+89%</td></tr><tr><td>batch fetch (EXISTS)</td><td>13764</td><td>0.4%</td><td>+133%</td></tr><tr><td>batch fetch (IN)</td><td>14341</td><td>0.6%</td><td>+143%</td></tr></tbody></table><br /><br />From the first run's results it seems that join fetching and batch fetching were similar, and about 1.9 to 2.4 times faster than the non optimized query. IN batch fetching seemed to perform the best, and JOIN batch fetching the worst for this small data set.<br /><br />The first run was kind of simple though. It only fetched two relationships. What happens when more relationships are fetched? This next run fetches all 9 of the employee relationships, including OneToOnes, OneToManys, ManyToOnes, ManyToManys, and ElementCollections.<br /><br /><h3>Complex run (fetch address, phoneNumbers, projects, manager, managedEmployees, emailAddresses, responsibilities, jobTitle, degrees)</h3><table><tbody><tr><td>Query</td><td>Average (queries/minute)</td><td>%STD</td><td>%DIF (of standard)</td></tr><tr><td>standard</td><td>1438</td><td>0.7%</td><td>0%</td></tr><tr><td>join fetch</td><td>1121</td><td>0.4%</td><td>-22%</td></tr><tr><td>batch fetch (JOIN)</td><td>3395</td><td>3.8%</td><td>+136%</td></tr><tr><td>batch fetch (EXISTS)</td><td>3768</td><td>2.6%</td><td>+162%</td></tr><tr><td>batch fetch (IN)</td><td>3893</td><td>0.5%</td><td>+170%</td></tr></tbody></table><br /><br />The second run results show that as more relationships are fetched, join fetching starts to have major issues. Join fetching actually had worse performance that the non optimized query (-22%). This is because the join becomes very big, and a lot of data must be processed. Batch fetching on the other hand did even better than the simple run (2.3 to 2.7x faster). IN batch fetching still performed the best, and JOIN batch fetching the worse.<br /><br />The amount of data is very small however. What happens when the size of the database and the query are scaled up? I will investigate that next.James Sutherlandhttps://plus.google.com/102096746046640528569noreply@blogger.com11