Consulta lenta - ¿índices?

[pedamarcos]

Hola a todos,

Mi problema es el siguiente: imaginen la siguiente consulta:

SELECT TOP 1 * FROM Tabla WHERE Campo1 = 1049 AND FechaHora < '05/08/2011' AND Campo2 > 0 ORDER BY FechaHora DESC

La tabla Tabla dispone de un índice del tipo:
PK_Campo nonclustered located on PRIMARY FechaHora, Campo1, Campo2

Los valores del Campo1 se almacenan en otra tabla Tabla2.

Al principio, en Tabla2 sólo había valores de Campo1 de 1 a 100. La sentencia se ejecutaba rápidamente.
Hace unos meses se introducen valores de Campo1 de 1000 a 1100. La sentencia se ejecuta lentamente sólo para estos valores altos, para los valores de 1 a 100 sigue ejecutándose rápidamente.

Siendo la misma tabla Tabla y el mismo índice PK_Campo, pero obteniéndose distintos tiempos de respuesta, entiendo que el problema reside en que el índice no está "actuando" para los nuevos valores de Campo1.

Por favor, agradecería ayuda en este tema, saber qué opináis y qué solución puedo darle.

¿Hay alguna manera de saber cómo de bien está funcionando un índice de una tabla? Para saber si hay que recalcularlo o no.

Si necesitáis más datos o no entendéis alguna cosa, no dudéis en preguntarme.

Muchas gracias de antemano.

[Libras]

Revisa el plan de ejecucion de tu query (ctrl+L) y ve que es lo que esta pasando si tu consulta ejecuta un scan en lugade de un index scan o un seek index, entonces esta pasando algo con tu indice o con tus datos.Si es necesario agrega nonclustered index

Saludos!

[pedamarcos]

He revisado el plan de ejecución de la sentencia para un valor 99 de Campo1 y para un valor 1049 de Campo1, y se obtienen resultados distintos. Igual es lógico, pero a mí me sorprende ésto, no se qué opináis.
Diferencias principales:
1. Campo1 99. Hace referencia a la clave principal, PK_Tabla y al índice PK_Campo, ambos con un costo de 47%, lo cual tiene sentido.
2. Campo1 1049. Existen dos items más, Filter y Parallelism, ambos con costo 0%. Hace referencia a la clave prinicipal, PK_Tabla (con costo 0%). Por último, no sólo no hace referencia al índice PK_Campo, sino que hace referencia a otro índice PK_Campo3, que es del tipo:
PK_Campo3 nonclustered located on PRIMARY FechaHora, Campo1, Campo3
Ésto último no parece tener mucho sentido.
Parece que estamos llegando al fondo del asunto.
¿Cómo se soluciona ésto? Gracias.

[Libras]

ya probaste poniendo un nonclustered index en el campo fecha hora? porque estas haciendo un order by por un campo que no tienes indexado y esto te consume tiempo, del noncluster que mencionas creo que lo hace porque estas tratando de obtener valores que hacen referencia a esos 3 campos, porque un select *? si sabes que un indice funciona mejor cuando metes al from y al where los campos que estan indexados??

Saludos!

[pedamarcos]

Iniciado por Libras

ya probaste poniendo un nonclustered index en el campo fecha hora? porque estas haciendo un order by por un campo que no tienes indexado y esto te consume tiempo

Se me olvidó mencionar que la tabla Tabla tiene una clave principal PK_Tabla del tipo:
PK_Tabla clustered, unique, primary key located on PRIMARY FechaHora, Campo1
Por tanto, creo que puedo hacer un Order By FechaHora, que no debe ir lento, al formar parte de la clave principal, ¿o sí? Si no te he entendido mal, me recomiendas que genere un índice non clustered sólo con la FechaHora, por el tema del Order By.

Iniciado por Libras

del noncluster que mencionas creo que lo hace porque estas tratando de obtener valores que hacen referencia a esos 3 campos

En la sentencia SQL sólo se hace referencia a Campo1, Campo2 y FechaHora, cómo no sea que te refieres al * para recoger todos los campos.

Iniciado por Libras

porque un select *? si sabes que un indice funciona mejor cuando metes al from y al where los campos que estan indexados??

Tienes toda la razón. Pero cuál es mi sorpresa cuando ejecuto la misma sentencia pero seleccionando campos en el Select y tarda igual o más que con el Select *:
SELECT TOP 1 Campo2 FROM Tabla WHERE Campo1 = 1049 AND FechaHora < '05/08/2011' AND Campo2 > 0 ORDER BY FechaHora DESC

Yo sigo pensando que el índice PK_Campo1 no se está aplicando para ciertos valores, ¿es posible lo que digo?
¿Cómo es posible recalcular un índice de una tabla en SQL Server Express?

Gracias de nuevo.

[Libras]

campo2 esta indexado??

[pedamarcos]

Sí, con el índice que recoge los campos FechaHora, Campo1 y Campo2:
PK_Campo nonclustered located on PRIMARY FechaHora, Campo1, Campo2

No hay un índice para el Campo2 únicamente.

[Libras]

a ver cuando dices que se tarda te refieres a que el con el numero 99 tarda 1 segundo y con el 1049 tarda 10?? o es significativo??

ejecuta esto:

set statistics io on;
tu query
set statistics io off;

y manda tu resultado :)

[iislas]

Aqui hay un CAST implicito: AND FechaHora < '05/08/2011'

Debe ser AND FechaHora < '20110805'

[pedamarcos]

Iniciado por Libras

a ver cuando dices que se tarda te refieres a que el con el numero 99 tarda 1 segundo y con el 1049 tarda 10?? o es significativo??

ejecuta esto:

set statistics io on;
tu query
set statistics io off;

y manda tu resultado :)

Con el número 99 tarda 1 segundo, y con el 1049 tarda entre 18 y 22 segundos, muy significativo.
Enviaré las estadísticas que dices.

[pedamarcos]

Iniciado por iislas

Aqui hay un CAST implicito: AND FechaHora < '05/08/2011'

Debe ser AND FechaHora < '20110805'

Lo probaré, gracias.
De todas formas, el retardo en la obtención de resultados depende del valor del Campo1, independientemente de la FechaHora, por lo que no creo que sea éste el motivo.

[pedamarcos]

Iniciado por pedamarcos

Con el número 99 tarda 1 segundo, y con el 1049 tarda entre 18 y 22 segundos, muy significativo.
Enviaré las estadísticas que dices.

Ejecutado:

set statistics io on;
SELECT TOP 1 * FROM Tabla WHERE Campo1 = 99 AND FechaHora < '05/08/2011' AND Campo2 > 0 ORDER BY FechaHora DESC
SELECT TOP 1 * FROM Tabla WHERE Campo1 = 1049 AND FechaHora < '05/08/2011' AND Campo2 > 0 ORDER BY FechaHora DESC
set statistics io off;

Resultado:

(1 filas afectadas)
Tabla 'Tabla'. Recuento de exploraciones 1, lecturas lógicas 19, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

(1 filas afectadas)
Tabla 'Worktable'. Recuento de exploraciones 0, lecturas lógicas 0, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.
Tabla 'Tabla'. Recuento de exploraciones 2, lecturas lógicas 18225, lecturas físicas 16, lecturas anticipadas 18867, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

No se cuál es la tabla Worktable, será del sistema.

[pedamarcos]

Finalmente he decidido regenerar el índice PK_Campo.
Como había muchos datos en la tabla Tabla (más de 30 millones de registros) y esta acción iba a durar bastantes horas, dejando la bbdd inoperativa, primero he borrado datos, dejando aproximadamente 4 millones de registros.
Simplemente con esta acción de limpieza ha sido suficiente para que tanto la consulta con valor 99 como la consulta con valor 1049 no dure prácticamente nada.
Por tanto, parece que el origen del problema era la cantidad ingente de información en la tabla Tabla.

[iislas]

Aqui estaba tu problema: lecturas lógicas 18225,

Creo que debes implementar un job que te REGENERE indices por lo menos cada domingo.

[pedamarcos]

Iniciado por iislas

Aqui estaba tu problema: lecturas lógicas 18225,

Creo que debes implementar un job que te REGENERE indices por lo menos cada domingo.

¿Me podrían explicar el significado del concepto lecturas lógicas?
Gracias.

[iislas]

Bueno, el NUMERO DE LECTURAS debe ser bajo, en dependencia con los registros a obtener, segun esta descripcion:

Tabla 'Tabla'. Recuento de exploraciones 2, lecturas lógicas 18225, lecturas físicas 16, lecturas anticipadas 18867, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Son demasiadas LECTURAS las que estas teniendo y todo puede deberse a la falta de indices o bien, que estos esten dañados.

¿Puedes hacernos llegar el SP_HELP de "Tabla"?, o bien decirnos si tiene indices.

[pedamarcos]

Iniciado por iislas

Bueno, el NUMERO DE LECTURAS debe ser bajo, en dependencia con los registros a obtener, segun esta descripcion:

Tabla 'Tabla'. Recuento de exploraciones 2, lecturas lógicas 18225, lecturas físicas 16, lecturas anticipadas 18867, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

Son demasiadas LECTURAS las que estas teniendo y todo puede deberse a la falta de indices o bien, que estos esten dañados.

¿Puedes hacernos llegar el SP_HELP de "Tabla"?, o bien decirnos si tiene indices.

Gracias.
Después de borrar datos y dejar poco más de 4 millones de registros en la tabla Tabla, calculo las estadísticas de ejecución y obtengo lo siguiente:

set statistics io on;
SELECT TOP 1 * FROM Tabla WHERE Campo1 = 99 AND FechaHora < '05/08/2011' AND Campo2 > 0 ORDER BY FechaHora DESC
SELECT TOP 1 * FROM Tabla WHERE Campo1 = 1049 AND FechaHora < '05/08/2011' AND Campo2 > 0 ORDER BY FechaHora DESC
set statistics io off;

Resultado:

(1 filas afectadas)
Tabla 'Tabla'. Recuento de exploraciones 1, lecturas lógicas 24, lecturas físicas 2, lecturas anticipadas 517, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

(1 filas afectadas)
Tabla 'Tabla'. Recuento de exploraciones 1, lecturas lógicas 15, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de LOB 0, lecturas físicas de LOB 0, lecturas anticipadas de LOB 0.

El tiempo de respuesta de ambas consultas es despreciable.


Índices según sp_help @objname = 'Tabla':
PK_Campo3 nonclustered located on PRIMARY FechaHora, Campo1, Campo3
PK_Campo nonclustered located on PRIMARY FechaHora, Campo1, Campo2
PK_Campo4 nonclustered located on PRIMARY FechaHora, Campo1, Campo4
PK_Campo5 nonclustered located on PRIMARY FechaHora, Campo1, Campo5
PK_Campo6 nonclustered located on PRIMARY FechaHora, Campo1, Campo6
PK_Tabla clustered, unique, primary key located on PRIMARY FechaHora, Campo1

[Libras]

Pues a mi parecer esos indices estan mal que no quedaria mejor asi:

PK_Campo3 nonclustered located on PRIMARY campo3
PK_Campo nonclustered located on PRIMARY campo2
PK_Campo2 nonclustered located on PRIMARY campo1
PK_Campo4 nonclustered located on PRIMARY campo4
PK_Campo5 nonclustered located on PRIMARY campo5
PK_Campo6 nonclustered located on PRIMARY Campo6
PK_Tabla clustered, unique, primary key located on PRIMARY FechaHora

Tu que opinas iislas??

Saludos!

[iislas]

¿Cuantos registros tienes en tu tabla, de esos, cuantos obtienes con tu query?, Es para medir que tan DESPRECIABLE puede ser tu tiempo de respuesta.

[senna5]

Yo tengo el mismo problema tengo 5 millones de registros y hago busqueda por rango de fechas ejemplo 2011-08-01 al 2011-08-25 con esta busqueda la consulta sera de mas de 2 millones de registros y tarda 30 min tengo indice pk a la fecha

Código:

SELECT  A.ID_GUIA
			  ,	A.ID_CUENTA
			  , A.ID_PRODUCTO
			  , A.NB_ORIGEN
			  , A.NB_DESTINO
			  , CONVERT(CHAR(10),A.FH_CAPTURA,103) AS FH_CAPTURA 
			  , CONVERT(CHAR(10),A.FH_SCL,103) AS FH_SCL 
			  , A.NB_SCL
			  , CONVERT(CHAR(10),A.FH_NEWFIN,103) AS FH_NEWFIN
			  , A.NB_NEWFIN
			  , CONVERT(CHAR(10),A.FH_VOLTA,103) AS FH_VOLTA
			  , A.NB_VOLTA
			  , CONVERT(CHAR(10),B.FH_RECIBIDO,103) AS FH_DVRA
			  , B.NB_ARCHIVO AS NB_DVRA
			  , CONVERT(CHAR(10),A.FH_RARG,103) AS FH_RARG
			  , A.NB_RARG
			  , CONVERT(CHAR(10),A.FH_IBS,103) AS FH_IBS
			  , A.NB_IBS
			  , CONVERT(CHAR(10),C.FH_SHIP,103) AS FH_DSD
			  , A.NB_DSD
			  --, D.ID_MAJOR
		--INTO #TMPPASO
		FROM   MM_GUIAS A(nolock)
		--INNER JOIN #TmpRowsNum E ON A.ID_GUIA = E.ID_GUIA AND A.ID_CUENTA = E.ID_CUENTA AND A.ID_CUENTA_VTA = E.ID_CUENTA_VTA AND A.ID_PRODUCTO = E.ID_PRODUCTO AND A.NB_ORIGEN = E.NB_ORIGEN AND A.NB_DESTINO = E.NB_DESTINO AND A.FH_CAPTURA = E.FH_CAPTURA
		LEFT OUTER JOIN DD_DVRA B(nolock) ON A.ID_GUIA = B.ID_GUIA AND A.ID_CUENTA = B.ID_CUENTA AND A.ID_PRODUCTO = B.ID_PRODUCTO AND A.NB_ORIGEN = B.NB_ORIGEN AND A.NB_DESTINO = B.NB_DESTINO
		LEFT OUTER JOIN DD_DSD C(nolock) ON A.ID_GUIA = C.ID_GUIA AND A.ID_CUENTA = C.ID_CUENTA AND A.ID_PRODUCTO = C.ID_PRODUCTO AND A.NB_ORIGEN = C.NB_ORIGEN AND A.NB_DESTINO = C.NB_DESTINO
		--LEFT OUTER JOIN MAJOR_ACCNT D(nolock) ON A.ID_CUENTA = D.ID_CUENTA AND D.ID_MAJOR = ISNULL (@ID_MAJOR, D.ID_MAJOR) 
		WHERE	A.FH_SCL		>= CAST ('2011-08-01' AS DATETIME)
			AND A.FH_SCL		<= CAST ('2011-08-24' AS DATETIME)

[iislas]

senna5

No hagas CAST en la fecha, ya que haces un cast implicito a tu columna, simplemente compara

WHERE A.FH_SCL >= '20110801' AND A.FH.SCL <= '20110824'

¿Tu campo FH_SCL es un PK?

[senna5]

no, no tengo pk ya quite los cast y la consulta sigue tardando una eternidad.
Tengo indices.

[iislas]

Pues muestranos los campos que son indices, de entrada de aclaro, todos aquellos campos despues del WHERE............... Son candidatos a ser indices.