McEII - Cost-based Optimization, Caching, Concurrency, and the Big Mac

He's on a roll.  Another guest post from Peter Spielvogel. - T.M.

The other day, over lunch, I was trying to explain to a technology executive in another industry some of Ipedo’s advantages in EII. Inspired by our fast food surroundings, I came up with several analogies that might help explain (1) cost-based query optimization vs. rule-based optimization, (2) policy-based caching and automatic cache invalidation, and (3) concurrency. These are three key areas that affect EII performance, and ones in which Ipedo excels among EII vendors.

Cost-based Optimization vs. Rule-based Optimization

Consider a McDonalds at lunch time. Based on past traffic patterns, they have a pretty good idea about how many people show up at what times of the day. Based on that data, they prepare burgers to meet the anticipated demand. This is rule-based optimization.

Now, imagine that a large tour bus arrives in the parking lot. A savvy manager will immediately add more food to the grill to meet this spike in demand. The manager is using real-time information based on current conditions to determine how much food to prepare. The existing rules are complemented by actual current traffic statistics.

This is analogous to cost-based query optimization.

In EII, the EII server uses information from the back-end data sources to determine join order rather than relying on a set of static rules that almost certainly will not apply to every scenario. Ipedo’s cost based optimizer considers cardinality (row count), indexing presence and selectivity, among other factors.

Policy-based Caching and Automatic Cache Invalidation

Caching is about balancing freshness with speed. Keeping with our fast food analogy, consider how restaurants maintain a small inventory of food to immediately fulfill orders. They have policies of how much to keep, based on demand patterns. If you look closely at the wrapper the next time you purchase a sandwich, you will probably see a small circle of numbers from 1 to 12 (corresponding to the numbers on a clock), usually with a mark next to one of them. This represents the cache invalidation policy. For example, if the mark is at the 2, the servers know that they must dispose of the food at 10 minutes after the hour. This is the invalidation policy, which ensures the customer receives (relatively) fresh product. To override the cache, order something special “no ketchup” and you will get a fresher burger, but you will need to wait a bit longer for it.

When a user sets up caching in Ipedo, they set a policy that the cache is only valid for a specific number of minutes or hours and then the cache clears itself. Ipedo users can refill the cache either on a pre-defined schedule or just the next time someone requests the data. They can determine how fresh they want their information and balance that with the performance improvement gained by pulling from the cache rather than going against the backend source. For the freshest data, bypass the cache and wait a little longer for your special order.

Concurrency

Finally, consider how many customers a restaurant can serve at one time. In a McDonalds, this often is a function of the number of open cash registers. More open registers should lead to higher concurrency. This leads to less waiting, resulting in higher customer satisfaction and more people served per hour. In EII software, managing concurrency is much more complex, but it depends on good design to minimize serialization, eliminate bottlenecks, and make optimal use of multithreaded processing. The outcome of high concurrency is less waiting for data, also resulting in higher satisfaction, less frustration, and better decisions. In addition, an EII server with high concurrency can allow an organization to use few servers to deliver information to everyone who needs it.

Other blogs have more technical coverage on cost-based query optimization and using caching to speed performance.  Or if you would like to discuss these in person, perhaps we can do it over lunch.

Editor's Note: While a lunch at McDonald's may serve useful for purposes of analogy, the author, fearing a 'Super Size Me'  fate, would be happy with a wide variety of lunch stops.