Elegir mejor consulta de estas dos

[Jamati]

Hola, tanto tiempo que no escribiía.
Estoy empezando a trabajar con SQL Server 2005, algunos conocimientos tento, pero al momento de hacer una consulta al motor, me surge una gran duda.

Supongamos esta tabla:

COMPROBANTES
- codigo --> PRIMARY IDENTITY
- estado -->varchar(1)
- fecha --> smalldate
- descripcion

"estado" puede ser C (de confirmado) , A (de anulado) o P (de pendiente).
"fecha" se graba la fecha actual.

Esta tabla puede tener miles de registros, aumentando cada día.

Yo tengo que hacer una consulta para saber los COMPROBANTES con un "estado" = C , y en un determinado rango de "fechas".

No sé cual de estas dos formas es la mejor:

SELECT * FROM comprobantes WHERE estado = 'C' AND fecha BETWEEN .....

SELECT * FROM comprobantes WHERE fecha BETWEEN ..... AND estado = 'C'

Cual es más óptima para el motor ??
Obviamente, según cual sea la consulta que elija, armaré un índice, el cual será en el orden:
estado,fecha
o sino
fecha, estado
según cual sea la mejor consulta.

Desde ya muchas gracias a quien me pueda ayudar.

Saludos

[GeriReshef]

1. Ambos son correctos. El motor del SQL Server ya va a elegir con que empezar según las estadísticas.

2. Varchar(1) es absolutamente redundante: cambia lo en Char(1).

[Jamati]

Gracias GeriReshef,

Te hago 2 preguntas:

1) Si bien SQL elegirá como empezar, yo quiero saber como armo la clave, si primero fecha y después estado, o al revés.... ya que según como arme la clave, voy a armar el WHERE de la consulta. Por lo que entiendo, los índices o claves hay que armarlos escribiendo primero el campo que separa mas registros, y eso es lo que estoy tratando de averiguar, como conviene hacer la clave.

2) Es redundante, pero causa algún problema el VARCHAR(1) ??

Muchas gracias

[GeriReshef]

(Espero que puedas entender mis explicaciones a pesar de que mi castellano no es perfecto)

La recuperación sera mas eficiente con Clustered Index que Estado es su primera columna y Fecha es la segunda porque el sistema tiene que encontrar el estado 'C', despues la fecha que empieza el rango, y desde alla segir hasta la fecha que termina el rango.
De lo contrario (Fecha y despues Estado) el sistema va a buscar la fecha que empieza el rango, desde alla va a buscar el estado 'C', desde alla va a seguir al proximo 'C' mientras omitiendo los 'P' y los 'A' etc.
La inserción de datos a la tabla es mas eficiente si el Clustered Index empieza con la fecha, porque probablemente insertas cada día las filas del día en curso, y así el sistema las va a almacenar al final de la tabla.
Yo diría que lo armas primero con la fecha y despues el estado, pero me gustaría escushar las opiniones de los demas (¿Libras? ¿iislas?)

Varchar es mejor que Char cuando no sabes las longitudes de las cadenas. En este caso el sistema almacena con cada cadena la información sobre su longitud.
En Varchar(1) la longitud de todas las cadenas es 1, y es redundante almacenarla.
Esto no debe influir el rendimiento significamente, pero aun es redundante...

[Jamati]

Muchas gracias GeriReshef,

Vamos a esperar la opinión de personas mas capacitadas en SQL.

Igualmente, por ahora, según tu opinión, es mas eficiente armar el índice:
estado,fecha

Por ahora me quedo con eso.

[Libras]

y si armaras tu tabla con un cluster index por el codigo(que seria el campo de insercion por default) y un noncluster index combinando estado y fecha(estado,fecha), ahora lo que seria bueno revisar seria los planes de ejecucion de tus consultas para ver cual le resulta mas eficiente al motor de base de datos.

[GeriReshef]

Opto por fecha, estado.

[Jamati]

Hola Libras,

Ejecuté ambas consultas en el motor, mostrando el plan de ejecución, y en ambas salió el mismo resultado, tal como muestro en la imágen:

http://img856.imageshack.us/img856/8906/imageniv.jpg

Tengo creada dos claves solamente:
PRIMARY KEY (codigo)
fktest (estado,fecha)

No entiendo muy bien los planes, pero parecería que no está usando el índice.

[Libras]

los 2 planes estan usando el indice y parece ser que son igualmente optimos, lo que podrias revisar para confirmar son las entradas y salidas de estos planes :)

[Jamati]

No entiendo muy bien los planes de ejecución, pero como sabés que está usando el índice ?? .... yo tapé los datos originales, y aclaré con un texto agregado que ahí hacía referencia al nombre de la clave primaria.

[Libras]

sencillo porque tu plan de ejecucion dice que hace un cluster index que quiere decir que recorre tu tabla siguiendo el indice predefinido, si tuvieras un table scan entonces si habria problemas :)

[Jamati]

La mayoría de las consultas que hago, siempre dice lo mismo.

[Libras]

mientras tu consulta diga que hace cluster index en lugar de table scans esta bien si hace un table scan es donde esta el problema, ya que eso quiere decir que para buscar un valor N en la tabla tiene que recorrer toda la tabla :), ya si tienes loop join, merge join, hash loop etc, ya seria analizar tus consultas, cuando son selects sencillos de una sola tabla lo mejor es que veas un search index o un index scan :)

[GeriReshef]

El Clustered Index Scan (en lugar de Clustered Index Seek) significa que el sistema no aprovecha las ventajas del Clustered Index.

Un Clustered Index no significa que la tabla tiene un Clustered Index adjuntado,
sino- que la tabla es Clustered Index.

¿Cómo está construido el Clustered Index actual?

[Libras]

Pero si nos vamos a que se construyo un cluster index sobre el campo id y un non cluster sobre los otros 2 campos cuando haces una consulta select * from tabla esta bien que se vaya sobre el non cluster index, ya que si se hiciera una consulta select id from tabla entonces si tendria que irse sobre el cluster index, recuerden que para que un indice se tienen que seleccionar los campos del indice en el select y si es posible tambien que los campos del where sean los contenidos en el mismo.

[Jamati]

Muchas gracias GeriReshef por la colaboración.

Aprovechando que no entiendo mucho lo que me estás hablando, voy a recurrir a tus conociemintos.:

No entiendo lo que es una tabla Clustered Index.
Yo cree una neva tabla, con los campos necesarios, le asigné la PRIMARY KEY, un indíce (no único) que es el que comenté al principio de este post, y nada mas.

Según puedo ver, mi motor la mayoría de las veces, viendo el plan de ejecución, muestra Clustered Index Scan, por lo tanto, ahora lentamente me voy dando cuenta que algunas consultas puede que sean lentas por esto, ya que no aparece Clustered Index Seek.

Pero no sé que se refiere con estos términos.
Si hay alguna página bien explicada que conocés, te voy a pedir si me avisás así luego te puedo seguir con esta gran ayuda que me estás brindando.

Nuevamente mil gracias !!

[Libras]

http://odetocode.com/articles/70.aspx

Una pagina donde se explica que es un indice como trabaja etc, claro en ingles :P

[GeriReshef]

Vamos a suponer que tienes una tabla con cuatro columnas: A,B,C,D;
sin ningun índice.

Si ejecutas Select * From MiTabla o Select * From MiTabla Where ... el sistema tendra que ejecutar Table Scan, es decir - pasar todas las filas de la tabla para encontrar lo que buscas (como Libras ya mencionó).

Ahora vamos a suponer que creas un Clustered Index con la columna A,
y un Index (Non Clustered) con las columnas B, C.
Select * From MiTabla el sistema va a hacer un Clustered Index Scan en lugar de Table Scan porque ahora la tabla esta almacenada ordenada a base a columna A (=Clustered Index).
Select * From MiTabla Where A=... el sistema va a ejecutar un Clustered Index Seek que significa que va a aprovechar el Clustered Index para encontrar las filas deseadas directamente sin tener que pasar todas las filas.
Select B,C From MiTabla Where B=... And C=... el sistema va a ejecutar un Index Seek para encontrar las filas con el índice.
Select * From MiTabla Where D=... el sistema va a ejecutar un Clustered Index Scan. (¿sabes porque?)
Select * From MiTabla Where B=... And C=... el sistema tendrá que elegir entre Clustered Index Scan y Index Seek + Clustered Index Seek porque el Index no incluye todas las columnas, sino- solamente B & C.

[Jamati]

Se nota tus conocimientos GeriReshef...
Antes que nada, muchísimas gracias.

A ver, siguiendo con tu ejemplo:

Select A,D From MiTabla Where B=... And C=... ejecuta un Index Seek ?? porque las condiciones del WHERE están en un índice.

Me interesa este tema ya que tengo varias tablas, y muchas de ellas con 20 columnas aproximadamente. Y al hacer consultas, elijo por ejemplo 10 columnas solamente, es decir, no todas las columnas que elijo están dentro de un índice, por lo tanto, puedo dedudir que los índices sirven solamente para cuando en el SELECT están esos dos campos, es así ??

Muy buena la web que envió Libras, lástma que está en inglés, pero es muy interesante, aunque aún así se me complica entender bien el tema de los índices o como trabaja el Motor SQL respecto a los índices.

Yo tenía entendido lo siguiente: todo lo que hay después del WHERE en una consulta, si son consultas que se hacen muy seguido en el sistema, conviene que estén en claves o índices, en el mismo orden, sin importar las columnas que se quiera recuperar.
Por ejemplo, una consulta:
SELECT * FROM mitabla WHERE A = ...... B = .....
Y si esa consulta puede tener muchos resultados, conviene hacer un índice de la siguiente forma: INDICE(A,B) y no INDICE(B,A)... y eso me iba a acelerar muchísimo la velocidad de respuesta del motor, ya que como tengo un índice en el mismo orden que el WHERE, el motor encuentra todo esos resultados mucho más rápido.

Parece que estoy un poco equivocado, es complicado de entender.

Igualmente, muchísimas gracias !!

[iislas]

Jamati, si aun no te queda claro el MANEJO DE INDICES en SQL Server (y otros motores de datos), ve a YOUTUBE y tecleas MANEJO DE INDICES, INDICES AGRUPADOS, INDICES PRIMARIOS, etc, y veras que hay muchos videos donde se explica el tema, muchos por cierto en español.

[Jamati]

Gracias iislas,

Me estuve fijando en Youtube, pero todos los videos que encontré, te enseñan como crear índices y dan una explicación muy básica.

Mientras tanto, la consulta mía anterior a este mensaje, no la puedo resolver con exactitud, me sigue quedando la duda.

Muchísimas gracias igual.

Saludos