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 «««89101112

SQL Function : Find ‘X’ Business Days in the Future Expand / Collapse
Author
Message
Posted Monday, July 26, 2010 11:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 28, 2010 11:58 AM
Points: 4, Visits: 51
Great way to do date advancement: http://codemiller.blogspot.com/2010/07/sql-server-2005-date-advancement.html
Post #959189
Posted Tuesday, July 27, 2010 7:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 4,013, Visits: 6,087
michaelberry67 (4/29/2009)
Well it is slow as most in-line functions are, but that is the nature of the beast I guess. It has worked well with larger output sets, but I have not benchmarked it. If you wanna...:) be my guest.

I do love this website though. Great information continually!
Mike



By the way, inline table valued functions are NOT slow. Multi-statement table valued functions are, but I've used inline functions many many times to replace user-defined functions and speed up queries.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #959375
Posted Tuesday, July 27, 2010 7:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 8:45 AM
Points: 531, Visits: 2,078
mmiller-511987 (7/26/2010)
Great way to do date advancement: http://codemiller.blogspot.com/2010/07/sql-server-2005-date-advancement.html

I don't think using recursive CTE is a good approach for doing this kind of date arithmentic.
Take a look at Jeff Modem's example earlier in this post.
I ran his against yours and his is 65 times faster!
Post #959387
Posted Tuesday, July 27, 2010 7:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 4,013, Visits: 6,087
Recursive CTEs are rarely the fastest performing way of getting something done.

Again, a calendar table solution will be both fast and flexible.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #959390
Posted Wednesday, July 28, 2010 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 28, 2010 11:58 AM
Points: 4, Visits: 51
Wasn't even thinking of a table driven way of date evaluation. I like it though, must of took some time to write that.

FYI: By adjusting the CTE to a week by week recursion rather than each day I was able to get performance 3x faster. A 1000 day recursion resulted on average a 0.009375 second penalty per invocation (on a moderately speced machine.) I will update that blog when I have time to get the negative advancement complete. Still I'm sure the table driven method is still 20x faster; not to mention all the extra benefits incurred from his toolset.

Thanks for replying!
Post #960274
Posted Wednesday, July 28, 2010 12:34 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 4,013, Visits: 6,087
One of the wonderful things about table-driven solutions is how easy the coding becomes once the table is in place. However, I'm bookmarking this to go back and take a look at your recursive solution.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #960297
« Prev Topic | Next Topic »

Add to briefcase «««89101112

Permissions Expand / Collapse