Thursday, March 16, 2006
SQL Complexity Problem
Harry Pierson, in his DevHawk blog, points to a post by Brian Beckman on building indexes to do LINQ queries. Brian says
This has been the way Visual FoxPro has functioned for many years. When you submit a query, the query optimizer first checks if there is an index that can be used. If not, it tries to determine if building one on the fly will optimize the query. If it is determined that an index would help, the index is created, the query run, then the index deleted. Is it any surprise that members of the VFP team have been instrumental on getting LINQ developed?
"In the terminology of relational databases, a “join” is, semantically, like a nested loop over a pair of lists (or tables) of records, saving only those where some certain fields match. Unless we do something smart, this could be very expensive. Imagine searching a database of a million DNA profiles for the closest match to a sample that has 10,000 DNA features (I have no idea whether those are real numbers: I just made them up, but they sound ballpark to me). A dumb join would search all 1 million profiles for each of the 10,000 features, resulting in 10 billion match tests, almost all of which will fail – by design, of course. That’s going to hurt.
The “something smart” is to build an index and search through that. Your database doesn’t have to be large at all for this to pay off. In fact, even with just a few records, it’s cheaper to build an index, use it, and throw it away than it is to do a nested loop."
This has been the way Visual FoxPro has functioned for many years. When you submit a query, the query optimizer first checks if there is an index that can be used. If not, it tries to determine if building one on the fly will optimize the query. If it is determined that an index would help, the index is created, the query run, then the index deleted. Is it any surprise that members of the VFP team have been instrumental on getting LINQ developed?
// posted by Craig Berntson @ 3:07:00 PM 
Subscribe to Posts [Atom]