After releasing Flex 4.4, we started noticing some fairly serious performance slow downs when loading lists of invoices, quotes, or pull sheets. It doesn’t happen on every system, but it happens often enough that we decided to profile it and figure out what was happening.
We analyzed our Quote/Pull Sheet search engine using our favorite performance testing tool: YourKit. We learned quickly that the problem related to resolving the property expressions configured as search options under a given project element. Further analysis revealed that the problem expressions were confined to a small number of property expressions that have to be lazy loaded at runtime.
As the more experienced Flex users know, the columns that appear when viewing a list of quotes, pull sheets, etc are dynamic and can be configured with property expressions to be just about anything you want. Consult the screen shot below to jar your memory:
We use an expression processing engine we developed to resolve these expressions to object properties using Java reflection. Our search engine pulls back objects that match the search and then we loop over those results and resolve the property expressions as part of serializing the results for display on the cilent.
This is all well and good and usually works just fine — except for when one of those expressions involves evaluating a lazy loaded property of the project element — like prep time on pull sheets, pricing and payment information on quotes — since calculating all that information involves looping over the line items. This is fast on a per quote/pull sheet basis, but when you have to do it hundreds or thousands of times it can become a performance problem. It’s something commonly called an N+1 select issue. In most cases when you run into this problem in a Spring/Hibernate architecture like ours, you solve it by eager fetching the associated objects and telling Hibernate to use a join fetch strategy in hopes that N+1 database queries will be reduced to 1 query.
Because the property expressions are so dynamic and we couldn’t possibly think of every scenario in which they’d be used, we don’t want to eager fetch every possible relationship because that 1 query would get long, expensive and would be wasteful because, in most cases, the query would pull back information that isn’t needed.
We looked at lazy loading the element data client side. Since the client supports paging, perhaps it would have made sense for the original query to pull back a DTO stub with just the project element ids, then use the client GUI’s paging to request full DTO’s for only those elements that are visible. This would have turned N+1 selects into PAGE SIZE + 1 selects. The problem with doing this is that our new GUI supports filtering and sorting. This doesn’t work unless all the information is available to the client right away. You can’t sort by totals unless you have all totals.
So we came up with a technique for hacking our way around the standard property expression method in situations where it would create an N+1 problem. The first step was to develop a way to identify which expressions would be problematic. We added an annotation called @PropertyExpressionHint that can be added to the getter for any property that might support an alternative evaluation method. We added a hint type enum that can flag whether the hint is an HQL or SQL hint. The annotation also has parameters for specifying the domain object and query property for HQL hints and table name and column for SQL hints.
When a search executes, the system now runs the original search, then checks the property expressions and determines which ones have @PropertyExpressionHint annotations. The HQL/SQL value, domain class, and table name values are then used to prepare query batches. The query returns the values that would otherwise have been loaded using expensive lazy loading and these values are matched up with objects returned in the original query. This technique cut the slow response time by at least an order of magnitude in our tests.
The enhancement is currently in QA and will be in version 4.4.14 of Flex.