Strange behavior of SQL function call function

Question

Strange behavior of SQL function call function

I am creating an ASP.NET MVC5 Application.

I have a DBML file representing my database (SQL Server 2008).

I have the following SQL function:

CREATE FUNCTION [dbo].[sp_GetResultsPerSubjects] (
    @program VARCHAR(MAX) = NULL,
    @pedagoPeriod VARCHAR(MAX) = NULL,
    @intake VARCHAR(MAX) = NULL,
    @acadYear VARCHAR(MAX) = NULL,
    @section VARCHAR(MAX) = NULL,
    @studentFilter VARCHAR(MAX) = NULL,
    @moduleFilter VARCHAR(MAX) = NULL
)
RETURNS TABLE
AS
RETURN (
    SELECT
        A.[id],
        A.[lastname],
        A.[firstname],
        D.[module_name],
        D.[id] as [module_id],
        H.[id] as [course_id],
        H.[course_name],
        G.[data_text]
    FROM
        [dbo].[Person] A
        INNER JOIN [dbo].[Inscription] B ON A.[id] = B.[person_Id]
        INNER JOIN [dbo].[Inscrsubject] C ON B.[id] = C.[inscription_Id] 
        INNER JOIN [dbo].[Module] D ON C.[id] = D.[inscrsubject_id]
        INNER JOIN [dbo].[Result] E ON C.[id] = E.[inscrsubject_id]
        INNER JOIN [dbo].[Note] F ON E.[id] = F.[result_id] 
        INNER JOIN [dbo].[Data] G ON F.[id] = G.[note_id]
        INNER JOIN [dbo].[Course] H ON H.[result_id] = E.[id]
        INNER JOIN [dbo].[GPS] I ON B.[gps_id] = I.[id]
    WHERE
        (@program IS NULL OR I.[program] IN (SELECT [Value] FROM [dbo].SplitString(@program, '|')))
        AND (@pedagoPeriod IS NULL OR I.[pedagoPeriod] IN (SELECT [Value] FROM [dbo].SplitString(@pedagoPeriod, '|')))
        AND (@intake IS NULL OR I.[intake] IN (SELECT [Value] FROM [dbo].SplitString(@intake, '|')))
        AND (@acadYear IS NULL OR I.[acadYear] IN (SELECT [Value] FROM [dbo].SplitString(@acadYear, '|')))
        AND (@section IS NULL OR I.[section] IN (SELECT [Value] FROM [dbo].SplitString(@section, '|')))
        AND (@studentFilter IS NULL OR A.[id] IN (SELECT CONVERT(DECIMAL, [Value]) FROM [dbo].SplitString(@studentFilter, '|')))
        AND (@moduleFilter IS NULL OR D.[id] IN (SELECT CONVERT(DECIMAL, [Value]) FROM [dbo].SplitString(@moduleFilter, '|')))
        AND G.[data] = 'AVERAGE'
        AND G.[data_Text] IS NOT NULL
        AND G.[data_Text] <> ''
)

I call the function this way from my controller (all parameters are string):

resultsPerSubjects = dataContext.sp_GetResultsPerSubjects(programs, pedagogicPeriods, intakes, academicYears, sections, studentSelection, moduleSelection).ToList();

I am getting timeout :The wait operation timed out

Using SQL Profiler , I see the following query being executed:

exec sp_executesql N'
    SELECT [t0].[id], [t0].[lastname], [t0].[firstname], [t0].[module_name], [t0].[module_id], [t0].[course_id], [t0].[course_name], [t0].[data_text]
    FROM [dbo].[sp_GetResultsPerSubjects](@p0, @p1, @p2, @p3, @p4, @p5, @p6) AS [t0]',
    N'@p0 varchar(8000), @p1 varchar(8000), @p2 varchar(8000), @p3 varchar(8000), @p4 varchar(8000), @p5 varchar(8000), @p6 varchar(8000)',
    @p0='AAAA', @p1='1', @p2='a', @p3='2013-2014', @p4='F', @p5='219894352', @p6='92955'

Indeed, this query takes 12 minutes to complete , longer than the datacontext timeout.

But the strange thing is that running a function directly from SQL takes arround 1 second ! With the same parameters in the sentence WHERE

:

('AAAA' IS NULL OR I.[program] IN (SELECT [Value] FROM [dbo].SplitString('AAAA', '|')))
    AND ('1' IS NULL OR I.[pedagoPeriod] IN (SELECT [Value] FROM [dbo].SplitString('1', '|')))
    AND ('a' IS NULL OR I.[intake] IN (SELECT [Value] FROM [dbo].SplitString('a', '|')))
    AND ('2013-2014' IS NULL OR I.[acadYear] IN (SELECT [Value] FROM [dbo].SplitString('2013-2014', '|')))
    AND ('f' IS NULL OR I.[section] IN (SELECT [Value] FROM [dbo].SplitString('f', '|')))
    AND ('219894352' IS NULL OR A.[id] IN (SELECT CONVERT(DECIMAL, [Value]) FROM [dbo].SplitString('2319894352', '|')))
    AND ('92955' IS NULL OR D.[id] IN (SELECT CONVERT(DECIMAL, [Value]) FROM [dbo].SplitString('902955', '|')))
    AND G.[data] = 'AVERAGE'
    AND G.[data_text] IS NOT NULL
    AND G.[data_text] <> ''

If I omit the last 2 parameters, the problem disappears ...

Any idea?

EDIT :

Rotate Arithabort

OFF

or ON

in SSMS does not change the query execution time (still arround 1 second).

I am not using ADO.NET or EF but a simple LINQ to SQL DBML file.

I cannot use OPTION RECOMPILE

or OPTIMIZE FOR

as it is a function and not a stored procedure (yes, I know I called it a sp_*

bug).

EDIT2 :

Now I'm not sure if this is a sniffing parameter issue. I tried:

Rotate Arithabort

OFF

or ON

in SSMS
Execution EXEC sp_updatestats

to update statistics
Changing the function to RETURNS @t TABLE(...)

so I can declare local variables and assign parameters
Using a stored procedure instead of a function, so I can use the option OPTION (RECOMPILE)
Using a stored procedure with parameter assignment to local variables