Eficiencia indices?¿? Como sacar lo mejor?

[CHiCoLiTa]

Buenas

estoy con SQL Server 2005 y me planteo una duda sobre los indices

Supongamos la base de datos de un callejero, en la que el usuario pueda buscar por lo que quiera
Tenemos, Via, Numero, Poblacion, Provincia, CP y asi hasta 13 campos diferentes por el que se puede hacer busquedas

Bien, ahora yo puedo buscar por cualquiera de estos terminos, de manera simple o compuesta, es decir, por CP y Via o por Provincia y Poblacion, o Via, Numero, Pblacion, CP o la combinacion que se me ocurra

Entonces pienso, si creo un indice sencillo por cada campo, el gestor de base de datos va a tardar en procesar cual es el indice que debe usar. Tambien me planteo la eficacia real de hacer eso (olvidandonos de inserciones y demas, solo buscamos eficacia de busqueda). Es realmente eficar hacerlo asi?

Tambien pienso que hacer un indice compuesto, con el primer campo Provincia, puesto que de una manera u otra voy a buscar por ese dato

Generando este ultimo indice compuesto, segun como sea la busqueda y que campos se quieran incluir me tarda apenas 1 segundo o se me alarga a mas de 1 minuto

Cual es la mejor manera de generar indices sobre un abanico de 13 campos que no se realmente cuales son los que va a entrar en la busqueda?
Cual es la forma optima de tratar esto?
O quizas mi planteamiento esta totalmente equivocado
Por que la primera busqueda tarda en realizarse y la segunda es mas rapida? Porque ya ha decidido el gestor que indice usar? porque lo tiene activo?

Se abre el debate

Ahh se me olvidaba. Dentro de la tabla hay tablas relacionadas como por ejemplo el ID_Provincia. Debo crear claves foraneas? Debo indexar a parte esas tablas? o con le multiple basta?

[Ñuño Martínez]

El funcionamiento de los índices es diferente en cada gestor de bases de datos, de ahí que cada uno tenga diferentes valores de rendimiento y unos sean "mejores" en ciertas situaciones y otros en otras.

Lo único que se puede hacer, creo yo, es que hagas pruebas de rendimiento tú mismo. Crea un programa que inserte unos cuantos cientos o miles de registros aleatorios y que luego haga consultas y te diga cuánto tiempo tarda. Luego cambias los índices, o los actualizas, y repites...

[CHiCoLiTa]

Empezado por Ñuño Martínez

Lo único que se puede hacer, creo yo, es que hagas pruebas de rendimiento tú mismo.

Eso es exactamente lo que estoy haciendo sobre una base de datos de 40 millones de registros.
Por eso preguntaba, porque cada cambio y prueba que me de valores reales tarda mucho.
Insertando menos registros en la tabla, las diferencias son minimas y apenas se aprecian

[Casimiro Notevi]

También debes pensar en la manera en que luego van a trabajar "en la vida real". Me explico: si lo normal es que den de alta un registro cada hora y que el resto del tiempo sea sólo consultas, entonces no sirve de nada el que hagas y monitorees un programita para insertar miles de registros de una vez, cuando es algo que no van a hacer.
Deberás simular la realidad de trabajo que va a tener la base de datos.

[CHiCoLiTa]

Creo que me he expresado mal.
Las pruebas y cambios que hago es sobre los indices. El callejero, salvo la alimentacion inicial de datos, no sufre variaciones hasta dentro de un par de años. Esto quiere decir que supuestamente la tabla no tiene inserciones ni modificaciones, solo se realizan busquedas sobre ella

Lo que decia es que probe con una tabla con menos registros

[mamcx]

Básicamente cualquier motor de datos tiene un buen sistema de manejo de indices y mas si es de gama medio o alta (sql server, oracle, postgress, etc).

Algunas ideas generales:

http://www.sql-server-performance.co...eneral_p1.aspx

Si necesitas ver que pasa con un indice usas el Sql profiler & el Index Tuning Wizard.


Primero que nada, no te pongas a agregar indices a la loca.

Un indice es como una tabla extra. Aumenta la CPU, el acceso I/O e implica hacer saltos entre los indices y la tabla fisica. Un numero excesivo de indices puede tener un impacto negativo.

Pero mas que nada, la eficiencia es en *como* almacenas los datos. El indice es solo una ayuda auxiliar. De hecho, a veces es posible que un indice de un resultado adverso o inutil (ej: Indexar sobre un campo booleano, donde casi al 50% uno es 0 y el otro es 1: No sirve pa nada de nada el indice).

Lo que debes saber es que existe una regla: optimizas por espacio, o por velocidad.

La forma de acelerar las consultas es expandir el dataset. Una BD es *muy* feliz si los datos se los dan mascaditos y lo unico que debe hacer es recorrer de arriba a abajo, saltando por medio de inidices sobre datos de longitud fija. Esa es el mejor caso.

Un ejemplo clasico son las fechas.

Una fecha se almacena por ejemplo asi: 01/01/2010. Ahora necesitas buscar los registros del año 2010. lo normal, una funcion tipo YEAR(fecha).... lo cual mata el indice (= no se usa!).

La forma de acelerar es partir la fecha en 3 columnas: Año, mes, dia e indexar si los cambios en cada una son variables... O sea, si en Año solo hay 2010, 2011 meter indice puede ser casi inutil.

En tal caso, mete los datos preordena los datos.. en vez de :

2011
2010
2011

Que esten

2010
2011
2011

(Eso es en Sql server un indice *clustered*).

En mi aplicacion de iphone, donde existen recursos mas limitados de CPu/Memoria ese solo cambio logro que una consulta pasara de unos 5-10 segundos a milisegundos. Sin meter indices!!

Otro ejemplo?

Otro cambio que me bajo de 10 segundos a milisegundos: Ordenar/Buscar por nombre.

Supongamos que debemos buscar todos los nombres que empiezan por 'A'. Supongase que existen 100 registros que cumplen, de 1 millon en total.

La forma pendeja es - pero practica en la gigantesca mayoria de los casos-:

Código SQL [-]

WHERE Left(Nombre,1)='A'

o

WHERE Nombre Like 'A%'

Dependiendo del motor, es una busqueda de 1 millon de registros.

Sin embargo, es mas eficiente si agregas una columna llamada IndexName y que guardas la primera letra. Ahora queda asi:

Código SQL [-]

WHERE  IndexNombre='A' AND Left(Nombre,1)='A'

o

WHERE IndexNombre='A' AND Nombre Like 'A%'

En *cualquier* motor, es una busqueda de 100 registros.

Eso acorta la consulta porque:

1) De entrada se filtra a solo lo que empieza por A en IndexNombre
2) Luego, la BD busca de esa porcion, el nombre

La gracia del truco se ve si Buscas por 'Alfo' para encontrar 'Alfonso'.

Ahora, primero se limita la busqueda a 100 registros y sobre ellos, se hace un table scan pa encontrar los 5 o 10 que sean alfonso.

----

En resumen, no es solo indices. Si quieres maximizar la eficiencia, es *como* almacenas los datos.

Mientras mas *plano* (menos funciones, menos joins, filtrar de izq a derecha lo mas global a lo mas concreto -o al revez, no se su es igual en cada motor).