bj_shenglong
I have one question for this test.
It seems the test data starts at 1999-12-01, but queries use '2012-01-01' to start. I am not sure what the end date is.
I was wondering if it makes any difference after we make some change on start date in these 2 queries, so that start date = the earlies test date, then sorting may go through all period for query 1. Of course, recursion will have more joins for query 2.
I put the date into the query to make the Island query similar to the last query you posted. That query will only report the first consecutive month for each name, however the Island query will report all consecutive months in the period for a name. For example
A 2000-01-01 2
A 2000-02-01 3
A 2000-03-01 4 <-- Returned by Recursive
A 2000-04-01 3 <-- Returned By Island
A 2000-05-01 1
A 2000-06-01 2
A 2000-07-01 1
A 2000-08-01 3
A 2000-09-01 3
A 2000-10-01 3
A 2000-11-01 4 <-- Returned By Island
A 2000-12-01 2
What I found interesting was even though I was only adding data prior to those dates the recursive query kept getting more expensive.