EF is giving Timeout in a query that should be instantaneous

Asked

Viewed 170 times

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:00

The [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?

entity-framework entity-framework-6

  • What is the size of each table? At first there is no problem with the code.

  • 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)

  • 1

    Today the Video table has one and a half million records, the video_category 4 million records.

  • The SQL access plan is great, I already did the test, uses the indexes and the PK. No problem in it.

  • 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.

  • 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_plans that I believe would be where to see what was run by the application (I’m not sure if that would be it).

  • 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?

  • 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...

  • 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?

  • 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.

Show 5 more comments

1 answer

2


The solution to the case presented in the question was the change of the column being ordered.

Of:

ORDER BY [Project1].[DT_INCLUIDO_BD] DESC

To:

ORDER BY [Project1].[ID] DESC

What I could understand is that the access plan used by the application or management is different. So I changed SQL in an attempt to make it run the same access plan in both ways.

  • Thanks for the reply. This information will be valuable to other users. + 1.

Browser other questions tagged entity-framework entity-framework-6

You are not signed in. Login or sign up in order to post.