I'm aware that SQL Server has issues with running user-defined functions over large rowsets. But my problem is a little bit different.
I have a test code like:
SELECT TESTNAME = TESTFUNC()
INTO #TEMPTABLE
FROM SOMETABLE
TESTFUNC has no code in it. Just returns 1 for testing purposes.
When I run this, it takes >= 24 seconds for 1.5 million rows.
If I put the same code inside a stored procedure and execute it with the same user and everything, it takes ~ 6 seconds.
The query time stats in the execution plan for the plans linked below are
vs
CREATE PROCEDURE TESTPROC AS
BEGIN
SELECT TESTNAME = TESTFUNC()
INTO #TEMPTABLE
FROM SOMETABLE
END
Estimated and actual execution plans are the same for both but "Compute Scalar" step takes a lot longer when the statement is called directly.
What makes the difference?
Edit: Actual execution plans
Edit 2: Function definition
CREATE FUNCTION TESTFUNC
(
)
RETURNS BIGINT
AS
BEGIN
RETURN 1
END
Plans: