5
When I run the SQL generated by the direct EF on Sqlserver it runs with less than 1 second.
When it runs through the timeout application it takes more than 30 seconds. I’ve seen a lot of this problem on the internet with Procedure calls, but when it comes to running an SQL I don’t think much of it.
Sqlserver 2012 Database, Entity Framework 6.
Follow everything 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 used later with Pagelist, filtering 120-line pages, so it will receive an extra portion in SQL:
I get SQL using the EF log: 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:00The [Extent2].[ID_CATEGORIA] which is used in the where is bigint , the variable arriving at the EF in the code is long. That’s why I ruled out problems of variable types with column.
The column of [DT_INCLUIDO_BD] has an index created only for it.
Does anyone have any suggestions?
What is the size of each table? At first there is no problem with the code.
– Leonel Sanches da Silva
If the query takes long to be executed when they come from the application, try to do the SQL Server Profiler of her to find the cause. This problem of you not being able to play by running the query directly may be related to the database cache (you have as clean also and test the query again)
– Dherik
Today the Video table has one and a half million records, the video_category 4 million records.
– Ricardo
The SQL access plan is great, I already did the test, uses the indexes and the PK. No problem in it.
– Ricardo
But if it is taking a long time in the database to query, something is wrong. Who will help you show this is SQL Server Profiler.
– Dherik
So, I checked the access plan by taking the query and putting it on Management Studio, and it says that the plan is fine, so much so that I rotate it there and it rotates instantly. I use a shared server, so I don’t have access to table
sys.dm_exec_cached_plansthat I believe would be where to see what was run by the application (I’m not sure if that would be it).– Ricardo
See the link I passed, method 3: If you can’t run your query directly (or your query doesn’t run slowly when you execute it directly - Remember we want a plan of the query Performing Badly), then you can capture a plan using a SQL Server Profiler trace. You came to clear the cache and test the query again, just to make sure it’s not cache?
– Dherik
I tried to put the command to clean Chace in a trial, but still I do not have access to run, when the Server Profile I can’t even log in to it due to lack of access tb...
– Ricardo
Can’t you talk to the DBA/person who has privileges to help you? There is a clear limit to where you can go, without sufficient privileges, to investigate this problem. If you can’t get access to all the tools you need to do your job, it gets hard to find the cause. You could show the execution plan you’ve already achieved?
– Dherik
Already one day I changed the pagination to 130 lines, now it runs SQL on the server in 2ms, even SQL only instead of 120 is 130... If you solve like this I will leave it that way. If you return the problem I will try to talk to the DBA.
– Ricardo