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.

[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í.