The Development Build 27 represents a new optimizer for SQL queries.  Specifically it is a way of building 2 bit per byte image masks in RAM to represent the result sets of SQL operations. 

MikeO has spent a lot of time on this and he very pleased with the results so far.  The basic premise is to use a few bits of database to represent possible result sets, then you can perform Boolean operations on bits to reduce the set of data that must be loaded from disk by the use of the indexes.  This is one of the ways to gain some serious performance in certain types of operations.  But that is always the trick, isn’t it? 

Where to optimize

Knowing WHICH operations it will work on, and which it will not. Much our optimization strategies are like any other product.  You run common usage scenarios, and profile them.  Then you look for bottlenecks, and ways to improve the codepath.  Database optimizations are a little different though because of the number of operations that can be performed on the result.   All SQL operations can be thought of as Boolean Algebra.  You are normally performing result set operations across domains, or collections of sets.  Any of you who remember your set theory from college will remember that the basic operations seem really simple, and then your professor would throw up a problem that would take two days and twelve sheets of paper to solve.  Same basic principles, but many times you can optimize some operations away entirely.  Think of the problem where you are comparing two square roots:

If( sqrt(x) < sqrt(y) )

Well, that will only ever happen if x < y, so you can skip the sqrt operation entirely and get the same logical result.  This is pretty easy to determine with pure mathematical values.  It is much more difficult with result sets.  Many times what you intuitively think will work does not, and non obvious things give the biggest gains.  Optimization of any application is more of an art than a science.  Yes, you have to test empirical data to validate that your optimizations are beneficial, but knowing where to apply the optimization is many times a “gut” feeling. I will repeat what my grandmother used to tell me; “If it were easy, everyone would do it”. Very true.

I want to thank MikeO (mo_vistadb on the boards) for his very hard work on this recent round of optimizations.  He has put in some extraordinary long hours over the past month testing theories and writing a ton of code.  I feel this is a very good direction for optimization and I look forward to more!