performance données VARBINARY(MAX)

Bonjour

Je cherche à optimiser les accès à la table autoref suivante

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

CREATE TABLE [dbo].[T_DEVPART_DPT]( [DPT_ID] [int] IDENTITY(1,1) NOT NULL, [DEV_ID] [int] NOT NULL, [DPT_PID] [int] NULL, [DPT_ORD] [int] NOT NULL, [DPS_ID] [dbo].[D_SOUS_TYP_ELEMENT] NOT NULL, [DPT_PRN] [bit] NULL CONSTRAINT [DF_T_DEVPART_DPT_DPT_PRN] DEFAULT ((1)), [DPT_FAMILLE] [dbo].[D_FAMILLE_ARTICLE] NULL, [DPT_SFAMILLE] [dbo].[D_FAMILLE_ARTICLE] NULL, [DPT_REF] [dbo].[D_REF_ARTICLE] NULL, [DPT_LIBELLE] [dbo].[D_LIBELLE_ARTICLE] NULL, [DPT_QTE] [dbo].[D_QTE_ARTICLE] NOT NULL, [UPU_CODE] [dbo].[D_UNIT_PU] NOT NULL, [DPT_PUA1] [dbo].[D_MT_MONETAIRE] NULL, [DPT_PUA2] [dbo].[D_MT_MONETAIRE] NULL, [DPT_PUVST] [dbo].[D_MT_MONETAIRE] NULL, [DPT_PUVHF] [dbo].[D_MT_MONETAIRE] NULL, [DPT_PUV1] [dbo].[D_MT_MONETAIRE] NULL, [DPT_MT_AJUST] [dbo].[D_MT_MONETAIRE] NULL, [DPT_PUVF] [dbo].[D_MT_MONETAIRE] NULL, [DPT_NB_HRS_MO] [dbo].[D_QUANTITE_GEN] NULL, [DPT_MT_MO] [dbo].[D_MT_MONETAIRE] NULL, [DPT_TEXTCOM] [varbinary](max) NULL, [DPT_TSTAMP] [timestamp] NULL, CONSTRAINT [PK_T_DEVPART_DPT] PRIMARY KEY CLUSTERED ( [DPT_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]   GO SET ANSI_PADDING ON GO ALTER TABLE [dbo].[T_DEVPART_DPT] WITH CHECK ADD CONSTRAINT [FK_T_DEVPART_DPT_DEV_ID] FOREIGN KEY([DEV_ID]) REFERENCES [dbo].[T_DEVIS_DEV] ([DEV_ID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[T_DEVPART_DPT] CHECK CONSTRAINT [FK_T_DEVPART_DPT_DEV_ID] GO ALTER TABLE [dbo].[T_DEVPART_DPT] WITH CHECK ADD CONSTRAINT [FK_T_DEVPART_DPT_PID] FOREIGN KEY([DPT_PID]) REFERENCES [dbo].[T_DEVPART_DPT] ([DPT_ID]) GO ALTER TABLE [dbo].[T_DEVPART_DPT] CHECK CONSTRAINT [FK_T_DEVPART_DPT_PID] GO ALTER TABLE [dbo].[T_DEVPART_DPT] WITH CHECK ADD CONSTRAINT [FK_T_DEVPART_DPT_UPU_CODE] FOREIGN KEY([UPU_CODE]) REFERENCES [dbo].[T_UNITPU_UPU] ([UPU_CODE]) GO ALTER TABLE [dbo].[T_DEVPART_DPT] CHECK CONSTRAINT [FK_T_DEVPART_DPT_UPU_CODE]

---

Je m'interroge sur l'impact de ce champ DPT_TEXTCOM en varbinary(mex)
NON NULL sur environ 10 % des lignes (2100000 au total) et dont

Code:

---

1 2 3

SELECT AVG(LEN(DPT_TEXTCOM)) FROM T_DEVPART_DPT WHERE DPT_TEXTCOM IS NOT NULL

---

donne 2500

ce champ peut assez facilement être requêté à part dans mon application cliente , donc je m'interroge sur l'utilité de le déplacer dans une deuxième table avec un champ répétant la même clef primaire, quelle incidence cela peut avoir sur la fragmentation des index notamment

Bruno

Aucun intérêt, en effet les LOBs (large Object Binary) ne sont pas stockée dans la ligne de la table mais dans des pages à part, sauf si vous avez précisé l'option "text in row" à l'aide de la procédure stockée sp_tableoption

Ceci est visible à l'aide de la commande :

Code:

---

SELECT OBJECTPROPERTY (OBJECT_ID( '[dbo].[T_DEVPART_DPT]') , 'TableTextInRowLimit' )

---

A +

La commande que vous donez renvoit NULL, donc je suppose que cela veut dire que les LOB ne sont pas stockée dans la ligne,
Ce qui est stocké dans la ligne c'est juste un pointeur alors donc?

Il n'est pas normal qu'elle renvoie NULL. Elle devrait renvoyer 0. Es-te vous bien dans la bonne base ?

A +

Bonjour,

En fait, ce n'est pas la bonne table, ou son nom a été écorché.
En effet, si la table n'existe pas dans la BD, alors la fonction retourne NULL.

@++ ;)

Oui en effet j'ai oublié le

Code:

---

1 2 3

USE GPG GO

---

avant , la fonction retourne bien 0 donc FALSE
je m'excuse

eeh non la fonction ne retourne pas false mais plutot la quantité en octets de BLOB stocké sur la ligne

Par contre est ce qu'il pourrait être interressant de faire migrer les colonnes

Code:

---

1 2 3 4 5

[DPT_FAMILLE] VARCHAR(16) NULL, [DPT_SFAMILLE] VARCHAR(16) NULL, [DPT_REF] VARCHAR(48) NULL, [DPT_LIBELLE] VARCHAR(96)

---

dans une deuxième table pour ne laisser dans la première table que des colonnes fortement mise à jour lors d'opération sur la table Hiérarchique
Je cherche à optimiser ce genre d'accès pour les gros set de valeur DEV_ID
(> 3000 lignes) pour lesquel par exemple le déplacement d'une branche de l'arbre d'un endroit à l'autre peut prendre jusqu'a 2 secondes, ce qui est trop pour mes utilsateurs finaux, il faut dire que ce genre d'opération est extremement courante dans une étude de prix
Evidemment je peux tester la chose mais cela comporte pas mal de travail sur une application en production et j'aimerais avoir des avis

L'utilisation systématique du VARCHAR n'est déjà pas une bonne chose... Surtout pour des petites données (moins de 10 caractères par exemple).

A +

Oui efffectivement si je prend par exemple la colonne DPT_FAMILLE dans 80 % des cas elle ne fait que 6 de long, mais tout de même dans les 20 autres il y a a besoin de 16 et c'est à peu près pareil pour les 3 autres
(80 % des chaines n'occupent que en dessous de la moitié de la capacité) mais je ne vois pas comment faire autrement

Dans ce cas, mieux vaut utiliser du CHAR, il y aura moins de fragmentation en cas d'update.


A +