Functions in joining Large tables causing execution slow

Transact-SQL
1

Hi,

I have two tables A and B. Both tables have more than 2 lacs records.

Lets say f1 is the field in B, and f1 is varchar field. For each value in f1, we are searching the corresponding record id in A. We have used a scalar function say GetcorrespondingID(B.f1). In Join query we are using condition as GetcorrespondingID(B.f1) = A.RecordID.

After using this function query becomes dam slow. Please advise is there any alternate way?

Thanks,

2

Hard to say without knowing quite a bit more. In general, scalar functions slow things down. If you post both the query using the function and the function definition, it would help. There's probably another way

3

Hi gbritton,

Please follow the sample query related to the post and advice on any alternative can be implemented.

Declare @tblchangeFrom table (EMPID char(8), changedfrom varchar(1000), [BackUp 1] varchar(200))

Insert @tblchangeFrom
Select distinct me.EMPID, ChangedFrom, COALESCE(me.OutlookPreferredName,me.NAME_COMBINED,'N/A') AS [BackUp 1]
from @tblAudit ad join Employee me
on
dbo.Employee(Ad.empfName) = me.EMPID

Thanks,

4

Please post the function definition.

5

Yes.

Please have a look.

FUNCTION [dbo].Employee]
(
@Name varchar(100)
)
RETURNS VARCHAR(8)
AS
BEGIN
Declare @ReturnValue varchar(8)
SELECT @ReturnValue = EMPID FROM MDREmployee WHERE ltrim(rtrim(name_last))+', '+ltrim(rtrim(name_first)) = @Name OR OutlookPreferredName = @Name OR name_combined = @Name
Return @ReturnValue
END

Thanks,

6

Try making the function into an inline function like shown below

CREATE FUNCTION [dbo].[Employee] ( @Name VARCHAR(100) )
RETURNS TABLE
AS 
		RETURN
			SELECT TOP (1) EMPID
			FROM    MDREmployee
			WHERE   LTRIM(RTRIM(name_last)) + ', ' + LTRIM(RTRIM(name_first)) = @Name
					OR OutlookPreferredName = @Name
					OR name_combined = @Name
	
GO

Then, instead of joining like what you are doing

dbo.Employee(B.f1) = A.RecordID.

join like shown below

(SELECT EMPID FROM dbo.Employee(B.f1))  = A.RecordID
7

Thanks for try-out. But its still the same.

8

If you query without using the function, is it faster? If you haven't tried that, what I am thinking is something like this:

SELECT
	-- your other columns ,
	x.EMPID
FROM
	YourOtherTablesAndJoins
	OUTER APPLY
	(
		SELECT TOP (1) EMPID
		FROM MDREmployee x
		WHERE LTRIM(RTRIM(x.name_last)) + ', ' + LTRIM(RTRIM(x.name_first)) = b.f1
						OR x.OutlookPreferredName = b.f1
						OR x.name_combined = b.f1
	) AS x
9

Is column f1 indexed? If not, make it so, also reverse the where so it reads:

WHERE f1 = ...

to make it SARGable

10

You really need to restructure the lookup table to have a single name column that contains all the possible lookup values. That is, create another table, which can be maintained by a trigger, that inserts the values of name_combined, OutlookPreferredName, and ltrim(rtrim(name_last))+', '+ltrim(rtrim(name_first)) into a single column. Cluster the lookup table on that column. Then the lookup becomes a simple keyed search:

SELECT TOP (1) EMPID
FROM dbo.MDREmployeeNameLookup 
WHERE 
    lookup_name = @Name

Until then, this may be the best you can do:

ALTER FUNCTION [dbo].Employee
(
@Name varchar(100)
)
RETURNS varchar(8)
AS
BEGIN
RETURN (
	SELECT EMPID 
	FROM dbo.MDREmployee 
	WHERE 
	    @Name IN (name_combined, OutlookPreferredName, ltrim(rtrim(name_last))+', '+ltrim(rtrim(name_first)))
)
END