Discussion :

[calagan99]

Bonjour,

je cherche le meilleur moyen d'exécuter une requête sur une base dont la modélisation est particulière.
La base contient plusieurs centaines de tables dont le nom représente une codification particulière (ex: VA_10128_B, VI_1256_F, etc).
Chaque table a la même structure : propriété (varchar), horodatage (datetime), valeur (numeric).
Le champ "propriété" contient des valeurs là aussi codifiées (ex: V_1012_H, T_1013_H).
Les tables peuvent contenir plusieurs centaines de milliers d'enregistrements.

Je cherche à générer deux requêtes :
- un one shot qui me permette d'identifier les tables qui contiennent VA% et qui contiennent une valeur commençant par "V" dans la propriété
- une requête quotidienne (exécution planifiée nocturne) qui me ramène (pour les mêmes contraintes que la requête précédente) le nom de la table concaténé au nom de la propriété, et la valeur pour l'horodatage le plus récent.

Je ne vois pas bien comment procéder sans passer par des curseurs.
Seulement l'exécution de curseurs sur cette base est à mon avis proscrite étant donné le nombre de tables, la structure de ces dernières et le nombre d'enregistrements qu'elles contiennent.

Et là, je suis un peu perdu sur la manière de procéder.

Auriez-vous des pistes à me conseiller ?

D'avance merci

[GeekMokona]

Essaye un truc dans le Genre pour ta Première Requête

Le faite de Construire la variable DynamicSQL a partir de ca valeur SQL Serveur va itéré pour la construire

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
declare @DynamicSQL nvarchar(Max)
select @DynamicSQL = COALESCE(@DynamicSQL + CHAR(13) + ' UNION ALL ' + CHAR(13),
                                '') + 
                                'SELECT DISTINCT' +
                                QUOTENAME(Table_NAME,'''') + 
                                ' as [Table Name] FROM ' + 
                                quotename(Table_schema) + '.' + QUOTENAME(TABLE_NAME) +' WHERE Propriete Like ''V%''' 
                                FROM      INFORMATION_SCHEMA.TABLES
                                Where table_name Like 'VA%'
 
 
select @DynamicSQL
 
Execute(@DynamicSQL)

[GeekMokona]

pour la seconde

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
 
declare @DynamicSQL nvarchar(Max)
select @DynamicSQL = COALESCE(@DynamicSQL + CHAR(13) + ' UNION ALL ' + CHAR(13),
                                '') + 
                                'SELECT' +QUOTENAME(Table_NAME,'''') 
                                + '+Propriete as [Name], Min(horodatage) FROM ' + 
                                quotename(Table_schema) + '.' + QUOTENAME(TABLE_NAME) +' WHERE Propriete Like ''V%'' GROUP BY '+
                                QUOTENAME(Table_NAME,'''') + '+Propriete ' 
                                FROM      INFORMATION_SCHEMA.TABLES
                                Where table_name Like 'VA%'
 
 
select @DynamicSQL
 
Execute(@DynamicSQL)

[calagan99]

Merci Mokona,

j'ai adapté la requête car elle ne me renvoyait pas la "top valeur" mais toutes les dates pour une même propriété.

Cela fonctionne mais au bout de 30min d'éxécution j'obtiens une erreur.
Il faut donc que j'optimise la requête.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
declare @DynamicSQL nvarchar(Max)
SELECT @DynamicSQL = COALESCE(@DynamicSQL + CHAR(13) + ' UNION ALL ' + CHAR(13),
    '') + 
    'SELECT dt.[PROPRIETE], dt.[HORODATAGE], dt.[VALEUR] FROM (' +    
    +'SELECT ' +QUOTENAME(Table_NAME,'''') + '+PROPRIETE AS [COMPTEUR], '
    +'VALEUR AS [VALEUR], HORODATAGE AS [HORODATAGE], ROW_NUMBER() OVER (PARTITION BY ' +QUOTENAME(Table_NAME,'''')
    + '+PROPRIETE ORDER BY HORODATAGE DESC) AS RNUM FROM ' + 
    quotename(Table_schema) + '.' + QUOTENAME(TABLE_NAME) +' WHERE PROPRIETE Like ''%.VA_%'' OR PROPRIETE Like ''%.TA_%'' ) as dt where RNUM = 1'
    FROM      INFORMATION_SCHEMA.TABLES
    WHERE table_name LIKE 'VA%'
    --+ '
SELECT @DynamicSQL
Execute(@DynamicSQL)

Et là, je ne vois pas bien quoi faire...

[Invité]

je cherche le meilleur moyen d'exécuter une requête sur une base dont la modélisation est particulière.
La base contient plusieurs centaines de tables dont le nom représente une codification particulière (ex: VA_10128_B, VI_1256_F, etc).
Chaque table a la même structure : propriété (varchar), horodatage (datetime), valeur (numeric).
Le champ "propriété" contient des valeurs là aussi codifiées (ex: V_1012_H, T_1013_H).
Les tables peuvent contenir plusieurs centaines de milliers d'enregistrements.

Ça c'est de la modélisation pour se créer de beaux problèmes !
Un bon moyen de faire une grosse facture à la moindre évolution ?
Bon courage en tout cas !

[calagan99]

Tout à fait!
C'est une modélisation débile d'un outil métier de niche dans laquelle chaque site est stocké dans sa propre table.
Table dans laquelle on retrouve tous les équipements d'un site.
Équipements sur lesquels on reçoit chaque jour plusieurs dizaines de valeurs.

Pour afficher les éléments d'une table, pas trop trop de problème.
Par contre, quand il s'agit de croiser les données, la moindre jointure prend dix minutes.

Mais je n'ai malheureusement pas d'autre choix que de faire avec...

Et je n'ai pas non plus d'autre choix que d'extraire ces données, d'où la volonté de faire du mieux possible pour avoir une exécution la moins pénalisante possible.

[SQLpro]

Ce que vous auriez intérêt à faire est de créer à côté une base bien modélisée et reporter les données dedans par le biais, par exemple, de déclencheurs.
De là vos utilisateurs pourront faire des requêtes performantes et efficaces !

A +

[GeekMokona]

Quel est l'erreur que tu obtient ?

[calagan99]

J'obtiens l'erreur suivante :

Une erreur grave s'est produite sur la commande actuelle. Les résultats éventuels doivent être ignorés.

Je pense à un timeout car l'erreur se produit après 30min d'exécution.

SQLPro : je suis complétement d'accord. Seulement des dizaines de reporting sont branchés sur cette base. Ainsi qu'un outil métier nécessaire à notre exploitation. J'ai déjà indiqué cela à de multiples reprises mais tant que les reportings fonctionnent, quelle que soit leur durée d'exécution, rien ne bouge.

[GeekMokona]

Ok donc oublis la requête que je t'ai donnée .

Déjà a tu un index sur Propriété chacune de table VA% ?

Je pense qu'il serais mieux que tu passe par une table de travaille qui est le résultat de l'union de toutes tes tables VA% + le nom de table, Filtré ou non sur les propriétés si présence d'un index sur la colonne propriété ...

Exemple de table de travail

Code :

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

1
2
3
4
5
Create table ods.TableDeTravail
(TableName  Nvarchar(128)
,[Propriete]  nVarchar(10)
,Valeur  int
,Horodatage  datetime);

Après insertion pose un index sur cette table de travail Sur les colonnes TableName, Propriete et horodatage , cet index ne doit pas exister pendant la phase d'insertion ....

Puis Faire les Requêtes sur cette table de travail

Pour la phase d'insertion si tu as une instance SSIS se serait plus simple de l'utiliser ... un Foreach Loop fera l'affaire pour itérer sur tes tables VA....

[SQLpro]

J'obtiens l'erreur suivante :

Je pense à un timeout car l'erreur se produit après 30min d'exécution.

Vous avez sans doute eu un problème matériel lors de l'exécution de la requête....

A +

[calagan99]

Vous avez sans doute eu un problème matériel lors de l'exécution de la requête....

A +

J'ai exécuté la requête à plusieurs reprises et ai obtenu le même résultat.
Cela dit, il y avait hier des opérations de maintenance sur la base et ce matin la requête est passée en 23minutes.

Je viens au passage de tomber de ma chaise en découvrant qu'il n'y a pas de clé primaire sur les tables.
Juste un index sur le couple PROPRIETE + HORODATAGE.

[SQLpro]

Je viens au passage de tomber de ma chaise en découvrant qu'il n'y a pas de clé primaire sur les tables.
Juste un index sur le couple PROPRIETE + HORODATAGE.

Vous avez raison d'être tombé....
En matière de bases de données relationnelle la règle est que toute table prétendant être relationnelle doit avoir une clef primaire. Sans cela la table est comparable à un vulgaire fichier comme ce qui se faisait en CoBOL !
En sus, MS indique clairement que, pour des raisons de performances : " every table should have a clustered index." ce que fait naturellement la pose d'une clef primaire !!!
http://msdn.microsoft.com/en-us/library/ms186342.aspx
Sans cela les accès a votre table se font essentiellement par balayage de toutes les lignes de la table...

Donc, il y a urgence.

Pour votre erreur, cela peut être un manque de mémoire à un moment clef (acquisition dans une transaction par exemple suite à l'impossibilité d'obtenir une escalade de verrous...)

A +