Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Solve Problems Using Recursive CTE Expand / Collapse
Author
Message
Posted Thursday, December 6, 2012 11:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:03 PM
Points: 1,192, Visits: 3,236
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.


Post #1393675
Posted Thursday, December 6, 2012 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 4, 2015 1:21 PM
Points: 6, Visits: 59

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


That is what I said in previous post. This perticular task is to find when someone are qualified at his first time. So, recursion will search at beginning and stop searching after reaching the goal.
Post #1393700
Posted Thursday, December 6, 2012 1:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:09 PM
Points: 37,843, Visits: 34,714
bj_shenglong (12/6/2012)

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


That is what I said in previous post. This perticular task is to find when someone are qualified at his first time. So, recursion will search at beginning and stop searching after reaching the goal.


I've haven't looked at the queries in any great detail but I'm thinking that add TOP 1 would easily solve such a problem.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1393720
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse