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
orON
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
In all cases, I still get a timeout when the last two parameters are non-zero. All requests are done under 2s in SSMS ...
Is there anything else I can try?
source to share
No one has answered this question yet
See similar questions:
or similar: