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 Thursday, February 05, 2009 2:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 1:58 PM
Points: 438, Visits: 902
:peter
You 'da man!
scalar value results:
500 rows: CPU time = 844 ms, elapsed time = 879 ms.
15,000 rows: CPU time = 25531 ms, elapsed time = 25603 ms.

tabular results:
500 rows: CPU time = 2703 ms, elapsed time = 2699 ms.
15,000 rows: CPU time = 6656 ms, elapsed time = 6654 ms.

i added another set size for the tabular results:
500 rows: CPU time = 2734 ms, elapsed time = 2738 ms.
5000 rows: CPU time = 3969 ms, elapsed time = 3967 ms.
15000 rows: CPU time = 6656 ms, elapsed time = 6663 ms.

So, the in-line tabular method takes a sec to get out the blocks, but once it gets going, it flies!

There is an error in your calculation somewhere (it miscalculates the date) but I'm sure it's a simple oversight. Thanks for sharing your talent!
Post #651269
Posted Thursday, February 05, 2009 2:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:10 AM
Points: 1,135, Visits: 317
I really like that solution too Jim - really simple and yet so neat :)
Post #651275
Posted Thursday, February 05, 2009 2:17 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:10 AM
Points: 1,135, Visits: 317
scalar value results:
500 rows: CPU time = 844 ms, elapsed time = 879 ms.
15,000 rows: CPU time = 25531 ms, elapsed time = 25603 ms.

tabular results:
500 rows: CPU time = 2703 ms, elapsed time = 2699 ms.
15,000 rows: CPU time = 6656 ms, elapsed time = 6654 ms.


So my question here would be do you assume worst case that it's going to be hit really hard and go for the tabular method and take a hit if only doing a few rows or vice versa?
Post #651279
Posted Thursday, February 05, 2009 2:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
So my question here would be do you assume worst case that it's going to be hit really hard and go for the tabular method and take a hit if only doing a few rows or vice versa?


It won't be the same from system to system. I'm considering doing two versions, one for long batch jobs like reports and one for interactive. Before choosing, you really need to study your data and the nature of your workload. Reports shouldn't drag on for minutes that could be generated in seconds, but displays shouldn't take two seconds of DB time if they can be delivered in a fraction of a second. On the other hand, if the calculation is done for a small number of rows only a few times a day, then a couple of seconds isn't that big a deal. You just have to think about the ultimate consequences of your choice, whatever it may be.

That said, do we have any performance numbers from precalculated table approaches?



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #651296
Posted Friday, February 06, 2009 6:31 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Bob Hovious (2/5/2009)
So my question here would be do you assume worst case that it's going to be hit really hard and go for the tabular method and take a hit if only doing a few rows or vice versa?


It won't be the same from system to system. I'm considering doing two versions, one for long batch jobs like reports and one for interactive. Before choosing, you really need to study your data and the nature of your workload. Reports shouldn't drag on for minutes that could be generated in seconds, but displays shouldn't take two seconds of DB time if they can be delivered in a fraction of a second. On the other hand, if the calculation is done for a small number of rows only a few times a day, then a couple of seconds isn't that big a deal. You just have to think about the ultimate consequences of your choice, whatever it may be.

That said, do we have any performance numbers from precalculated table approaches?



Yes. 1-million rows in about 17 seconds. Less than 1 millisecond for a single-row calculation. Posted earlier in the thread. In other words, over 100 times faster than the second-place version.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #651566
Posted Friday, February 06, 2009 1:09 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
I was getting 1 million rows in 8.5-9.5 seconds on my laptop without precalculation, but 4 ms for a single row. So faster in batch, but slower in low volumes. Sounds like pre-calc might be the most versatile solution.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #651945
Posted Wednesday, February 18, 2009 5:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 06, 2014 10:18 AM
Points: 8, Visits: 92
Here's a quick 2005 example of a single select using a tally table.

stage 1 uses a tally table to generate a set of dates.
stage 2 adds a date enumeration after removing the non-working days (Sat/Sun)
stage 3 gets the matching 'business days' using the enumeration.

If you need to include a holiday table then include an outer join/is null or 'not in' at stage 2.

select max(dts)
from ( select dts, ROW_NUMBER ( ) over (order by dts) as nRow
from ( select dateadd(dd,datediff(dd,0,GETDATE ()),0)+n as dts
from dbo.Tally
where n < @requiredDays) x
where DATENAME(dw, dts) not in ('Saturday','Sunday') ) y
where nRow < @requiredDays;

G.
Post #659256
Posted Tuesday, April 28, 2009 10:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 11, 2009 5:15 AM
Points: 7, Visits: 18
I'm trying to use this function but need my begin date to be today's date,
trying [dbo].fn_AddBizDays(GETDATE, 1) but not working does anyone know how to get this operational from todays date?
Post #706069
Posted Tuesday, April 28, 2009 10:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:45 AM
Points: 3,949, Visits: 5,944
GETDATE() is a function. You have to include the parens after it.

[dbo].fn_AddBizDays(GETDATE(), 1)

Be advised that GETDATE includes the current time as well as date, unless fn_AddBizDays strips it off.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #706077
Posted Wednesday, April 29, 2009 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 11, 2009 5:15 AM
Points: 7, Visits: 18
thanks bob..
Post #706735
« Prev Topic | Next Topic »

Add to briefcase «««89101112»»»

Permissions Expand / Collapse