Petites tables ou grandes tables. . . Quelles conséquences sur les performances ?

Version imprimable

Voir 40 message(s) de cette discussion en une page

08/09/2015, 12h21
SQLpro

Citation:

Envoyé par fsmrel

Bonsoir,

Qui détermine la préférence ? Le DBA ? SQL Server ?
Si le granule de verrouillage est la page, et s’il y a de la concurrence en mise à jour, SQL Server prend-il dynamiquement l’initiative de passer au verrouillage au niveau de la ligne, quitte à revenir plus tard à la page (en supposant qu'il sache inférer que ça vaut le coup) ?

D'après ce que je lis dans la doc, le SGBD se baserait plutôt sur les caractéristiques du schéma et des requêtes :

The Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query.

Par défaut niveau ligne. Si plusieurs lignes mise à jour et en fonction du nombre de ligne par page et de la distribution, alors niveau page.
Le DBA peut forcer/interdire les verrous de page et ligne dans chaque index et chaque table.

Citation:

Envoyé par fadace

Il y a une notion d'escalade de verrouillage, faite par le moteur.

A un certain seuil (à l'époque éculée de mes derniers cours Internals, c'était 200 pages), le verrouillage page passe à un verrouillage table.

Visiblement, [source], les seuils ont changé mais le principe est resté au travers des ans...

On peut bien sûr forcer cela, si l'on se croit plus malin que lui :aie:.

L'escalade se fait :
de la ligne ou la page vers la partition, puis vers la table. Le seuil est d'environ 5000.

A +
09/09/2015, 13h34
escartefigue

Citation:

Envoyé par SQLpro

Par défaut niveau ligne. Si plusieurs lignes mise à jour et en fonction du nombre de ligne par page et de la distribution, alors niveau page.
Le DBA peut forcer/interdire les verrous de page et ligne dans chaque index et chaque table.
L'escalade se fait :
de la ligne ou la page vers la partition, puis vers la table. Le seuil est d'environ 5000.
A +

Avec DB2 for Z/OS, le seuil est paramétrable via le mot clef "LOCKMAX"

Extrait de la doc DB2V11 (mais c'était déjà vrai avec les versions précédentes ):

LOCKMAX
Specifies the maximum number of page, row, or LOB locks an application
process can hold simultaneously in the table space. If a program requests more
than that number, locks are escalated. The page, row, or LOB locks are released
and the intent lock on the table space or segmented table is promoted to S or X
mode. If you specify LOCKMAX a for table space in a work file database, DB2
ignores the value because these types of locks are not used.
integer
Specifies the number of locks allowed before escalating, in the range 0 to
2 147 483 647.

Sur le site où j'interviens actuellement, il y a environ 16% des tables avec plus de 20 colonnes (toutes bases de données confondues)
Mais si on élimine les clefs étrangères et les colonnes mouchard (date, heure, traitement et user de création, date, heure, traitement et user de dernière modif), cette proportion diminue au moins de moitié
15/09/2015, 11h35
SQLpro

Citation:

Envoyé par escartefigue

...
Sur le site où j'interviens actuellement, il y a environ 16% des tables avec plus de 20 colonnes (toutes bases de données confondues)
Mais si on élimine les clefs étrangères et les colonnes mouchard (date, heure, traitement et user de création, date, heure, traitement et user de dernière modif), cette proportion diminue au moins de moitié

Tu as parfaitement raison de le préciser :
ne pas prendre en compte les FK (elle n'apparaissent d'ailleurs pas dans le MCD)
ne pas prendre les données "administratives" (métadonnées du DML de mise à jour)

8% c'est pas énorme... Mais si tu corrèle les tables les plus utilisées par les tables les plus longues en nombre de colonne, je pense que tu devrait être à plus de 50% !!!

A +
05/10/2015, 11h25
frfancha

Citation:

Envoyé par CinePhil

Si cela a été fait, c'est que la syntaxe explicite est meilleure que la syntaxe implicite

Absolument pas, c'est uniquement par symétrie avec le left outer join.

Et de toute fçon c'est un débat qui n'a AUCUN lien avec le design des tables pour la performance
05/10/2015, 11h42
frfancha

Citation:

Envoyé par SQLpro

En effet, si vous ne faite que des index classiques, alors une colonne ne figurant pas dans la clef (à l'exception de celle(s) concernant la clef de l'index clustered) oblige à une double lecture.
MAIS...
SQL Server propose depuis 10 ans, la clause INCLUDE qui permet de rajouter dans l'index non clustered, toutes les colonnes que l'on souhaite afin de rendre l'index couvrant pour la requête...
Dans ce cas, SQL Server sera encore plus rapide que Oracle ou DB2 puisqu'une seule recherche dans l'index donne l'intégralité des informations nécessaire à la requête.

J'allais le dire ;-)
05/10/2015, 12h02
CinePhil

Citation:

Envoyé par frfancha

Citation:

Envoyé par CinéPhil

Si cela a été fait, c'est que la syntaxe explicite est meilleure que la syntaxe implicite

Absolument pas, c'est uniquement par symétrie avec le left outer join.

Et de toute fçon c'est un débat qui n'a AUCUN lien avec le design des tables pour la performance

Sur une discussion de 5 pages étalée sur 5 ans, ce serait bien de préciser à quel message vous répondez.
Je viens de parcourir ces 5 pages en cherchant mes messages mais je n'ai pas trouvé votre citation.
05/10/2015, 13h20
frfancha

Citation:

Envoyé par CinePhil

Sur une discussion de 5 pages étalée sur 5 ans, ce serait bien de préciser à quel message vous répondez.

Vous avez tout-à-fait raison, je n'ai vu qu'après que c'était un ancien message, c'était resorti chez moi à cause de la mise à jour de la liste des tutoriels SQL Server.
Il s'agit de ce message 02/08/2011, 22h49:

Citation:

Envoyé par CinePhil

Peut-être, je ne connais pas la norme par coeur.
Mais je ne compte plus le nombre de requêtes que j'ai débugguées dans nos forums rien qu'en récrivant les jointures avec la syntaxe explicite. C'est pas pour rien qu'on est passé du mélange des conditions de jointures et des conditions de restriction à leur séparation je pense ? Si cela a été fait, c'est que la syntaxe explicite est meilleure que la syntaxe implicite et c'est pour ça que je rappelle cette meilleure syntaxe et que j'encourage vivement tout le monde à l'utiliser et à bannir la vieille syntaxe qui présente tant de défauts.

Citation:

Envoyé par CinePhil

Je viens de parcourir ces 5 pages en cherchant mes messages mais je n'ai pas trouvé votre citation.

CTRL-F le trouve instanément (en étant sur la bonne page bien sûr donc potentiellement 5 ctrl-F)
05/10/2015, 14h11
CinePhil

OK alors je réponds à ça :

Citation:

Absolument pas, c'est uniquement par symétrie avec le left outer join.

Avez-vous une source qui corrobore votre écrit ?

Chez Oracle (je ne sais pas si c'était le cas avec d'autres SGBD), la jointure externe pouvait s'écrire avec un (+) à côté de la table externe de la jointure. Votre argument me semble donc ne pas tenir.
Et il n'est pas question de symétrie puisque la jointure interne (INNER JOIN) n'est pas symétrique de la jointure externe.

Ceci dit, vous avez raison sur un point :

Citation:

Et de toute fçon c'est un débat qui n'a AUCUN lien avec le design des tables pour la performance

Il arrive que les débats dérivent ; ce n'est pas bien grave. Depuis 4 ans, le sujet a quand même été largement traité dans cette discussion.
05/10/2015, 15h44
frfancha

Citation:

Envoyé par CinePhil

Avez-vous une source qui corrobore votre écrit ?

"To allow a consistent coding style for the different types of joins, the SQL-92 standard also added support for a similar JOIN-keyword-based syntax for cross and inner joins. That's the history explaining why you have two standard syntaxes for cross and inner joins and only one standard syntax for outer joins."

T-SQL Querying Itzik Ben-Gan, page 225
05/10/2015, 15h52
CinePhil

OK merci.
N'empêche que je persiste à dire que c'est quand même bien mieux d'écrire les jointures avec JOIN.
12/10/2015, 15h02
Népomucène

chargement dans une page d'une ligne peu remplie

Citation:

une ligne d’une table doit impérativement tenir intégralement dans une page

Et donc "8060 octets dans SQL Server"

Si ma table est composée de 8 colonnes varchar(1000) elle fait donc potentiellement 8000 octets.
Cependant, si ces 8 colonnes ne sont remplies qu'avec 50 caractères chacune,
est-ce SQL Server va estimer la ligne à 8000 octets (taille de la structure) ou bien à 50 X 8 = 400 octets (taille réelle) ;
auquel cas il pourrait charger 20 lignes ...
13/10/2015, 17h16
SQLpro

La barrière de 8060 octets a été levée avec SQL Server 2005 ou 2008 (je ne me souviens plus) mais cela reste immonde car cela engendre du row overflow et donc une fragmentation irréfragable !!!
Avec un VARCHAR(n) la données représentée est la longueur réelle des caractères stockés + 2 octets, soit n + 2. En NVARCHAR(n) c'est 2 *n + 2. Les deux octets supplémentaires servant à indiquer la longueur de la donnée stockée.

A +
13/10/2015, 18h26
Népomucène

Merci de cette précision !

Voir 40 message(s) de cette discussion en une page