busquedas lentas una subcadena en campo memo sqlite

[juank1971]

Saludos:
He retomado hoy un viejo proyecto que busco en una base de datos gigante en sqlite dentro de un campo de tipo "text" llamado contenido .

con esta consulta :

Código Delphi [-]
 ASQL :=  ' select id_fichero from ficheros where contenido like' + ''''+'%2n3055%'+''''+
                 ' order by id_fichero  limit 10'  ;

no logro agilizar esa búsqueda lo suficiente como para que no se desespere un cliente, se demora ya que la base de daos es muy grande 4gb.

Estoy usando delphi xe4 y estoy conectandome con los componentes TADConnection y TADQuery de los firedac

alguna sugerencia
gracias

[Casimiro Notevi]

No sé si podrás optimizarla mediante algún índice o algo así.
De todas formas, ¡¡¡ a quién se le ocurre usar una BD de juguete para estas cosas !!!

[juank1971]

Gracias por responder tan rapido:
ja.. me parece que tu apellido debiera ser casimiro sitevi porque ves todo rapidísimo.

El problema es que como indexo? no se me ocurre como indexar un campo TEXT.

y lo de base de datos de jugete dame otra idea tu sabes que delphi es facil de cambiar el acceso a otro motor de datos,
lo que necesito que sea base de datos local y portable, y si es posible que no se instale nada y mono usuario me sirve.

en concreto estoy guardando miles de ficheros mayormente PDF, que le extraigo el texto antes de guardarlos y lo tengo en ese campo TEXT de la base de datos
y así poder disponer de toda la información
entonces puedo hacer búsquedas por el contenido de los ficheros en ese campo texto. pero a la ves tambien tengo guardados los ficheros que me los llevo a donde sea como una gran biblioteca.

Si me das una sugerencia de otro motor de base de datos que no se instale y funcione portable te lo agradezco

el problema es que solo me llevo la base de datos sqlite y el ejecutable y ahora con la conexión en firedac no me hace falta ni dll de sqlite solo el ejecutable y la base de datos.

estuve probando con hilos y funciona mejor que pero se demora un poco también, o sea mando a ejecutar la consulta en un hilo y le pongo limit 2 cuando encuentra los dos primeros registros manda a buscar los otros 2 en otra consulta con otro hilo y la por lo menos veo aparecer dos registros y así sucesivamente.

no se si hay algun firebird o interbase o lo que sea local y sin instalar o al menos que no sea compleja la instalación para el usuario.
gracias

[Casimiro Notevi]

¿No se puede crear un índice en ese campo?

Puedes probar con firebird, que tiene una versión "embedded"

[juank1971]

ok voy a probar pero si hay firebird embebido supongo sea mucho mejor que sqlite, gracias por tu tiempo

[Casimiro Notevi]

No sé cómo te irá para lo que quieres hacer, es algo un poco "especial".
Prueba a ver qué tal te va.

[juank1971]

Casimiro , saludos de nuevo gracias por le consejo, tienes toda la razón estaba trabajando con una base de datos de juguete,
la base de datos Firebird se comporta de mil maneras muchisimo mas rápido que sqlite, hasta ahora no he probado la embebida,
solo instalando en mi pc local el servidor y la famosa consulta en una base de 4bg de tamaño en campos de texto, se tomo unos milisegundos en firebird,
sin embargo la misma consulta en la misma cantidad de datos en sqlite se demora casi un minuto.

o sea me quedo con firebird, ademas es muchisimo mas robusta, tiene casi todo lo necesario importante, vistas,procedimientos almacenados ,triggers, la veo muy completa,
solo veo un poco pobre la documentación casi toda es online no importa que este en ingles lo que me importaría es que sea descargable y no encuentro mucho, no he encontrado nada bastante completo para bajarlo y estudiarlo en casa que no tengo conexión de noche.

Gracias muchas,

juank

[ecfisa]

Hola juank1971.

Empezado por juank1971

...
o sea me quedo con firebird, ademas es muchisimo mas robusta, tiene casi todo lo necesario importante, vistas,procedimientos almacenados ,triggers, la veo muy completa...
...

Totalmente de acuerdo

...
solo veo un poco pobre la documentación casi toda es online no importa que este en ingles lo que me importaría es que sea descargable y no encuentro mucho, no he encontrado nada bastante completo para bajarlo y estudiarlo en casa que no tengo conexión de noche.
...

Algunos enlaces como para comenzar:

• Firebird SQL Reference Guide
• Documentación Firebird
• El lenguaje SQL de Firebird
• Documentos
• ...

Si podes acceder a él, un excelente libro es The Firebird Book de Hellen Borrie (ISBN-10: 1590592794 - ISBN-13: 978-1590592793).

Saludos

[mamcx]

Empezado por Casimiro Notevi

De todas formas, ¡¡¡ a quién se le ocurre usar una BD de juguete para estas cosas !!!

Juguete? Sqlite es una BD muy capaz, para sus casos de uso.

Aunque es cierto que FB por ser una BD servidora tiene mas funcionalidad, el problema que se expone realmente como lo va a ser mejor?

En *CUALQUIER* motor de BD es el mismo y exacto problema. Esto es un caso patologico para cualquier BD relacional:

1- Hay muchos registros
2- Es Texto
3- Lo que se busca esta en cualquier parte del campo
4- No hay indice

Por lo tanto, hay un TABLE SCAN forzado. Y es mas, apuesto (aun sin hacer pruebas) que SQLITE es mas rapido que cualquier otro motor *precisamente* porque es una BD local y no una servidor que tiene que hacer todo por protocolos de red remotos.

Ademas, el usar un indice aqui dificilmente va a servir de NADA:

http://stackoverflow.com/questions/9...ebird-database
http://web.utk.edu/~jplyon/sqlite/SQ...ation_FAQ.html

FB & Sqlite (Y no veo que BD sea capaz) tienen el mismo problema al hacer busquedas donde el texto a encontrar estan en cuaquier parte, y solo (posiblemente) pueden usar indice si el LIKE es asi: 'Prefijo%', pero falla si es asi: '%Sufjio' o peor '%QuienSabe%'.

Asi que hacer un cambio de codigo/bd/tecnologia por esto solo no servira de mucho.

Cual es la solucion?

Una idea se da en:
http://web.utk.edu/~jplyon/sqlite/SQ...ation_FAQ.html

Código SQL [-]
If a * or % wildcard occurs in the middle of a string, or a complex pattern appears at the end, we can still use the above techniques to create a filter test which is done before the more expensive pattern test.
Example: The expression (x GLOB 'abc*jkl*') can be replaced by (x > 'abc' AND x < 'abd' AND x GLOB 'abc*jkl*').
Example: The expression (x LIKE 'abc_') can be replaced by (x > 'abc' AND x < 'abd' AND length(x) == 4)

La otra, mas simple es usar Full Text Search (un punto extra por Sqlite: FB no tiene una solucion FTS INTEGRADA):

https://www.sqlite.org/fts3.html

Con esto, las busquedas se resuelven en milisegundos, con el trade-off de que la BD crecera un poco mas por el uso de FTS.

El asunto es que como dice la teoria, uno escoje optimizar TAMAÑO o optimizar TIEMPO y no se puede ambas, asi que aparte de hacer malabares con los querys, usar FTS o encontrar una forma de almacenar el texto emulando una estructura de datos (ej: Cada palabra se guarda en una tabla separada con relacion a la tabla donde esta el texto "grande", o se usa algo similar a un ROPE (https://en.wikipedia.org/wiki/Rope_(data_structure)) u otra cosa) no hay vuelta con esto.

[juank1971]

Gracias amigos por defenderse me gusta eso. en la polémica salen las mejores soluciones.

Evidentemente mamcx se ve que dominas el tema de sqlite, es que lo dijo bill gates las personas llegara el dia que no se mataran por dinero sino por la información , de verdad me fue tanto difícil llegar a ese despliegue de razones que me disparaste de ráfaga, yo había buscado un poco y no logré mucho avance en sqlite3, y cuando probé firebird me sorprendió la velocidad de respuesta , ademas lo uso de la misma manera embebido o sea una sola base de datos mono usuario nunca vi nada de lo que me comentas en sqlite , voy a investigar y te cuento como me va.

En concreto lo que estoy trabajando es en un sistema para llevar todo lo referente a un tema específicamente de electrónica, llevarlo portable a donde quieras y disponer cuanto necesites lo mas rápido posible, una enciclopedia.

La esencia es guardar todo lo que el cliente le llega de esos temas .doc,xls,pdf,html en ese mismo formato, o sea todo lo que tiene y agregar cuando desee cualquier fichero.
entonces la aplicacion solamente tiene un ejecutable y una base de datos que la tenia sqlite, y un campo text que lo que hace es que a la hora de importar los ficheros a la base de datos extrae el texto de todos los ficheros y lo guarda en un campo de la base de datos.

las busquedas entonces son de las mas malas de hacer o sea %QuienSabe%

Gracias amigos por sus respuestas siempre se puede exprimir algo un poco mas, voy a ver si le saco mas jugo a la naranja de sqlite pero te advierto que el firebird de la primera probada me dejo un buen sabor.

depues pongo los resultados si es que logro entender bien lo que me dejo de tarea extra clase el amigo mamcx


juank

[Casimiro Notevi]

Empezado por mamcx

Juguete? Sqlite es una BD muy capaz, para sus casos de uso.

Sí, es un jueguetito. Nada más.
No es una base de datos relacional. No es multiusuario. Tiene unas grandes limitaciones con los triggers. Stored procedures, ni saben lo que es. Claves foráneas, mediante inventos por código. Tipos de datos... para qué hablar de eso. Y no sigo.
Sqlite está muy bien para lo que es, para una agenda de contactos, para una BD simple y cosas así. Si quieres hacer algo "serio" te mueres de pena con tantos problemas.

Por cierto, con firebird, desde 2008 hay un "addon/pluggin/soft de terceros" que permite "Full Text Search". Y me parece, creo recordar, que en las últimas versiones de FB ya iba implementado.

Y de todas formas, no hay comparación posible, yo vengo de usar firebird desde que se creó y este último año he estado usando sqlite... ni punto de comparación en nada, sqlite es como un DBF más moderno, nada más.

[roman]

Casimiro,

En esta estoy con Mario. Es decir, yo creo que ambos tienen razón pero a veces como que se nos va un poco la defensa a ultranza (que también Mario la hace ) Pero a ver, él claramente dice: Juguete? Sqlite es una BD muy capaz, para sus casos de uso. El que no haga todo lo que hace FB o carezca de procedimientos almacenados, disparadores, etc. no la convierte en un juguete. Como decía un conocido; hay casos que incluso Excel te los resuelve; y eso no convierte a Excel en un juguete ni quien lo hace debe sentir pena. Y mucho menos con sqlite.

// Saludos

[Casimiro Notevi]

Empezado por roman

Casimiro,

Sí, tienes razón, por eso digo:

Sqlite está muy bien para lo que es, para una agenda de contactos, para una BD simple y cosas así.

[juank1971]

bueno amigos realmente el final de la polémica es lograr algo mas concreto, hacer que funcione.

Casimiro he estado mirando pero no logro aterrizar que que quiero todavia , estaba mirando lo de Sphinx Full Text Search , dime si has probado algo de eso ya que tienes mas experiencia que yo en el firebird. en sphinx-0_9_8_1_Firebird_Win32 hay algunos ejecutables pero me quede un poco perdido en la pequeña explicacion que dan, has visto algo de eso u otra manera de implementar lo del full text Search.

gracias juank

[mamcx]

Algo que falta: Que es lo que esta almacenado en ese campo? Podrias dar ejemplos? En especial los peores casos

[Casimiro Notevi]

Empezado por juank1971

bueno amigos realmente el final de la polémica es lograr algo mas concreto, hacer que funcione.

¿Pero no lo habías instalado, probado y funcionaba muy bien?
Si es así, ¿para qué quieres instalar lo otro?

[juank1971]

en el campo contenido tengo almacenado el texto de cada fichero por ejemplo en un manual de un equipo de radio es un pdf de 200 paginas extraigo el texto de cada pagina lo concateno quitando lineas en blanco y todo ese texto lo guardo en el campo , entonces si quieres finalmente hacer una busqueda por el contenido de los ficheros puedes hacer la que hemos estado hablando y logras encontrar digamos que ficheros hablan de un transistor específico, cosa la cual prácticamente no se puede hacer en windows buscar dentro de una carpeta de miles de ficheros pdf,word,xls etc. ademas lo otro bueno que tiene la aplicación es que tienes todo los ficheros que necesitas para el trabajo en un solo lugar y lo puedes transporta a cualquier parte teniendo siempre una biblioteca de electrónica portable que se puede incrementar cuando se desee, no es otra cosa que un gran saco lleno de ficheros pdf,word etc con unas herramientas de búsquedas en su contenido.

solamente tres tablas tengo

Código SQL [-]
CREATE TABLE "Ficheros" (
"Id_Fichero"  INTEGER,
"Fichero"  BLOB,
"contenido"  text,
PRIMARY KEY ("Id_Fichero" ASC)
);

otra con los nombres de los ficheros y otra con el nombre de las carpetas y la estructura en arbol que habia en windows.

esa es la idea les pongo una capura de pantalla de la aplicación


y lo otro casimiro es que pensé en hacer la llegar un poco mas lejos y hacer un tipo de filtro mientras se escribe, o sea en el keyup y hacer una búsqueda mas instantánea según escribas las letras , estaba mirando lo del limit y en el caso de sqlite esta también el offset , probe esto

Código SQL [-]
select id_fichero  from ficheros  where contenido like 
         '%2n3055%' order by id_fichero limit 2 OFFSET 1

despues haces esto 

select id_fichero  from ficheros  where contenido like 
         '%2n3055%' order by id_fichero limit 2 OFFSET 4
despues 
select id_fichero  from ficheros  where contenido like 
         '%2n3055%' order by id_fichero limit 2 OFFSET 6

y así sucesivamente y te van apareciendo los ficheros encontrados primero muestras 2 luego encuentras 2 mas que serian 4 pero son los 2 que le siguen a los primeros 2 encontrados
eso lo estaba probando con hilos y me va encontrando y sumando al resultado poco a poco lo que va encontrando hasta que devuelve una consulta vacia que ya no hay mas registros que cumplan la condición.
nada complicándome la vida. saludos jk

[Casimiro Notevi]

¿En lugar de like, has probado con containing?

[mamcx]

Ah, en tu caso definitivamente FTS es la opcion. En ese caso el soporte de sqlite es decente, pero el de postgress es mucho mejor:

http://www.postgresql.org/docs/9.3/s...rch-intro.html

No se en tal caso que tan bueno sea FB, asi que se lo dejo a los que saben del tema..

[Casimiro Notevi]

Si ya tienes el sistema hecho con sqlite, si funciona bien, sigue usándolo.
Postgresql no te sirve porque necesita instalación, no es "embebido"