mis-appying a cross apply table value function?

Question

mis-appying a cross apply table value function?

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

Dave Ballantyne (4/9/2010)
Hmmm
Removing the Ip calculation and just setting it to zero , brings the run time crashing down to 600 - 700 ms..

The in-line version ends up doing rather more calculation than the scalar function.

Specifically, the calculation performed by the iTVF is:

[Expr1013] = Scalar Operator(CASE WHEN ((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)-CONVERT(decimal(20,16),floor((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)),0))*(29.53)<(1.845660) THEN 'New Moon' ELSE CASE WHEN ((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)-CONVERT(decimal(20,16),floor((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)),0))*(29.53)<(5.536990) THEN 'Waxing crescent' ELSE CASE WHEN ((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)-CONVERT(decimal(20,16),floor((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)),0))*(29.53)<(9.228310) THEN 'First quarter' ELSE CASE WHEN ((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)-CONVERT(decimal(20,16),floor((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)),0))*(29.53)<(12.919630) THEN 'Waxing near full moon' ELSE CASE WHEN ((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)-CONVERT(decimal(20,16),floor((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)),0))*(29.53)<(16.610960) THEN 'Full Moon ' ELSE CASE WHEN ((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)-CONVERT(decimal(20,16),floor((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)),0))*(29.53)<(20.302280) THEN 'Waning near full moon' ELSE CASE WHEN ((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)-CONVERT(decimal(20,16),floor((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)),0))*(29.53)<(23.993610) THEN 'Last quarter' ELSE CASE WHEN ((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)-CONVERT(decimal(20,16),floor((CASE WHEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))>(2299160.) THEN (((floor((365.25)*CONVERT_IMPLICIT(numeric(10,0),([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))+(4712),0))+floor((30.6)*CONVERT_IMPLICIT(numeric(10,0),CASE WHEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))>=(12) THEN ([tempdb].[dbo].[TallyCalendar].[Month]+(9))-(12) ELSE [tempdb].[dbo].[TallyCalendar].[Month]+(9) END,0)+(0.5)))+CONVERT_IMPLICIT(numeric(10,0),[tempdb].[dbo].[TallyCalendar].[Day],0))+(59.))-(floor(CONVERT_IMPLICIT(numeric(10,0),floor(([tempdb].[dbo].[TallyCalendar].[Year]-floor(((12)-[tempdb].[dbo].[TallyCalendar].[Month])/(10)))/(100)+(49)),0)*(0.75))-(38.)) ELSE NULL END-(2451550.1))/(29.530588853)),0))*(29.53)<(27.684930) THEN 'Waning crescent' ELSE 'New Moon' END END END END END END END END)

The expression is 19,384 characters in length.

Repeating the same calculation over and over again, rather than saving it in a variable in the scalar function, makes all the difference.

It might be possible to rewrite the iTVF to code around this, but I won't be attempting it.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Apologies for the size of this.....

Hmm , although i hear what you are saying comparing the Inline to the Scalar

Consider this

Drop FUNCTION dbo.pr_LunarPhaseITVF

go

CREATE FUNCTION dbo.pr_LunarPhaseITVF(@Year integer,@Month integer,@Day integer)

returns table

as

return(

--initialize our vars

with cteInitVars

as

(

SELECT year = @YEAR,

month = @MONTH,

day = @DAY

),

cteJulian

as

(

Select year,month,day,

yy = year - floor( ( 12 - month ) / 10 ),

mm = CASE

WHEN (month + 9) >= 12

THEN (month + 9) - 12

ELSE month + 9

END

from cteInitVars

)

,

cteCalc

as

(

Select year,month,day,

yy,mm,

k1 = floor( 365.25 * ( yy + 4712 ) ),

k2 = floor( 30.6 * mm + 0.5 ),

k3 = floor( floor( ( yy / 100 ) + 49 ) * 0.75 ) - 38

from cteJulian

)

,

cteCalc2

as

(

Select pi=0,year,month,day,

yy,mm,

k1,k2 ,k3,

jd = k1 + k2 + day + 59 -- % for dates in Julian calendar

from cteCalc

),

cteCalc3

as

(

Select pi,year,month,day,

yy,mm,

k1,k2 ,k3,

jd = case when jd > 2299160 then jd - k3 end --% for Gregorian calendar

from cteCalc2

),

cteCalc35

as

(

Select jdt = cast(( jd - 2451550.1 ) / 29.530588853 as decimal(20,16)),

pi,year,month,day,

yy,mm,

k1,k2 ,k3,

jd

from cteCalc3

),

cteCalc4

as

(

Select pi,year,month,day,

yy,mm,

k1,k2 ,k3,

jd,

--ip = (jdt) - (jdt)

ip = (jdt) - floor(jdt)

from cteCalc35

),

cteCalc5

as

(

Select pi,year,month,day,

yy,mm,

k1,k2 ,k3,

jd,

ip, ag = ip * 29.53

from cteCalc4

),

ctePhases

as

(

Select 1.84566 as Ltag ,'New Moon' as Phase union all

select 5.53699 ,'Waxing crescent' union all

select 9.22831 , 'First quarter' union all

select 12.91963 ,'Waxing near full moon'union all

select 16.61096,'Full Moon 'union all

select 20.30228 , 'Waning near full moon'union all

select 23.99361 ,'Last quarter'union all

select 27.68493 , 'Waning crescent'union all

select 50.0000 , 'New Moon'

)

Select top 1 Phase

from cteCalc5

join ctePhases

on cteCalc5.ag < ctePhases.ltag

order by cteCalc5.ag

/* SELECT Phase =cast( CASE

WHEN ag < 1.84566

THEN 'New Moon'

WHEN ag < 5.53699

THEN 'Waxing crescent'

WHEN ag < 9.22831

THEN 'First quarter'

WHEN ag < 12.91963

THEN 'Waxing near full moon'

WHEN ag < 16.61096

THEN 'Full Moon '

WHEN ag < 20.30228

THEN 'Waning near full moon'

WHEN ag < 23.99361

THEN 'Last quarter'

WHEN ag < 27.68493

THEN 'Waning crescent'

ELSE 'New Moon'

END as varchar(30))

from cteCalc5*/

)

go

Take a look at the execution plans for two runs , 1 with the floor calc in IP and one without.

The only difference i can see is the floor itself, its about (1280 chars in)

Comparing the .sqlplan with a file diff shows that the plan hasent changed other than the addition of the <floor> operators

SNIPPED OUT --- Sent the formatting mad!

I wouldnt of thought that it would lead to such a performance difference, after all there are quite a few other floor's occuring.

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 3

Dave,

With the FLOOR, the Constant Scan produces 182,511 rows, which are then Filtered to 36,500 rows.

Without the FLOOR, the Constant Scan produces 36,500 rows.

This is too complex to analyse in detail, but my guess is that the optimizer is able to take a shortcut when it sees (jdt) - (jdt), rather than (jdt) - floor(jdt). The expression is probably not NULLable, and can probably be simplified to zero.

There are also some rules about what logic can be pushed up from a Filter, but I don't recall enough of the details offhand, sorry.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lowell SSC Guru Points: 323518 More actions · Answer 4

Once again, you guys helped me be a better programmer; I learned a lot with this thread.

converting that MTVF to an ITVF made me understand the concept better, and this thread made me understand why the ITVF is better;

my first iteration didn't give correct results, but my second try at it looks similar to Dave Ballentine's; I just did a couple of steps together, where Dave left them spread out for readability and to follow the original conversion.

IF OBJECT_ID('sp_LunarPhaseITVF') IS NOT NULL

DROP FUNCTION dbo.sp_LunarPhaseITVF

GO

CREATE FUNCTION dbo.sp_LunarPhaseITVF(@TheDate datetime)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURN(

--initialize our vars

with cteDtSplit as (

SELECT

YEAR(@TheDate) AS TheYear,

MONTH(@TheDate) AS TheMonth,

DAY(@TheDate) AS TheDay

),

cteDates as (

SELECT

TheYear - floor( ( 12 - TheMonth ) / 10 ) AS yy,

CASE

WHEN (TheMonth + 9) >= 12

THEN (TheMonth + 9) - 12

ELSE TheMonth + 9

END AS mm,

TheDay as dd

FROM cteDtSplit

),

ctePre as (

SELECT

dd,

floor( 365.25 * ( yy + 4712 ) ) AS k1,

floor( 30.6 * mm + 0.5 ) AS k2,

floor( floor( ( yy / 100 ) + 49 ) * 0.75 ) - 38 AS k3

FROM cteDates

),

cteAdj as (

SELECT

CASE

WHEN (k1 + k2 + dd + 59) > 2299160

THEN (k1 + k2 + dd + 59) - k3

ELSE k1 + k2 + dd + 59

END AS jd -- % for dates in Julian calendar

FROM ctePre

),

cteFin as (

SELECT

((( jd - 2451550.1 ) / 29.530588853) - cast((floor( ( jd - 2451550.1 ) / 29.530588853 )) as decimal(20,16))) * 29.53 AS AG

FROM cteAdj

)

SELECT CASE

WHEN ag < 1.84566 THEN 'New Moon'

WHEN ag < 5.53699 THEN 'Waxing crescent'

WHEN ag < 9.22831 THEN 'First quarter'

WHEN ag < 12.91963 THEN 'Waxing near full moon' -- the web calls this "Gibbous ", WTH is that?

WHEN ag < 16.61096 THEN 'Full Moon '

WHEN ag < 20.30228 THEN 'Waning near full moon' -- the web calls this "Gibbous ", WTH is that?

WHEN ag < 23.99361 THEN 'Last quarter'

WHEN ag < 27.68493 THEN 'Waning crescent'

ELSE 'New Moon'

END As Phase

FROM cteFin

) --END Return

GO

select * from TallyCalendar

cross apply dbo.sp_LunarPhaseITVF(TheDate)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Paul White SSC Guru Points: 150467 More actions · Answer 5

For anyone wanting to try the SQLCLR scalar function implementation for comparison:

-- Assembly

CREATE ASSEMBLY ScalarFunctions

AUTHORIZATION dbo

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300A63CBF4B0000000000000000E00002210B010800001000000020000000000000BE2A00000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000000000000300408500001000001000000000100000100000000000001000000000000000000000006C2A00004F000000004000006003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000C40A0000002000000010000000100000000000000000000000000000200000602E7273726300000060030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A02A00000000000048000000020005002822000044080000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000013300300C2010000010000110F00280F00000A0A1200281000000A0B1200281100000A0C1200281200000A0D071F0C08591F0A5B59130408130C110C1759450200000002000000020000002B08081F095813052B050819591305230000000000D4764011042068120000586C5A691306239A99999999993E4011056C5A23000000000000E03F5869130711041F645B1F31586C23000000000000E83F5A691F26591308110611075809581F3B5813091109201815230031071109110859130911096C23CDCCCC0C2FB442415923D541CBABD4873D405B11096C23CDCCCC0C2FB442415923D541CBABD4873D405B281300000A59130A110A2348E17A14AE873D405A130B110B23D690B8C7D287FD3F340B7201000070731400000A2A110B23BD1DE1B4E0251640340B7213000070731400000A2A110B2314B35E0CE5742240340B7233000070731400000A2A110B2349D74CBED9D62940340B724F000070731400000A2A110B23062AE3DF679C3040340B727B000070731400000A2A110B23213CDA38624D3440340B728F000070731400000A2A110B2383FA96395DFE3740340B72BB000070731400000A2A110B239E0C8E9257AF3B40340B72D5000070731400000A2A7201000070731400000A2A1E02281500000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000008020000237E000074020000B002000023537472696E67730000000024050000F8000000235553001C0600001000000023475549440000002C0600001802000023426C6F620000000000000002000001471502000900000000FA0133001600000100000013000000020000000200000002000000150000000C00000001000000010000000200000000000A00010000000000060043003C000A006B0056000A00750056000600A40092000600DC00BD000600F000920006000901920006002401920006003F0192000600580192000600710192000600900192000600AD0192000600E401C40106000402C4010A004D0232020A0062023202060074023C000600A2023C000000000001000000000001000100010010001E000000050001000100502000000000960081000A0001001E220000000086188C001100030000000000000000000100740221008C00150029008C001A0031008C00150039008C00150041008C00150049008C00150051008C00150059008C00150061008C00150069008C00150071008C001F0079008C00110081008C00110089008C00110019007D029E0191008702A30191009002A30191009A02A3019900A702A70111008C00150009008C00110020006B002400240073006C012E003300DB012E002300BD012E002B00C3012E001300BD012E003B00BD012E004300BD012E004B00BD012E005300DB012E005B00F0012E006300F901AC010480000001000000A80EFB1900000000000022020000020000000000000000000000010033000000000002000000000000000000000001004A00000000000000003C4D6F64756C653E005363616C617246756E6374696F6E732E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670053716C4461746554696D65004C756E61725068617365002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005363616C617246756E6374696F6E73004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053716C4661636574417474726962757465004461746554696D65006765745F56616C7565006765745F59656172006765745F4D6F6E7468006765745F446179004D61746800466C6F6F720000000000114E006500770020006D006F006F006E00001F57006100780069006E00670020006300720065007300630065006E007400001B4600690072007300740020007100750061007200740065007200002B57006100780069006E00670020006E006500610072002000660075006C006C0020006D006F006F006E000013460075006C006C0020006D006F006F006E00002B570061006E0069006E00670020006E006500610072002000660075006C006C0020006D006F006F006E0000194C0061007300740020007100750061007200740065007200001F570061006E0069006E00670020006300720065007300630065006E00740000000000B6334D77AECA1D4F8FEB6B7F922D84720008B77A5C561934E0890600011109110D03200001042001010E042001010204200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E6973746963015402094973507265636973650154557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737300000000310100030054020D497346697865644C656E6774680054020A49734E756C6C61626C65005408074D617853697A651E0000000420001149032000080400010D0D10070D11490808080808080808080D0D0805010000000017010012436F7079726967687420C2A920203230313000001401000F5363616C617246756E6374696F6E7300000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301942A00000000000000000000AE2A0000002000000000000000000000000000000000000000000000A02A0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000040300000000000000000000040334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100FB19A80E00000100FB19A80E3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00464020000010053007400720069006E006700460069006C00650049006E0066006F000000400200000100300030003000300030003400620030000000480010000100460069006C0065004400650073006300720069007000740069006F006E00000000005300630061006C0061007200460075006E006300740069006F006E00730000003C000E000100460069006C006500560065007200730069006F006E000000000031002E0030002E0033003700350032002E003600360035003100000048001400010049006E007400650072006E0061006C004E0061006D00650000005300630061006C0061007200460075006E006300740069006F006E0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100300000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005300630061006C0061007200460075006E006300740069006F006E0073002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D006500000000005300630061006C0061007200460075006E006300740069006F006E007300000040000E000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033003700350032002E003600360035003100000044000E00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033003700350032002E00360036003500310000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000C03A000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE;

GO

-- Function

CREATE FUNCTION dbo.LunarPhase

(

@DateTime DATETIME

)

RETURNS NVARCHAR(30)

WITH EXECUTE AS CALLER

AS EXTERNAL NAME ScalarFunctions.UserDefinedFunctions.LunarPhase;

-- Test

SELECT TC.TheDate,

LunarPhase = dbo.LunarPhase(TheDate)

FROM TallyCalendar TC

ORDER BY

TC.TheDate;

GO

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Paul White NZ (4/8/2010)
Lynn Pettis (4/8/2010)
I rewrote your MLTVF as an in-line TVF last night and was testing it against your scalar function and it was still slower, approx 3 seconds vs approx 1 second.
Wow - what did you do??? :laugh:
Can't wait to see the code.

Looks very similar to what others have posted. Sorry guys, got home from officiating a High School Girls Varsity soccer game last night and crashed. totally forgot about this.

I will, for the sake of completeness on my part, post the code tonight. I am curious if I can speed it up any and I would like to see a comparision between the iTVF and the SQLCLR.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Paul White NZ (4/9/2010)
Dave Ballantyne (4/9/2010)
Hmmm
Removing the Ip calculation and just setting it to zero , brings the run time crashing down to 600 - 700 ms..
The in-line version ends up doing rather more calculation than the scalar function.
Specifically, the calculation performed by the iTVF is:
[Expr1013] = Scalar Operator(CASE WHEN ((CASE WHEN ... snipped ...
The expression is 19,384 characters in length.
Repeating the same calculation over and over again, rather than saving it in a variable in the scalar function, makes all the difference.
It might be possible to rewrite the iTVF to code around this, but I won't be attempting it.
Paul

This is actually a problem that I've seen with iTVFs before, the constant-per-row calculations just don't seem to get factored out by the optimizer...

I've got some Split() iTVFs with similar, but much less pronounced, behavior.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 8

RBarryYoung (4/9/2010)
This is actually a problem that I've seen with iTVFs before, the constant-per-row calculations just don't seem to get factored out by the optimizer...

This is why I tend to use iTVFs only for relatively simple tasks. This is not a criticism, I believe it to be the correct usage - right tool for the job, and all that.

We also have to be realistic in our expectations of the QO. It is not an optimizing compiler - its goals are very different. I am personally amazed that it copes with this sort of thing at all.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

Lynn Pettis (4/9/2010)
I will, for the sake of completeness on my part, post the code tonight. I am curious if I can speed it up any and I would like to see a comparision between the iTVF and the SQLCLR.

Don't worry about the code Lynn (speaking just for me) - there's enough here already! I think we have determined why it is relatively slow, anyway.

I posted the SQLCLR code so you can try it for yourself 😉

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi