IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
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

Administration SQL Server Discussion :

Mauvaise conception => optimisation via View Indexée ?


Sujet :

Administration SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Homme Profil pro
    Chef de projet en SSII
    Inscrit en
    Octobre 2018
    Messages
    87
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Tunisie

    Informations professionnelles :
    Activité : Chef de projet en SSII

    Informations forums :
    Inscription : Octobre 2018
    Messages : 87
    Par défaut Mauvaise conception => optimisation via View Indexée ?
    Bonjour,
    Nous avons développé depuis 3 ans un module (en faite c'est un re-developpement d'un très ancien module qui était sur Access depuis 20 ans)
    Malheureusement nous avons choisi un modèle conceptuel totalement non normalisée : une seule table obèse qui englobe plusieurs domaines avec une Colonne D_Domaine pour faire la distinction et aussi avec un très mauvais choix des types de données (des nvarchar(max) par tout, tous les entiers avec le type int...).

    est ce que l’utilisation des views indexés peut améliorer les résultats d’exécution des requêtes (au lieu d'attaquer une seule table, on créé une view indexées pour chaque domaine) ?

    Merci par avance

  2. #2
    Membre Expert
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Septembre 2016
    Messages
    959
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 58
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2016
    Messages : 959
    Par défaut
    Bonjour,

    Étrange.
    Vous connaissez l'origine du mal et vous refuser de le traiter comme il se doit

    Le fait d'être sous SQL server va vous sauver la mise car il y a tous les outils pour faire une "évolution" de la structure tout en acceptant les anciennes requêtes.

    Pour cela il vous faut (c'est la même phrase que chez Ikéa )
    - savoir créer des tables (et les contraintes)
    - savoir transférer les données dans les nouvelles tables
    - savoir créer des vues
    - savoir créer des trigger instead of
    - savoir lancer un script qui fait tout ça (les tests sont faits sur une base du même type, on est d'accord )

    ne sachant pas ce que vous savez ou pas, dites nous ce dont vous avez besoin.

  3. #3
    Membre confirmé
    Homme Profil pro
    Chef de projet en SSII
    Inscrit en
    Octobre 2018
    Messages
    87
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Tunisie

    Informations professionnelles :
    Activité : Chef de projet en SSII

    Informations forums :
    Inscription : Octobre 2018
    Messages : 87
    Par défaut
    Citation Envoyé par Michel.Priori Voir le message
    Étrange.
    Vous connaissez l'origine du mal et vous refuser de le traiter comme il se doit
    Malheureusement on n'y peut rien faire, des mauvais choix imposés ...
    On essaye de se rattraper dans les nouveaux modules

  4. #4
    Membre Expert
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Septembre 2016
    Messages
    959
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 58
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2016
    Messages : 959
    Par défaut
    Citation Envoyé par erpWorld Voir le message
    Malheureusement on n'y peut rien faire
    C'est justement l'inverse que je vous dis, vous pouvez corriger
    - la base uniquement
    - pas l'applicatif (dans un premier temps du moins )

    1 seule table c'est pas la mer à boire même s'il y a 20 ans de données à brasser.

    En pièce jointe un fichier zip qui contient un fichier BACPAC (SSMS : Clic droit sur le noeud "base de données" et choisir "importer une application de la couche de donnée"
    Ça va créer une base avec 1 table avec quelques lignes.
    Par défaut la base s'appelle : Bad_conception

    C'est pour se donner de la matière

    L'objectif est de décomposer "big_table" en 3 tables tout en autorisant les insertions sur big_table (comme avant la manœuvre)

    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
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    /* Création des objets */
    exec sp_rename 'dbo.BigTable', 'dbo.OldBigTable'
    go
    CREATE schema Person;
    go
     
    DROP TABLE if exists [Person].[Address]
    DROP TABLE if exists [Person].[StateProvince]
    DROP TABLE if exists [Person].[CountryRegion]
     
     
    CREATE TABLE [Person].[CountryRegion]
    (
    	[CountryRegionCode] [nvarchar](3) NOT NULL,
    	[Name] nvarchar(50) NOT NULL,
    	[ModifiedDate] [datetime] NOT NULL,
         CONSTRAINT [PK_CountryRegion_CountryRegionCode] PRIMARY KEY CLUSTERED ([CountryRegionCode] ASC)
    )
    GO
    ALTER TABLE [Person].[CountryRegion] ADD  CONSTRAINT [DF_CountryRegion_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
    GO
     
    CREATE TABLE [Person].[StateProvince]
    (
    	[StateProvinceID] [int] IDENTITY(1,1) NOT NULL,
    	[StateProvinceCode] [nchar](3) NOT NULL,
    	[CountryRegionCode] [nvarchar](3) NOT NULL,
    	[IsOnlyStateProvinceFlag] bit NOT NULL,
    	[Name] nvarchar(50) NOT NULL,
    	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    	[ModifiedDate] [datetime] NOT NULL,
     CONSTRAINT [PK_StateProvince_StateProvinceID] PRIMARY KEY CLUSTERED ([StateProvinceID] ASC)
    )
    GO
    ALTER TABLE [Person].[StateProvince] ADD  CONSTRAINT [DF_StateProvince_IsOnlyStateProvinceFlag]  DEFAULT ((1)) FOR [IsOnlyStateProvinceFlag]
    ALTER TABLE [Person].[StateProvince] ADD  CONSTRAINT [DF_StateProvince_rowguid]  DEFAULT (newid()) FOR [rowguid]
    ALTER TABLE [Person].[StateProvince] ADD  CONSTRAINT [DF_StateProvince_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
    ALTER TABLE [Person].[StateProvince]  
        WITH CHECK 
        ADD CONSTRAINT [FK_StateProvince_CountryRegion_CountryRegionCode] 
        FOREIGN KEY([CountryRegionCode])
        REFERENCES [Person].[CountryRegion] ([CountryRegionCode])
    GO
     
     
    CREATE TABLE [Person].[Address](
    	[AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    	[AddressLine1] [nvarchar](60) NOT NULL,
    	[AddressLine2] [nvarchar](60) NULL,
    	[City] [nvarchar](30) NOT NULL,
    	[StateProvinceID] [int] NOT NULL,
    	[PostalCode] [nvarchar](15) NOT NULL,
    	[SpatialLocation] [geography] NULL,
    	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    	[ModifiedDate] [datetime] NOT NULL,
     CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED ([AddressID] ASC)
    ) 
    GO
    ALTER TABLE [Person].[Address] ADD  CONSTRAINT [DF_Address_rowguid]  DEFAULT (newid()) FOR [rowguid]
    ALTER TABLE [Person].[Address] ADD  CONSTRAINT [DF_Address_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
    ALTER TABLE [Person].[Address]  
        WITH CHECK 
        ADD CONSTRAINT [FK_Address_StateProvince_StateProvinceID] 
        FOREIGN KEY([StateProvinceID])
        REFERENCES [Person].[StateProvince] ([StateProvinceID])
    GO
     
    DROP view if exists dbo.BigTable
    GO
    CREATE VIEW dbo.BigTable as
        SELECT A.AddressID,
    		 A.AddressLine1,
    		 A.AddressLine2,
    		 A.City,
    		 A.PostalCode,
    		 A.SpatialLocation,
    		 S.StateProvinceCode,
    		 S.CountryRegionCode,
    		 S.IsOnlyStateProvinceFlag,
    		 S.Name AS StateProvinceName,
    		 C.Name AS CountryName
        FROM Person.Address AS A
    	    INNER JOIN Person.StateProvince AS S ON A.StateProvinceID = S.StateProvinceID
    	    INNER JOIN Person.CountryRegion AS C ON S.CountryRegionCode = C.CountryRegionCode;
    GO
     
    Drop trigger if exists Trg_InsOfINSERT_BigTable
    go
    CREATE TRIGGER Trg_InsOfINSERT_BigTable On dbo.BigTable 
    instead of INSERT  
    AS
    BEGIN
        --Ajout des [Person].[CountryRegion] si besoin
        MERGE	  INTO [Person].[CountryRegion] as target
    		  USING (SELECT  [CountryRegionCode], MAX(CountryName) as CountryName
    				FROM inserted
    				GROUP BY [CountryRegionCode]
    				) as source
    			  on source.[CountryRegionCode]  = target.[CountryRegionCode]
    		  WHEN  NOT MATCHED THEN  
    			 INSERT ([CountryRegionCode],[Name]) VALUES (source.[CountryRegionCode],source.CountryName);
        --Ajout des [Person].[StateProvince] si besoin
        MERGE	  INTO [Person].[StateProvince] as target
    		  USING (SELECT DISTINCT 
    				     [StateProvinceCode]
    				    ,[CountryRegionCode]
    				    ,[IsOnlyStateProvinceFlag] 
    				    ,[StateProvinceName]
    				FROM inserted
    			   ) as source
    			 on  source.[StateProvinceCode]		  = target.[StateProvinceCode]
    			 And source.[CountryRegionCode]		  = target.[CountryRegionCode]
    			 And source.[IsOnlyStateProvinceFlag]	  = target.[IsOnlyStateProvinceFlag]
    			 And source.[StateProvinceName]		  = target.[Name]
    		  WHEN  NOT MATCHED THEN  
    			 INSERT ([StateProvinceCode],[CountryRegionCode],[IsOnlyStateProvinceFlag],[Name])
    			 VALUES (source.[StateProvinceCode],source.[CountryRegionCode],source.[IsOnlyStateProvinceFlag],source.[StateProvinceName]);
        --Ajout des [Person].[Address] si besoin
        MERGE	  INTO [Person].[Address] as target
    		  USING (SELECT I.[AddressLine1]	
    		  			 ,I.[AddressLine2]	
    		  			 ,I.[City]			
    		  			 ,SP.[StateProvinceID]
    		  			 ,I.[PostalCode]		
    		  			 ,I.[SpatialLocation]
    				FROM inserted I 
    				    JOIN [Person].[StateProvince] SP
    				    on    SP.[StateProvinceCode]		=I.[StateProvinceCode]		
    					 And SP.[CountryRegionCode]		=I.[CountryRegionCode]		
    					 And SP.[IsOnlyStateProvinceFlag]	=I.[IsOnlyStateProvinceFlag]	
    					 And SP.[Name]					=I.[StateProvinceName]		
    				) as source
    				on  source.[AddressLine1]		   = target.[AddressLine1]
    				And source.[AddressLine2]	   = target.[AddressLine2]
    				And source.[City]			   = target.[City]
    				And source.[StateProvinceID]	   = target.[StateProvinceID]
    				And source.[PostalCode]		   = target.[PostalCode]
    				--And source.[SpatialLocation].STEquals( target.[SpatialLocation])=1
    		  WHEN  NOT MATCHED THEN  
    			 INSERT ([AddressLine1],[AddressLine2],[City],[StateProvinceID],[PostalCode],[SpatialLocation])
    			 VALUES (source.[AddressLine1],source.[AddressLine2],source.[City],source.[StateProvinceID],source.[PostalCode],source.[SpatialLocation]);
    END;
    GO
    /* remplissage des tables */
    INSERT INTO [dbo].[BigTable]
               ([AddressID]
               ,[AddressLine1]
               ,[AddressLine2]
               ,[City]
               ,[PostalCode]
               ,[SpatialLocation]
               ,[StateProvinceCode]
               ,[CountryRegionCode]
               ,[IsOnlyStateProvinceFlag]
               ,[StateProvinceName]
               ,[CountryName])
    SELECT [AddressID]
          ,[AddressLine1]
          ,[AddressLine2]
          ,[City]
          ,[PostalCode]
          ,[SpatialLocation]
          ,[StateProvinceCode]
          ,[CountryRegionCode]
          ,[IsOnlyStateProvinceFlag]
          ,[StateProvinceName]
          ,[CountryName]
      FROM [dbo].[dbo.OldBigTable]
    GO
    Il manque de prévoir les update et delete mais l'idée est là
    Fichiers attachés Fichiers attachés

Discussions similaires

  1. optimisation via index?
    Par Thsl007 dans le forum SQLite
    Réponses: 0
    Dernier message: 23/12/2010, 09h20
  2. [Conception] Stockage optimise
    Par Jack_serious dans le forum C++
    Réponses: 6
    Dernier message: 13/01/2008, 10h22
  3. Materialized view + Indexs + Contraintes
    Par hair_peace dans le forum Oracle
    Réponses: 4
    Dernier message: 05/09/2006, 17h57
  4. [Access] Optimisation performance requête - Index
    Par fdraven dans le forum Access
    Réponses: 11
    Dernier message: 12/08/2005, 14h30
  5. mauvaise conception et changement de clé primaire
    Par delphim dans le forum Schéma
    Réponses: 4
    Dernier message: 21/05/2004, 11h39

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo