database - sp4 - La lentitud de SQL ''ORDER BY''

sql server express 2005 sp4 (14)

¿Es cierto que ORDER BY es generalmente bastante lento? Estoy tratando de ejecutar algunas sentencias SQL donde la cláusula WHERE es bastante simple, pero luego estoy intentando ORDER BY en una VARCHAR(50) indexada VARCHAR(50) .

Necesito ordenar alfabéticamente por motivos de visualización. Pensé que conseguir la base de datos para hacerlo por mí es la más eficiente.

En este punto, estoy buscando a cualquiera

optimizar la consulta sql
ordenar el conjunto de resultados en el código

Aquí está la consulta real que estoy tratando de ejecutar:

// B.SYNTAX is a TEXT/CLOB field // Indexes on NAME, MODULENAME. PREVIOUS is a CHAR(1) with no index "SELECT A.NAME, B.SYNTAX, B.DESCRIPTION, A.RATE1, A.RATE2, A.RATE3, A.STARTDATE, A.ENDDATE, A.HIDE, A.CATEGORYNAME FROM A, B WHERE A.MODULENAME=''"+loadedModuleName+"'' AND A.NAME = B.NAME AND (A.PREVIOUS<>''N'' OR A.PREVIOUS IS NULL) ORDER BY A.NAME"

El tamaño de la tabla A es ~ 2000 filas y B es aproximadamente ~ 500.

Probablemente también debería mencionar que no puedo hacer mucha optimización específica de la base de datos ya que admitimos múltiples bases de datos. Además, la aplicación se implementa en un sitio del cliente.

Estoy esperando que se devuelvan cientos de registros (menos de 1000).

¿Qué harías? Cualquier consejo es apreciado. Gracias.

Aquí hay un montón de sugerencias realmente buenas, pero hay una pequeña cosa que no creo que me gustaría comentar.

¿Qué base de datos estas usando? Como alguien que pasa mucho tiempo en MySQL, lo que se me ocurre es la declaración O. MySQL puede ser realmente estúpido con las RUP. He visto que es más rápido hacer dos selecciones y UNIONARlas juntas.

Si el recuento de filas es grande (en la tabla, no se devuelve) podría ser un factor.

De lo contrario, estaría de acuerdo con las otras publicaciones. Los índices deben hacerlo rápido, y a menudo es mejor dejar que DB lo haga en lugar de manejarlo usted mismo. El DB sabe lo que está haciendo. A menos que tenga un conjunto de datos REALMENTE grande y quiera cambiar la carga de clasificación al cliente (para que el DB pueda realizar más consultas), permitiría que el DB haga el trabajo de clasificación.

El orden en un campo indexado no debe ser lento ya que puede extraer los datos en orden de índice. Es posible que desee colocar información sobre la estructura de la base de datos (el DDL) y la consulta real para que la gente pueda echarle un vistazo.

Debería usar una ordenación SQL en lugar de una clasificación de código cuando sea posible para que esté en el camino correcto allí.

ACTUALIZACIÓN: Ok, algunas cosas. Primero, no debe usar el constructo "+ loadedModuleName +" ya que hace que cada consulta sea única y arruina el optimizador. Usa un parámetro En segundo lugar, su cláusula Order by es ambigua en cuanto a si se trata de la tabla A o B: haga esto explícito y elija la tabla con el índice (incluso si ambos tienen índices, hágalo explícito). Finalmente, su campo "Anterior" todavía se puede indexar incluso como un char (1). Haría todo menos el último índice sugerido, la velocidad de prueba y, si todavía es lento, buscaré el índice y verificaría nuevamente.

ACTUALIZAR Entonces, devolverá <1000 registros, pero ¿cuál es el tamaño de la tabla en total?

ACTUALIZACIÓN Oh, hombre, lo siento, no he entendido esto antes. Si desea implementarlo correctamente en SQL Server, su consulta debería ser:

SELECT A.NAME, B.SYNTAX, B.DESCRIPTION, A.RATE1, A.RATE2, A.RATE3, A.STARTDATE, A.ENDDATE, A.HIDE, A.CATEGORYNAME FROM Table1 A join Table2 B on (A.Name=B.Name) WHERE (A.MODULENAME=@ModuleName) AND (A.PREVIOUS<>''N'' OR A.PREVIOUS IS NULL) ORDER BY A.NAME

Prueba esto y casi te garantizaré que verás una gran aceleración.

Hay muchos problemas en juego aquí.

En términos de rendimiento puro y asumiendo que los índices están configurados correctamente , las bases de datos son muy buenas para ordenar. Para una consulta aislada , la clasificación en la base de datos es probablemente la más rápida.

Desafortunadamente, en la práctica, la base de datos a menudo se convierte en el cuello de botella de una aplicación. Cualquier cosa que pueda hacer para alejar el trabajo de la base de datos mejorará el rendimiento general de la aplicación. Eso incluye mover las operaciones de clasificación a un nivel de negocio, web o presentación menos ocupado. El nivel de presentación puede no ser capaz de ordenar una consulta de manera eficiente, pero podría estar mejor posicionada para manejar esa carga general. Esto es especialmente cierto cuando puede llevar ese trabajo de manera confiable a máquinas individuales de usuarios finales, aunque eso puede ser problemático.

Por otro lado, hay más que considerar aquí que el rendimiento puro. También quiere pensar en el mantenimiento futuro. ¿Qué es más sostenible que una simple cláusula de "PEDIDO POR"? Esa es solo una línea de código, en comparación con quién sabe cuánto trabajo de programador extra para obtener un tipo de trabajo en otro lugar. Este podría ser un caso en el que es mejor que invierta algo de dinero en el problema para asegurarse de que su base de datos simplemente pueda mantener un nivel aceptable de rendimiento, clases y todo.

Incluso aquí el problema no está cortado y seco. Hay una escuela de pensamiento que cree que la ordenación realmente debería considerarse una función del nivel de presentación de todos modos, y que, en última instancia, el nivel de presentación es también el lugar más fácil de mantener para realizar este trabajo. No me suscribo a esa teoría, pero está ahí fuera.

No es una afirmación justa decir que "ordenar por" es lento en sí mismo. Tiene que considerar muchos RDBM en cuanto a su propia implementación y el tipo de datos y el esquema de indexación. Sin embargo, dudaría que pueda ordenarlo más rápido del lado del cliente que en el servidor, pero eso no significa que ordenarlo en el servidor sea lo correcto.

ORDER BY obliga al RDBMS a ordenar.

La ordenación requiere recursos que pueden no estar presentes en su servidor RDBMS.

En algunos casos (es decir, consultas de tabla única) puede escribir un ORDER BY que coincida con los índices, y si su RDBMS garantiza que las tablas se mantienen en un orden de índice, podría ser de costo cero. [Un diseño de base de datos que depende de muchas consultas de tabla única se puede mejorar aún más descartando el RDBMS y usando archivos.]

Generalmente, ORDER BY tendrá que ordenar.

"Pensé que lograr que la base de datos lo hiciera por mí es la más eficiente".

Esa suposición es defectuosa. La base de datos no es necesariamente más eficiente que su programa fuera de la base de datos.

Ordenar en el cliente, en mi opinión, es algo que no debes hacer. Los motores de base de datos están optimizados para clasificar datos.

Al igual que otros, si puede limitar el número de filas que selecciona, se ejecutará más rápido.

Responder las siguientes preguntas podría ayudar a ir más allá:

¿Cuántas filas devuelve la consulta?
¿Cuántas columnas están siendo seleccionadas?
¿Te unes a alguna mesa?
¿Cuánto tiempo lleva con / sin el ORDER BY?

Tenga en cuenta que muchos editores de consulta mostrarán resultados después de que solo los primeros 50 o más hayan regresado de la base de datos.

Agregar un ORDER BY lo obligará a esperar en la base de datos todos los resultados, lo que revelará la velocidad real de la consulta.

En esos casos, la consulta original y la ORDERed son de la misma velocidad; simplemente te engañaron para que pensaras que el primero era rápido, porque tu editor fue rápido para obtener las 50 filas más o menos.

ORDER BY no es particularmente lento, especialmente si hay un índice en esa columna. En particular, si tiene un índice agrupado en esa columna, los datos ya están ordenados.

También puede usar paginación ( TOP o ROW_NUMBER ), etc. para ayudar.

Anoche realicé algunas pruebas de rendimiento en una base de datos más de tipo producción (no en la de desarrollador) y aquí está lo que encontré:

Total de filas en la tabla A: 13000

Total de filas en la tabla B: 5000

Filas devueltas por consulta de unión: 5000

Tiempo tomado si se usa con la cláusula ORDER BY: ~ 5.422 segundos

Tiempo tomado si no se utiliza la cláusula ORDER BY: ~ 5,334 segundos.

Por lo tanto, parecía que ORDER BY no hacía mucha diferencia. (Estoy de acuerdo con los pocos milisegundos agregados).

También probé estableciendo todos los valores de B.SYNTAX en NULL para asegurarme de que no era solo la latencia de la red con la transferencia de tantos datos.

¡Ahora eliminé B.SYNTAX de la cláusula SELECT y la consulta tomó solo 0.8 segundos!

Entonces parece que toda la columna CLOB es el cuello de botella. Lo que no significa que haya obtenido la solución para hacer esta consulta más rápida, pero al menos no dedicaré tiempo a escribir un algoritmo de clasificación.

Gracias a todos los que respondieron. Aprendí bastante y eso me llevó a probar algunas cosas diferentes.

No debería ser lento. Optimice su consulta y la estructura de su base de datos (al menos índices y estadísticas si es SQL Server). ¿Tal vez haya alguna otra cosa en su consulta que no sea ORDER BY que causa esta lentitud?

SELECT A.NAME, B.SYNTAX, B.DESCRIPTION, A.RATE1, A.RATE2, A.RATE3, A.STARTDATE, A.ENDDATE, A.HIDE, A.CATEGORYNAME FROM Table1 A JOIN Table2 B on A.Name = B.Name WHERE A.MODULENAME = @ModuleName AND A.PREVIOUS<>''N'' OR A.PREVIOUS IS NULL ORDER BY A.NAME

Opción 1

Si está cuestionando solo unas pocas columnas simples (2-4), puede incluirlas en el índice también. De esta forma, su consulta se ejecutará más rápido. También asegúrese de que el orden de clasificación en esa columna de índice coincida con el orden de clasificación en su consulta.

// if your query looks like this: SELECT [Name], [Title], [Count] ORDER BY [COUNT] // you can create an index on [Name], [Title], [Count]

Opción 3

Crea una view y vincúlala al schema . Luego consulta los datos de esa view .

Opción 3

Si usa SQL Server 2005 y obove, también puede intentar ejecutar su consulta en SQL Server Profiler y le recomendará el mejor índice y estadísticas que puede aplicar a su tabla para optimizar el rendimiento de esta consulta en particular.

Opción 4

Intenta reconstruir tus índices y estadísticas.

Opción 5

Puede intentar colocar su índice / tabla en un grupo de archivos diferente en un disco duro diferente.

Si está seleccionando pocas filas suficientes para mostrar, no es concebible que la cláusula ORDER BY tome una cantidad perceptible de tiempo a menos que esté limitando el número de filas devueltas con LIMIT o TOP .

Necesitamos más información. ¿Qué dbms? ¿Cómo se ve el plan de consulta? ¿Has mirado los planes de consulta con y sin ORDER BY ? ¿Qué diferencias ves?

EDITAR:

SELECT A.NAME, B.SYNTAX, B.DESCRIPTION, A.RATE1, A.RATE2, A.RATE3, A.STARTDATE, A.ENDDATE, A.HIDE, A.CATEGORYNAME FROM A, B WHERE A.MODULENAME=''"+loadedModuleName+"'' AND A.NAME = B.NAME AND (A.PREVIOUS<>''N'' OR A.PREVIOUS IS NULL) ORDER BY NAME

¿ NAME es la primary key ? ¿Hay un index en NAME ? Por sí mismo, o con otros campos? ¿En qué secuencia?
¿Cuántas filas se devuelven para un loadedModuleName?
Sospecho que la lentitud viene de " A.PREVIOUS <> ''N'' OR A.PREVIOUS IS NULL" Intenta usar (NOT A.PREVIOUS = ''N'') que creo que es equivalente y puede ayudar un poco.
Mida la consulta con y sin el ORDER BY y vea si el tiempo es diferente. No debería ser.

EDITAR:

Si NAME no es único en A o B , tu unión se A.NAME parcialmente balística cuando todas A.NAME instancias de A.NAME se B.NAME en B.NAME . Si 50 filas coinciden y 50 filas B coinciden, terminará con 2500 filas de resultados, que pueden no ser las que pretende.

ORDER BY no suele ser lento, siempre que la base de datos pueda encontrar un índice que se corresponda con la expresión ORDER BY .

Sin embargo, su instrucción SQL puede incluir otras cosas que fuercen a la base de datos a escanear toda la tabla antes de devolver los resultados, como SELECT TOP n

ACTUALIZACIÓN : como la consulta que publicó, creo que la mejor opción es considerar la consulta como buena porque:

Para algunas filas, no importa quién hace el trabajo. Entonces, lo más fácil para usted es usar el ORDER BY .
Para muchas filas, no deje que el cliente haga el trabajo: el RDMBS es más especializado y seguro que el servidor tiene más memoria y CPU.

Los consejos para las órdenes que debe considerar son:

ORDER BY es la ÚNICA manera de garantizar la clasificación en una consulta SQL.
El mejor trabajador en la clasificación es la base de datos en cualquier caso: ¡ASEGÚRESE DE ESTO!
Intenta minimizar la cardinalidad para las filas devueltas.
Crear índices según la consulta. Significa poner las columnas ordenadas al último en el índice.
Evite la indexación si la consulta es rápida.
Puede considerar que los índices están ordenados, luego, si ordena solo una tabla y tiene buenos índices, el orden puede haber costado cerca de cero.

Para obtener más reglas generales sobre los índices, busque esta otra pregunta de SO .

Si su filtro se ve así:

WHERE col1 = @value1 AND col2 = @value2 AND col3 = @value3 ORDER BY col4

, entonces necesitarás crear un índice en (col1, col2, col3, col4) .

El optimizador usará el índice tanto para filtrar los primeros tres valores como para ordenar por el cuarto.

Si no tiene dicho índice, entonces ocurrirá uno de los siguientes:

El optimizador utilizará un índice para filtrar en la condición WHERE , pero aún tendrá que ORDER las filas restantes.
El Optimizador usará un índice para ORDER los valores, pero TODOS los valores deberán ser considerados para filtrarlos.
El Optimizador no usará ningún índice, por lo que será necesario considerar los contras de 2 "TODOS los valores para filtrarlos" y 1 "todas las filas restantes deben pedirse" son verdaderas.