I understand your concern, but readability is more important than performance in most cases.
Optimize last.
Up to you, but optimising out a scalar function will be non trivial, and personally anything I know of as being a likely performance bottleneck I avoid from Day One. The reality is that stuff like that rarely gets fixed until it becomes a problem, and then its instantly critical from the users' perspective and that interferes with our development schedule and delivery promises that we have made for other stuff .. so we prefer right-first-time.
Two ways to convert Oracle to SQL:
- Looking as close to the original Oracle syntax as possible
- Doing it the MS-SQL way.
Obviously #2 is harder if you have no idea what that should be. I've done #1 in circumstances where I didn't know much about the target platform. In every instance I've regretted it ...
A scalar function is not a bottleneck. Run a performance test between a CASE and a function.
In any case, pulling that function out into an inline statement is very simple if it needed to be optimized.
Readability comes first.
Fixing performance at the outset is not at odds with that.
Here's the original Oracle statement with T-SQL functions instead:
SELECT dbo.FN_Decode(dbo.FN_Greatest(COUNT(*), 2), COUNT(*), 'Y', '')
here's the code for T_SQL:
SELECT CASE WHEN COUNT(*) >= 2 THEN 'Y' ELSE '' END
I can't see how the functions assist readability here. Even if you ditch the Oracle DECODE() function you still have
SELECT CASE WHEN dbo.FN_Greatest(COUNT(*), 2) = COUNT(*) THEN 'Y' ELSE '' END
which I think is less readable - and performs worse because of the scalar function
There is also the risk that the developer uses a Scalar Function with parameters that are inappropriate. For example, if the GREATEST() function used by the dev, in error, had String parameters instead of INT ones, the results would not be as expected. So for GREATEST() multiple functions will be needed, for different parameter types; SQL has no operator-overloading, so up to the developer to choose the name of the correct function. Here's the sort of mess that accidental use of the String-parameters Function would make of a numeric comparison:
DROP FUNCTION dbo.TEMP_FN_GreatestString
GO
CREATE FUNCTION dbo.TEMP_FN_GreatestString
(
@Value1 varchar(10)
, @Value2 varchar(10)
)
RETURNS varchar(10)
AS
BEGIN
RETURN CASE WHEN @Value1 >= @Value2 THEN @Value1 ELSE @Value2 END
END
SELECT [ABC_XYZ] = dbo.TEMP_FN_GreatestString('ABC', 'XYZ')
SELECT [11_100_Numb] = dbo.TEMP_FN_GreatestString(11, 100) -- Integer parameters, implicitly converted by SQL
SELECT [Silently_truncated] = dbo.TEMP_FN_GreatestString('11', '12345678901234567890')
ABC_XYZ
----------
XYZ -- Correct
11_100_Numb
-----------
11 -- Incorrect for numeric comparison
Silently_truncated
------------------
1234567890
Waste of my time, but here you are:
SELECT EMPLID,
(
SELECT ... test statement here ...
FROM dbo.TEMP_DM_EMPL_CURR_VW AS h2
WHERE h2.supervisor_id = ECV.EMPLID
and h2.empl_status in('A','L','S','P')
and h2.empl_type = 'H'
) AS Supervise_2_hourly
INTO #TEMP
FROM dbo.TEMP_ECV AS ECV
I created TEMP_DM_EMPL_CURR_VW with 63,157 rows of random data in line with the query and selected 10% of the supervisor_id at random to populate TEMP_ECV with 6,315 rows
Here are the three tests:
SELECT CASE WHEN COUNT(*) >= 2 THEN 'Y' ELSE '' END
(6315 row(s) affected)
Table 'TEMP_ECV'. Scan count 1, logical reads 24
Table 'TEMP_DM_EMPL_CURR_VW'. Scan count 1, logical reads 314
CPU time = 47 ms, elapsed time = 34 ms.
SELECT CASE WHEN dbo.TEMP_FN_Greatest(COUNT(*), 2) = COUNT(*) THEN 'Y' ELSE '' END
(6315 row(s) affected)
Table Table 'TEMP_ECV'. Scan count 1, logical reads 24
Table 'TEMP_DM_EMPL_CURR_VW'. Scan count 1, logical reads 314
CPU time = 63 ms, elapsed time = 73 ms.
SELECT dbo.TEMP_FN_Decode(dbo.TEMP_FN_Greatest(COUNT(*), 2), COUNT(*), 'Y', '')
(6315 row(s) affected)
Table Table 'TEMP_ECV'. Scan count 1, logical reads 24
Table 'TEMP_DM_EMPL_CURR_VW'. Scan count 1, logical reads 314
CPU time = 109 ms, elapsed time = 98 ms.
If the Functions had been used in WHERE clause, rather than SELECT, where a suitable index was available then the use of a FUNCTION would have a much more dramatic impact on performance because it would prevent the query from begin SARGable
select column_id * 2
from sys.all_columns
select dbo.Test(column_id)
from sys.all_columns
alter FUNCTION dbo.Test(@parm int)
RETURNS int AS
BEGIN
-- declare @var int
-- set @var = 2
return @parm * 2
END
SELECT performance ranged between 56ms and 110ms.
dbo.Test function performance ranged between 60ms and 120ms.
May well be the retrieval time for the (lots of) rows. I tested using SELECT ... INTO #TEMP ... FROM to get a run-time excluding "display time".
I get reasonably consistently figures (using your function):
(29230 row(s) affected)
Table 'syscolpars'. Scan count 1, logical reads 402
Table 'syscolpars'. Scan count 1, logical reads 685
CPU time = 78 ms, elapsed time = 75 ms.
(29230 row(s) affected)
Table 'syscolpars'. Scan count 1, logical reads 402
Table 'syscolpars'. Scan count 1, logical reads 685
CPU time = 203 ms, elapsed time = 247 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 18 ms.
Table '#t__________________________________________________________________________________________________________________000000000008'. Scan count 0, logical reads 9978, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 467, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 58 ms.
(9962 rows affected)
Table 'syscolpars'. Scan count 1, logical reads 467, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 89 ms.
(9962 rows affected)
Function is 58ms.
SELECT is 89ms.
Change the order of your test.