Query accross multiple database

Version imprimable

Bonjour,

J'ai besoin de vos avis / conseils pour structurer une requête qui va récupérer des données dans plusieurs bases de production.

Actuellement, j'ai une requête de ce genre sous forme de procédure stockée avec paramètres :
Code:

1 2 3 4 5 6 7 SELECT champ1,champ2,champ3... FROM DB1.dbo.MyTable WHERE ... UNION ALL SELECT champ1,champ2,champ3... FROM DB2.dbo.MyTable WHERE ... UNION ALL SELECT champ1,champ2,champ3... FROM DB3.dbo.MyTable WHERE ... ...
Qui est répété plus de 70 fois. Donc forcément la requête est hyper longue.
Est ce qu'il existe un moyen d'utiliser qu'une seule requête et de boucler dessus en changeant les noms des bases et les cumuler en UNION ALL ?
Si vous avez d'autres solutions, je suis preneur.
Merci d'avance.

Bonne journée. 8-)

28/06/2017, 14h41
SQLpro

Sans la requête compléte avec son WHERE et le détails des colonnes et index de la table impossible de vous aider....

A +
28/06/2017, 15h06
Cyr69

Bonjour SQLpro, je vous ai envoyé un MP avec quelques explications sur le pourquoi de l'exemple de la requête.
Bonne réception.
Merci par avance.

Cordialement.
28/06/2017, 16h59
SQLpro

Les performances sont directement liées à l'écriture de la requête, la structure des tables, la volumétrie et l'indexation.

Il est impossible de changer la volumétrie sans perdre des données.
Vous ne pouvez pas changer la structure des tables si c'est un logiciel tiers.

Il vous reste l'indexation et l'écriture.

Le droit européen consacrant la structure d'une base comme étant une œuvre de l'esprit, cette dernière est donc naturellement protégée par la législation. Vous pouvez donc sans crainte décrire les tables dans ce forum à titre de citation.

Les index étant des éléments techniques d'administration vous appartenant, vous pouvez aussi sans crainte les publier ici même.

La seule chose effectivement confidentiel, reste les données. Il vous suffit de les anonymiser. Il existe des logiciels pour cela.

Sans ces éléments toute tentative pour vous aider est impossible !

A +
29/06/2017, 10h41
Cyr69

Bonjour,

Suite à votre retour, je poste donc la fameuse requête (sans informations confidentielles) qui est répliqué sur X bases avec un union all : https://pastebin.com/WcZx8TMa
Comme je disais dans le premier mail. Il n'y a que les noms des bases qui change dans les jointures le reste est identique.
Je voulais donc savoir s'il existe un moyen d'éviter de répéter la requête et de passer directement par une boucle ? Ce qui serait plus pratique pour maintenir la requête de base.

Qu'en pensez vous ? Merci d'avance.
29/06/2017, 11h11
escartefigue

Bonjour,

Je n'arrive pas a visualiser la page en lien (peut être l'url est elle bloquée par le firewall), pouvez vous poster la requete directement sur ce fil avec des balises code

C'est un réflexe de passer par pastebin :)

ci joint le contenu :

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
 
	SELECT
		    E_DATECOMPTABLE as 'DATE COMPTABLE'	
		   ,YEAR([E_DATECOMPTABLE]) 	AS 'ANNEE DE LA DATE COMPTABLE'
		   ,RIGHT ('0'+ CAST(MONTH([E_DATECOMPTABLE]) AS VARCHAR),2) AS 'MOIS DE LA DATE COMPTABLE'
		   ,E_DATECREATION				AS 'DATE DE CREATION'
		   ,E_DATEMODIF					AS 'DATE DE MODIFICATION'
		   ,E_LIBREDATE					AS 'DATE LIBRE'
		   ,''                          AS 'CODE ENTITE DECLARANTE'
		   ,E_ETABLISSEMENT				AS 'ETABLISSEMENT'
		   ,E_NUMEROPIECE				AS 'NUMERO ECRITURE'
		   ,E_NUMLIGNE					AS 'NUMERO DE LIGNE'
		   ,E_QUALIFPIECE				AS 'TYPE DE MOUVEMENT'
		   ,E_GENERAL                   AS 'COMPTE COMPTABLE'
		   ,''							AS 'AXE ANALYTIQUE'
		   ,E_AUXILIAIRE				AS 'COMPTE AUXILIAIRE'
		   ,G_LIBELLE                   AS 'LIBELLE DU COMPTE GENERAL'
		   ,E_LIBELLE					AS 'LIBELLE ECRITURE'
		   ,E_NATUREPIECE				AS 'NATURE DE LA PIECE'
		   ,SUBSTRING ([E_GENERAL],7,3) AS 'CODE PARTENAIRE'
		   ,E_JOURNAL 					AS 'CODE DU JOURNAL'
		   ,E_DEBIT                     AS 'DEBIT GENERAL'
		   ,E_CREDIT                    AS 'CREDIT GENERAL'
		   ,0   						AS 'DEBIT ANALYTIQUE'
		   ,0   						AS 'CREDIT ANALYTIQUE'
		   ,E_DEBITDEV                  AS 'DEBIT DEVISE '
		   ,E_CREDITDEV                 AS 'CREDIT DEVISE '
		   ,E_TAUXDEV					AS 'VALEUR TAUX DEVISE'
		   ,E_DATETAUXDEV				AS 'DATE VALEUR TAUX DEVISE'
		   ,E_COUVERTUREDEV				AS 'MONTANT EN DEVISE GLOBAL'
		   ,E_DATEREFEXTERNE			AS 'DATE DE REFERENCE EXTERNE'
		   ,E_REFEXTERNE				AS 'REFERENCE EXTERNE'
		   ,E_REFINTERNE				AS 'REFERENCE INTERNE'
		   ,E_REFLIBRE					AS 'REFERENCE LIBRE ECRITURE'
		   ,E_REGIMETVA					AS 'REGIME FISCAL'
		   ,E_TVA						AS 'TAUX DE TVA'
		   ,E_CONTREPARTIEGEN			AS 'COMPTE GENERAL DE CONTREPARTIE'
		   ,E_CONTREPARTIEAUX			AS 'COMPTE AUXILIAIRE DE CONTREPARTIE'
		   ,E_LETTRAGE					AS 'CODE LETTRAGE'
		   ,E_MODEPAIE					AS 'MODE DE PAIEMENT'
		   ,E_DATEECHEANCE				AS 'DATE ECHEANCE'
		   ,E_DEVISE					AS 'DEVISE'
		   ,E_CREERPAR					AS 'CREER PAR'
		   ,E_LIBRETEXTE0				AS 'COMMENTAIRE 0 SUR ECRITURE'
		   ,E_LIBRETEXTE1				AS 'COMMENTAIRE 1 SUR ECRITURE'
		   ,E_LIBRETEXTE2				AS 'COMMENTAIRE 2 SUR ECRITURE'
		   ,E_LIBRETEXTE3				AS 'COMMENTAIRE 3 SUR ECRITURE'
		   ,E_LIBRETEXTE4				AS 'COMMENTAIRE 4 SUR ECRITURE'
		   ,E_LIBRETEXTE5				AS 'COMMENTAIRE 5 SUR ECRITURE'
		   ,E_LIBRETEXTE6				AS 'COMMENTAIRE 6 SUR ECRITURE'
		   ,E_LIBRETEXTE7				AS 'COMMENTAIRE 7 SUR ECRITURE'
		   ,E_LIBRETEXTE8				AS 'COMMENTAIRE 8 SUR ECRITURE'
		   ,E_LIBRETEXTE9				AS 'COMMENTAIRE 9 SUR ECRITURE'
		   ,E_LIBREMONTANT0				AS 'MONTANT LIBRE 0'
		   ,E_LIBREMONTANT1				AS 'MONTANT LIBRE 1'
		   ,E_LIBREMONTANT2				AS 'MONTANT LIBRE 2'
		   ,E_LIBREMONTANT3				AS 'MONTANT LIBRE 3'
		   ,E_TABLE0					AS 'TABLE LIBRE 0'
		   ,E_TABLE1					AS 'TABLE LIBRE 1'
		   ,E_TABLE2					AS 'TABLE LIBRE 2'
		   ,E_TABLE3					AS 'TABLE LIBRE 3'
		   ,E_REFGESCOM					AS 'REFERENCE GESTION COMMERCIALE'
		   ,''    AS 'SECTION ANALYTIQUE'
		   ,''    AS 'METIER'
		   ,''    AS 'SOUS METIER'
		   ,''	  AS 'GEO'
 
  	    FROM XXX.[dbo].[ECRITURE]
		LEFT JOIN XXX.[dbo].[GENERAUX] ON G_GENERAL = E_GENERAL
 
		WHERE YEAR (E_DATECOMPTABLE)= @ANNEE AND  MONTH (E_DATECOMPTABLE)<= @MOIS AND (E_GENERAL>='XXX'AND E_GENERAL<='XXX') AND E_JOURNAL<>'XXX'
 
		UNION ALL
 
		SELECT
		    Y_DATECOMPTABLE 			AS 'DATE COMPTABLE'	
		   ,YEAR([Y_DATECOMPTABLE]) 	AS 'ANNEE DE LA DATE COMPTABLE'
		   ,MONTH([Y_DATECOMPTABLE]) 	AS 'ANNEE DE LA DATE COMPTABLE'
		   ,Y_DATECREATION				AS 'DATE DE CREATION'
		   ,Y_DATEMODIF					AS 'DATE DE MODIFICATION'
		   ,Y_LIBREDATE					AS 'DATE LIBRE'
		   ,''                          AS 'CODE ENTITE DECLARANTE'
		   ,Y_ETABLISSEMENT				AS 'ETABLISSEMENT'
		   ,Y_NUMEROPIECE				AS 'NUMERO ECRITURE'
		   ,Y_NUMLIGNE					AS 'NUMERO DE LIGNE'
		   ,Y_QUALIFPIECE				AS 'TYPE DE MOUVEMENT'
		   ,Y_GENERAL                   AS 'COMPTE COMPTABLE'
		   ,Y_AXE						AS 'AXE ANALYTIQUE'
		   ,''							AS 'COMPTE AUXILIAIRE'
		   ,G_LIBELLE                   AS 'LIBELLE DU COMPTE GENERAL'
		   ,Y_LIBELLE					AS 'LIBELLE ECRITURE'
		   ,Y_NATUREPIECE				AS 'NATURE DE LA PIECE'
		   ,SUBSTRING ([Y_GENERAL],7,3) AS 'CODE PARTENAIRE'
		   ,Y_JOURNAL					AS 'CODE DU JOURNAL'
		   ,0                           AS 'DEBIT GENERAL'
		   ,0                           AS 'CREDIT GENERAL'
		   ,Y_DEBIT                     AS 'DEBIT ANALYTIQUE'
		   ,Y_CREDIT                    AS 'CREDIT ANALYTIQUE'
		   ,Y_DEBITDEV                  AS 'DEBIT DEVISE '
		   ,Y_CREDITDEV                 AS 'CREDIT DEVISE '
		   ,Y_TAUXDEV					AS 'VALEUR TAUX DEVISE'
		   ,Y_DATETAUXDEV				AS 'DATE VALEUR TAUX DEVISE'
		   ,Y_TOTALDEVISE				AS 'MONTANT EN DEVISE GLOBAL'
		   ,Y_DATEREFEXTERNE			AS 'DATE DE REFERENCE EXTERNE'
		   ,Y_REFEXTERNE				AS 'REFERENCE EXTERNE'
		   ,Y_REFINTERNE				AS 'REFERENCE INTERNE'
		   ,Y_REFLIBRE					AS 'REFERENCE LIBRE ECRITURE'
		   ,'-'							AS 'REGIME FISCAL'
		   ,'-'							AS 'TAUX DE TVA'
		   ,Y_CONTREPARTIEGEN			AS 'COMPTE GENERAL DE CONTREPARTIE'
		   ,Y_CONTREPARTIEAUX			AS 'COMPTE AUXILIAIRE DE CONTREPARTIE'
		   ,''							AS 'CODE LETTRAGE'
		   ,''							AS 'MODE DE PAIEMENT'
		   ,''							AS 'DATE ECHEANCE'
		   ,Y_DEVISE					AS 'DEVISE'
		   ,Y_CREERPAR					AS 'CREER PAR'
		   ,Y_LIBRETEXTE0				AS 'COMMENTAIRE 0 SUR ECRITURE'
		   ,Y_LIBRETEXTE1				AS 'COMMENTAIRE 1 SUR ECRITURE'
		   ,Y_LIBRETEXTE2				AS 'COMMENTAIRE 2 SUR ECRITURE'
		   ,Y_LIBRETEXTE3				AS 'COMMENTAIRE 3 SUR ECRITURE'
		   ,Y_LIBRETEXTE4				AS 'COMMENTAIRE 4 SUR ECRITURE'
		   ,Y_LIBRETEXTE5				AS 'COMMENTAIRE 5 SUR ECRITURE'
		   ,Y_LIBRETEXTE6				AS 'COMMENTAIRE 6 SUR ECRITURE'
		   ,Y_LIBRETEXTE7				AS 'COMMENTAIRE 7 SUR ECRITURE'
		   ,Y_LIBRETEXTE8				AS 'COMMENTAIRE 8 SUR ECRITURE'
		   ,Y_LIBRETEXTE9				AS 'COMMENTAIRE 9 SUR ECRITURE'
		   ,Y_LIBREMONTANT0				AS 'MONTANT LIBRE 0'
		   ,Y_LIBREMONTANT1				AS 'MONTANT LIBRE 1'
		   ,Y_LIBREMONTANT2				AS 'MONTANT LIBRE 2'
		   ,Y_LIBREMONTANT3				AS 'MONTANT LIBRE 3'
		   ,Y_TABLE0					AS 'TABLE LIBRE 0'
		   ,Y_TABLE1					AS 'TABLE LIBRE 1'
		   ,Y_TABLE2					AS 'TABLE LIBRE 2'
		   ,Y_TABLE3					AS 'TABLE LIBRE 3'
		   ,''  						AS 'REFERENCE GESTION COMMERCIALE'
		   ,Y_SECTION					AS 'SECTION ANALYTIQUE'
		   ,SUBSTRING(Y_SECTION,1,3)    AS 'METIER'
		   ,SUBSTRING(Y_SECTION,4,3)    AS 'SOUS METIER'
		   ,SUBSTRING(Y_SECTION,10,3)   AS 'GEO'
 
		FROM XXX.[dbo].[ANALYTIQ]
		LEFT JOIN XXX.[dbo].[GENERAUX] ON G_GENERAL = Y_GENERAL
 
		WHERE YEAR (Y_DATECOMPTABLE)=@ANNEE AND  MONTH (Y_DATECOMPTABLE)<=@MOIS AND (Y_GENERAL>='XXX'AND Y_GENERAL<='XXX') AND Y_JOURNAL<>'XXX'

03/07/2017, 11h46
Cyr69

Bonjour,

Je relance le sujet si quelqu'un parmi vous a une idée pour optimiser la requête ? Et comment boucler sur plusieurs bases de données. Et pour cela bien sur il faudra prévoir une table paramètres avec la liste des bases physiques etc :)
Merci par avance.

Bonne journée.
03/07/2017, 14h19
aieeeuuuuu

Bonjour,

Pour améliorer la requête, modifiez votre clause WHERE pour la rendre sargable en évitant les fonctions appliquées aux colonnes.

Pour ce qui est d'effectuer la requête sur toutes vos BDD, vous pouvez éventuellement utiliser la procédure non documentée sys.sp_MSforeachdb. mais il faudra faire le tri pour les bases non concernées (système ou autre)

Par curiosité, quelle est la raison d'une telle architecture ?
05/07/2017, 08h42
SQLpro
Votre table étant une table obèse qui viole la première forme normale par apocope, aucune optimisation de la requête n'est possible sans remodéliser en respectant les forme normales.
A lire : https://blog.developpez.com/sqlpro/p...mances_petites

Cela concerne en particulier toutes les colonnes du style :
- E_LIBRETEXTEn
- E_LIBREMONTANTn
- E_TABLEn
Qui devraient être situées dans 3 tables externes....

A +