Why the OFF SET leaves SQL slower?

Question

Why the OFF SET leaves SQL slower?

Navigation

#1 by (2 votes)

6

I have the following SQL, generated by Entity Framework 6.

SELECT 
    [Project1].[C1] AS [C1], 
    [Project1].[ID] AS [ID], 
    [Project1].[CAMPO1] AS [CAMPO1], 
    [Project1].[CAMPO2] AS [CAMPO2], 
    [Project1].[CAMPO3] AS [CAMPO3], 
    [Project1].[CAMPO4] AS [CAMPO4], 
    [Project1].[CAMPO5] AS [CAMPO5], 
    [Project1].[CAMPO6] AS [CAMPO6], 
    [Project1].[TOTALCLICKS] AS [TOTALCLICKS]
    FROM ( SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[CAMPO1] AS [CAMPO1], 
        [Extent1].[CAMPO2] AS [CAMPO2], 
        [Extent1].[CAMPO3] AS [CAMPO3], 
        [Extent1].[CAMPO4] AS [CAMPO4], 
        [Extent1].[CAMPO5] AS [CAMPO5], 
        [Extent1].[CAMPO6] AS [CAMPO6], 
        [Extent1].[TOTALCLICKS] AS [TOTALCLICKS], 
        1 AS [C1]
        FROM  [VIDEO] AS [Extent1]
        INNER JOIN [VIDEO_CATEGORIA] AS [Extent2] ON [Extent1].[ID] = [Extent2].[ID_VIDEO]
        WHERE [Extent2].[ID_CATEGORIA] = 25
    )  AS [Project1]
    ORDER BY [Project1].[TOTALCLICKS] DESC
    OFFSET 273910 ROWS FETCH NEXT 130 ROWS ONLY

Basically it's a relationship between the video table and video_category.

The access plan informs you that it uses the index in video_category by id_video. Which is good.

Then it goes to the video table and uses the key id. Here it spends 98% of the time.

Then it loops and uses the video index of total_clicks to sort and get the 130s of the OFF SET.

This query takes more than three minutes, which I do not understand is that if I put the OFF SET starting from 1 it runs in two seconds ( OFFSET 1 ROWS FETCH NEXT 130 ROWS ONLY )

Why this difference?

sql sql-server entity-framework

asked by anonymous 17.11.2015 / 13:44

1 answer

What is the difference between git reset - file_name and git reset HEAD file_name? Conflicts with the ConfideUser class

score 2 · Accepted Answer

It is normal to decrease the performance, and the research has to count the first offset + limit. The higher the value, the longer the search will take.

The search does not go straight to OFFSET because, first, the record may be different in size, and second, there can be no gaps in deleted records. He needs to check and count each record again.

This code in mysql can help you, being id the primary key, you can use this to increase speed:

SELECT  t.*
FROM    (
        SELECT  id
        FROM    tabelaX
        ORDER BY
                id
        LIMIT 10000, 30
        ) q
JOIN    tabelaX t
ON      t.id = q.id