Optimization of the choice of the type TSQL with the function

Question

I have this simple SQL as the source in the SSIS task:

Select * from budgetview 

source:

 CREATE VIEW [dbo].[BudgetView] AS SELECT DISTINCT Country, SDCO AS Company, SDAN8 AS Customer, SDLITM AS PrintableItemNumber, dbo.fn_DateFromJulian(SDIVD) AS Date, SDPQOR/100.0 AS Quantity, SDAEXP/100.0 AS Value, SDITWT/10000.0 AS Weight FROM dbo.F553460 

There are no recommendations for indexes; every thing seems optimized.

Source fn_DateFromJulian :

 CREATE FUNCTION [dbo].[fn_DateFromJulian] ( @JulianDate numeric(6,0) ) RETURNS date AS BEGIN declare @resultdate date=dateadd(year,@JulianDate/1000,'1900-01-01') set @resultdate=dateadd(day,@JulianDate%1000 -1,@resultdate) return @resultdate END 

The problem is that I wait about 20 minutes to get the lines going to SSIS ....

I wait there 20 minutes before it starts.

Are there any suggestions to find the culprit?

Answer 1

I assume that the time spent on the submission is spent by calculating the date value in Julian format. Not seeing the actual plan of the request, this seems to be the correct assumption based on the articles below.

Rewrite the original function as a table function below (I just combined your code, there is a chance of improvement)

 CREATE FUNCTION dbo.fn_DateFromJulianTVF ( @JulianDate numeric(6,0) ) RETURNS TABLE AS RETURN ( SELECT dateadd(day,@JulianDate%1000 -1,dateadd(year,@JulianDate/1000,CAST('1900-01-01' AS date))) AS JDEDate ) 

Use will be

 CREATE VIEW [dbo].[BudgetView] AS SELECT DISTINCT Country, SDCO AS Company, SDAN8 AS Customer, SDLITM AS PrintableItemNumber, J.JDEDate AS [Date], SDPQOR/100.0 AS Quantity, SDAEXP/100.0 AS Value, SDITWT/10000.0 AS Weight FROM dbo.F553460 AS T CROSS APPLY dbo.fn_DateFromJulianTVF(T.SDIVD) AS J 

Scalar function, smells like code reuse, performs like a disposable diaper

• https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html
• http://blogs.lobsterpot.com.au/2011/11/08/when-is-a-sql-function-not-a-function/

Answer 2

Just check, but do I understand correctly that for each unique value of T.SDIVD will be only one unique value of the result of the function? In other words, no two different T.SDIVD return the same value from a function?

In this case, what happens here (IMHO) is that you first look at the whole table, for each record, calculate the value of f (SDIVD), and then send the entire result set through aggregation (DISTINCT).

Since functions are far from optimal in MSSQL, I would suggest limiting their use, bypassing the chain of events and doing so:

 CREATE VIEW [dbo].[BudgetView] AS SELECT /* DISTINCT */ Country, Company, Customer, PrintableItemNumber, dbo.fn_DateFromJulian(SDIVD) AS Date, Quantity, Value, Weight FROM ( SELECT DISTINCT Country, SDCO AS Company, SDAN8 AS Customer, SDLITM AS PrintableItemNumber, SDIVD, SDPQOR/100.0 AS Quantity, SDAEXP/100.0 AS Value, SDITWT/10000.0 AS Weight FROM dbo.F553460 ) dist_F553460 ) 

If you had a lot of duplicate entries, this should improve performance, if you had few of them, it will not matter much if there are any. If you know that you do not have doubles, you must first get rid of DISTINCT , as this causes a delay!

In any case, regarding the function, you can add the following trick:

 CREATE FUNCTION [dbo].[fn_DateFromJulian] ( @JulianDate numeric(6,0) ) RETURNS date WITH SCHEMABINDING AS BEGIN declare @resultdate date=dateadd(year,@JulianDate/1000,'1900-01-01') set @resultdate=dateadd(day,@JulianDate%1000 -1,@resultdate) return @resultdate END 

WITH SCHEMABINDING causes some internal optimizations that will make it run a little faster, YMMV. There are limitations, but it will work well here.

Edit: deleted the "external" DISTINCT, since it (probably from my first guess) is not needed.