We have the following Sql Server Table-Valued Function (TVL) that retrieves all Users having a given phone number in 0.271 seconds:
select * from dbo.ClientsHavingPhoneNumberEver(null,'510','5273123',null,null,null)
GO
But, when the same TVL is executed with the same arguments via the dynamic SQL generated by a LINQ-to-SQL (L2SQL) data model it takes over 81 seconds:
exec sp_executesql N'SELECT [t0].*
FROM [dbo].[ClientsHavingPhoneNumberEver](NULL, @p0, @p1, NULL, NULL, NULL) AS [t0]'
,N'@p0 nvarchar(4000),@p1 nvarchar(4000)',@p0=N'510',@p1=N'5273123'
GO
The above code snippet is abbreviated to spare you the trouble of reading a long list of select column names that do not change the performance.
Can anyone explain why the dynamic SQL execution is so much slower?
In case it matters, the TVL being called performs the union of three select statements:
CREATE FUNCTION ClientsHavingPhoneNumberEver
(
@countryCode nvarchar(10) = null,
@areaCode nvarchar(50),
@number nvarchar(100),
@contactMethodTypeID int = null,
@effectiveDate DateTime = null,
@phoneRecordStateID int = null
)
RETURNS TABLE
AS
RETURN
(
select u.*
from dbo.UserContactMethod ucm with (nolock) -- select UserContactMethods by CountryCode, AreaCode and Number
inner join dbo.Users u with (nolock) on ucm.UserID = u.UserID
and (u.RecordStateID = dbo.RecordStateActive())
and ((@phoneRecordStateID is null) or (ucm.RecordStateID = @phoneRecordStateID))
and (((@contactMethodTypeID is null) and (ucm.ContactMethodTypeID in (dbo.ContactMethodCellPhone(), dbo.ContactMethodWorkPhone(), dbo.ContactMethodHomePhone())))
or (ucm.ContactMethodTypeID = @contactMethodTypeID))
and ((@countryCode is null) or (ucm.CountryCode is null) or (ucm.CountryCode = ucm.CountryCode))
and (ucm.AreaCode is not null) -- include only phone numbers that have an area code
and ((@areaCode is null) or (ucm.AreaCode = @areaCode))
and (ucm.Number is not null) -- include only phone numbers that are not null
and ((@number is null) or (ucm.Number like (@number + '%')))
and ((@effectiveDate is null) or (ucm.EffectiveDate <= @effectiveDate))
and ((@effectiveDate is null) or (ucm.ObsoleteDate is null) or (@effectiveDate < ucm.ObsoleteDate))
and (dbo.IsStudent(ucm.UserID) = 0)
UNION -- select UserContactMethods by FullPhone
select u.*
from dbo.UserContactMethod ucm with (nolock)
inner join dbo.Users u with (nolock) on ucm.UserID = u.UserID
and (u.RecordStateID = dbo.RecordStateActive())
and ((@phoneRecordStateID is null) or (ucm.RecordStateID = @phoneRecordStateID))
and (((@contactMethodTypeID is null) and (ucm.ContactMethodTypeID in (dbo.ContactMethodCellPhone(), dbo.ContactMethodWorkPhone(), dbo.ContactMethodHomePhone())))
or (ucm.ContactMethodTypeID = @contactMethodTypeID))
and (ucm.FullPhone like (isnull(@countryCode,'') + isnull(@areaCode,'') + @number + '%'))
and ((@effectiveDate is null) or (ucm.EffectiveDate <= @effectiveDate))
and ((@effectiveDate is null) or (ucm.ObsoleteDate is null) or (@effectiveDate < ucm.ObsoleteDate))
and (dbo.IsStudent(ucm.UserID) = 0)
UNION -- select FormPersonContactMethods by CountryCode, AreaCode and Number
select u.*
from dbo.Form f with (nolock)
inner join dbo.FormDetail fd with (nolock) on fd.FormID = f.FormID
and f.RequiredTypeID = dbo.FormRequiredTypeID_Required() -- only required forms
and f.RecordStateID = dbo.RecordStateActive()
and fd.RecordStateID = dbo.RecordStateActive()
inner join dbo.UserFormDetail ufd with (nolock) on fd.FormDetailID = ufd.FormDetailID
and ufd.RecordStateID = dbo.RecordStateActive()
and (dbo.IsStudent(ufd.UserID) = 0)
inner join dbo.FormPerson fp with (nolock) on ufd.UserFormDetailID = fp.UserFormDetailID
and fp.FormRoleTypeID in (dbo.FormPersonRole_PrimaryContact(), dbo.FormPersonRole_SecondaryContact(), dbo.FormPersonRole_AuthorizedPickupPerson())
and fp.RecordStateID = dbo.RecordStateActive()
and ((@effectiveDate is null) or (fp.EffectiveDate <= @effectiveDate))
and ((@effectiveDate is null) or (fp.ObsoleteDate is null) or (@effectiveDate < fp.ObsoleteDate))
inner join dbo.FormPersonContactMethod fpcm with (nolock) on fp.FormPersonID = fpcm.FormPersonID
and ((@phoneRecordStateID is null) or (fpcm.RecordStateID = @phoneRecordStateID))
and (((@contactMethodTypeID is null) and (fpcm.ContactMethodTypeID in (dbo.ContactMethodCellPhone(), dbo.ContactMethodWorkPhone(), dbo.ContactMethodHomePhone())))
or (fpcm.ContactMethodTypeID = @contactMethodTypeID))
and ((@countryCode is null) or (fpcm.CountryCode is null) or (fpcm.CountryCode = fpcm.CountryCode))
and (fpcm.AreaCode is not null) -- include only phone numbers that have an area code
and ((@areaCode is null) or (fpcm.AreaCode = @areaCode))
and (fpcm.Number is not null) -- include only phone numbers that are not null
and ((@number is null) or (fpcm.Number like (@number + '%')))
and ((@effectiveDate is null) or (fpcm.EffectiveDate <= @effectiveDate))
and ((@effectiveDate is null) or (fpcm.ObsoleteDate is null) or (@effectiveDate < fpcm.ObsoleteDate))
inner join dbo.Users u with (nolock) on ufd.UserID = u.UserID
and u.RecordStateID = dbo.RecordStateActive()
and (dbo.IsStudent(u.UserID) = 0)
)
GO
