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 «««12345»»»

Calculating interest query Expand / Collapse
Author
Message
Posted Saturday, February 27, 2010 5:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 11,194, Visits: 11,108
Chris,

Turns out all that is needed to get rid of the index spool is to define the clustered index as UNIQUE. Given that information, a better plan is produced (without the extra index spool), and the recursive CTE now starts to return results after four seconds on my laptop. Well done!

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #873971
Posted Saturday, February 27, 2010 8:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:50 AM
Points: 1,074, Visits: 6,379
Paul White (2/27/2010)
Chris,

Turns out all that is needed to get rid of the index spool is to define the clustered index as UNIQUE. Given that information, a better plan is produced (without the extra index spool), and the recursive CTE now starts to return results after four seconds on my laptop. Well done!

Paul


Heh nice work Paul!
I figured it would be sufficient to bring the time down to a few seconds - it only took half an hour with Jeff's modified sample generator - but this is the icing on the cake. I'll try this on the pagination query at work. Thanks!



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #873995
Posted Saturday, February 27, 2010 9:08 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 36,789, Visits: 31,247
Paul White (2/27/2010)
This is a method called 'set-based iteration' by MVP Hugo Kornelis, which was first brought to my attention here in a discussion of one of Jeff's fine articles.


Heh... that's why I listed cursors as the "third fastest". I even helped Hugo with a tweak on his fine method.

I have shamelessly re-used Jeff's fine test script, up to the point marked 'Solution to the problem starts here'. The following code runs from that point, and again draws heavily on Jeff's work. I hope he won't mind.


I never mind but this one was all provided by the OP. He did a really fine job of providing test tables and data especially for a newbie to the forum.


--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 #874005
Posted Saturday, February 27, 2010 9:13 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 36,789, Visits: 31,247
Paul White (2/27/2010)
Chris,

Turns out all that is needed to get rid of the index spool is to define the clustered index as UNIQUE. Given that information, a better plan is produced (without the extra index spool), and the recursive CTE now starts to return results after four seconds on my laptop. Well done!

Paul


Was that for the 100k rows, Paul?


--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 #874007
Posted Saturday, February 27, 2010 9:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 11:57 AM
Points: 71, Visits: 172
I'm overwhelmed with all the help. When I first posted this problem I figured it might be quite some time if ever that someone came up with a solution. Thank you all so much. I will be running these queries and puzzling out how they work on monday. I will let you all know how it goes. Once again thank you so much.
Post #874010
Posted Saturday, February 27, 2010 10:06 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 36,789, Visits: 31,247
nathan 7372 (2/27/2010)
I'm overwhelmed with all the help. When I first posted this problem I figured it might be quite some time if ever that someone came up with a solution. Thank you all so much. I will be running these queries and puzzling out how they work on monday. I will let you all know how it goes. Once again thank you so much.


You only have yourself to thank. Thanks for taking the time to read the "etiquette" article I led you to... I just knew a bunch of great people would jump in on this one if they had a little data to work with. Well done on the data you posted, Nathan.


--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 #874018
Posted Saturday, February 27, 2010 6:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 11,194, Visits: 11,108
Jeff Moden (2/27/2010)
Paul White (2/27/2010)
Chris,

Turns out all that is needed to get rid of the index spool is to define the clustered index as UNIQUE. Given that information, a better plan is produced (without the extra index spool), and the recursive CTE now starts to return results after four seconds on my laptop. Well done!

Paul


Was that for the 100k rows, Paul?

Sure was, yes.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #874093
Posted Saturday, February 27, 2010 6:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 11,194, Visits: 11,108
Jeff Moden (2/27/2010)
nathan 7372 (2/27/2010)
I'm overwhelmed with all the help. When I first posted this problem I figured it might be quite some time if ever that someone came up with a solution. Thank you all so much. I will be running these queries and puzzling out how they work on monday. I will let you all know how it goes. Once again thank you so much.


You only have yourself to thank. Thanks for taking the time to read the "etiquette" article I led you to... I just knew a bunch of great people would jump in on this one if they had a little data to work with. Well done on the data you posted, Nathan.

+1




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #874094
Posted Monday, March 1, 2010 1:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 11:57 AM
Points: 71, Visits: 172
I just wanted to let everyone know how things went. I have to say I learned a lot about the different ways to solve this problem. All three queries ran exceptionally fast. I had to modify Chris' query a tad since it was off by one month on the interest rates used. Since the interest rates are so similiar it only had a minor effect on the outcome.

On an interesting note just by using the different datatypes between Jeff and Paul we get a difference of $374 out of a total around $4.5M. Just goes to show you the power of decimal places and rounding.

Thank you all for tackling this problem. I really feel I learned a lot and the accounting department is ecstatic to finally have the numbers so we can move forward. Thank you again.
Post #874685
Posted Monday, March 1, 2010 4:09 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 36,789, Visits: 31,247
Thanks for the feedback. I am a bit confused, though... I thought Paul and I both used DECIMAL(28,17).

--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 #874778
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse