Discussion :

[StringBuilder]

Bonjour,

J'utilise SQL Server 2014 CTP2 Express et non 2012. Je ne sais pas si cela peut avoir une incidence.

Devant mettre en place une application qui travaille intensivement sur des intervales de dates, j'ai créé une table calendrier.

Et là, je me pose une question.

Je n'ai, pour le moment tout du moins, pas besoin de colonnes autres que le jour. Pas de notion de vacances, trimerstre ou autre. Juste les jours.

J'étais donc parti sur une telle modélisation :

Code SQL :

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

1
2
3
4
5
6
 
create table Calendar
(
	id int identity not null primary key,
	[day] date not null unique
);

Que j'allimente avec cette procédure :

Code SQL :

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
 
create procedure CreateCalendar
(
	@year int
)
as
BEGIN
	declare @dt1 datetime;
	declare @dt2 datetime;
 
	SET @dt1 = CONVERT(datetime, '01/01/' + cast(@year as varchar), 103);
	SET @dt2 = CONVERT(datetime, '31/12/' + cast(@year as varchar), 103);
 
	WITH Cal (dt) AS
	(
	SELECT @dt1
	 union ALL
	SELECT dt + 1
	  FROM Cal
	 WHERE dt < @dt2
	)
	insert into Calendar ([day])
	SELECT dt
	FROM Cal
	WHERE dt not in (select [day] from Calendar)
	option (maxrecursion 366);
end
go

Plusieurs choses :
1/ Vous remarquerez que dans ma procédure de remplissage de la table calendrier, j'utilise non pas le type "date" comme dans la table, mais le type "datetime". Je n'ai pas du tout compris pourquoi, mais lorsque j'utilise "date" dans la procédure, j'ai des erreurs de compilation (bon, à la limite, OSEF, puisque c'est du quasi one-shot de toute façon, et que ça fonctionne quand même).
2/ Ai-je un réel intérêt à utliser une colonne "id" de type "int" comme clé primaire à ma table "calendar" ? N'est-ce pas mieux si c'est la colonne "day" qui est utilisé comme clé primaire (donc ordonnée physiquement par ordre chronologique, et pas de double contrainte d'unicité) ?

Ensuite, dans mon code, j'ai par exemple ce genre de choses :

Code sql :

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
 
create function ComputePrice(@camping_id int, @from datetime, @to datetime, @options OptionsType readonly)
RETURNS money
AS
BEGIN
	return (
		select sum(p.amount * oo.amount)
		from season s
		inner join pricelevel pl on pl.season_id = s.id
		inner join [level] l on l.id = pl.level_id
		inner join calendar c on c.[day] between pl.[from] and pl.[to]
		cross join @options oo
		inner join price p on p.level_id = l.id and p.option_id = oo.option_id
		where s.camping_id = @camping_id
		and c.[day] between @from and @to
	);
END
GO

On voit bien que mes jointures (et c'est toujours le cas) avec la table "calendar" portent non pas sur la colonne "id", mais la colonne "day".
Cependant, SQLPro, dans un ancien article (qui date de SQL Server 2005 il me semble) indiquait que l'utilisation d'une date (datetime) comme clé primaire nécessitait de mettre en place un paramétrage particulier. Ici il ne s'agit pas du type datetime, mais du type date, qui n'existait pas à l'époque...

Enfin, la procédure que j'ai mis en exemple n'est pas là par hasard...
Pensez-vous que le passage en paramètre d'une table soit performant ? Quelles autres solutions, plus performantes existes ?
Je pense notamment à l'utilisation d'une table temporaire, ce que j'ai failli faire de prime abord.

Merci d'avance pour vos réponses.

[SQLpro]

C'est votre constructeur de date qui est mauvais. Une date c'est :
AAAA-MM-JJ et non JJ/MM/AAAA
De plus l'ajout d'une journée à une date ne se fait pas avec + 1 (une date n'est pas compatible avec un entier !!!!!)

donc :

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
CREATE procedure CreateCalendar
(
	@year int
)
AS
BEGIN
	declare @dt1 date;
	declare @dt2 date;
 
	SET @dt1 = CAST(cast(@year AS CHAR(4)) + '-01-01' AS DATE);
	SET @dt2 = CAST(cast(@year AS CHAR(4)) + '-12-31' AS DATE);
 
	WITH Cal (dt) AS
	(
	SELECT @dt1
	 union ALL
	SELECT DATEADD(day, 1, dt)
	  FROM Cal
	 WHERE dt < @dt2
	)
	INSERT INTO Calendar ([day])
	SELECT dt
	FROM   Cal
	WHERE dt NOT IN (SELECT day FROM Calendar)
	OPTION (maxrecursion 366);
end
go

A +

[StringBuilder]

Merci pour la correction

Sinon, pour mes trois autres questions ? (surtout la deuxième)

[SQLpro]

2/ Ai-je un réel intérêt à utliser une colonne "id" de type "int" comme clé primaire à ma table "calendar"

Pour une table avec des données statiques aucune importance car vous pouvez créer autant d'index couvrant que nécessaire sans aucun dommage.

Pour le reste, j'ai pas identifié les questions....

A +

[StringBuilder]

Ok, donc je garde cette structure.

Sinon, les deux autres questions sont :

3/ D'après un de vos article (SQLPro) les jointures entre datetime, sous SQL Sever 2005, nécessitent la mise en place d'un paramètre afin d'être performantes. Est-ce toujours valable avec 2014 et le type date ?

http://baptiste-wicht.developpez.com...rver/datetime/

Si vous avez des requêtes dont les jointures s'effectuent sur des dates et que ces colonnes de type DATETIME sont indexées (des clefs par exemple), les performances des requêtes d'extraction portant sur de tels critères seront améliorées si vous avez optimisé votre base de données pour ce faire.

ALTER DATABASE <MaBase>
SET DATE_CORRELATION_OPTIMIZATION ON

Attention, si cela vous apporte un gain dans le SELECT, la performance en mise à jour (INSERT, UPDATE, DELETE) décroit. Soyez sûr de ce que vous faîtes et mesurez-en l'impact avant une mise en production définitive !

4/ Je suis parti pour utiliser des variables de type "table" en paramètre de certaines fonctions ou procédures stockées. J'ai déjà travaillé avec des fonctions retournant une "table" (RETURNS TABLE) et il s'avère que, tout du moins à l'époque, c'était bien plus lent que de faire une procédure et faire dedans un bête select, ou alors d'alimenter une table temporaire et travailler dedans.
Vu que je suis assez mitigé entre "faire de l'art pour l'art", c'est à dire exploiter la sémantique correctement, au détriment des performances, ou "faire le goret", en créant des tables temporaires, plutôt que de les passer en paramètre, je me pose la question des performances des variables de type table : est une bonne chose de les utiliser, y compris pour les performances ?

[hmira]

2/ Ai-je un réel intérêt à utliser une colonne "id" de type "int" comme clé primaire à ma table "calendar" ? N'est-ce pas mieux si c'est la colonne "day" qui est utilisé comme clé primaire (donc ordonnée physiquement par ordre chronologique, et pas de double contrainte d'unicité) ?

Je dirais NON. Vous n'avez, selon moi, aucun intérêt à faire cela. En effet, l'ajout d'une clé primaire de type int se justifiait avec le type datetime qui nécessite 8 octets pour le stockage, mais pas avec le type date qui ne nécessite que 3 octets. Par ailleurs la structure interne du type datetime est (et toujours) complexe où les 4 premier octets sont utilisés pour représenter le nombre de jours depuis le 01/01/1900, et les 4 autres octets pour représenter les millisecondes écoulés depuis minuit, etc.).

Le type date est épuré, il représente vraisemblablement un ensemble fini dénombrable ordonné, en ce sens qu'il doit présenter une bijection avec un sous ensemble de N (N étant l'ensemble des entier naturels), plus exactement le sous ensemble [1, 2, , 3652058 ]

3 652 058 représente le nombre de jours entre le 01/01/0001 et le 31/12/9999 (en effet, selon le BOL, la plage des valeurs du type DATE* est [ 0001-01-01 … 9999-12-31]

la valeur 3 652 058 se situe bien entre le max du smallint (32 767) qui nécessite 2 octets et le max du int (2 147 483 647) qui nécessite 4 octets; on est donc bien entre le smallint (2 octets) et le int (4 octets) ; ce qui explique vraisemblablement le fait que le type date nécessite 3 octets.

Par ailleurs, pour les pages intermédiaires (Non-leaf level) de l’index (clustered ou non, représentant la clé primaire de la table Calendar), une clé de 3 octets (type date) est plus compacte qu'une clé de 4 octets (type int) et permet donc de loger plus de clés dans une page. L'index sera ainsi plus performant. Pour les pages de niveau feuille ( leaf level) le champ int (celui qui était ajouté) serait en moins (et donc 4 octets de moins pour chaque lignes), la aussi, cela permet de loger plus lignes dans une page feuille et l'index sera plus performant.

La corrélation des dates est un autre sujet qui n'a rien à voir avec ce que je viens d'écrire ci-haut, et sur lequel je ne me prononce pas encore !

A+

[SQLpro]

Sinon, les deux autres questions sont :

3/ D'après un de vos article (SQLPro) les jointures entre datetime, sous SQL Sever 2005, nécessitent la mise en place d'un paramètre afin d'être performantes. Est-ce toujours valable avec 2014 et le type date ?

Non, car c'était du à l'imprécision du DATETIME qui était limité à 3 ms et pouvait donner des effets de bords dans les jointures...

4/ Je suis parti pour utiliser des variables de type "table" en paramètre de certaines fonctions ou procédures stockées. J'ai déjà travaillé avec des fonctions retournant une "table" (RETURNS TABLE) et il s'avère que, tout du moins à l'époque, c'était bien plus lent que de faire une procédure et faire dedans un bête select, ou alors d'alimenter une table temporaire et travailler dedans.
Vu que je suis assez mitigé entre "faire de l'art pour l'art", c'est à dire exploiter la sémantique correctement, au détriment des performances, ou "faire le goret", en créant des tables temporaires, plutôt que de les passer en paramètre, je me pose la question des performances des variables de type table : est une bonne chose de les utiliser, y compris pour les performances ?

Cela dépend. Les variables tables ont une cardinalité estimée systématiquement à 1 ligne, quelque soit le nombre de lignes qu'elles comportent, car on ne peut pas mettre en place des statistiques sur un objet dynamique et volatile...

A +

[hmira]

Non, car c'était du à l'imprécision du DATETIME qui était limité à 3 ms et pouvait donner des effets de bords dans les jointures...

Ce n'est pas tout à fait ça ! L'option DATE_CORRELATION_OPTIMIZATION ne permet pas de corriger une quelconque imprécision ni effet de bord. Le résultat de la comparaison de 2 valeurs de type datetime est déterministe. C'est vrai qu'il y a cette imprécision du datetime. Celle-ci doit être gérée par le développeur, et d'ailleurs votre excellent article sur le sujet explique bien comment formuler les clauses WHERE sur les colonnes de type datetime pour ne pas tomber dans le piège réel de l’imprécision du type datetime.

L'option DATE_CORRELATION_OPTIMIZATION est tout autre chose. Elle permet à SQL Server d'utiliser les statistiques de corrélation pour inférer de nouvelles restriction (celles-ci ne faisant pas partie de la requête initiale). Ces nouvelles restrictions sont ajoutées à la requête initiales avec la certitude que cela ne change pas le résultat final de la requête.
L' optimiseur de requête utilise ces conditions inférées lorsqu'il choisit un plan de requête. Les restrictions inférées et rajoutés à la requêtes initiales sont clairement visibles si on consulte le plan d'exécution de la requête.

A+

[StringBuilder]

Merci pour toutes vos réponses.