Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

  1. #1
    Futur Membre du Club
    Récupérer les derniers enregistrement de chaque matricule d'une table sql server
    Bonsoir,

    Bon j'ai une table Agent avec un Matricule comme PK et une autre Table Bulletin avec plusieurs enregistrements lié a l'agent (FK Matricule) en gros cela donne les deux table suivantes :





    J' Aimerai crée une vue dans lequel j'aurais toutes les Dernières occurrences de chaque Matricule ajouter dans la Table Bulletin (dans l'exemple j'aurais simplement les deux lignes dont ID est respectivement 4 et 5)

    j' ai utiliser cette requête qui marche bien :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT L.* FROM Bulletin L 
    INNER JOIN 
    (SELECT Matricule, Max(date_Ajout) AS Date FROM Bulletin GROUP BY Matricule ) G  
    ON  L.Matricule = G.Matricule AND L.Date_Ajout = G.Date


    Mais je ne peut malheureusement pas transformer cette requête en Vue Indexé j'aimerais savoir si il aurais une autre solution plus simple a mettre en place pour avoir une Vue indexable.

  2. #2
    Nouveau membre du Club
    Il est impossible d'indexer une vue dont la requête contient une sous-requête (ou une jointure avec sous-requête dans le cas présent), encore moins avec un opérateur MAX.

    De plus quel est l'objectif de création d'un telle vue qui ne référence au final que la table bulletin?

    En dehors de ces considérations la requête la plus optimisée devrait être quelque chose comme :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    	SELECT B1.MATRICULE, B1.DATE_AJOUT, etc....
    	FROM BULLETIN B1
    	LEFT JOIN BULLETIN B2
    		ON (B1.MATRICULE = B2.MATRICULE AND B1.DATE_AJOUT < B2.DATE_AJOUT)
    	WHERE B2.DATE_AJOUT IS NULL;


    Mais dans tous les cas une vue ne serait pas indexable via cette requête

  3. #3
    Futur Membre du Club
    l'objectif de création d'un telle vue
    En gros dans la table agent j'ai 379000 agents et la table Bulletin plus de 6 million, en utilisant ma requête j'ai un temps propre d’exécution de 8 seconde sachant que c'est une requête joint sur la Table Bulletin elle même (PS: La requête que vous avais proposé tourne au alentour de 1m12s) bref je voulais soit trouver une meilleur requête qui me fait moins de 8 seconde ou bien une solution pour utiliser une vue indexe qui en fin de compte n'aura que 379000 enregistrements et qui m'offrira grâce a son indexation un temps propre beaucoup plus avantageux au alentour de 4 seconde de plus elle aura l’avantage d’être interroger directement pour d'autre traitements futures.


    PS : d'autre conseil ou un changement de cap pour une autre méthodologie est le bienvenue Merci (je m'excuse pour mon français je suis encore débutant dans cette langue)

  4. #4
    Modérateur

    bonjour,

    plus d'une minute pour une telle requête sur un si petit volume ? soit votre serveur est à la ramasse, soit il y a un autre problème.

    Quelle est la structure complète de la table ? a-t-elle une clef primaire ? a-t-elle un index cluster ?
    y a-t-il des index secondaires ?
    s'il n'existe pas, vous pourriez créer un index pour aider la requête sur (matricule, date_ajout DESC), en incluant éventuellement les autres colonnes. D'ailleurs, pourquoi faire un SELECT *, avez-vous réellement besoin de toutes les colonnes de la table ?


    Par ailleurs quel est votre SGBDR ?
    S'il supporte les fonctions analytique, testez la requête suivante :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    WITH CTE AS (
    	SELECT * , row_number() over(PARTITION BY matricule ORDER BY date_ajout desc) AS RN
    	FROM Bulletin AS B
    )
    SELECT ... --(spécifiez uniquement les colonne nécessaires)
    FROM CTE
    WHERE RN = 1


    et sur certains SGBDR, notamment SQL Server, cette requête, bien que moins "jolie", pourrait être très efficace pour peu que le bon index soit présent :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    select B.*
    from agent AS A
    cross apply (select top(1) * from Bulletin AS B where B.matricule = A.matricule order by date_ajout desc) AS B

  5. #5
    Nouveau membre du Club
    Effectivement la requête que j'avais proposée n'est pas la plus satisfaisante en terme de temps de réponse (testée sous SQL SERVER)

    Les requête proposées par aieeeuuuuu ne donne pas résultats plus probant, surtout que la requête de "base" ne dépasse pas les 2 secondes en local sur mon environnement.
    A voir également ce que cache ce "B.*" et la volumétrie de données qui est impliqué derrière et la localisation du SGBD.

    Au vue du nombre de matricule existant ne serait-il pas plus judicieux de faire une jointure sur des int plutôt que sur du varchar? Cette question n'est que supposition, je ne suis pas assez expérimenté sur le sujet.

  6. #6
    Futur Membre du Club
    Citation Envoyé par aieeeuuuuu Voir le message
    bonjour,

    plus d'une minute pour une telle requête sur un si petit volume ? soit votre serveur est à la ramasse, soit il y a un autre problème.


    je m'excuse, je n'est pas préciser que je suis sur un simple Laptop low cost (simple test pour le développement) je n'est pas encore tester mon système dans un environnement fiable.

    Bien que je n'est pas bien compris la totalité de votre repense je ferais des test sur vos requêtes

    PS : non je n'est pas d'index cluster a ma connaissance. je n'est qu'une clé primaire composé entre Matricule est date_ajout et pour le SGBD c'est SQL SERVER VERSION 11.0.2100 ENTRPRISE Edition

  7. #7
    Modérateur

    Sous SQL Server, la clef primaire fait par défaut l'objet d'un index cluster, vous devez donc bien en avoir un, qui plus est sur (matricule, date_ajout). Est-ce bien dans cet ordre ?

    Si c'est le cas, alors il devrait être utilisé pour répondre à la requête (vous pouvez le vérifier dans le plan d'exécution)

    Et, toujours si c'est le cas, il a de grandes chances d'être fortement fragmenté, car on peut supposer que les lignes sont ajoutées au fur et à mesure des dates et non des matricules.
    Vous pouvez vérifier la présence de cet index cluster, et sa fragmentation sous l'onglet index (en dessous de la table dans management studio). Vous pourriez aussi générer le script CREATE TABLE et le poster afin qu'on ait une idée de la structure de la table.

  8. #8
    Futur Membre du Club
    Voici le code de Création des deux tables

    Table Agent

    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
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
     
    ---Table Agent----
    USE [Bdd]
    GO
    /****** Object:  Table [dbo].[Tab_Agent]    Script Date: 30/06/2020 11:28:22 ******/
    SET ANSI_NULLS ON
    GO
     
    SET QUOTED_IDENTIFIER ON
    GO
     
    CREATE TABLE [dbo].[Tab_Agent](
    	[Code_Commune_Residence] [varchar](20) NOT NULL,
    	[Code_Commune_Naissance] [varchar](20) NOT NULL,
    	[Date_Naissance] [datetime] NULL,
    	[Num_SS] [varchar](20) NOT NULL,
    	[Nationalite] [varchar](15) NOT NULL,
    	[Nbr_Enfant] [tinyint] NULL,
    	[Observation] [varchar](250) NOT NULL,
    	[Photo] [image] NULL,
    	[Matricule] [varchar](15) NOT NULL,
    	[Nbr_Annee_Exp_Hors_Entreprise] [real] NOT NULL,
    	[Distance_KM] [real] NOT NULL,
    	[Code_Dossier] [varchar](50) NOT NULL,
    	[Type_Charge_Agent] [tinyint] NOT NULL,
    	[Nbr_Annee_Exp_Entreprise] [float] NOT NULL,
    	[Civilite] [tinyint] NOT NULL,
    	[Code_Caisse] [varchar](5) NOT NULL,
    	[Annee_Presumee] [int] NULL,
    	[Ancien_Matricule] [varchar](15) NULL,
    	[User_ID] [varchar](15) NULL,
    	[Date_Modification] [datetime] NULL,
    	[Avec_Mutuel] [bit] NULL,
    	[Avec_Assurence_Groupe] [bit] NULL,
    	[Avec_Cacobath] [bit] NULL,
    	[Avec_Chomage_Intemperie] [bit] NULL,
    	[Avec_Oprebat] [bit] NULL,
    	[Groupe_Sanguine] [varchar](3) NULL,
    UNIQUE NONCLUSTERED 
    (
    	[Matricule] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT (NULL) FOR [Date_Naissance]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Nbr_Enfant]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ('0') FOR [Matricule]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Nbr_Annee_Exp_Hors_Entreprise]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Distance_KM]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Type_Charge_Agent]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Nbr_Annee_Exp_Entreprise]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Civilite]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Annee_Presumee]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT (NULL) FOR [User_ID]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT (NULL) FOR [Date_Modification]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Avec_Mutuel]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Avec_Assurence_Groupe]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Avec_Cacobath]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Avec_Chomage_Intemperie]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent] ADD  DEFAULT ((0)) FOR [Avec_Oprebat]
    GO
     
    ALTER TABLE [dbo].[Tab_Agent]  WITH CHECK ADD FOREIGN KEY([Code_Caisse])
    REFERENCES [dbo].[Tab_Caisse_Cotisation] ([Code_Caisse])
    GO


    Table Bulletin

    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
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
     
    ----Table Bulettin_Agent
     
    USE [Bdd]
    GO
     
    /****** Object:  Table [dbo].[Tab_Information_Bulletin_Agent]    Script Date: 30/06/2020 11:30:09 ******/
    SET ANSI_NULLS ON
    GO
     
    SET QUOTED_IDENTIFIER ON
    GO
     
    CREATE TABLE [dbo].[Tab_Information_Bulletin_Agent](
    	[Nom] [varchar](30) NULL,
    	[Prenom] [varchar](30) NULL,
    	[Mmaa] [datetime] NULL,
    	[Code_Agence] [varchar](15) NULL,
    	[Mode_Paiement] [varchar](1) NULL,
    	[Num_Compte] [varchar](20) NULL,
    	[Matricule] [varchar](15) NULL,
    	[User_ID] [varchar](15) NULL,
    	[Date_Modification] [datetime] NULL,
    	[Date_Affectation] [datetime] NULL,
    	[Date_Interval] [datetime] NULL,
    	[Ref_Contrat] [varchar](15) NULL,
    	[Code_Poste_Travail] [varchar](10) NULL,
    	[Classification] [varchar](10) NULL,
    	[Code_Structure] [varchar](5) NULL,
    	[Nbr_Promtion_Agent] [tinyint] NULL,
    	[Code_Evenement] [varchar](10) NULL,
    	[Date_Evenement] [datetime] NULL,
    	[Code_Promotion_Agent] [varbinary](36) NULL,
    	[Code_Evenement_Agent] [varbinary](33) NULL,
    	[Code_Affectation_Agent] [varbinary](28) NULL,
    	[Est_Archiver] [bit] NULL,
    	[Nbr_Enfant] [tinyint] NULL,
    	[Situation_Famille] [varchar](1) NULL,
            [Code_Info_Bulettin] [varchar](23) NULL,
       UNIQUE NONCLUSTERED 
     (
    	[Code_Info_Bulettin] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     ) ON [PRIMARY]
     GO
     
    ALTER TABLE [dbo].[Tab_Information_Bulletin_Agent] ADD  DEFAULT ((0)) FOR [Nbr_Promtion_Agent]
    GO
     
    ALTER TABLE [dbo].[Tab_Information_Bulletin_Agent] ADD  DEFAULT ((0)) FOR [Est_Archiver]
    GO
     
    ALTER TABLE [dbo].[Tab_Information_Bulletin_Agent] ADD  DEFAULT ((0)) FOR [Nbr_Enfant]
    GO
     
    ALTER TABLE [dbo].[Tab_Information_Bulletin_Agent]  WITH CHECK ADD FOREIGN KEY([Code_Agence])
    REFERENCES [dbo].[Tab_Agence] ([Code_Agence])
    GO
     
    ALTER TABLE [dbo].[Tab_Information_Bulletin_Agent]  WITH CHECK ADD FOREIGN KEY([Matricule])
    REFERENCES [dbo].[Tab_Agent] ([Matricule])
    GO
     
    ALTER TABLE [dbo].[Tab_Information_Bulletin_Agent]  WITH CHECK ADD FOREIGN KEY([Ref_Contrat], [Classification], [Code_Poste_Travail], [Nbr_Promtion_Agent])
    REFERENCES [dbo].[Tab_Promotion_Agent] ([Ref_Contrat], [Classification], [Code_Poste_Travail], [Nbr_Promtion_Agent])
    GO
     
    ALTER TABLE [dbo].[Tab_Information_Bulletin_Agent]  WITH CHECK ADD FOREIGN KEY([Matricule], [Code_Evenement], [Date_Evenement])
    REFERENCES [dbo].[Tab_Evenement_Agent] ([Matricule], [Code_Evenement], [Date_Evenement])
    GO
     
    ALTER TABLE [dbo].[Tab_Information_Bulletin_Agent]  WITH CHECK ADD FOREIGN KEY([Ref_Contrat], [Code_Structure], [Date_Affectation])
    REFERENCES [dbo].[Tab_Affectation_Agent] ([Ref_Contrat], [Code_Structure], [Date_Affectation])
    GO


    PS : En utilisant les clé composer j'ai remarqué qu'il faut impérativement introduire toutes les colonnes qui la composent dans le cas ou elle est en clé étrangère ? alors que sur d'autre SGBD se n'et pas obligatoire ou bien je n'est rien compris au fonctionnement des clé composé

  9. #9
    Rédacteur

    Citation Envoyé par Zaks1991 Voir le message
    ....

    j' ai utiliser cette requête qui marche bien :

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT L.* FROM Bulletin L 
    INNER JOIN 
    (SELECT Matricule, Max(date_Ajout) AS Date FROM Bulletin GROUP BY Matricule ) G  
    ON  L.Matricule = G.Matricule AND L.Date_Ajout = G.Date


    Mais je ne peut malheureusement pas transformer cette requête en Vue Indexé j'aimerais savoir si il aurais une autre solution plus simple a mettre en place pour avoir une Vue indexable.
    Cela ne peut être une vue indexées pour deux raisons :
    1) la présence du calcul MAX
    2) la présence d'une sous requête.

    Néanmoins je ne voit qu'une seule table dans cette requête.

    Assurez vous qu'il y a un index dont la clef est composés des seules colonnes (Matricule, date_ajout).

    D'autre part il existe des erreurs graves dans la conception de vos tables :
    1) il n'y a pas de PRIMARY KEY dans aucune de vos tables ce qui explique des performances de merde
    2) le type image est obsolète depuis la version 2005 de SQL Server et doit être remplacé par le type VARBINARY(max)
    3) en présence de LOBs il est préférable de dissocier le stockage des données relationnelles des données non relationnelles (LOBs) si vous voulez es performances. Donc en particulier le BLOB "Photo". Pour cela utilisez la directive TEXTIMAGE_ON à la création de la table après avoir créé un nouveau FILEGROUP avec des fichiers dedans...
    4) le type datetime est déprécié et contre performant. Utilisez les types DATE, DATETIME2(n), TIME, DATETIMEOFFSET(n) à la place
    5) vos clefs étrangères sont "monstrueuses. Exemple : FOREIGN KEY([Ref_Contrat], [Classification], [Code_Poste_Travail], [Nbr_Promtion_Agent]) REFERENCES [dbo].[Tab_Promotion_Agent] ([Ref_Contrat], [Classification], [Code_Poste_Travail], [Nbr_Promtion_Agent]). Ce qui suppose un très mauvais modèle et plombe les performances...

    À lire sur ces sujets :
    https://docs.microsoft.com/fr-fr/sql...l-server-ver15
    https://docs.microsoft.com/fr-fr/sql...l-server-ver15


    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  10. #10
    Futur Membre du Club
    Merci Beaucoup pour les explication.

    * Pour la clé composé "Monstrueuse" elle est composé de plusieurs clé étrangère FK c'est pour cela qu'elle est aussi longue. mais vous avais raison il vaux mieux crée une autre clé primaire pour la table promotion en plus de cette clé composé que je laisserais seulement en clé unique et j'utiliserais la clé primaire dans ma table bulletin.
    * Concernant les clé composite dans le cas ou elle fait l'objet d'une FK dans une autre table il faut que tous les champs qui la composent soit dans la table associé ?
    * Pour les clé primaire le champ Matricule n'est pas Primary avec se code ?
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    UNIQUE NONCLUSTERED 
    (
    	[Matricule] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

  11. #11
    Rédacteur

    Visiblement vous n'avez pas compris ce qu'est une clef étrangère.... Quand vous dites :

    Citation Envoyé par Zaks1991 Voir le message

    * Pour la clé composé "Monstrueuse" elle est composé de plusieurs clé étrangère FK c'est pour cela qu'elle est aussi longue.

    Vous vous trompez. Vos clefs étrangères ne sont pas composées de plusieurs clefs... mais de plusieurs colonnes



    mais vous avais raison il vaux mieux crée une autre clé primaire pour la table promotion en plus de cette clé composé que je laisserais seulement en clé unique et j'utiliserais la clé primaire dans ma table bulletin.
    oui !


    * Concernant les clé composite dans le cas ou elle fait l'objet d'une FK dans une autre table il faut que tous les champs qui la composent soit dans la table associé ?
    oui.


    * Pour les clé primaire le champ Matricule n'est pas Primary avec se code ?
    Non ! une clef primaire c'est une contrainte PRIMARY KEY et non UNIQUE !
    De plus, pour des raisons de performances, les clefs primaire de SQL Server construisent un index CLUSTERED alors que les contraintes UNIQUE construisent un index NONCLUSTERED ce qui induit une double lecture préjudiciable au performances....
    Donc, commencez par rectifier ce point dans vos tables :
    • supprimer les contraintes UNIQUE (ALTER TABLE MaTable DROP CONSTRAINT MonNomDeContrainte)
    • Créer des PRIMARY KEY (ALTER TABLE MaTable ADD CONSTRAINT PRIMARY KEY (listeColonneClef)


    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  12. #12
    Futur Membre du Club
    Merci pour vos conseil

    - je ne vois pas trop la différence vu que mes colonnes qui composent la clé étrangères sont des colonnes référencés comme étant des clés étrangères.