When I run the SQL generated by direct EF in SQLServer it runs less than 1 second.
When you run the application it times out because it takes more than 30 seconds. I have seen a lot of this problem on the internet with procedure calls, but when talking about running a SQL I do not think much.
SQLServer 2012 database, Entity Framework 6.
Follow all that I have information:
(from v in _db.VIDEO.AsNoTracking()
join cv in _db.VIDEO_CATEGORIA.AsNoTracking() on v.ID equals cv.ID_VIDEO
where cv.ID_CATEGORIA == codcat
orderby v.DT_INCLUIDO_BD descending
select
new VideoModel
{
ID = v.ID,
DURATION = v.DURATION,
SCREENSHOT = (v.ID_ORIGEM == NUMERO_4_TIPO_LONG) ? v.ScreenShotServer : v.SCREENSHOT,
URL = v.URL,
VIDEO_TITLE = v.VIDEO_TITLE,
DATE = v.DT_INCLUIDO_BD,
origemlong = v.ID_ORIGEM,
totalclicks = v.TOTALCLICKS
});This is then used with the PageList, filtering pages of 120 rows, then it will receive an extra part in SQL:
I get SQL using the log of the EF itself: db.Database.Log = s => log.Debug(s);
2015-08-24 15:02:44,035 SELECT
[Project1].[C1] AS [C1],
[Project1].[ID] AS [ID],
[Project1].[DURATION] AS [DURATION],
[Project1].[C2] AS [C2],
[Project1].[URL] AS [URL],
[Project1].[VIDEO_TITLE] AS [VIDEO_TITLE],
[Project1].[C3] AS [C3],
[Project1].[ID_ORIGEM] AS [ID_ORIGEM],
[Project1].[TOTALCLICKS] AS [TOTALCLICKS]
FROM ( SELECT
[Extent1].[ID] AS [ID],
[Extent1].[VIDEO_TITLE] AS [VIDEO_TITLE],
[Extent1].[DURATION] AS [DURATION],
[Extent1].[DT_INCLUIDO_BD] AS [DT_INCLUIDO_BD],
[Extent1].[ID_ORIGEM] AS [ID_ORIGEM],
[Extent1].[URL] AS [URL],
[Extent1].[TOTALCLICKS] AS [TOTALCLICKS],
1 AS [C1],
CASE WHEN (4 = [Extent1].[ID_ORIGEM]) THEN [Extent1].[ScreenShotServer] ELSE [Extent1].[SCREENSHOT] END AS [C2],
CAST( [Extent1].[DT_INCLUIDO_BD] AS datetime2) AS [C3]
FROM [ricardocorpore].[VIDEO] AS [Extent1]
INNER JOIN [ricardocorpore].[VIDEO_CATEGORIA] AS [Extent2] ON [Extent1].[ID] = [Extent2].[ID_VIDEO]
WHERE [Extent2].[ID_CATEGORIA] = @p__linq__0
) AS [Project1]
ORDER BY [Project1].[DT_INCLUIDO_BD] DESC
OFFSET 0 ROWS FETCH NEXT 120 ROWS ONLY
2015-08-24 15:02:44,035
2015-08-24 15:02:44,035 -- p__linq__0: '28' (Type = Int64, IsNullable = false)
2015-08-24 15:02:44,035 -- Executing at 24/08/2015 15:02:44 -03:00
2015-08-24 15:03:14,081 -- Failed in 30041 ms with error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
2015-08-24 15:03:14,081
2015-08-24 15:03:14,081 Closed connection at 24/08/2015 15:03:14 -03:00 The [Extent2].[ID_CATEGORIA] that is used in the where is bigint , the variable that arrives in the EF in the code is long . This is why I have thrown problems with variable types of columns.
The [DT_INCLUIDO_BD] column has an index created just for it.
Does anyone have any suggestions?