SQL Function in column running slow

Question

I have a computed column(function) that is causing one of my tables to be extremely slow (its output is a column in my table. I thought it might be some logical statements in my function. I commented those out and just returned a string called 'test'. This still caused the table to be slow. I believe the SELECT statement is slowing down the function. When I comment out the select statement, everything is cherry. I think I am not using functions in the correct manner.

FUNCTION [dbo].[Pend_Type](@Suspense_ID int, @Loan_ID nvarchar(10),@Suspense_Date datetime, @Investor nvarchar(10))
RETURNS nvarchar(20)
AS
BEGIN
DECLARE @Closing_Date Datetime, @Paid_Date Datetime
DECLARE @pendtype nvarchar(20)

--This is the issue!!!!
SELECT @Closing_Date = Date_Closing, @Paid_Date = Date_Paid from TABLE where Loan_ID = @Loan_ID

SET @pendtype = 'test'

--commented out logic

RETURN @pendtype
END

UPDATE:

I have another computed column that does something similar and is a column in the same table. This one runs fast. Anyone see a difference in why this would be?

Declare @yOrn AS nvarchar(1)
IF((Select count(suspense_ID) From TABLE where suspense_ID = @suspenseID) = 0)
SET @yOrn = 'N'
ELSE
SET @yOrn = 'Y'
RETURN @yOrn

Answer 1

You have isolated the performance problem in the select statement:

SELECT TOP 1 @Closing_Date = Date_Closing, @Paid_Date = Date_Paid
from TABLE
where Loan_ID = @Loan_ID;

To make this run faster, create a composite index on table(Load_id, Date_Closing, Date_Paid).

By the way, you are using top with no order by. When multiple rows match, you can get any one of them back. Normally, top is used with order by.

EDIT:

You can create the index by issuing the following command:

create index idx_table_load_closing_paid on table(Load_id, Date_Closing, Date_Paid);

Answer 2

Scalar functions are often executed like cursors, one row at a time; that is why they are slow and are to be avoided. I would not use the function as written but would write a set-based version instead. incidentally a select top 1 without an order by column will not always give you the same record and is generally a poor practice. In this case I would think you would want the latest date for instance or the earliest one.

In this particular case I think you would be better off not using a function but using a derived table join.