Discussion :

[ganjakass]

Bonjour, j aimerai calculer l'intervalle de temps entre chaque écriture dans ma table.
J ai déjà une colonne dateTime qui me donne la date et l heure a chaque écriture dans la table.
J aimerai rajouter une colonne me donnant la difference de temps comprise jusqu a l ecriture suivant.
Genre colonne "dureeTps" = ("dateTime" de la ligne 3 ) - ("dateTime" de la ligne 2)
Est- ce possible d effectuer cela en rajoutant une colonne calculé à partir de datediff contenu dans la même colonne?

D avance merci de vos retour.

[al1_24]

Sans les fonctions analytiques ou fenêtrées :

Code :

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

1
2
3
4
5
6
7
8
select  tps.heure       as heure_fin
    ,   max(prc.heure)  as heure_prec
from    matable tps
    inner join
        matable prc
        on  tps.heure > prc.heure
-- Edit : (21/11/14 09:37) ligne oubliée
group by tps.heure

[ganjakass]

désolé je suis novice en language SQL,
peut m'expliquer ton bout de code, stp.
pas possible de faire avec dateDiff?

[pcaboche]

Tu as ta table avec les dates.
Il va chercher la date précédente, c'est-à-dire : il va chercher les dates précédentes (en faisant ce qu'on appelle une "inéqui-jointure") et il ne garde que la dernière de ces dates ( MAX() ).

(au passage, il manque un GROUP BY pour pouvoir faire le MAX() )

Ça, c'est en ce qui concerne la jointure.
Une fois que l'on récurpère la dernière date, il est possible de faire un DATEDIFF entre la date courante (tps.heure) et la date précédente ( MAX(prc.heure) ).

Note :
On explique le principe. Par contre, on n'est pas là pour faire le boulot à la place des visiteurs (c'est interdit, il faut un permis de travail. lol).
Il faut faire un minimum de recherches (ici : inéqui-jointure, MAX, GROUP BY).

[al1_24]

As-tu testé cette requête ?
As-tu essayé de la "démonter" (enlever le group by et le max, par exemple) pour comprendre son fonctionnement ?
Pour le datediff() : tu as les dates extrèmes, ça n'est plus vraiment compliqué de calculer la différence...

-- Edit :
Merci pcaboche, j'avais en effet oublié de copier la dernière ligne de la requête. Correction effectuée.

[ganjakass]

Merci pour vos explications, je vais tester cela et reviendrai cloturer ou poser d'autres questions si besoins.

Bonne journée

[pcaboche]

Merci pour vos explications, je vais tester cela et reviendrai cloturer ou poser d'autres questions si besoins.

Pas de problème.

désolé je suis novice en language SQL,

Désolé, mais vu ta localisation, je suis obligé de faire la blague :

[ganjakass]

Ahaha
Le Cenovis, un produit bien de chez nous.
Pas de problème c'est compréhensible et drôle.
Il faudrait offrir une tartine pour Tsonga et Monfils, peut etre que ca leur portera change.
lol

[Donpi]

Sinon depuis SQL2012 il y a les fonctions LEAD et LAG pour faire des traitement sur les données des lignes suivantes ou précédentes.

[aieeeuuuuu]

Sinon depuis SQL2012 il y a les fonctions LEAD et LAG pour faire des traitement sur les données des lignes suivantes ou précédentes.

comme ganjakass est en 2008R2, il faut s'en passer.

Mais on peut les remplacer par ceci, qui évite l'autojointure :

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
 
CTE AS (
	SELECT 
		heure
		,ROW_NUMBER() OVER(ORDER BY heure) AS Rn
	FROM matable
)
SELECT
	heure
	,CASE WHEN RN%2 = 1
		THEN
			MAX(CASE WHEN RN%2 = 0 THEN heure END) OVER(PARTITION BY Rn/2)
		ELSE
			MAX(CASE WHEN Rn%2 = 1 THEN heure END) OVER(PARTITION BY (Rn-1)/2)
	END		AS HeurePrecedente
from cte

[pcaboche]

comme ganjakass est en 2008R2, il faut s'en passer.

Mais on peut les remplacer par ceci, qui évite l'autojointure :

Non. Les fonctions avec fenêtrage (toutes celles avec la clause OVER dedans), c'est relativement lent.
Tu suggères d'en utiliser 2, c'est encore pire.

Si c'est ça, autant faire :

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
; WITH CTE
AS
(
  SELECT 
    heure
    , ROW_NUMBER() OVER(ORDER BY heure) AS Rn
  FROM matable
)
SELECT 
  cur.heure,
  DATEDIFF(ms, prec.heure, cur.heure)
    AS duration
FROM CTE cur
LEFT JOIN CTE prec
  ON prec.Rn = cur.Rn - 1
ORDER BY cur.Rn

Dans l'exemple précédent, la CTE apparaît dans une auto-jointure (self-join), et dans ce cas elle est invoquée 2 fois, ce qui n'est pas terrible (mais pas beaucoup pire que d'utiliser plusieurs fonctions de fenêtrage).

Pour éviter d'avoir la même requête invoquée 2 fois, on peut stocker le résultat dans une table temporaire, mais bof...

Donc la solution de al1_24 reste la meilleure.

Mais le mieux niveau performances, c'est de ne pas calculer la durée en SQL mais dans le programme lui-même (car il est alors relativement facile de connaître la valeur précédente dans une liste ordonnée d'enregistrements).

Pour se rendre compte des différences de performance, comparer les plans d'exécution des différentes solutions :
- dans un même script, mettre plusieurs de ces requêtes
- ouvrir le plan d'exécution (théorique pour ne pas avoir à les exécuter, réel pour avoir les vraies valeurs)
- comparer les pourcentages de chaque requête (plus faible -> plus rapide)

[iberserk]

Non. Les fonctions avec fenêtrage (toutes celles avec la clause OVER dedans), c'est relativement lent.

La vous vous avancez sacrément!!! le contraire a même était maintes fois démontré, et de ma propre expérience il y a souvent pas photos...

Bien évidemment, prototyper toujours prototyper car sur certaines requètes celà peut dépendre...

[pcaboche]

La vous vous avancez sacrément!!! le contraire a même était maintes fois démontré, et de ma propre expérience il y a souvent pas photos...

C'est pour cela que j'ai utilisé le mot "relativement".

Il y a aussi des articles (plus ou moins pertinents) qui tendent a prouver que c'est lent. Comme celui-là :
https://www.simple-talk.com/sql/t-sq...dow-functions/

Donc qui est dans le vrai ?
Ça dépend du contexte (voir plus bas (*) ).

Ok, ça ne fait pas vraiment avancer le schmilblick...

Un problème que je peux constater, c'est que certains ont tendance à abuser des fonctions de fenêtrage, en utilisant plusieurs dans la même requête (comme c'est le cas de la requête de aieeeuuuuu) et quand on regarde le plan d'exécution, des fois c'est vraiment pas terrible.

Donc au final, il n'y a pas de secret : il faut tester.

Ça veut dire :
- regarder le plan d'exécution (pour voir s'il y a beaucoup d'opérations coûteuses)
- vérifier s'il existe une solution avec les méthodes "traditionelles"
- si oui, se demander :
-> est-ce qu'elle est facile à implémenter ?
-> a-t-on besoin d'un éventuel gain de performance dans le contexte en question ?

- si oui, est-ce qu'il y a un gain de performance ? (plan d'exécution encore)
- faire un choix (vaut-il mieux une requête plus rapide ou plus lisible ?)

----

(*) "Ça dépend du contexte" :

sur certaines requètes celà peut dépendre...

...pas uniquement de la requête, mais aussi des données.

Prenons un exemple : "Pour chaque enregistrement d'une table A, on veut le dernier enregistrement d'une table d'historique H".
Là, ça dépend de la volumétrie.
Si pour un enregistrement dans A le nombre d'enregistrements correspondant dans H est raisonnable, alors utiliser une fonction de fenêtrage de type ROW_NUMBER() OVER (PARTITION BY ...) aura de bonnes performances.
En revanche, si pour un enregistrement dans A il y a de nombreux enregistrements dans H (ex: plusieurs milliers, voire plus), les performances risquent d'être très mauvaises (car les opérations de tri sont très coûteuses dans ce contexte).

La encore, il faut mesurer (et regarder le plan d'exécution).

----

En regardant ce thread, on se dit que ça fait beaucoup de choses nouvelles pour un débutant.

À mon avis, ce qu'il faut retenir :
- il faut souvent mesurer et comparer
- les performances peuvent dépendre de la requête, mais aussi du volume de données
- pour cela, il faut apprendre à comparer des plans d'exécution

[aieeeuuuuu]

Non. Les fonctions avec fenêtrage (toutes celles avec la clause OVER dedans), c'est relativement lent.

Relativement par rapport à quoi ? à une auto jointure ? j'en doute...

Donc la solution de al1_24 reste la meilleure.

Je ne serai pas aussi péremptoire.

Déjà, pour qu'elles soient équivalentes, il faudrait faire une jointure externe pour conserver la date la plus ancienne (qui n'a donc pas de correspondance dans la jointure)

Testons !

Création et peuplement de la table avec 10 000 lignes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--creation de la table
CREATE TABLE matable (
	id		INT NOT NULL PRIMARY KEY IDENTITY
	,heure	DATETIME2(0)
)
 
--ajout de 10 000 lignes
;WITH cte AS (
	select top(10000) row_number() over(order by a.object_id) as n
	from sys.objects A, sys.objects B, sys.objects C, sys.objects D
)
INSERT INTO matable(heure)
	SELECT	dateadd(s, n, '1970-01-01')
	FROM	cte

requete avec jointure (externe) :

Table 'Worktable'. Nombre d'analyses 1, lectures logiques 320110, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'matable'. Nombre d'analyses 4, lectures logiques 99, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

 SQL Server \endash Temps d'exécution*: 
, Temps UC = 23900*ms, temps écoulé = 25043*ms.

requete avec fonction fenêtrée :

Table 'Worktable'. Nombre d'analyses 6, lectures logiques 80006, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'matable'. Nombre d'analyses 1, lectures logiques 26, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

 SQL Server \endash Temps d'exécution*: 
, Temps UC = 125*ms, temps écoulé = 204*ms.

Soit environ 120 fois plus rapide, avec 4 fois moins d'IO...


Avec un index :

Code :

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

CREATE  INDEX IX_HEURE ON matable(heure )

La requete avec jointure s'en sort mieux :

Table 'matable'. Nombre d'analyses 10003, lectures logiques 123921, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

 SQL Server \endash Temps d'exécution*: 
, Temps UC = 13027*ms, temps écoulé = 7813*ms.

Mais reste encore à la traine par rapport à la requete avec fonctions fenêtrées :

Table 'Worktable'. Nombre d'analyses 6, lectures logiques 80006, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'matable'. Nombre d'analyses 1, lectures logiques 22, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

 SQL Server \endash Temps d'exécution*: 
, Temps UC = 109*ms, temps écoulé = 222*ms.

Il y a aussi des articles (plus ou moins pertinents) qui tendent a prouver que c'est lent. Comme celui-là :
https://www.simple-talk.com/sql/t-sq...dow-functions/

Plutôt moins (pertinent) que plus !
Le cas d'utilisation des fonctions fenêtrées dans cet article n'est pas le cas le plus pertinent. Il est facile de démontrer qu'un tracteur est plus rapide qu'une Ferrari, si l'on effectue les tests au milieu d'un champs après trois jours de pluie !
D'ailleurs cet article est presque de la désinformation... Pourquoi un CROSS APPLY alors qu'une simple jointure sur une table dérivée aurait suffit :
- heureusement, l'optimiseur "corrige" l'erreur et fourni le même plan d’exécution qu'avec une jointure
- malheureusement, le lecteur peu averti (et/ou peu critique) conclut trop rapidement "vive cross apply" comme on peu le voir dans les commentaires.

Malgré tout, il contient un (trop) petit paragraphe spécifique aux fonctions de classement (donc celles que nous utilisons ici), lequel se termine par un

Kudos to Microsoft on this one!

Donc au final, il n'y a pas de secret : il faut tester.

Ça veut dire :
- regarder le plan d'exécution (pour voir s'il y a beaucoup d'opérations coûteuses)
- vérifier s'il existe une solution avec les méthodes "traditionelles"
- si oui, se demander :
-> est-ce qu'elle est facile à implémenter ?
-> a-t-on besoin d'un éventuel gain de performance dans le contexte en question ?

- si oui, est-ce qu'il y a un gain de performance ? (plan d'exécution encore)
- faire un choix (vaut-il mieux une requête plus rapide ou plus lisible ?)

Là par contre, on est bien d'accord !
Attention toutefois aux comparaisons des plan d’exécution effectuées par SSMS (même les plans réels) : les pourcentages relatifs indiqués ne correspondent pas toujours à la réalité. Il convient de vérifier également les temps d’exécution et le IO des requêtes comparées.

[pcaboche]

Déjà, pour qu'elles soient équivalentes, il faudrait faire une jointure externe pour conserver la date la plus ancienne (qui n'a donc pas de correspondance dans la jointure)

Testons !

(...)

Ok, j'aurais pensé l'inverse. Comme quoi, on peut avoir des surprises.

[Donpi]

Déjà, 1000 excuses pour avoir détourné le sujet.
Vraiment ce n’était pas le but.

Mais pour ajouter mon grain de sel à la défense des fonctions fenêtrées, personne a abordé la consommation de RAM.

Dans mon cas concret j'ai une table de prix avec ~1 millions de prix et une table de mouvements de stock avec 80 millions de mouvements.
Lorsque je veux connaître le stock aux jours les jours par date et par prix je fais un savant mélange de fonctions fenêtrées.
LEAD et LAG pour les prix et SUM Unbounded preceding, là j'ai un résultat complet en moins de 5 minutes.

J'ai essayé de le réaliser en 2008R2 avec des "with" et des autos jointures mais la consommation de Ram explose et il est juste impossible d'avoir un résultat.

Voilà, encore une fois ce n'était pas le but de détourner le sujet.

A+

[pcaboche]

Dans mon cas concret j'ai une table de prix avec ~1 millions de prix et une table de mouvements de stock avec 80 millions de mouvements.
Lorsque je veux connaître le stock aux jours les jours par date et par prix je fais un savant mélange de fonctions fenêtrées.
LEAD et LAG pour les prix et SUM Unbounded preceding, là j'ai un résultat complet en moins de 5 minutes.

J'ai essayé de le réaliser en 2008R2 avec des "with" et des autos jointures mais la consommation de Ram explose et il est juste impossible d'avoir un résultat.

Ok, j'aurais appris un truc aujourd'hui (par rapport à la performance des fonctions fenêtrées, j'ai longtemps cru qu'elles étaient lentes).

Merci d'avoir détourné la conversation...

[iberserk]

Déjà, 1000 excuses pour avoir détourné le sujet.
Vraiment ce n’était pas le but.

Mais pour ajouter mon grain de sel à la défense des fonctions fenêtrées, personne a abordé la consommation de RAM.

Dans mon cas concret j'ai une table de prix avec ~1 millions de prix et une table de mouvements de stock avec 80 millions de mouvements.
Lorsque je veux connaître le stock aux jours les jours par date et par prix je fais un savant mélange de fonctions fenêtrées.
LEAD et LAG pour les prix et SUM Unbounded preceding, là j'ai un résultat complet en moins de 5 minutes.

J'ai essayé de le réaliser en 2008R2 avec des "with" et des autos jointures mais la consommation de Ram explose et il est juste impossible d'avoir un résultat.

Voilà, encore une fois ce n'était pas le but de détourner le sujet.

Quand vous parlez de RAM nous parlons d'io...

[SQLpro]

Ok, j'aurais appris un truc aujourd'hui (par rapport à la performance des fonctions fenêtrées, j'ai longtemps cru qu'elles étaient lentes).

Cela servirait à quoi d'introduire de nouvelles fonctionnalité qui seraient plus lentes que des équivalents sous forme de sous requêtes et autres constructions (vues, CTE...) ?

Heureusement que dans la plupart des cas les fonctions de fenêtrage tirent les perf vers le haut, car dans leur conceptions elles ne doivent faire en principe qu'une seule passe dans la table alors qu'avec les sous requêtes c'est bien plus !

A +

[pcaboche]

Cela servirait à quoi d'introduire de nouvelles fonctionnalité qui seraient plus lentes que des équivalents sous forme de sous requêtes et autres constructions (vues, CTE...) ?

Pour faciliter la vie du dévelopeur, parce que la fonctionnalité est demandée, mais pas forcément pour des raisons de performance.

Par exemple, toutes les fonctions XML :
- c'est bien, on peut stocker des données semi-structurées sans avoir un modèle relationnel de fou...
- par contre, qu'est-ce que c'est lent ! (ne serait-ce que les opérations de base : ajout, suppression, remplacement...)

Donc je fais la distinction entre :
- nouvelle fonctionnalité, pour faciliter la vie du développeur (ou pour raisons marketing)
- amélioration de performances (qui peut survenir bien plus tard, lors d'un patch ou changement de version...)


J'ai utilisé ROW_NUMBER() pour la première fois sur 2005 et je ne serais pas surpris que les performances n'aient pas été au rendez-vous à l'époque, et qu'ils aient amélioré les performances plus tard...

Une recherche rapide sur "ROW_NUMBER 2005 performance" :
http://jerschneid.blogspot.sg/2008/0...ber-sucks.html
http://thehobt.blogspot.sg/2009/02/r...denserank.html