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

MS SQL Server Discussion :

Utilisation des vues partitionnées


Sujet :

MS SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Profil pro
    Inscrit en
    Décembre 2008
    Messages
    23
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2008
    Messages : 23
    Par défaut Utilisation des vues partitionnées
    Bonjour,



    J'essaie désespérément d'utiliser des vues partitionnées pour mettre à jour des tables relativement massives.

    Pour alléger les problématiques de backup, j'ai splitté mes tables sur 2 bases distinctes.

    J'ai construit mes tables comme suit:

    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
     
    USE [BASE_003_SI]
    GO
     
    create table TEST2(
    champ1 varchar(10),
    champ2 varchar(10),
    champ3 varchar(10),
    champ4 varchar(10),
    champ5 varchar(10),
     CONSTRAINT [PK_TEST2_15] PRIMARY KEY 
    (
    	champ1 ASC,
    	champ5 ASC
    )
    )
    ALTER TABLE [dbo].[TEST2] WITH NOCHECK ADD CONSTRAINT [CHK_2_TEST2] CHECK (([champ5]='2'))
    GO
    ALTER TABLE [dbo].[TEST2] CHECK CONSTRAINT [CHK_2_TEST2]
    GO
     
     
    USE [BASE_004_SI]
    GO
    create table TEST3(
    champ1 varchar(10),
    champ2 varchar(10),
    champ3 varchar(10),
    champ4 varchar(10),
    champ5 varchar(10),
     CONSTRAINT [PK_TEST3_15] PRIMARY KEY 
    (
    	champ1 ASC,
    	champ5 ASC
    )
    )
     
    ALTER TABLE TEST3 WITH NOCHECK ADD CONSTRAINT [CHK_3_TEST3] CHECK (([champ5]='3'))
    GO
    ALTER TABLE TEST3 CHECK CONSTRAINT [CHK_3_TEST3]
    GO
     
     
    USE [BASE_002_SI]
    GO
    create view TESTVW AS SELECT * FROM BASE_003_SI.dbo.TEST2
    			UNION ALL SELECT * FROM BASE_004_SI.dbo.TEST3
    Lorsque j'essaie d'insérer une ligne dans ma vue:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    INTO TESTVW VALUES ('2','2','2','2','2')
    Il me renvoie l'erreur

    UNION ALL view 'BASE_002_SI.dbo.TESTVW' is not updatable because a partitioning column was not found.

    Pourtant, il me semblait que c'était fait pour.

    Pourriez-vous m'assister? Merci d'avance

    sOnO

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 999
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 999
    Billets dans le blog
    6
    Par défaut
    C'est à vous de créer et code des déclencheurs INSTEAD OF sur chacun des partitions.

    Exemple :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    USE BASE_004_SI
    GO
     
    CREATE TRIGGER E_I_B4_T3
    ON dbo.TEST3
    INSTEAD OF INSERT
    AS
     
    INSERT INTO dbo.TEST3
    SELECT * FROM inserted WHERE Colonne5 = 3
     
    GO
    Cela dit, faire une vue partitionnée sur le même serveur n'aura aucun gain de performance mais plutôt de larges pertes... L'intérêt des vues partitionnées est de faire cela sur différents serveurs liés.

    Sinon, optez pour le partitionnement de table.

    Avant tout les gains de perf peuvent être beaucoup plus importants :
    1) avec un modèle correct (respect des formes normales, hyper normalisation, etc)
    2) avec u choix judicieux des types de données (éviter le VARCHAR systématique et taillez au plus juste, etc...)
    3) avec une gestion fine des espaces de stockage (taillage de fichiers, placement des agrégats, niveau des RAIDS...)

    Lisez ce que j'ai écrit sur l'optimisation : http://sqlpro.developpez.com/optimisation/

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre averti
    Profil pro
    Inscrit en
    Décembre 2008
    Messages
    23
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Décembre 2008
    Messages : 23
    Par défaut
    Merci de votre réponse.

    Je suis du coup face à deux problématiques:
    1/ Ce modèle m'a été présenté comme utilisable pour de l'insertion, de l'update, du delete sous certaines contraintes. D'où ma question...

    2/ Il m'est plus ou moins imposé, donc je vais avoir du mal à faire autrement. En effet, le choix des vues partitionnées n'a pas été guidé par l'optimisation du requêtage, mais la simplification de flux d'alimentation de mes tables à travers du Business Object Data Services...

    Bon, du coup, je vais me pencher sur les trigger.

    Merci

    sOnO

  4. #4
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 999
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 999
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par sono_strass Voir le message
    Merci de votre réponse.

    Je suis du coup face à deux problématiques:
    1/ Ce modèle m'a été présenté comme utilisable pour de l'insertion, de l'update, du delete sous certaines contraintes. D'où ma question...
    A vous justement de réaliser lesdites contraintes !

    Citation Envoyé par sono_strass Voir le message
    2/ Il m'est plus ou moins imposé, donc je vais avoir du mal à faire autrement. En effet, le choix des vues partitionnées n'a pas été guidé par l'optimisation du requêtage, mais la simplification de flux d'alimentation de mes tables à travers du Business Object Data Services...
    Là je suis pas convaincu !!!!! Pourquoi BO vous imposerait une chose aussi stupide ?

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  5. #5
    Membre averti
    Profil pro
    Inscrit en
    Août 2010
    Messages
    20
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2010
    Messages : 20
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Cela dit, faire une vue partitionnée sur le même serveur n'aura aucun gain de performance mais plutôt de larges pertes... L'intérêt des vues partitionnées est de faire cela sur différents serveurs liés.

    Bonjour,

    Concernant les vues partitionnées, je pensais justement que le fait de rajouter une contrainte et de réduire le nombre de ligne par table améliorerait les performances, (l'optimiseur à l'aide de la contrainte va savoir directement où chercher l'information et a moins de ligne à parcourir...), pourquoi dites vous que cela entraine des pertes ?

    merci pour votre réponse

  6. #6
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 999
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 999
    Billets dans le blog
    6
    Par défaut
    Les vues partitionnées sont faites pour répartir la charge entre différents serveurs. Si les requêtes sont faites sur le même serveur, les plans de requêtes sont plus complexes et la charge est supportée sur le même serveur donc aucun gain ! Et même un peu de perte + de la complexité !!!

    Vous auriez intérêt à faire du partitionnement de table... Ce n'est pas la même chose...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Expert confirmé
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Par défaut
    Essayez ceci :

    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
    USE [BASE_003_SI]
    GO
     
    CREATE TABLE TEST2
    (
     champ1 varchar(10),
     champ2 varchar(10),
     champ3 varchar(10),
     champ4 varchar(10),
     champ5 varchar(10) CHECK ([champ5]='2'),
     CONSTRAINT [PK_TEST2_15] PRIMARY KEY 
     (
      champ1 ASC,
      champ5 ASC
     )
    )
    GO
     
    USE [BASE_004_SI]
    GO
     
    CREATE TABLE TEST3
    (
     champ1 varchar(10),
     champ2 varchar(10),
     champ3 varchar(10),
     champ4 varchar(10),
     champ5 varchar(10) CHECK ([champ5]='3'),
     CONSTRAINT [PK_TEST3_15] PRIMARY KEY 
     (
      champ1 ASC,
      champ5 ASC
     )
    )
    GO
     
    USE [BASE_003_SI]
    GO
     
    CREATE VIEW TESTVW 
    AS 
     
    SELECT * 
    FROM dbo.TEST2
    UNION ALL 
    SELECT * FROM [BASE_004_SI].dbo.TEST3 
    GO
     
    INSERT INTO TESTVW VALUES ('2','2','2','2','2') 
    INSERT INTO TESTVW VALUES ('3','3','3','3','3')
    ++

  8. #8
    Membre averti
    Profil pro
    Inscrit en
    Août 2010
    Messages
    20
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Août 2010
    Messages : 20
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    Les vues partitionnées sont faites pour répartir la charge entre différents serveurs. Si les requêtes sont faites sur le même serveur, les plans de requêtes sont plus complexes et la charge est supportée sur le même serveur donc aucun gain ! Et même un peu de perte + de la complexité !!!

    Vous auriez intérêt à faire du partitionnement de table... Ce n'est pas la même chose...

    A +

    merci pour votre réponse,

    je croyais que cela améliorerait les performances en faisant des vues partitionnées sur mes tables volumineuses (plusieurs million de lignes).
    Je m'étais inspiré de ce tutoriel : http://blog.developpez.com/mikedavem...-optimisation/ et nul part était question de faire cela sur plusieurs serveurs.
    Juste pour info, comment répartir la charge sur plusieurs serveurs ? J'ai crées mes tables partitionnées sur un autre serveur, (la vue faisant appel à ces tables via un serveur lié) histoire de tester mais c'est largement pire qu'avant...


    pour le partitionnement de table, j'y avais pensé mais le fait est que nos disques dur sont des partitions de SAN et il me semble que cela avait un impact que s'il s'agissait de vrais DD séparés ?
    est-ce vrai où même sur un seul disque, il est tout de même intéressant de faire des partitions de table ?

    merci,

  9. #9
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 999
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 999
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par mighty nagty Voir le message
    je croyais que cela améliorerait les performances en faisant des vues partitionnées sur mes tables volumineuses (plusieurs million de lignes).
    Je m'étais inspiré de ce tutoriel : http://blog.developpez.com/mikedavem...-optimisation/ et nul part était question de faire cela sur plusieurs serveurs.
    Cela n'a d'intérêt que dans le cas de multiserveur
    Juste pour info, comment répartir la charge sur plusieurs serveurs ? J'ai crées mes tables partitionnées sur un autre serveur, (la vue faisant appel à ces tables via un serveur lié) histoire de tester mais c'est largement pire qu'avant...
    Vous avez à nouveau tout faux....

    Vous devez créer une base de données sur chaque serveur (même nom si possible.
    Vous devez créer la même table dans chacune des base de chaque serveur.
    Vous devez trouver le critère de partition. Admettons que c'est une date (sur une colonne de type DATE NOT NULL (impératif))
    Vous devez ensuite placer dans chaque table une contrainte CHECK qui valide les date de la colonne dans l'intervalle précisé;
    Vous devez ensuite créer un index CLUSTERED sur cette colonne (l'idéal étant qu'il soit UNIQUE !)
    Vous devez ensuite créer des serveurs liés.
    Vous devez ensuite créer des vues concaténant les tables des différents serveur par des UNION ALL.
    Derrière chaque vue, vous implantez des trigger INSTAED OF (INSERT, UPDATE, DELETE) qui renvoient au tables.

    A +





    pour le partitionnement de table, j'y avais pensé mais le fait est que nos disques dur sont des partitions de SAN et il me semble que cela avait un impact que s'il s'agissait de vrais DD séparés ?
    est-ce vrai où même sur un seul disque, il est tout de même intéressant de faire des partitions de table ?

    merci,[/QUOTE]
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

Discussions similaires

  1. Utilisation des vues.
    Par Sedor29 dans le forum MS SQL Server
    Réponses: 5
    Dernier message: 19/03/2008, 16h43
  2. Utilisation des vues matérialisées Oracle
    Par yphilogene dans le forum Autres outils décisionnels
    Réponses: 6
    Dernier message: 14/12/2007, 00h48
  3. Fonctionnement et utilisation des vues matérialisées
    Par gOgHi dans le forum Administration
    Réponses: 7
    Dernier message: 19/10/2004, 14h29
  4. Utilisation des vues
    Par Andry dans le forum Débuter
    Réponses: 2
    Dernier message: 19/07/2004, 08h00
  5. [Crystal Report] Utilisation des vues de sql serveur
    Par Olivierakadev dans le forum SAP Crystal Reports
    Réponses: 2
    Dernier message: 15/11/2002, 17h44

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