Tengo el siguiente problema

Antecedente

Estoy tratando de implementar un selector de autocompletar con MVC3, EF4 y jquery sobre una tabla con 4.5 millones de registros.

Esta es la tabla:

CREATE TABLE [dbo].[CONSTA] (
  [afpCUIT] nvarchar(11) COLLATE Modern_Spanish_CI_AS NOT NULL,
  [afpNombre] nvarchar(30) COLLATE Modern_Spanish_CI_AS NULL,
  [afpGanancias] varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  [afpIVA] varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  [afpMonot] varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  [afpIntSoc] varchar(1) COLLATE Modern_Spanish_CI_AS NULL,
  [afpEmpl] varchar(1) COLLATE Modern_Spanish_CI_AS NULL,
  [afpAct] varchar(2) COLLATE Modern_Spanish_CI_AS NULL,
  CONSTRAINT [CONSTA_pk] PRIMARY KEY CLUSTERED ([afpCUIT])
)
ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [CONSTA_Nombre_idx] ON [dbo].[CONSTA]
  ([afpNombre])
WITH (
  PAD_INDEX = OFF,
  DROP_EXISTING = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  SORT_IN_TEMPDB = OFF,
  ONLINE = OFF,
  ALLOW_ROW_LOCKS = OFF,
  ALLOW_PAGE_LOCKS = OFF)
ON [PRIMARY]
GO

La tabla es bastante estática (solo necesita una actualización mensual por lotes) y solo lectura.

si a alguien le importa descargar los registros (54 MB), esta es la URL:

http: //www.afip.gob.ar/genericos/cInscripcion/22102011.zi

y aquí está la descripción del registro:

http: //www.afip.gob.ar/genericos/cInscripcion/archivoCompleto.as

Aquí está el código de la aplicación:

CONTROLADOR

public class AltaMasivaController : Controller
{
    //
    // GET: /AltaMasiva/

    public ActionResult Index()
    {
        return View();
    }

    public JsonResult GetUsers(string query)
    {
        CENT2Entities db = new CENT2Entities();
        bool isCUIT = true;

        for(int j = 0; j < query.Length; j++)
            if (! Char.IsDigit(query, j))
            {
                isCUIT = false;
                break;
            }

        if (isCUIT)
        {
            // nvarchar search
            var x = from u in db.CONSTA
                    where u.afpCUIT.StartsWith(query)
                    orderby u.afpNombre
                    select new { label = u.afpNombre.TrimEnd(), id = u.afpCUIT };

            return Json(x.Take(50), JsonRequestBehavior.AllowGet);
        }
        else
        {
            // nvarchar search
            var x = from u in db.CONSTA
                    where u.afpNombre.StartsWith(query)
                    orderby u.afpNombre
                    select new { label = u.afpNombre.TrimEnd(), id = u.afpCUIT };

            return Json(x.Take(50), JsonRequestBehavior.AllowGet);
        }
    } 
}

VER

@{
    viewbag.title = "index";
}

<h2>index</h2>
@html.textbox("user", "", new { style="width: 400px;" })

<script type="text/javascript">

$("input#user").autocomplete(
{ 
    source: function (request, response) 
    { 
        // define a function to call your action (assuming usercontroller) 
        $.ajax(
        { 
            url: '/altamasiva/getusers', type: "post", datatype: "json", 

            // query will be the param used by your action method 
            data: { query: request.term }, 

            success: function(data){ 
                response( $.map(data, function (item){ return { label: item.label + " (" + item.id + ")", value: item.label, id: item.id }; })); 
            } 
        }) 
    }, 
    minlength: 1, // require at least one character from the user
});

</script>

Y ahora

EL PROBLEM

omo puede ver, el código sigue diferentes caminos si la cadena de consulta contiene solo números.

Cuando todos los caracteres del parámetro del controlador son números (donde u.afpCUIT.StartsWith (query)), se supone que el optimizador de consultas "realiza" una búsqueda de índice agrupado (lo que hace) y devuelve las primeras 50 filas que encuentra. Cuando llega la primera cadena de "autocompletar" (generalmente uno o dos caracteres como máximo), la consulta se realiza extraordinariamente rápido, pero, cuando aumenta la longitud de la cadena, el rendimiento se degrada notablemente (tarda entre 20 segundos y 2 minutos con 9 o más caracteres) Sorprendentemente, después de "reiniciar" el Servicio SQL Server, si la cadena inicial contiene 10 caracteres, también funciona muy bien, pero el rendimiento se degrada cuando eliminamos caracteres de la cadena "consulta", todo lo contrario.

¿Por qué está pasando esto

Cuando el servidor SQL compila el primer plan de ejecución, lo optimiza para funcionar realmente rápido con un gran conjunto de resultados (o viceversa). Las consultas posteriores, que reducen (o expanden) el conjunto de resultados, requieren un plan de ejecución diferente ... PERO ... EF generó SQL utiliza parámetros commad para (con precisión) evitar la recompilación de instrucciones ...

Limpieza de la caché del plan de ejecución ejecutando:

db.ExecuteStoreCommand("DBCC FREEPROCCACHE");

restaura el rendimiento a excelentes tiempos de respuesta ... PERO ... mata todos los planes en todas las bases de datos, lo que degrada el rendimiento de todos los otros planes en caché (que generalmente funcionan bien).

Después de hacer algunos perfiles en las declaraciones de sql EF, ejecuté DBCC FREEPROCCACHE en el Analizador de consultas antes de que el sql EF generara, lo que resultó para generar diferentes planes de ejecución, todos con un rendimiento en el rango de 250 ms, independientemente de la longitud del parámetro:

DBCC FREEPROCCACHE

exec sp_executesql N'SELECT TOP (50) 
[Project1].[C1] AS [C1], 
[Project1].[C2] AS [C2], 
[Project1].[afpCUIT] AS [afpCUIT]
FROM ( SELECT 
    [Extent1].[afpCUIT] AS [afpCUIT], 
    [Extent1].[afpNombre] AS [afpNombre], 
    1 AS [C1], 
    RTRIM([Extent1].[afpNombre]) AS [C2]
    FROM [dbo].[CONSTA] AS [Extent1]
    WHERE [Extent1].[afpCUIT] LIKE @p__linq__0 ESCAPE N''~''
)  AS [Project1]
ORDER BY [Project1].[afpNombre] ASC',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'2023291%'

LA PREGUNT

¿Existe una alternativa más elegante a

db.ExecuteStoreCommand("DBCC FREEPROCCACHE");

?

Sorprendentemente, la segunda ruta de la consulta (donde u.afpNombre.StartsWith (query)) no se ve afectada por el mismo problema y funciona muy bien. Obviamente, los planes de ejecución no cambian cuando cambia la longitud de la cadena ...

Encontré un parámetro ObjectContext en versiones anteriores de EF:

System.Data.EntityClient.EntityCommand.EnablePlanCaching

pero no pude encontrarlo en EF4, y no estoy seguro de si los resultados globales serían los mismos.

Estoy realmente perplejo con este problema, y no sé dónde está el verdadero problema

Diseño de índice pobre? La falta de particiones? SQL SERVER 2008 Express edition? EF generó SQL? ¿Mala suerte?

Cualquier ayuda sería genial. Gracias de antemano!