Discussion :

[im-souf]

Bonjour,

Je suis en pleine recherche sur le meilleur outil gratuit à utiliser pour une gestion de données temporelles. C'est à dire que j'ai énormément de données qui dépendent d'une date.

Basiquement la base de données recevra énormément d'insert liés à chaque fois à une date. Elle recevra également beaucoup de select, encore une fois liés à une recherche sur une date (avant, apres, intervals...).

Intuitivement j'utiliserais MySQL pour tout cela, mais mes connaissances limitées dans les différents SGBD ne me permettent pas de savoir s'il existe de meilleurs outils.

Bien à vous.

[SQLpro]

benchmark MySQL, PostGreSQL, MS SQL Server sur des requêtes d'agrégation temporelles :
http://blog.developpez.com/sqlpro/p9...alles_en_sql_1

A +

[Jester]

SQLPro votre benchmark est intéressant.

Cependant, en 10 minutes j'ai trouvé un solution pour que postgresql soit comparable à votre meilleure solution SQL Server.

Notons que cette requête n'utilise que du SQL de base mais que SQL Server ne sait pas l'exécuter. On pourrait la réécrire pour la faire tourner sur SQL Server 2012 donc la toute dernière version.

Sur une VM (que vous convenez être une catastrophe pour les perfs d'un SGBD) quadri core (sur 4 dispo) 3.1GHz avec 8Go (sur 32) de RAM et un disque 2To standard 5400RPM. Disons comparable à votre serveur j'imagine avec des + et des -. J'obtiens :
340s pour le 10M de lignes
58s pour les 3M

Je n'ai pas le paramétrage par défaut car ce serait stupide, mais rien d'incroyable ou hors des bonnes pratiques du manuel postgresql. Aucun index n'est utilisé.

Le problème est un sort qui est effectué sur le disque dur ce qui prend la majorité du temp. Optimisable? On est sur un seul core utilisé, les autres se tournent les pouces, c'est PostgreSQL.

Voici le script SQL. Cette méthode existe probablement déjà quelque part, sinon je la nomme la solution du bouffon.

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
 
with projection as (
  select itv_item, itv_debut as dt, +1 as direction
  from t_interval_itv
  union all
  select itv_item, itv_fin as dt, -1 as direction
  from t_interval_itv
),
sumsumsum as (
  select itv_item, dt, 
	sum(direction) over (partition by itv_item order by dt, direction desc) as cnt,	
	coalesce(sum(direction) over (partition by itv_item order by dt, direction desc rows between unbounded preceding and 1 preceding),0) as lag_cnt
  from projection
),
make_order as (
  select itv_item, dt, 0 as pos
  from sumsumsum
  where cnt > 0 and lag_cnt = 0
  union all
  select itv_item, dt, 1 as pos
  from sumsumsum
  where cnt = 0 and lag_cnt >0
),
make_interval as (
  select itv_item, dt as itv_debut, 
	lead(dt) over (partition by itv_item order by dt) as itv_fin,
	pos
  from make_order
)
select  itv_item, itv_debut, itv_fin
from make_interval
where pos = 0

La même requête sur un serveur Greenplum (gratuit en community sur un seul noeud, pas de limite de taille) donne :
87s pour 10M
20s pour 3M
Aucun index, aucune astuce greenplum (partitionning, bidouille sur les distributions, compression, ...).

Certes ce n'est pas un SGBD prévu pour l'OLTP.

D'ailleurs je doutes que ce soit le genre de requête qu'im-souf compte utiliser, il me semble plutôt qu'il cherche de l'archi basique.

[el_muchacho]

Bonjour, c'est du Postgres 9.2 ou du 8.4 ? Parce qu'entre 8.4 et 9.2, il y a eu du chemin de réalisé en terme de perfs. Et il serait intéressant de faire un test avec MariaDB, dont l'optimiseur a été amélioré par rapport à MySQL 5.5 qui comme le montre le test intéressant de SQLPro, est très basique et ne s'en sort pas avec des requêtes un tant soit peu complexes.

[Jester]

C'est postgresql 9.1. Sur cette requête du 8.4 donnerais le même résultat je pense, il n'y a rien de très avancé dans le plan d'exécution.

Je ne sais pas l'écrire pour MariaDB. C'est probablement possible en utilisant les fonctionnalité de variables de MySQL, mais ce n'est pas mon style de SQL. Ce serait du niveau de Postgresql (il y a juste 2 sort à faire). MySQL et forks ne sont pas vraiment prévu pour ce genre de requêtes.

[SQLpro]

Jester, pour une fois, vous avez raison ! ;-)

J'ai testé votre requête sur les 2 systèmes : PostGreSQL 9.1 et MS SQL Server 2012 et les temps sont comparables : 2 secondes chacun !

Félicitation.

Il me semble que dans la version 8.4 les fonctions LEAD et LAG ou RANGE et ROWS n'existait pas....

N'hésitez pas à ajouter votre solution à mon blog !

Merci

A +

[Fred_34]

@SQLPro

Voici une requête pour MySQL :

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
SELECT  ITV_ITEM, ITV_DEBUT, MAX(ITV_FIN) AS ITV_FIN FROM
(
	SELECT	IF(currentItem=@previousItem, 
			IF(@previousFin < currentFin,
				IF(currentDebut <= @previousFin,
					0,
					@previousDebut:=currentDebut
				) & (@previousFin:=currentFin),
				0
			),
			(@previousItem:=currentItem) & (@previousDebut:=currentDebut)  & (@previousFin:=currentFin)
		) AS calc,
		@previousItem AS ITV_ITEM,
		@previousDebut AS ITV_DEBUT,
		@previousFin AS ITV_FIN
	FROM 
	(
		SELECT ITV_ITEM AS currentItem,ITV_DEBUT AS currentDebut,ITV_FIN AS currentFin 
		FROM `t_interval_itv`		
	)t1 
	ORDER BY currentItem,currentDebut
) t2
STRAIGHT_JOIN (SELECT @previousItem:=NULL ) just_for_init
GROUP BY ITV_ITEM,ITV_DEBUT
ORDER BY ITV_ITEM,ITV_DEBUT;

J'avais proposé cette solution sous forme de procédure, mais ça ne convenait pas...

Je ne sais pas si elle est aussi rapide que la solution de Jester, mais elle devrait également mettre à mal vos conclusions.

Edit: Mise en forme

[Jester]

@Fred_34 je pense que votre solution est plus rapide en mono-threadé. Par contre, elle n'est pas parallélisable (ou en étant un super-super-optimiseur improbable). C'est l'avantage de mysql qui ajoute des features sans trop se demander quel impact ça aura dans le futur lointain. Elle est aussi un peu trop simple à comprendre

@SQLPro lead et lag sont dispo depuis postgresql 8.4 donc depuis 2009. Je posterais un truc la semaine prochaine si j'y pense.