I use a legacy database with a compatibility level set to 100 on a SQL Server 2017.

To query it I use Entity Framework Core 8 and, for the DbContext, I use:

UseSqlServer(_cs, o => o.UseCompatibilityLevel(100))

For one (at least) query I have the following behavior:

• query generated by EF uses sp_executesql
• execution time is 3 minutes and 55 seconds

I catch the query using SQL Server Profiler and run it without sp_executesql, execution time is less than 1 second.

I can't get the execution plan used (or not) by sp_executesql.

After different manipulations regarding resetting statistics I finally turn compatibility level to 140 on server side and... sp_executesql returns result in less than 1 second.

The slow query contains:

OFFSET @__p_1 ROWS FETCH NEXT @__p_1 ROWS ONLY

with @__p_1 = 10.

One weird point is that the same query with

OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

and @__p_1 = 0 and @__p_2 = 10 returns in less than 1 second.

My question is: what can I do to get a better execution time for sp_executesql and keep compatibility level at 100 ?

EDIT 1 =====

sql for table creation an queries: fast and slow

EDIT 2 =====

sqlplan: I can't get it from Processes, the menu was grayed, but I got it from Active Expensive queries

pastetheplan (by brentozar) hangs after submit the file.

EDIT 3 =====

It seems to be a missing index on one column (thank you to allow me to access to the query plan).

For me it remains weird that this missing index impact so much the sp_executesql but not the 'direct' query (both in ssms)

EDIT 4 =====

the generated slow sql:

exec sp_executesql N'SELECT COALESCE(LTRIM(RTRIM([t].[GP_NATUREPIECEG])), N'''') + COALESCE(CONVERT(varchar(11), [t].[GP_NUMERO]), N''''), [a0].[AFF_CHARLIBRE3], [t].[GP_NATUREPIECEG], [t].[GP_SOUCHE], [t].[GP_NUMERO], [t].[GP_INDICEG], [a0].[AFF_AFFAIRE], [t0].[T_TIERS], [t1].[AFF_CHARLIBRE3], COALESCE([t0].[T_LIBELLE], N'''') + CASE
    WHEN [t0].[T_PARTICULIER] = ''X'' THEN N'' ''
    ELSE N''''
END + COALESCE([t0].[T_PRENOM], N''''), [t].[GP_DATEPIECE], [t].[GP_TOTALHT]
FROM (
    SELECT [p].[GP_NATUREPIECEG], [p].[GP_SOUCHE], [p].[GP_NUMERO], [p].[GP_INDICEG], [p].[GP_AFFAIRE], [p].[GP_TIERS], [p].[GP_DATEPIECE], [p].[GP_TOTALHT]
    FROM [dbo].[PIECE] AS [p]
    WHERE [p].[GP_NATUREPIECEG] <> ''AFF'' AND ([p].[GP_NATUREPIECEG] + COALESCE(CONVERT(varchar(11), [p].[GP_NUMERO]), N'''') LIKE @__v_0_contains ESCAPE N''\'' OR EXISTS (
        SELECT 1
        FROM [dbo].[LIGNE] AS [l]
        LEFT JOIN [dbo].[AFFAIRE] AS [a] ON [l].[GL_AFFAIRE] = [a].[AFF_AFFAIRE]
        WHERE [p].[GP_NATUREPIECEG] = [l].[GL_NATUREPIECEG] AND [p].[GP_SOUCHE] = [l].[GL_SOUCHE] AND [p].[GP_NUMERO] = [l].[GL_NUMERO] AND [p].[GP_INDICEG] = [l].[GL_INDICEG] AND [a].[AFF_CHARLIBRE3] LIKE @__v_0_startswith ESCAPE ''\'')) AND [p].[GP_VENTEACHAT] = ''VEN''
    ORDER BY [p].[GP_DATEPIECE]
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t]
LEFT JOIN [dbo].[AFFAIRE] AS [a0] ON [t].[GP_AFFAIRE] = [a0].[AFF_AFFAIRE]
LEFT JOIN [dbo].[TIERS] AS [t0] ON [t].[GP_TIERS] = [t0].[T_TIERS]
OUTER APPLY (
    SELECT DISTINCT [a1].[AFF_CHARLIBRE3]
    FROM [dbo].[LIGNE] AS [l0]
    LEFT JOIN [dbo].[AFFAIRE] AS [a1] ON [l0].[GL_AFFAIRE] = [a1].[AFF_AFFAIRE]
    WHERE [t].[GP_NATUREPIECEG] = [l0].[GL_NATUREPIECEG] AND [t].[GP_SOUCHE] = [l0].[GL_SOUCHE] AND [t].[GP_NUMERO] = [l0].[GL_NUMERO] AND [t].[GP_INDICEG] = [l0].[GL_INDICEG] AND COALESCE([l0].[GL_AFFAIRE], '''') <> '''' AND ([a1].[AFF_CHARLIBRE3] <> '''' OR [a1].[AFF_CHARLIBRE3] IS NULL)
) AS [t1]
ORDER BY [t].[GP_DATEPIECE], [t].[GP_NATUREPIECEG], [t].[GP_SOUCHE], [t].[GP_NUMERO], [t].[GP_INDICEG], [a0].[AFF_AFFAIRE], [t0].[T_TIERS]',N'@__v_0_contains nvarchar(4000),@__v_0_startswith varchar(35),@__p_1 int,@__p_2 int',@__v_0_contains=N'%FAC599%',@__v_0_startswith='FAC599%',@__p_1=10,@__p_2=10

from the following c# code parts, (I'm currently rewriting it)

private static Expression<Func<Piece, bool>> getDataBase = PredicateBuilder.New<Piece>(
                x => x.Naturepieceg != "AFF");

// ===== ===== =====
                
Expression<Func<Piece, bool>> q = getDataBase;              

string v = "FAC599"

q = q.And(x =>
    (x.Naturepieceg + x.Numero.ToString()).Contains(v) ||
    x.Lignes.Any(l => l.Affaire.Charlibre3.StartsWith(v))
);
                
var dtp = {
    Start = 10,
    Length = 10
}
string stOb = "DocDate"             
                
await ccc.Set<Piece>().Where(q.Expand()).Select(p => new AccountingDocumentDTO {
    Id = null,
    InnerRef = p.Naturepieceg.Trim() + p.Numero.ToString(),
    AssetRef = p.Affaire.Charlibre3,
    AssetRefs = (from l in p.Lignes where (l.KAffaire ?? "") != "" && l.Affaire.Charlibre3 != "" select l.Affaire.Charlibre3).Distinct(),
    ClientLN = p.Tiers.Libelle + (p.Tiers.Particulier == "X" ? " " : "") + p.Tiers.Prenom,
    Nat = p.Naturepieceg,
    Num = p.Numero,
    DocDate = p.Datepiece,
    TotalHT = p.Totalht
}).
OrderBy(stOb).
Skip(dtp.Start).Take(dtp.Length).
ToListAsync();