texto - string_split sql

¿La forma más rápida de encontrar cadenas por subcadenas en SQL? (7)

Tengo una mesa enorme con 2 columnas: Id y Título. Id es bigint y puedo elegir libremente el tipo de columna Título: varchar, char, text, lo que sea. El título de la columna contiene cadenas de texto aleatorias como "abcdefg", "q", "allyourbasebelongtous" con un máximo de 255 caracteres.

Mi tarea es conseguir cadenas por subcadena dada. Las subcadenas también tienen una longitud aleatoria y pueden ser inicio, medio o final de las cadenas. La forma más obvia de realizarlo:

SELECT * FROM t LIKE ''%abc%''

No me importa INSERTAR, solo necesito hacer selecciones rápidas. ¿Qué puedo hacer para realizar la búsqueda lo más rápido posible?

Utilizo MS SQL Server 2008 R2, la búsqueda de texto completo será inútil, por lo que veo.

Use el conjunto de caracteres ASCII con agrupación indexando la columna char. El conjunto de caracteres influye en el rendimiento de la búsqueda debido al tamaño de los datos tanto en el ram como en el disco. El cuello de botella es a menudo I / O.
Su columna tiene 255 caracteres de longitud, por lo que puede usar un índice normal en su campo de caracteres en lugar de texto completo, que es más rápido. No seleccione columnas innecesarias en su declaración de selección.
Por último, agregue más memoria RAM al servidor y aumente el tamaño del caché .

Crear vista de índice hay una nueva característica en sql create index en la columna que necesita buscar y usar esa vista después en su búsqueda que le dará un resultado más rápido.

Haga una cosa, use la clave principal en una columna específica e indíquela en forma de clúster.

Luego, realice una búsqueda utilizando cualquier método (comodín o = o cualquiera), buscará de manera óptima porque la tabla ya está en forma agrupada, por lo que sabe dónde puede encontrar (porque la columna ya está en forma ordenada)

Parece que has descartado todas las buenas alternativas.

Ya sabes que tu consulta.

SELECT * FROM t WHERE TITLE LIKE ''%abc%''

no utilizará un índice, hará un escaneo completo de la tabla cada vez.

Si estuviera seguro de que la cadena estaba al principio del campo, podría hacerlo

SELECT * FROM t WHERE TITLE LIKE ''abc%''

Lo que usaría un índice en el título.

¿Estás seguro de que la búsqueda de texto completo no te ayudaría aquí?

Dependiendo de los requisitos de su negocio, a veces he usado la siguiente lógica:

Haga primero una consulta "comienza con" ( LIKE ''abc%'' ), que utilizará un índice.
Dependiendo de si se devuelve alguna fila (o cuántas), pase condicionalmente a la búsqueda "más difícil" que realizará la exploración completa ( LIKE ''%abc%'' )

Depende de lo que necesite, por supuesto, pero lo he usado en situaciones donde puedo mostrar los resultados más fáciles y más comunes primero, y solo pasar a la consulta más difícil cuando sea necesario.

Puede agregar otra columna calculada en la tabla: titleLength as len (title) PERSISTED. Esto almacenaría la longitud de la columna "título". Crea un índice sobre esto.

Además, agregue otra columna calculada llamada: ReverseTitle as Reverse (título) PERSISTADO.

Ahora, cuando alguien busca una palabra clave, verifique si la longitud de la palabra clave es la misma que la de titlelength. Si es así, haga una búsqueda "=". Si la longitud de la palabra clave es menor que la longitud de la duración del título, entonces haga un LIKE. Pero primero haga un título como "abc%", luego haga un título inverso como "cba%". Similar al enfoque de Brad, es decir, usted hace la siguiente consulta difícil solo si es necesario.

Además, si las reglas 80-20 se aplican a sus palabras clave / subcadenas (es decir, si la mayoría de las búsquedas se realizan en una minoría de las palabras clave), también puede considerar hacer algún tipo de almacenamiento en caché. Por ejemplo, digamos que encuentra que muchos usuarios buscan la palabra clave "abc" y esta búsqueda de palabras clave devuelve registros con los identificadores 20, 22, 24, 25; puede almacenar esto en una tabla separada y tener esto indexado. Y ahora, cuando alguien busca una nueva palabra clave, primero busque en esta tabla de "caché" para ver si la búsqueda ya fue realizada por un usuario anterior. Si es así, no es necesario volver a mirar en la tabla principal. Simplemente devuelve los resultados de la tabla "caché".

También puede combinar lo anterior con SQL Server TextSearch. (Suponiendo que tenga un motivo válido para no usarlo). Sin embargo, puede utilizar la búsqueda de texto primero para hacer una lista corta del conjunto de resultados. y luego ejecute una consulta SQL en su tabla para obtener resultados exactos utilizando los identificadores devueltos por la Búsqueda TExt como un parámetro junto con su palabra clave.

Todo esto es obviamente asumiendo que tienes que usar SQL. Si no, puedes explorar algo como Apache Solr.

Si desea usar menos espacio que la respuesta de Randy y hay una repetición considerable en sus datos, puede crear una estructura de datos de árbol N-Ary donde cada borde sea el siguiente carácter y colgar cada cadena y subcadena final en sus datos.

Numeras los nodos en primer orden de profundidad. Luego, puede crear una tabla con hasta 255 filas para cada uno de sus registros, con el ID de su registro y el ID de nodo en su árbol que coincida con la cadena o la subcadena final. Luego, cuando hace una búsqueda, encuentra el ID de nodo que representa la cadena que está buscando (y todas las subcadenas finales) y realiza una búsqueda de rango.

Si no le importa el almacenamiento, puede crear otra tabla con entradas de Título parciales, comenzando con cada subcadena (hasta 255 entradas por título normal).

De esta manera, puede indexar estas subcadenas, y hacer coincidir solo el principio de la cadena, debería mejorar considerablemente el rendimiento.