Discussion :

[Loris_A]

Bonjour à tous,
Je suis nouveau ici et vous m'excuserai par avance si je ne poste pas au bon endroit.
Je suis en alternance en licence pro BDD a Annecy, et en entreprise je rencontre un problème.

J'ai pour mission de faire des insertions en masse sur certaines tables de ma base avec des données provenant de cette même base. Ces données viennent de plusieurs tables et sont nécessaire pour respecter les Foreign-Key. Ce n'est pas grave si les lignes insérées ne sont pas cohérentes c'est seulement pour des tests de performances.

Je vous demande donc quelle serait la meilleure solution pour y parvenir. (Procédures stockées, Outils spécifiques, ...)

Dans l'attente de vos réponses,
Cordialement,

Loris.

[mikedavem]

Tu peux par exemple passer par du code T-SQL (requêtes ou procédures stockées) ou via SSIS également qui est un outil adapté pour cela.
Après tout dépend ce que tu appelles insertion en masse.

++

[Loris_A]

Et bien tout dépend des tables, je dois faire des inserts variant entre 20k et 20M de lignes.

[SQLpro]

les performances en mise à jour (les insertions de lignes font partie des mises à jour) reposent sur de multiples facteurs, mais les plus importants sont :
1) l'ajustement de la RAM
2) la modélisation de la base (respect des formes normales)
3) une bonne architecture du stockage
4) une indexation adéquate
...
cela peut faire varier dans un rapport de 1 à 1 000 les performances !

A +

[Loris_A]

Pour ce qui est des ces 4 points, cela a surement été respecté par les personnes qui ont développé la base.
L'application est aujourd'hui en phase de test, et ces INSERT ont seulement pour but de voir comment l'application fonctionne avec une base de données semblable à celle qui sera mise en production car la migration n'est pas terminée.
Mon travail est simplement d'effectuer ces inserts avec la méthode de mon choix. Réalisant déjà des scripts pour l'école et le travail, je souhaite savoir si des outils comme SSIS par exemple peuvent répondre à mes attentes afin de voir des outils nouveau.

A+

[Invité]

Pour ce qui est des ces 4 points, cela a surement été respecté par les personnes qui ont développé la base.

Ne jamais rien prendre pour acquis, bien au contraire ! Le doute fait gagner beaucoup de temps.

[Loris_A]

Ce n'est pas faux. J'ai seulement pu vérifier la modélisation et les indexes. Pour ce qui est du reste ce sont des notions qui me sont floues.
Après l'optimisation des performances n'est pas ma tâche principale, effectivement si je peux le faire ce sera toujours un plus. Je doit avant tout insérer les données, et une autre équipe s'occupera de tester et corriger.

[SQLpro]

Pour ce qui est des ces 4 points, cela a surement été respecté par les personnes qui ont développé la base.
L'application est aujourd'hui en phase de test, et ces INSERT ont seulement pour but de voir comment l'application fonctionne avec une base de données semblable à celle qui sera mise en production car la migration n'est pas terminée.
Mon travail est simplement d'effectuer ces inserts avec la méthode de mon choix. Réalisant déjà des scripts pour l'école et le travail, je souhaite savoir si des outils comme SSIS par exemple peuvent répondre à mes attentes afin de voir des outils nouveau.

A+

Les audits que je pratique et mes interventions en pompier, me montre que c'est très généralement strictement le contraire !

Les vraies compétences en matières de SGBDR et de DBA SQL Server sont rare et chères... Donc, on s'en passe jusqu'au jour ou tout foire !

je vous propose 3 requête pour mesurer la qualité de votre système :

La première regarde si vos bases sont en auto croissance au niveau des fichiers ce qu'il ne faut jamais faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT DISTINCT DB_NAME(database_id), 'DANGER : fichier(s) de la base en auto croissance !'
FROM   sys.master_files
WHERE  database_id > 4
  AND  name NOT LIKE 'ReportServer%'
  AND  is_percent_growth = 1;

La seconde (à faire dans le contexte de votre base de production) regarde si vos tables ne sont pas obèses :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT T.TABLE_SCHEMA, T.TABLE_NAME,
       CASE  
          WHEN COUNT(*) > 160 THEN 'ATTERANT'
          WHEN COUNT(*) > 80 THEN 'HORRIBLE'
          WHEN COUNT(*) > 40 THEN 'DÉGUEULASSE'
          ELSE 'MAUVAIS'
       END           
FROM   INFORMATION_SCHEMA.COLUMNS AS C
       INNER JOIN INFORMATION_SCHEMA.TABLES AS T
             ON C.TABLE_SCHEMA = T.TABLE_SCHEMA 
             AND C.TABLE_NAME = T.TABLE_NAME
WHERE  TABLE_TYPE = 'BASE TABLE' 
GROUP  BY T.TABLE_SCHEMA, T.TABLE_NAME
HAVING COUNT(*) > 20;

Tout ce qui apparait dans ce résultat sont des tables potentiellement mal modélisées...
Si tel est le cas, lisez l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p1...mances_petites


La dernière vérifie la présence des contraintes CHECK et FOREIGN KEYs :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
50
51
52
53
54
55
56
57
58
59
60
61
62
WITH
TT AS
(
SELECT CAST(COUNT(*) AS FLOAT) AS NOMBRE_TABLE
FROM   sys.objects
WHERE  "TYPE" = 'U'
),
TC AS
(
SELECT CAST(COUNT(*) AS FLOAT) AS NOMBRE_COLONNE
FROM   INFORMATION_SCHEMA.COLUMNS AS C
       INNER JOIN INFORMATION_SCHEMA.TABLES AS T
             ON C.TABLE_SCHEMA = T.TABLE_SCHEMA 
             AND C.TABLE_NAME = T.TABLE_NAME
WHERE  TABLE_TYPE = 'BASE TABLE' 
),
TCK AS
(
SELECT SUM(NOMBRE_CHECK) AS NOMBRE_CHECK
FROM   (SELECT CAST(COUNT(*) AS FLOAT) AS NOMBRE_CHECK
        FROM   sys.objects
        WHERE  "TYPE" = 'C'
        UNION ALL
        SELECT COUNT(*)
        FROM   sys.columns AS c  
               INNER JOIN sys.types AS t 
                     ON c.user_type_id = t.user_type_id 
               INNER JOIN sys.objects AS r 
                     ON t.rule_object_id = r.object_id) AS T_CHEK
),
TFK AS
(
SELECT DISTINCT CAST(COUNT(object_id) OVER() AS FLOAT) AS NOMBRE_FK
FROM   sys.objects
WHERE  "TYPE" = 'F'
),
T AS
(
SELECT NOMBRE_TABLE, (NOMBRE_CHECK / NOMBRE_COLONNE) * 100 AS RATIO_CHECK, 
                     (NOMBRE_FK/ NOMBRE_TABLE) * 100 AS RATIO_FK 
FROM   TT
       OUTER APPLY TC
       OUTER APPLY TCK
       OUTER APPLY TFK
)
SELECT NOMBRE_TABLE, 
       RATIO_CHECK, CASE WHEN RATIO_CHECK > 80 THEN 'excellent'
                         WHEN RATIO_CHECK > 40 THEN 'bien'
                         WHEN RATIO_CHECK > 20 THEN 'correct'
                         WHEN RATIO_CHECK > 10 THEN 'faible'
                         WHEN RATIO_CHECK >= 5 THEN 'insignifiant'
                         WHEN RATIO_CHECK < 5 THEN 'mauvais'
                         ELSE 'nul !' END AS RATIO_CHECK_QUALITY,
       RATIO_FK, CASE WHEN RATIO_FK > 100 THEN 'excellent'
                         WHEN RATIO_FK > 80 THEN 'bien'
                         WHEN RATIO_FK > 60 THEN 'correct'
                         WHEN RATIO_FK > 40 THEN 'faible'
                         WHEN RATIO_FK >= 20 THEN 'insignifiant'
                         WHEN RATIO_FK < 20 THEN 'mauvais'
                         ELSE 'nul !' END AS RATIO_FK_QUALITY
 
FROM T;

L'absence des contraintes empêche l'optimiseur de fournir des requêtes efficaces !

Dites nous les résultats de ces trois requêtes... peut être me trompe-je, mais je pense que vous ne serez pas déçue et redescendrez de votre piédestal !

A +

[Loris_A]

Bonjour,

Alors la première requête ne retourne aucunes lignes.

La seconde retourne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
TABLE_SCHEMA	TABLE_NAME	(No column name)
dbo	           T1	            MAUVAIS
dbo	           T2	            MAUVAIS
dbo	           T3	            MAUVAIS
dbo	           T4	            MAUVAIS
dbo	           T5	            MAUVAIS
dbo	           T6	            MAUVAIS
dbo	           T7	            DÉGUEULASSE
dbo	           T8	            MAUVAIS
dbo	           T9               MAUVAIS
dbo	           T10              MAUVAIS
dbo	           T11	            MAUVAIS

Ensuite pour la dernière requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
NOMBRE_TABLE	RATIO_CHECK	             RATIO_CHECK_QUALITY	    RATIO_FK	         RATIO_FK_QUALITY
301	        0,0544069640914037	      mauvais	                   202,65780730897	excellent

[Kropernic]

Sur 301 tables, n'avoir "que" 11 tables "obèses" est plutôt un bon score je trouve.

Bon maintenant, sqlpro a défini la limite acceptable à 20 colonnes. Peut-être est-ce trop (probable), pas assez (sûrement pas) ?

Dans ma pratique, dans la DB la plus utilisée, la table avec le plus de colonnes en compte 14. Et encore, 4 de ces colonnes sont des colonnes "de tracking" contenant quand et par qui une ligne a été insérée ainsi que quand et par qui une ligne a été modifiée pour la dernière fois (histoire de pouvoir taper sur les doigts de quelqu'un en cas de boulette^^). Cela ne fait donc que 10 colonnes significatives au niveau de la modélisation.

[SQLpro]

Il y a sans doute quelques tables à revoir. Sinon, l'absence de contraintes CHECK est souvent pénalisante....

Le reste semble pas mal !

A +

[Loris_A]

Merci pour vos réponses, je ne pense pas qu'il y ait beaucoup de modifications possibles a apporter au niveau de la taille des tables, mais par contre pour les CHECK je vais y jeter un œil.

Pour ce qui est de mon problème initial, je vais donc utiliser SSIS pour mes insertions.