Discussion :

[OokieDookie91]

Bonjour à tous,

Je travaille sur un projet nécessitant des extractions multiples, et au sein d'un même jeu de résultat je peux avoir des périodes de références différentes pour une colonne donnée.

Dans les grandes lignes, je voulais utiliser des variables... dans une vue
Après lecture du forum , j'ai du me résoudre à utiliser une table de paramètres pour entrer une date de référence, et via DATEADD je calcule les dates relatives à mes traitements. Pas de souci de ce côté-là, mais la lecture est devenue un poil rébarbative, car au sein des vues de bases il y de nombreux UNION ALL pour gérer ces conditions variant en fonction de la donnée récupérée, et donc autant de WHERE.

Au-delà de la cosmétique, je me demande si la création de fonctions peut-être (ou non) un plus. Dans le cadre de ce projet mon seul besoin est de passer des dates à de nombreuses clauses WHERE, mais si votre vision est plus large ça m’intéresse aussi bien sûr

Merci de vos conseils et lumières.

Ci-après quelques exemples, j'aimerai remplacer les - longs - DATEADD par des noms conviviaux. J'aimerai notamment avoir des précisions sur les implications en termes de performance. (+/- 1 millions de lignes).

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @Annee INT, @DateDuJour DATETIME, @DateDebN1 DATETIME, @DateFinN1 DATETIME, 
@DateDebCPN DATETIME, @DateFinCPN DATETIME, @DateDebMoisM1 DATETIME, @DateFinMoisM1 DATETIME
 
SET @DateDuJour = GETDATE()
SET @DateDebN1 = DATEADD(yy, YEAR(GETDATE()) - 2001, '20000101')
SET @DateFinN1 = DATEADD(yy, YEAR(GETDATE()) - 2001, '20001231')
SET @DateDebCPN = DATEADD(yy, YEAR(GETDATE()) - 2000, '20000601')
SET @DateFinCPN = DATEADD(yy, YEAR(GETDATE()) - 2000, '20010531')
SET @DateDebMoisM1 = DATEADD(mm, MONTH(GETDATE()) - 2, DATEADD(yy, YEAR(GETDATE()) - 2000, '20000101'))
SET @DateFinMoisM1 = DATEADD(dd, -1, DATEADD(mm, MONTH(GETDATE()) - 1, DATEADD(yy, YEAR(GETDATE()) - 2000, '20000101')))
 
SELECT @DateDuJour AS DateDuJour, @DateDebN1 AS DateDebN1, @DateFinN1 AS DateFinN1, @DateDebCPN AS DateDebCPN,
@DateFinCPN AS DateFinCPN, @DateDebMoisM1 AS DateDebMoisM1, @DateFinMoisM1 AS DateFinMoisM1

[aieeeuuuuu]

Bonjour,

Selon quels critères varient vos périodes ?
Comment sont construites vos clauses WHERE ? de manière générale, évitez d'appliquer des fonctions (udf on built-in) sur les colonnes...

Attention avec les fonctions scalaires, leur cout n'est pas inclus dans les plans d’exécution, ce qui est trompeur : elles semblent plus performantes, mais c'est souvent tout le contraire.

préférez les fonctions table en lignes.

Si vous postiez votre code, on pourrait vous donner des réponses plus concrètes

[OokieDookie91]

Bonjour,

J'ai une table de cumuls qui archive par mois, notion et salarié les historiques d'un bulletin de salaire.
J'ai donc des notions variées telles que salaire brut, heures travaillées, et brut congés payés, identifiées par une colonne "Entité"

Pour l'exemple, admettons que je doive produire, en un seul jeu de résultats :
- la somme des salaires de l'année en cours
- le nombre d'heures travaillées le mois dernier
- la base congés payés utilisées dans le calcul de la rémunération

Ce qui correspond pour Juin 2013 à :
Notion, Début, Fin
Brut, 01/01/2013, 31/12/2013
HTrav, 01/05/2013, 31/05/2013
CP, 01/06/2013, 31/05/2014

Que j'exprime donc ainsi :

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
SELECT Salarie, Entite, SUM(ValeurCumul) AS Valeur
FROM T_HCUM HC
WHERE DateHist BETWEEN DATEADD(yy, YEAR(GETDATE()) - 2000, '20000101') AND DATEADD(yy, YEAR(GETDATE()) - 2000, '20001231')
AND Entite = 'BRUT'
GROUP BY Salarie, Entite
 
UNION ALL
 
 
SELECT Salarie, Entite, SUM(ValeurCumul) AS Valeur
FROM T_HCUM HC
WHERE DateHist BETWEEN DATEADD(mm, MONTH(GETDATE()) - 2, DATEADD(yy, YEAR(GETDATE()) - 2000, '20000101')) AND DATEADD(dd, -1, DATEADD(mm, MONTH(GETDATE()) - 1, DATEADD(yy, YEAR(GETDATE()) - 2000, '20000101')))
AND Entite = 'HTRAV'
GROUP BY Salarie, Entite
 
UNION ALL
 
SELECT Salarie, Entite, SUM(ValeurCumul) AS Valeur
FROM T_HCUM HC
WHERE DateHist BETWEEN DATEADD(yy, YEAR(GETDATE()) - 2000, '20000601') AND DATEADD(yy, YEAR(GETDATE()) - 2000, '20010531')
AND Entite  = 'CP'
GROUP BY Salarie, Entite

Exemple sur 3 valeurs, mais je peux en gérer beaucoup plus.
Et je cherche plus simple que de commenter chaque DATEADD, mais pas si les perfs doivent chuter.

Merci

[aieeeuuuuu]

Dans ce cas, en effet, vous pouvez créer des fonctions pour rendre les requêtes plus lisibles

Par ailleurs, vous pouvez tester de remplacer vos UNION ALL par:

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
 
SELECT Salarie, Entite, SUM(ValeurCumul) AS Valeur
FROM T_HCUM HC
WHERE (
    Entite = 'BRUT' 
    AND DateHist BETWEEN 
            DATEADD(yy, YEAR(GETDATE()) - 2000, '20000101') 
            AND DATEADD(yy, YEAR(GETDATE()) - 2000, '20001231')
)
OR(
     Entite = 'HTRAV'
     AND DateHist BETWEEN 
            DATEADD(mm, MONTH(GETDATE()) - 2, DATEADD(yy, YEAR(GETDATE()) - 2000, '20000101'))
            AND DATEADD(dd, -1, DATEADD(mm, MONTH(GETDATE()) - 1, DATEADD(yy, YEAR(GETDATE()) - 2000, '20000101')))
)
OR (
    Entite  = 'CP'
    AND DateHist BETWEEN 
             DATEADD(yy, YEAR(GETDATE()) - 2000, '20000601') 
             AND DATEADD(yy, YEAR(GETDATE()) - 2000, '20010531')
)
GROUP BY Salarie, Entite

[Kropernic]

Oui mais avec un opérateur OR dans la clause where, la requête n'est plus "sargable". Ce qui va dégrader les performances non ?

[OokieDookie91]

Bonjoue et désolé pour ce retour tardif

Suite à la remarque de Kropernic, j'ai découvert la notion de requête sargable

Donc d'après ce que j'ai compris, dans les grandes lignes :
- même si c'est plus contraignant, il faudrait effectivement que je conserve les UNION ALL.
- En revanche, l'utilisation de fonctions sur mes "paramètres" ne devrait apparemment pas altérer les performances outre mesure, étant donné que j'évite bien d'utiliser des fonctions type YEAR etc.

J'ai bien capté le truc ?

[Kropernic]

Utilisé des fonctions sur les paramètres (par exemple dans une SP), c'est ok.

Ce qu'il ne faut pas faire, c'est utilisé une fonction sur une colonne de la table. Car du coup, l'index n'est plus utilisable (à moins d'avoir créer un index spécialement prévu pour).

Je ne sais pas si c'est cet article que tu vas vu mais sqlpro a écrit un article détaillé sur ce qui est sargable ou non.

[OokieDookie91]

Oui c'est bien celui là.

Et donc je pourrais remplacer dans le dernier tableau :

Code :

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

1
2
3
4
5
6
7
8
--Non sargable :
YEAR(COL_DATE) = 2008 AND MONTH(COL_DATE) = 9 
 
--Sargable :          
COL_DATE >= '2008-09-01' AND COL_DATE < '2008-09-30'  
 
--Sargable + fonction sur table paramètre :
COL_DATE >= dbo.FnDebPeriode AND COL_DATE < dbo.FnFinPeriode

Donc je n'applique pas la fonction sur la colonne mais sur la borne, et donc ça devrait le faire, non ?

[Kropernic]

Y a un p'tit souci dans les bornes de votre 2e example je pense ^^.

Mais bon, j'ai compris l'idée

EDIT : Le message précédent a été corrigé.

[OokieDookie91]

Oui j'ai copié/collé...
Au moins c'est propre pour les suivants

Merci encore