Nuestro team de redactores ha pasado mucho tiempo investigando la respuesta a tu interrogante, te dejamos la resolución por eso esperamos resultarte de mucha ayuda.
Solución:
Esto es un poco largo, quizás, pero sus problemas reales no se pueden resolver mirando los planes de ejecución. Hay dos problemas principales y ambos son arquitectónicos.
Distracciones
Comencemos con los elementos que son no sus principales áreas problemáticas. Estas son cosas que deben tenerse en cuenta, ya que definitivamente ayuda a mejorar el rendimiento al usar los tipos de datos que necesitar y no solo un tipo de datos general, de talla única. Hay una muy buena razón por la que existen diferentes tipos de datos, y si se almacenan 100 caracteres en NVARCHAR(MAX) no tuvo un impacto negativo en las consultas (o cualquier otro aspecto del sistema), entonces todo se almacenaría como NVARCHAR(MAX). Sin embargo, limpiar estas áreas no conducirá a una verdadera escalabilidad.
a MAX, o no a MAX
Estoy trabajando con una tabla que tiene todos los tipos de caracteres configurados en
nvarcharalgunos de ellos sonnvarchar(max).
Está bien. Esto no es necesariamente algo malo, aunque la mayoría de las veces hay al menos un campo que es de tipo numérico como ID. Sin embargo, ciertamente hay casos válidos para el escenario descrito hasta ahora. Y no hay nada intrínsecamente malo en MAX campos, ya que almacenarán los datos en la página de datos (es decir, en fila) si los datos pueden caber allí. Y en esa situación debería funcionar tan bien como un valor no MAX del mismo tipo de datos. Pero sí, un montón de MAX Los campos de tipo son un signo de descuido en el modelado de datos y es mucho más probable que tengan la mayoría (o todos) de eso. MAX datos almacenados en páginas de datos independientes (es decir, fuera de la fila) que necesitan una búsqueda adicional, por lo que son menos eficientes.
VARCHAR vs NVARCHAR
Estamos convirtiendo todos estos a
varchar…
Ok, pero por qué exactamente (sí, sé que la información y los comentarios que siguen a esta declaración agregan claridad, pero voy a preservar el aspecto conversacional por una razón). Cada tipo de datos tiene su lugar. VARCHAR es 1 byte por carácter y puede representar 256 caracteres (la mayor parte del tiempo) como se define en un página de código único. Si bien los valores de caracteres 0-127 son iguales entre las páginas de códigos, los valores de caracteres entre 128 y 255 pueden cambiar:
;WITH chars ([SampleCharacters]) AS
(
SELECT CHAR(42) + ' ' -- *
+ CHAR(65) + ' ' -- A
+ CHAR(126) + ' ' --
-------------------------------
+ CHAR(128) + ' ' -- €
+ CHAR(149) + ' ' -- •
+ CHAR(165) + ' ' -- ¥, Y, ?
+ CHAR(183) + ' ' -- ·, ?
+ CHAR(229) + ' ' -- å, a, ?
)
SELECT chr.SampleCharacters COLLATE SQL_Latin1_General_CP1_CI_AS AS [SQL_Latin1_General_CP1_CI_AS],
chr.SampleCharacters COLLATE SQL_Latin1_General_CP1255_CI_AS AS [SQL_Latin1_General_CP1255_CI_AS],
chr.SampleCharacters COLLATE Thai_CI_AS_KS_WS AS [Thai_CI_AS_KS_WS],
chr.SampleCharacters COLLATE Yakut_100_CS_AS_KS AS [Yakut_100_CS_AS_KS],
chr.SampleCharacters COLLATE Albanian_CS_AI AS [Albanian_CS_AI]
FROM chars chr;
Tenga en cuenta que es posible VARCHAR los datos ocupan 2 bytes por carácter y representan más de 256 caracteres. Para obtener más información sobre los conjuntos de caracteres de doble byte, consulte la siguiente respuesta: Almacenamiento de caracteres japoneses en una tabla.
NVARCHAR se almacena como UTF-16 (Little Endian) y tiene 2 o 4 bytes por carácter, lo que puede representar el espectro Unicode completo. Por lo tanto, si sus datos necesitarán almacenar más caracteres de los que puede representar una sola página de códigos, entonces cambie a VARCHAR realmente no te ayudará.
Antes de convertir a VARCHAR, debe asegurarse de no almacenar ningún carácter Unicode. Pruebe la siguiente consulta para ver si hay filas que no se pueden convertir a VARCHAR sin perder datos:
SELECT tbl.PKfield, tbl.SubType
FROM dbo.[Listings] tbl
WHERE tbl.SubType <> CONVERT(NVARCHAR(MAX), CONVERT(VARCHAR(MAX), tbl.SubType))
Para aclarar como NVARCHAR funciona: la longitud máxima de un NVARCHAR campo es el número de 2 bytes caracteres. Por eso, NVARCHAR(50), permitirá un máximo de 100 bytes. La cantidad de caracteres que caben en esos 100 bytes depende de la cantidad de caracteres de 4 bytes que haya: ninguno le permitirá caber en los 50 caracteres, todos los caracteres de 4 bytes solo caben en 25 caracteres y muchas combinaciones entre ellos.
Otra cosa a considerar con respecto al espacio ocupado por VARCHAR vs NVARCHAR: a partir de SQL Server 2008 (¡solo en las ediciones Enterprise y Developer!), puede habilitar la compresión de filas o páginas en tablas, índices y vistas indexadas. Para situaciones en las que gran parte de los datos de un NVARCHAR el campo realmente puede caber dentro VARCHAR sin pérdida de datos, la compresión permitirá caracteres que encajen en VARCHAR para ser almacenado como 1 byte. Y solo los caracteres que requieren 2 o 4 bytes ocuparán ese espacio. Esto debería eliminar una de las razones más importantes por las que las personas a menudo optan por seguir VARCHAR. Para obtener más información sobre la compresión, consulte la página de MSDN para crear índices y tablas comprimidos. Tenga en cuenta que los datos en MAX Los tipos de datos que se almacenan fuera de la fila no se pueden comprimir.
Áreas reales de preocupación
Se deben abordar las siguientes áreas si desea que esta tabla sea realmente escalable.
Problemo Numero Uno
… y especificando un ancho de carácter basado en el uso real en producción. Los datos de producción utilizan un rango de 2 caracteres hasta 900 caracteres de ancho real utilizado para cualquier columna dada. Agregaremos un relleno del 10% cuando corresponda.
¿Cómo? ¿Ha sumado todos esos valores? Dado cuantos MAX campos que tiene, es posible que uno o más de esos campos tenga 900 caracteres, y aunque eso debería equivaler a 1800 bytes, el valor almacenado en la página de datos principal es solo 24 bytes (no siempre 24 ya que el tamaño varía en relación con varios factores). Y esa podría ser la razón por la que hay tantos MAX campos: no caben en otro NVARCHAR(100) (ocupando hasta 200 bytes), pero tenían espacio para 24 bytes.
Si el objetivo es mejorar el rendimiento, convertir las cadenas completas en códigos es, en algunos niveles, un paso en la dirección correcta. Está reduciendo drásticamente el tamaño de cada fila, lo que es más eficiente para el grupo de búferes y la E / S de disco. Y las cadenas más cortas toman menos tiempo para comparar. Eso es bueno, pero no asombroso.
Si el objetivo es dramáticamente mejorar el rendimiento, entonces la conversión a códigos es la incorrecto da un paso en la dirección correcta. Todavía se basa en escaneos basados en cadenas (con 30 índices y 140 columnas, debería haber muchos escaneos, a menos que la mayoría de los campos no se usen para filtrar), y supongo que esos serán mayúsculas y minúsculas.enescaneos sensibles en eso, que son menos eficientes que si fueran binarios o sensibles a mayúsculas y minúsculas (es decir, utilizando una intercalación binaria o sensible a mayúsculas y minúsculas).
Además, la conversión a códigos basados en cadenas, en última instancia, pierde el sentido de cómo optimizar adecuadamente un sistema transaccional. ¿Estos códigos se ingresarán en un formulario de búsqueda? Que la gente use 'S' por [SubType] es mucho menos significativo que buscar en 'Single Family'.
Hay una manera de conservar su texto descriptivo completo mientras reduce el espacio utilizado y acelera enormemente las consultas: cree una tabla de búsqueda. Deberías tener una tabla llamada [SubType] que almacena cada uno de los términos descriptivos de forma distinta y tiene un [SubTypeID] para cada uno. Si los datos son parte del sistema (es decir, un enum), entonces el [SubTypeID] el campo debe no frijol IDENTITY campo, ya que los datos deben completarse a través de un script de lanzamiento. Si los valores son ingresados por usuarios finales, entonces el [SubTypeID] campo deberían ser una IDENTIDAD. En ambas situaciones:
[SubTypeID]es la clave principal.- Uso más probable
INTpor[SubTypeID]. - Si los datos son datos internos / del sistema, y sabe que el número máximo de valores distintos siempre será inferior a 40k, entonces podría salirse con la suya.
SMALLINT. Si comienza a numerar en 1 (ya sea manualmente o mediante semilla de IDENTIDAD), obtendrá un máximo de 32,768. Pero, si comienza con el valor más bajo, -32,768, obtendrá los valores completos de 65,535 para usar. - Si está utilizando Enterprise Edition, habilite la compresión de filas o páginas
- El campo de texto descriptivo se puede llamar
[SubType](igual que el nombre de la tabla), o tal vez[SubTypeDescription] UNIQUE INDEXsobre[SubTypeDescription]. Tenga en cuenta que los índices tienen un tamaño máximo de 900 bytes. Si la longitud máxima de estos datos en Producción es de 900 caracteres, y si necesitaNVARCHAR, luego esto podría trabajar con compresión habilitada, O usarVARCHARsolo si definitivamente NO necesita almacenar caracteres Unicode, ELSE aplica la unicidad a través de unAFTER INSERT, UPDATEdesencadenar.[Listings]mesa tiene[SubTypeID]campo.[SubTypeID]campo en[Listings]la tabla es una clave externa, haciendo referencia[SubType].[SubTypeID].- Las consultas pueden ahora
JOINlos[SubType]y[Listings]tablas y buscar en el texto completo de[SubTypeDescription](distingue entre mayúsculas y minúsculas, incluso, igual que la funcionalidad actual), mientras usa esa ID para realizar una búsqueda muy eficiente en el campo FK indexado en[Listings].
Este enfoque puede (y debe) aplicarse a otros campos de esta tabla (y otras tablas) que se comportan de manera similar.
Problemo Numero Dos
Una gran revisión de esta tabla que consta literalmente de 140 columnas (nvarchar), siendo 11 MAX. Dejaré caer 30 índices y los volveré a crear después.
Si se trata de un sistema transaccional y no de un almacén de datos, diría que (de nuevo, en general) 140 columnas es demasiado para manejar de manera eficiente. Dudo mucho que los 140 campos se usen al mismo tiempo y / o tengan los mismos casos de uso. El hecho de que 11 sean MAX es irrelevante si deben contener más de 4000 caracteres. PERO, tener 30 índices en una tabla transaccional es nuevamente un poco difícil de manejar (como puede ver claramente).
¿Existe una razón técnica por la que la tabla debe tener los 140 campos? ¿Se pueden dividir esos campos en algunos grupos más pequeños? Considera lo siguiente:
- Busque los campos “principales” (los más importantes / de uso frecuente) y colóquelos en la tabla “principal”, denominada
[Listing](Prefiero mantenerme con palabras en singular para que el campo ID pueda ser fácilmenteTableName + "ID"). [Listing]tabla tiene este PK:[ListingID] INT IDENTITY(1, 1) NOT NULL CONSTRAINT [PK_Listing] PRIMARY KEY- las tablas “secundarias” se denominan
[ListingGroupName](p.ej[ListingPropertyAttribute]– “atributos” como en: NumberOfBedrooms, NumberOfBathrooms, etc.). [ListingPropertyAttribute]tabla tiene este PK:[ListingID] INT NOT NULL CONSTRAINT [PK_ListingPropertyAttribute] PRIMARY KEY, CONSTRAINT [FK_ListingPropertyAttribute_Listing] FOREIGN KEY REFERENCES([Listing].[ListingID])- nota que no
IDENTITYaquí - observe que el nombre de PK es el mismo entre las tablas “principales” y “secundarias”
- observe que PK y FK para la tabla “core” es el mismo campo
- nota que no
- “centro”
[Listing]la mesa obtiene ambos[CreatedDate]y[LastModifiedDate]los campos - las tablas “secundarias” solo obtienen
[LastModifiedDate]campo. El supuesto es que todas las tablas secundarias obtienen sus filas al mismo tiempo que la tabla “principal” (es decir, todas las filas deben estar siempre representadas en todas las tablas “secundarias”). Por lo tanto, la[CreatedDate]valor en el “núcleo”[Listing]La tabla siempre será la misma en todas las tablas “secundarias”, por fila, por lo que no es necesario duplicarla en las tablas “secundarias”. Pero cada uno puede actualizarse en tiempos diferentes.
Esta estructura aumenta el número de JOIN que necesitarán muchas consultas, aunque se pueden crear una o más Vistas para encapsular los JOIN que se usan con más frecuencia, para facilitar la codificación. Pero en el lado positivo:
- cuando se trata de declaraciones DML, debería haber mucha menos controversia ya que la tabla “principal” debería recibir la mayoría de las actualizaciones.
- la mayoría de las actualizaciones tomarán menos tiempo ya que están modificando filas más pequeñas.
- el mantenimiento del índice en cada una de las nuevas tablas (tanto las tablas “principales” como las “secundarias”) debería ser más rápido, al menos por tabla.
Resumen
El modelo actual está diseñado para ser ineficiente y parece estar cumpliendo con ese objetivo de diseño (es decir, ser lento). Si desea que el sistema sea rápido, entonces el modelo de datos debe diseñarse para que sea eficiente, no simplemente menos ineficiente.
En que situaciones se prefiere varchar (max)
Los comentaristas ya han abordado este punto en detalle. yo diría que VARCHAR(MAX) generalmente tiene sentido si está 100% seguro de que la columna nunca necesitará caracteres que no sean ASCII y la longitud máxima de la columna es desconocida o superior a 8.000 caracteres. Puede leer https://stackoverflow.com/questions/7141402/why-not-use-varcharmax para una pregunta similar.
Se están agotando los procedimientos de actualización.
Según el plan de ejecución, creo que un factor importante que afecta el rendimiento de su actualización podría ser que tiene un índice de texto completo en la columna que se está actualizando y está utilizando CHANGE_TRACKING = AUTO para ese índice de texto completo.
El script en la parte inferior de esta respuesta muestra una declaración de actualización simple en un número modesto de filas donde el rendimiento va de 19 ms a más de 500 ms con solo agregar un índice de texto completo.
Dependiendo de las necesidades comerciales para su búsqueda de texto completo, es posible que pueda crear el índice de texto completo con CHANGE_TRACKING = OFF (que no incurrirá en ninguno de estos gastos generales) y se ejecutará periódicamente ALTER FULLTEXT INDEX...START FULL POPULATION o START INCREMENTAL POPULATION para sincronizar los datos de la tabla en el índice de texto completo. Consulte el artículo de BOL aquí: https://msdn.microsoft.com/en-us/library/ms187317.aspx
-- Create test data on a new database
CREATE DATABASE TestFullTextUpdate
GO
USE TestFullTextUpdate
GO
CREATE TABLE dbo.fulltextUpdateTest (
id INT NOT NULL IDENTITY(1,1)
CONSTRAINT PK_fulltextUpdateTest PRIMARY KEY,
object_counter_name NVARCHAR(512) NOT NULL
)
GO
--13660 row(s) affected
INSERT INTO dbo.fulltextUpdateTest (object_counter_name)
SELECT object_name + ': ' + counter_name
FROM sys.dm_os_performance_counters
CROSS JOIN (SELECT TOP 10 * FROM master..spt_values) x10
GO
-- ~19ms per update
DECLARE @startDate DATETIME2 = GETDATE()
SET NOCOUNT ON
UPDATE dbo.fulltextUpdateTest SET object_counter_name = object_counter_name
SET NOCOUNT OFF
DECLARE @endDate DATETIME2 = GETDATE()
PRINT(DATEDIFF(ms, @startDate, @endDate))
GO 10
-- Add a fulltext index with the default change-tracking behavior
CREATE FULLTEXT CATALOG DefaultFulltextCatalog AS DEFAULT AUTHORIZATION dbo
GO
CREATE FULLTEXT INDEX ON dbo.fulltextUpdateTest (object_counter_name)
KEY INDEX PK_fulltextUpdateTest
WITH CHANGE_TRACKING = AUTO
GO
-- ~522ms per update
-- Execution plan, like the plan in your question, shows that we must
-- touch the fulltext_index_docidstatus for each row that is updated
DECLARE @startDate DATETIME2 = GETDATE()
SET NOCOUNT ON
UPDATE dbo.fulltextUpdateTest SET object_counter_name = object_counter_name
SET NOCOUNT OFF
DECLARE @endDate DATETIME2 = GETDATE()
PRINT(DATEDIFF(ms, @startDate, @endDate))
GO 10
-- Cleanup
USE master
GO
DROP DATABASE TestFullTextUpdate
GO
Si te gustó nuestro trabajo, tienes la libertad de dejar una noticia acerca de qué te ha gustado de esta sección.