Discussion :

[Sergejack]

Il manque des "macros preprocessors".

Du genre:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE PROCEDURE PerfPourries.spViveLeSniffing (
 @pMaCle INT = NULL
)
AS
BEGIN
 SELECT *
 FROM TableIndexeeSurMaCle AS T
 WHERE #PERTINENT_IF(@pMaCle IS NOT NULL, T.MaCle = @pMaCle)
END
 
EXEC PerfPourries.spViveLeSniffing -- Full Scan
EXEC PerfPourries.spViveLeSniffing @pMaCle = 1000 -- Pas de Sniffing, pas de full scan

En effet, ce n'est plus la norme SQL, c'est meiux.

[Monstros Velu]

Pour le cas cité, je ne vois pas l’intérêt de macro preprocessor :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
CREATE PROCEDURE PerfPourries.spViveLeSniffing (
 @pMaCle INT = NULL
)
AS
BEGIN
 SELECT *
 FROM TableIndexeeSurMaCle AS T
 WHERE @pMaCle IS NULL or T.MaCle = @pMaCle
END

D'autant que dans ce cas, la procédure n'a pas besoin d'être recompilé, alors qu'avec des macro-preprocessor, il faut la recompiler à chaque passage...

Ou alors, j'ai raté quelque chose ?

[Sergejack]

Il faudrait un mécanisme pour éviter les problèmes de parameters sniffing pour les simples cas où des paramètres sont optionnels (NULL).

L'idée d'une macro preprocessor est que SQL Server génère lui même un query adapté (et s'il y a déjà un plan pour le query ainsi adapté, tant mieux).

Ajd, on peut faire des query dynamiques pour éviter le sniffing mais c'est pas top.

[mikedavem]

Ajd, on peut faire des query dynamiques pour éviter le sniffing mais c'est pas top.

Je pensais plus à des options de requête comme OPTIMIZE FOR UNKNOW ou utiliser des variable locales pour éviter le parameter sniffing

L'idée d'une macro preprocessor est que SQL Server génère lui même un query adapté (et s'il y a déjà un plan pour le query ainsi adapté, tant mieux).

Hmm ca ressemble presque aux adaptatives plans d'Oracle cela dans l'idée

[Sergejack]

utiliser des variable locales pour éviter le parameter sniffing

J'ai entendu parler de cette technique, elle est vraiment efficace ? (j'espère que ce petit HS sera toléré, je ne m'étendrai pas outrancièrement dessus)

[mikedavem]

J'ai entendu parler de cette technique, elle est vraiment efficace ? (j'espère que ce petit HS sera toléré, je ne m'étendrai pas outrancièrement dessus)

Pas de souci Sergejack , on est sur un forum d'échange après tout ..

Efficace oui et non je dirais. Tout dépend le cas (encore une réponse de normand je sais). Le fait d'utiliser les variables locales désactive le phénomène de parameter sniffing mais empêche aussi SQL Server d'utiliser l'histogramme des statistiques pour trouver un plan optimal. A la place, il va se servir des informations de densité qui sont plus "globales". Dans certains cas on aura un plan d'exécution plus "générique" et cela conviendra pour la plupart des scénarios dans d'autres le plan généré ne sera pas suffisamment efficace ... cela se vérifie bien entendu par des tests.

De part mon expérience chez des clients, j'ai vu des cas où cela résolvait le problème et dans d'autres pas forcément ...

++

[hmira]

I - PROLEGOMENES

Actuellement, L'objet séquence ne permet pas de générer des clés uniques en fonction d'un paramètre variable. Exemple :
Comment générer des numéros uniques de factures, par année civile. C.à.d, numérotation des factures 1, 2, 3 pour l'année 2014, puis en recommence la numérotation 1, 2, 3, .. pour 2015 et ainsi de suite ?
Sachant que dans la pratique, un comptable travaille sur plusieurs années simultanées. Il doit traiter en même temps les factures de fin 2014 et les factures début 2015.
Dans ce contexte si on veut utiliser les séquences, il faudra une séquence par année civile, en plus, il faudra recourir systématiquement au SQL Dynamique pour utiliser la bonne séquence en fonction de l'année cible (2014, 2015 etc..). En effet, le nom d'une séquence dans les instructions comme NEXT VALUE FOR <Nom_de_la_sequence> ne peut pas être remplacée par une variable.

II - CE QUI MANQUE A SQL SERVER

D'après moi, il manque la notion de groupe ou collection d'objet séquences.
Il manque la possibilité d'avoir un groupe d'objets séquences (SEQUENCE) où chaque élément du groupe (ie chaque séquence sera identifiée par une clé unique de type sysname par exemple ).
Cela permettra de générer des clés uniques en fonction d'une paramètre (ou cle) variable (exemple des numéros uniques par année civile). comme expliqué ci-haut, l'objet séquence ne permet pas de le faire sans une gymnastique complexe (SQL Dynamaique etc..)
Ci-dessous quelques exemple pour expliquer et étayer mon idée (suggestion d'évolution) :

Exemple 1 : Groupe de séquence : GRP_SEQENCe_NUMERO_FACTURE_ANNEE représenté ci-dessous comme collection (key, {objet SEQUENCE}) :

Code :

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

1
2
3
4
    Key 	 Objet Séquence 
	'2014' 	 {objet SEQUENCE} 
	'2015'   {objet SEQUENCE} 
	'2016'   {objet SEQUENCE}

Dans la synoptique ci-dessus, j'ai utilisé des années au format chaîne de caractères pour illustrer mon exemple, mais le concept reste générale, j'aurais pu choisir 'Compteur1', 'Compteur2', etc. comme le montre l'exemple n° 2 :
Exemple 2 : Groupe de séquence : GRP_SEQENCE_COMPTEUR Exemple :

Code :

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

1
2
3
4
	Key 	      Objet Séquence 
	'Compteut_1'  {objet SEQUENCE} 
	'Compteut_2'  {objet SEQUENCE} 
	'Compteut_3'  {objet SEQUENCE}

Chaque Objet séquence ({objet SEQUENCE}) représentera une séquence (SEQUENCE) telle existe déjà actuellement sous SQL Server depuis la version SQL Server 2012.

Il faudra ensuite enrichir le langage T-SQL pour permettre la manipulation des groupes de séquences :

Création d'un groupe de séquence

Code SQL :

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

CREATE GROUP SEQUENCE dbo.GRP_SEQENCE_NUMERO_FACTURE_ANNEE;

Ajouter une Clé/Séquence au groupe de séquences GRP_SEQENCE_NUMERO_FACTURE_ANNEE.
GROUP SEQUENCE étant un nouveau mot clé réservé du langage T-SQL

Code SQL :

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

1
2
ALTER GROUP SEQUENCE dbo.GRP_SEQENCE_NUMERO_FACTURE_ANNEE dbo.GRP_SEQUENCE ADD KEY '2014' START WITH 1 INCREMENT BY 1 MINVALUE 1  MAXVALUE 999999999 NO CYCLE  
ALTER GROUP SEQUENCE dbo.GRP_SEQENCE_NUMERO_FACTURE_ANNEE dbo.GRP_SEQUENCE ADD KEY '2015' START WITH 1 INCREMENT BY 1 MINVALUE 1  MAXVALUE 999999999 NO CYCLE

Supprimer une Clé/séquence d'un groupe de séquence

Code SQL :

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

ALTER GROUP SEQUENCE dbo.GRP_SEQENCE_NUMERO_FACTURE_ANNEE dbo.GRP_SEQUENCE DROP KEY '2014';

Obtenir les informations détaillées pour chacune des clés du groupe de séquences
Remarque :
. sys.GroupSequences serait une nouvelle table système
. on obtiendrais un peu près l'équivalent de la vue système sys.Sequences, mais une ligne par clé (key) du groupe de séquence

Code SQL :

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

1
2
3
SELECT * FROM sys.GroupSequences 
WHERE  SCHEMA_NAME(Schema_id) = 'dbo' 
AND name 'GRP_SEQENCE_NUMERO_FACTURE_ANNEE' ;

Utilisation de l'objet groupe de séquences.
L'exemple ci-dessous permettra d'obtenir le numéro suivant de l'objet séquence ayant pour clé '2014', appartenant au groupe de séquences dbo.GRP_SEQENCE_NUMERO_FACTURE_ANNEE
Tout l'intérêt de l'exposé est là :
. Il faut que la syntaxe soit "souple" sans que l'on soit obligé de recourir au SQL Dynamique.
. Il faut que l'on puisse utiliser les variables pour la clé (Key) de la séquence

Exemple 1.1 :
Il faut que l'instruction ci-dessous ne génère pas d'erreur si la clé '2014' n'existe pas dans le groupe de séquences. Si la clé n'existe pas l'instruction ci-dessous retournera NULL,

Code SQL :

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

SELECT NEXT VALUE FOR dbo.GRP_SEQENCE_NUMERO_FACTURE_ANNEE WITH( KEY('2014')  )

Exemple 1.2
. Il faut que l'on puisse utiliser les variables pour la clé (Key) de la séquence

Code SQL :

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

1
2
3
DECLARE @Cle sysname; 
SET @Cle = '2015' 
SELECT NEXT VALUE FOR dbo.GRP_SEQENCE_NUMERO_FACTURE_ANNEE WITH( KEY(@Cle))

On pourrait prévoir une option RAISERROR, afin de générer le cas échéant une erreur de sévérité 16 si la clé (dans notre exemple '2014') n'existe pas.

Code SQL :

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

SELECT NEXT VALUE FOR dbo.GRP_SEQENCE_NUMERO_FACTURE_ANNEE WITH( KEY('2014'), RAISERROR )

III - CONCLUSION

En fait l'objectif et mon souhait c'est l'implémentation native dans SQL Server, au travers les objets séquences (et le nouvel objet groupe de séquences) de la solution proposée par SQLPRo dans son excellent article http://sqlpro.developpez.com/cours/clefs/ paragraphe "3.2. La solution : une table des clefs". SQLPro propose une solution éprouvée fiable et sûre pour une gestion des clés (un distributeur de clé unique).
solution que personnellement, j'ai déjà, mis en œuvre, dans plusieurs applications et je pense que cette solution mérite d'être généralisée et intégrée nativement dans SQL Server au travers les objets séquence et le nouvel objet groupe de séquences). Cela permet notamment :
- d'Eviter de créer une table exprès pour les séquences applicatives (Cf table LesClefs dans l'article de SQLPro), ce point est moins important
- Le plus important étant de masquer au développeur lambda (non expérimenté), la complexité de jongler avec les niveaux d'isolation pour générer des clés uniques en fonction d'un paramètre variable (exemple des clés unique par année civile).

Qu'en pensez-vous ?

A+

[berceker united]

Je commencerais par l'ajout:

• de l'opérateur de jointure naturelle (NATURAL JOIN)
• d'un vrai type Booléen
• des Assertions

Je déterre le sujet mais pourquoi dis-tu que c'est pas un vrai boolean ?

[rw72000]

Bonjour,

A mon avis un vrai booléen vaut vrai ou faux et non 1 ou 0 ce qui permet d'écrire :

if(@monbooléen)
begin
un traitement...
end

au lieu de
if(@monbit = 1)
begin
un traitement
end

ce qui éviterait de se poser la question sur quelle est la représentation de vrai en bit pour celui qui l'a retourné.

[mikedavem]

J'en reviens à ce qu'avait dit Joe Celko sur le fameux type BIT (faux booléen) et qui me paraît très pertinent:

Le langage SQL est un language basé sur les prédicats et non un language orienté flag. Nous utilisons les prédicats et le code déclaratif pour découvrir l'état courant d'une base de données et assurer l'intégrité de nos données.

Admettons que @monboolen concerne les clients d'une société qui au bout de 3 ans de fidélité sont considérés comme des clients VIP, ce qui donne droit à certains avantages, rabais ou autre ...

Pour chercher mes clients VIP je devrais faire quelque chose dans le genre:

Code :

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

SELECT ... FROM Customer WHERE is_vip = 1

Mais entre temps la règle change et pour être client VIP dorénavant, un client devrait avoir une ancienneté supérieure à 5 ans. Cela voudrait dire qu'il faudrait mettre à jour tous les "flags" clients en fonction de la nouvelle règle alors qu'avec le prédicat suivant il serait possible de changer la règle plus facilement.

Code :

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

1
2
3
4
 
DECLARE @year_vip = 5;
 
SELECT ... FROM Customer WHERE date_creation <= DATEADD(YEAR, -1 * @year_vip , CURRENT_TIMESTAMP)

Au final la question serait: est-ce que nous serions vraiment gagnant à utiliser ces flags?

++

[JeanNoel53]

il semble que le nombre de champs identity soit imité dans SQLExress. Dans mon cas ça ne marchait plus avec 18 identity et ça marchait normalement après sue il y ait 16 identity

[SQLpro]

J'en reviens à ce qu'avait dit Joe Celko sur le fameux type BIT (faux booléen) et qui me paraît très pertinent : ...

La norme SQL est d'ailleurs très précise à ce sujet : un booléen peut prendre la valeur 0 ou 1 !

La plupart des fonctions standard de la norme SQL qui renvoie des booléens, renvoient d'ailleurs 0 ou 1, notamment les fameuses fonction spatiale (OGC comme SQL) telle que ST_Within, ST_Intersect...

A +

[JeanYvette]

Moi j'aime bien déterrer les vieux sujets !

Si il me manque une chose, c'est les :old et :new qu'on a dans Oracle... Ca nous réduit tellement le nombre de ligne de code dans les triggers... Après niveau interface je trouve celle d'Oracle jdevelopper plus fournie
Par exemple une tache automatisée sur Oracle ce fait de manière graphique ( a part le pl/sql bien sur). Ca fait un peu assisté mais j'assume

[Sergejack]

J'ai récemment remarqué que SQL Server 2014 n'avait toujours pas solutionné le problème de performance/jointure avec des sous query incluant une fonction de ranking.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
*
FROM A
INNER JOIN (
    SELECT B.*, ROW_NUMBER() OVER (PARTITION BY B.INDEXEDCOLUMN ORDER BY B.ORD) AS NB
    FROM B
) AS SUB ON (
    SUB.INDEXEDCOLUMN  = A.XXX
    AND
    SUB.NB = 1
)
WHERE A.NAME LIKE 'Blah%'

Le sous query causera un full scan range à prioris... et c'est naze.

[StringBuilder]

Peut-être avec une CTE ?

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
 
with cte as
(
   SELECT B.*, ROW_NUMBER() OVER (PARTITION BY B.INDEXEDCOLUMN ORDER BY B.ORD) AS NB
    FROM B
)
SELECT
*
FROM A
INNER JOIN CTE ON (
    CTE.INDEXEDCOLUMN  = A.XXX
    AND
    CTE.NB = 1
)
WHERE A.NAME LIKE 'Blah%'

[StringBuilder]

Moi j'aime bien déterrer les vieux sujets !

Si il me manque une chose, c'est les :old et :new qu'on a dans Oracle... Ca nous réduit tellement le nombre de ligne de code dans les triggers... Après niveau interface je trouve celle d'Oracle jdevelopper plus fournie
Par exemple une tache automatisée sur Oracle ce fait de manière graphique ( a part le pl/sql bien sur). Ca fait un peu assisté mais j'assume

Qu'est-ce que tu entends par là ?

Il y a bien les pseudo tables "inserted" et "deleted", qui correspondent bien à ces pseudo variables oracle (au détail près que SQL Server ne travaille pas comme un porc au niveau FOREACH mais au niveau du lot entier).

Il suffit alors de faire une jointure entre les deux tables sur la clé primaire pour pouvoir identifier les différences entre les données avant et après la mise à jour.

[JeanYvette]

Beh avec Oracle de suite dans la requete tu peux faire quelque chose genre

Code :

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

where :new.Maviariable = 2

tandis que quand tu le fais dans ms sql tu dois déclarer la variable, pis la set genre

Code :

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

set @MaVariable = (select MaVariable from inserted)

et je dois avouer ne pas être très fan de cela

[Waldar]

Non, vous pouvez directement utiliser la table inserted dans un select, une jointure ou quoi que ce soit dont vous ayez besoin.

[StringBuilder]

Oui, parce que tu bosses au niveau ligne (et c'est mal).
D'ailleurs, ton code plante si tu mets à jours plusieurs lignes d'un coup.

Code sql :

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

1
2
3
4
5
6
7
8
9
10
 
update CRM_04
set crm_04.[Text] = tmp.Artikelbez
from (
   select deleted.id_04, inserted.Artikelbez
   from deleted
   inner join inserted on inserted.ID = deleted.ID
   where deleted.Artikelbez <> inserted.Artikelbez
) tmp
where tmp.id_04 = crm_04.id;

Je vois pas avec Oracle comment tu fais plus rapide et plus simple avec :old et :new

[JeanYvette]

Ah... J'avais jamais vu cette syntaxe là c'est pour ça *Va se pendre*
Beh merci, ca va m'être utile dans le futur je pense