Qu'est-ce qu'une manière maintenable de stocker de grands champs de text sans sacrifier la performance?

J'ai dansé autour de cette question pendant un certain time mais ça continue à monter. Nous avons un système et notre may de nos tables commence avec une description qui est à l'origine stockée comme un NVARCHAR(150) et puis nous obtenons un ticket demandant d'étendre la taille du champ à 250, puis 1000 etc, etc …

Ce cycle est répété sur tous les champs "note" et / ou "description" que nous ajoutons à la plupart des arrays. Bien sûr, le souci pour moi est la performance et de briser la limite de 8k de la page. Cependant, mon autre souci est de rendre le système less maintenable en séparant ces champs de la table EVERY du système en une reference chargée paresseuse.

Donc, ici, je suis confronté à ces deux options qui m'ont regardé en face. (d'autres sont les bienvenus) s'il vous plaît prêter vos opinions.

Changez toutes les notes et / ou descriptions de NVARCHAR(MAX) et assurez-vous d'exclure ces champs dans toutes les lists. Fondamentalement, ne jamais faire un: SELECT * FROM [TableName] less qu'il ne récupère qu'un seul logging.
Supprimez toutes les notes et / ou champs de description et remplacez-les par une reference de key forign à une table [Notes] .

CREATE TABLE [dbo].[Notes] ( [NoteId] [int] NOT NULL, [NoteText] [NVARCHAR] CREATE TABLE [dbo].[Notes] ( [NoteId] [int] NOT NULL, [NoteText] [NVARCHAR] ( MAX ) NOT NULL )

Évidemment, je préférerais utiliser l'option 1 parce que ça changera tellement dans notre système si on y va avec 2. Mais si l'option 2 est vraiment la seule bonne façon de procéder, alors au less je peux dire que ces changements sont nécessaires et j'ai fait le devoirs.

MISE À JOUR: J'ai exécuté plusieurs tests sur une database exemple avec 100 000 loggings. Ce que je trouve, c'est que l'IO requirejs pour l'option 1 est «grossièrement» le double de celui de l'option 2. Si je sélectionne un grand nombre d'loggings (1000 ou plus), l'option 1 est deux fois plus lente, même si je fais ne pas inclure le grand champ de text dans le select. Comme je request less de lignes les lignes floues plus. Je une application web où les tailles de page de 50 ou plus sont la norme, donc l'option 1 va fonctionner, mais je vais convertir toutes les instances à l'option 2 dans (très) proche avenir pour l'évolutivité.

L'option 2 est meilleure pour plusieurs raisons:

Lorsque vous interrogez vos tables, les champs de text volumineux remplissent rapidement les pages, ce qui oblige la database à parsingr plusieurs pages pour récupérer des données. Cela est particulièrement pénible lorsque vous n'avez pas besoin de renvoyer datatables de text.
Comme vous l'avez mentionné, cela vous permet de changer de type de données en une fois. Microsoft a déprécié TEXT dans SQL Server 2008, donc vous devriez coller avec VARCHAR / VARBINARY.
Séparez les groupes de files. Le fait d'avoir toutes vos données textuelles dans un lieu de stockage plus lent et less cher pourrait être quelque chose que vous décidez de poursuivre à l'avenir. Si non, pas de mal, pas de faute.

Alors que l'option 1 est plus facile pour l'instant, l'option 2 vous donnera plus de flexibilité à long terme. Ma suggestion serait de mettre en œuvre une preuve de concept simple avec les informations "notes" séparées de la table principale et d'effectuer certaines de vos requêtes sur les deux exemples. Comparez les plans d'exécution, les statistics client et les lectures d'E / S logiques (SET STATISTICS IO ON) pour certaines de vos requêtes sur ces tables.

Une note rapide à ceux qui suggèrent l'utilisation d'un TEXT / NTEXT de MSDN:

Cette fonctionnalité sera supprimée dans une future version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans les nouveaux travaux de développement et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Utilisez plutôt les types de données varchar (max), nvarchar (max) et varbinary (max). Pour plus d'informations, voir Utilisation de types de données à grande valeur.

J'irais avec l'option 2.

Vous pouvez créer une vue qui joint les deux tables pour faciliter la transition sur tout le monde, puis passer par un process de nettoyage qui supprime la vue et utilise la table unique dans la mesure du possible.

Vous voulez utiliser un champ TEXT. Les champs TEXT ne sont pas stockés directement dans la ligne; à la place, il stocke un pointeur sur datatables de text. Ceci est transparent pour les requêtes, cependant – si vous requestz un champ TEXT, il returnnera le text réel, pas le pointeur.

Essentiellement, l'utilisation d'un champ TEXT est quelque peu entre vos deux solutions. Il maintient vos lignes de table beaucoup plus petit que l'utilisation d'un varchar, mais vous aurez toujours envie d'éviter de les requestr dans vos requêtes si possible.

Le type de données TEXT / NTEXT a une longueur pratiquement illimitée tout en prenant à peu près rien dans votre dossier.

Il est livré avec quelques strings attachées, comme un comportement spécial avec des fonctions de string, mais pour un type de champ "notes / description" secondaire, cela peut être less un problème.

Juste pour développer l'option 2

Vous pourriez:

Renommez MyTable existant en MyTable_V2

Déplacez la colonne Notes dans une table Notes jointe (avec ID de jointure 1: 1)

Créer une vue appelée MyTable qui joint les tables MyTable_V2 et Notes

Créez un triggersur INSTEAD OF sur la vue MyTable qui enregistre la colonne Notes dans la table Notes (IF NULL puis supprimez toute ligne Notes existante, si NOT NULL puis Insert si introuvable, sinon Update). Effectuez l'action appropriée sur la table MyTable_V2

Note: Nous avons eu du mal à faire cela quand il y a une colonne Computed dans MyTable_V2 (je pense que c'était le problème, de toute façon nous avons frappé des accrocs en faisant cela avec des tables "inhabituelles")

Tout nouveau code Insérer / Mettre à jour / Supprimer doit être écrit pour fonctionner directement sur les tables MyTable_V2 et Notes

Facultatif: avoir le triggersur INSERT OF sur MyTable consigner le fait qu'il a été appelé (il peut le faire au minimum, UPDATE une ligne de table de journal préexistante avec GetDate () uniquement si la date de la ligne existante est> 24 heures une mise à jour une fois par jour).

Lorsque vous ne recevez plus d'loggings, vous pouvez supprimer le triggersur INSTEAD OF sur la vue MyTable et vous êtes maintenant entièrement compatible avec MyTable_V2!

Énormément de tracas à mettre en œuvre, comme vous l'avez supposé.

Vous pouvez également saisir le code pour toutes les references à MyTable et les modifier en MyTable_V2, mettre une vue à la place de MyTable pour SELECT uniquement et ne pas créer le triggersur INSTEAD OF.

Mon plan serait de corriger toutes les instructions Insérer / Mettre à jour / Supprimer faisant reference à MyTable maintenant obsolète. Pour moi, cela serait un peu plus facile car nous utilisons des noms uniques pour toutes les tables et colonnes de la database, et nous utilisons les mêmes noms dans tous les codes d'application.

PS Option 2 est également préférable si vous avez un SELECT * qui traîne. Nous avons eu des clients dont les performances des applications ont baissé rapidement quand ils ont ajouté de grandes colonnes Text / Blob aux tables existantes – à cause des instructions SELECT * paresseuses. J'espère que ce n'est pas le cas dans votre magasin si!