comodines - SQL Query continúa ejecutándose durante mucho tiempo si no se encuentra el término de búsqueda

sql like comodines

Question

En mi sitio ASP.NET Core alojado en Azure, tengo una tabla de usuarios y la he implementado de la siguiente manera:

    var inner = from user in db.Users
             select  new
             {
                 Name = user.Name,
                 Verified = user.Verified,
                 PhotoURL = user.PhotoURL,
                 UserID = user.Id,
                 Subdomain = user.Subdomain,
                 Deleted=user.Deleted,
                 AppearInSearch = user.AppearInSearch
             };
    return await inner.Where(u=>u.Name.Contains(name)&& !u.Deleted && u.AppearInSearch)
                                    .OrderByDescending(u => u.Verified)
                                    .Skip(page * recordsInPage)
                                    .Take(recordsInPage)
                                    .Select(u => new UserSearchResult()
                                    {
                                        Name = u.Name,
                                        Verified = u.Verified,
                                        PhotoURL = u.PhotoURL,
                                        UserID = u.UserID,
                                        Subdomain = u.Subdomain
                                    }).ToListAsync();

Esto se traduce a una declaración SQL similar a la siguiente:

SELECT [t].[Name], [t].[Verified],
       [t].[PhotoURL], [t].[Id], 
       [t].[Subdomain], [t].[Deleted], 
       [t].[AppearInSearch]  
FROM (      
        SELECT [user0].[Name], [user0].[Verified], 
               [user0].[PhotoURL], [user0].[Id], 
               [user0].[Subdomain], [user0].[Deleted], 
               [user0].[AppearInSearch]      
        FROM [AspNetUsers] AS [user0]
        WHERE (((CHARINDEX(''khaled'', [user0].[Name]) > 0) OR (''khaled'' = N'''')) 
          AND ([user0].[Deleted] = 0)) 
          AND ([user0].[AppearInSearch] = 1)      
        ORDER BY [user0].[Verified] DESC      
        OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY  ) AS [t]

Si el término de búsqueda está disponible en la base de datos, el resultado se obtiene en menos de un segundo. Sin embargo, si no se encuentra, la consulta se ejecuta durante mucho tiempo (la he visto una vez que alcanza los 48 segundos).

Esto afecta en gran medida el rendimiento cuando publicamos esta función en Internet.

¿Puede sugerir amablemente una forma de resolver este problema?

Gracias

Actualización: este problema continúa aquí: nombre de inicio de sesión vacío al mostrar sys.processes

Answer 1

Ya puedes simplificar tu consulta de esta manera;):

int start=page * recordsInPage;

var inner = (from user in db.Users
            where user.Name.Contains(name) && !user.Deleted && user.AppearInSearch
            orderby user.Verified descending
            select  new
                   {
                     Name = user.Name,
                     Verified = user.Verified,
                     PhotoURL = user.PhotoURL,
                     UserID = user.Id,
                     Subdomain = user.Subdomain,
                     Deleted=user.Deleted,
                     AppearInSearch = user.AppearInSearch
                   }
             ).Skip(start).Take(recordsInPage);

return await inner.ToListAsync();

Si tiene un problema de rendimiento, intente crear un procedimiento almacenado con su SQL y úselo con la entidad Framework.

Answer 2

SQL Server tiene que usar un escaneo para encontrar filas que coincidan con la cláusula .Contains. No hay forma de evitar esto.

Sin embargo, si reducimos la cantidad de datos que SQL Server debe escanear, aceleraremos la consulta.

Cubrir el índice filtrado

Un índice está "cubriendo" si contiene todos los datos necesarios para ser devueltos en una consulta.

CREATE INDEX IX_User_Name_filtered ON USER ([Verified], [Name]) 
INCLUDE ( [PhotoURL], [Id], [Subdomain], [Deleted], [AppearInSearch]  ) 
WHERE [AppearInSearch]=1 AND [Deleted]=0

Es probable que este índice sea sustancialmente más pequeño que la tabla original, por lo que incluso si se requiere un escaneo, será más rápido.

Dependiendo del plan que se genere, este índice puede ser una mejor opción. no incluye las columnas adicionales y será aún más pequeño. Se requerirán pruebas para determinar la mejor opción.

CREATE INDEX IX_User_Name_filtered ON USER ([Verified], [Name]) 
WHERE [AppearInSearch]=1 AND [Deleted]=0