sql-server - vista - exportar base de datos sql server management studio

Esquema de la base de datos para grupos jerárquicos

Question

Estoy trabajando en un diseño de base de datos para la jerarquía de grupos utilizada como base de un sistema más grande. Cada grupo puede contener otros grupos, y también ''dispositivos'' como objetos de hoja (nada pasa debajo del dispositivo).

La base de datos utilizada es MS SQL 2005. (Aunque trabajar en MS SQL 2000 sería una ventaja, una solución que requiere MS SQL 2008 lamentablemente no es factible en este momento).

Existen diferentes tipos de grupos, y estos deben ser dinámicos y definibles en tiempo de ejecución por los usuarios. Por ejemplo, los tipos de grupo pueden ser "cliente", "cuenta", "ciudad" o "edificio", "piso", y cada tipo va a tener un conjunto diferente de atributos, definibles por el usuario. También se aplicarán reglas comerciales, por ejemplo, un "piso" solo puede estar contenido debajo de un grupo "de construcción", y nuevamente, estos son definibles en tiempo de ejecución.

Gran parte de la funcionalidad de la aplicación proviene de la ejecución de informes basados ​​en estos grupos, por lo que debe haber una manera relativamente rápida de obtener una lista de todos los dispositivos contenidos dentro de un determinado grupo (y todos los subgrupos).

El almacenamiento de grupos utilizando la técnica de recorrido de árbol preordenado modificado tiene la ventaja de ser rápido, pero la desventaja es que es bastante complejo y frágil: si los usuarios / aplicaciones externos modifican la base de datos, existe la posibilidad de que se rompa por completo. También estamos implementando una capa ORM, y este método parece complicar el uso de las relaciones en la mayoría de las bibliotecas ORM.

El uso de expresiones de tabla comunes y una relación "estándar" de id / parentid groups parece ser una forma poderosa de evitar ejecutar múltiples consultas recursivas. ¿Hay algún inconveniente en este método?

En cuanto a los atributos, ¿cuál es la mejor manera de almacenarlos? ¿Una mesa larga y estrecha que se relaciona con el grupo? ¿Debería almacenarse un atributo común, como "nombre" en una tabla de grupos, en lugar de la tabla de atributos (muchas veces, el nombre será todo lo que se requiere para mostrar)?

¿Habrá problemas de rendimiento con este método (supongamos que un promedio alto de 2000 grupos con un promedio de 6 atributos cada uno y un promedio de 10 usuarios simultáneos en un hardware razonable, por ejemplo, cuatro núcleos Xeon 2 Ghz, 4 GB de ram , descontando cualquier otro proceso)?

Siéntase libre de sugerir un esquema completamente diferente de lo que he esbozado aquí. Solo estaba tratando de ilustrar los problemas que me preocupan.

Answer 1

El preorden modificado es, esencialmente, el método de conjuntos anidados de Joe Celko. Su libro, "Árboles y jerarquías ..." abarca tanto la lista de adyacencia como la NS, con descripciones de las ventajas y desventajas de cada una. Con una indexación adecuada, el CTE de las listas de adyacencia obtiene el rendimiento más equilibrado. Si vas a leer en su mayoría, entonces NS será más rápido.

Lo que parece estar describiendo es un procesador de Lista de materiales. Si bien no es M $, Graeme Birchall tiene un libro libre de DB2, con un capítulo sobre el procesamiento de jerarquía utilizando CTE (la sintaxis es prácticamente idéntica, IIRC, en la que la sintaxis ANSI adoptó DB2, que M $ adoptó): http: // mysite .verizon.net / Graeme_Birchall / libro de cocina / DB2V95CK.PDF

Answer 2

Pre-ordenar Tree Traversal es muy útil. Puede hacerlo más robusto manteniendo los números de recorrido actualizados con desencadenantes.

Una técnica similar que he usado es mantener una tabla separada de (ancestor_id, descenddant_id) que enumera todos los ancestros y descendientes. Esto es casi tan bueno como los números de cruce previos a la orden.

Usar una tabla separada es útil, porque a pesar de que introduce una combinación adicional, elimina la complejidad en una tabla separada.

Answer 3

Recomiendo que realmente construyas la forma más fácil de mantener (la configuración padre / hijo "estándar") y ejecutes al menos algunos puntos de referencia básicos sobre ella.

Se sorprendería de lo que un motor de base de datos puede hacer con la indexación adecuada, especialmente si su conjunto de datos puede caber en la memoria.

Suponiendo 6 atributos por grupo, 2000 grupos y 30 bytes / atributo, estás hablando de 360 ​​KB * elementos esperados / grupo - cifra de 400 KB. Si espera tener 1000 artículos / grupo, solo está buscando 400MB de datos, que encajarán en la memoria sin problemas, y las bases de datos se unen rápidamente cuando todos los datos están en la memoria.

Answer 4

Las expresiones de tabla comunes le permitirán obtener una lista de grupos con las relaciones padre-hijo. Aquí hay un ejemplo de un sproc que usa CTE para una aplicación diferente. Es razonablemente eficiente, pero ten cuidado con las siguientes advertencias:

1. Si una parte aparece más de una vez en la jerarquía, se informará en cada ubicación. Es posible que deba postprocesar los resultados.
2. Los CTE son algo obtusos y ofrecen un alcance limitado para filtrar los resultados dentro de la consulta; es posible que el CTE no aparezca más de una vez dentro de la declaración de selección.

El CONNECT BY de Oracle es algo más flexible, ya que no impone casi tantas limitaciones en la estructura de consultas como las de CTE, pero si usa SQL Server, esto no será una opción.

Si necesita hacer algo inteligente con los resultados intermedios, escriba un sproc que use el CTE para obtener una consulta sin formato en una tabla temporal y trabajar desde allí. SELECT INTO minimizará el tráfico en el que se incurre. La tabla resultante estará en caché, por lo que las operaciones serán razonablemente rápidas.

Algunas optimizaciones físicas posibles que podrían ayudar:

• Los índices agrupados en el padre para que los nodos secundarios para un padre utilizan menos E / S.
• Gran cantidad de RAM y (dependiendo del tamaño de su tabla BOM) servidores de 64 bits con aún más RAM para que la tabla principal de la lista de materiales pueda almacenarse en el núcleo. En un O / S de 32 bits, el conmutador de arranque 3G es tu amigo y no tiene una desventaja real para un servidor de base de datos
• DBCC PINTABLE puede ayudar a obligar al administrador de la base de datos a mantener la tabla en caché.

Las tablas de codificación de atributos de tipo padre-atributo no funcionarán bien con los CTE, ya que terminará con una explosión combinatoria en los recuentos de filas si incluye la tabla de atributos. Esto excluiría cualquier lógica de negocio en la consulta que se filtró en los atributos. Sería mucho mejor si almacenara los atributos directamente en la entrada de la tabla BOM.