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

Développement SQL Server Discussion :

Aide pour optimisation d'une requête


Sujet :

Développement SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Homme Profil pro
    Directeur technique
    Inscrit en
    Février 2012
    Messages
    26
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Boutique - Magasin

    Informations forums :
    Inscription : Février 2012
    Messages : 26
    Par défaut Aide pour optimisation d'une requête
    Bonjour,
    j'aurai besoin d'aide pour résoudre un problème.
    Je dois réaliser des calculs sur une table qui ce compose ainsi:

    TABLE :MOUVEMENT
    DATE ID_PRODUIT QTE_IN QTE_OUT TYPE_MOUVEMENT

    01/01/2012 1 1 0 ACH
    01/01/2012 1 1 0 ACH
    01/01/2012 2 0 1 VTE
    01/02/2012 1 10 0 INV
    03/01/2012 1 0 3 VTE


    LE but est d'obtenir une table STOCK contenant tous les ID_PRODUIT et une colonne STOCK

    Je parviens a faire le regroupement et la somme de QTE_IN et QTE_OUT par cette requête.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    insert into STOCK (ID_PRODUIT,TOTAL_QTE_IN,TOTAL_QTE_OUT)
            select distinct ID_PRODUIT ,sum(QTE_IN),sum(QTE_OUT) from MOUVEMENT where GROUP BY ID_PRODUIT

    Mais le problème est que chaque ligne TYPE_MOUVEMENT='INV' affecte un nouveau stock et non une entrée.

    D'après vous quel serait la meilleure approche ?
    Merci d'avance.

  2. #2
    Membre averti
    Homme Profil pro
    Directeur technique
    Inscrit en
    Février 2012
    Messages
    26
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Boutique - Magasin

    Informations forums :
    Inscription : Février 2012
    Messages : 26
    Par défaut
    J'ai trouvé une solution en utilisant un curseur.
    Cette solution me permet de calculer l’évolution du stock sur chaque ligne et non d'avoir uniquement un stock final.

    Voila un exemple du code,si cela peut servir a quelqu'un.
    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
     
    CREATE TABLE [dbo].[temp](
    	[REFERENCE] char(13) NULL,
    	[STOCK] [numeric] default 0 NULL,
    	[CODE_ORIGINE] char(10)  NULL,
    )
     
    declare @REFERENCE char(13)
    declare @REFERENCEold char(13)
    declare @QTE_ENTREE numeric 
    declare @QTE_SORTIE numeric 
    declare @CODE_ORIGINE char(10)
    declare @stock numeric
    declare @stockold numeric
     
    DECLARE Mon_Curseur CURSOR FOR
    SELECT  REFERENCE,QTE_ENTREE,QTE_SORTIE,CODE_ORIGINE from MOUVSTK01 order by REFERENCE
    OPEN Mon_Curseur;
     
    FETCH Mon_Curseur INTO @REFERENCE,@QTE_ENTREE,@QTE_SORTIE,@CODE_ORIGINE
     
    WHILE @@FETCH_STATUS =0
    	BEGIN
    		IF @REFERENCEold<>@REFERENCE OR @CODE_ORIGINE='INV'
    			BEGIN
    			set @stock=@QTE_ENTREE-@QTE_SORTIE
    			insert into temp (REFERENCE,STOCK,CODE_ORIGINE) values (@REFERENCE,@STOCK,@CODE_ORIGINE)
     
    			set @stockold=@stock
    			set @REFERENCEold=@REFERENCE
    			END
    		ELSE
    			BEGIN
    			SET @stock=@STOCK+@QTE_ENTREE-@QTE_SORTIE
    			insert into temp (REFERENCE,STOCK,CODE_ORIGINE) values (@REFERENCE,@STOCK,@CODE_ORIGINE)
     
    			set @stockold=@stock
    			set @REFERENCEold=@REFERENCE
    			END
     
    		FETCH Mon_Curseur INTO @REFERENCE,@QTE_ENTREE,@QTE_SORTIE,@CODE_ORIGINE
     
    	END
     
     
    CLOSE Mon_Curseur
    DEALLOCATE Mon_Curseur
    Je vais voir si je peux directement alimenter un champ stock sur ma table mouvstk01 plutot que de créer une table temp.

    Je suis ouvert a toute autre solution qui pourrait être moins consommatrice pour le serveur et plus rapide (le temps de traitement des différent fichiers est autour des 1 heure 30 actuellement).

    Merci par avance.

  3. #3
    Membre expérimenté
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    Juillet 2006
    Messages
    3 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : Juillet 2006
    Messages : 3 932
    Par défaut
    Pourrais-tu préciser ?

    J'ai bien compris que ACH = achat et donc que ton stock est augmenté de la valeur de la colonne QTE_IN.
    J'ai bien compris aussi que VTE = vente et donc que ton stock est diminué de la valeur de la colonne QTE_OUT.

    Par contre, pour INV, je ne vois pas bien ce que tu veux dire par "une nouvelle entrée".

    Aussi, quel résultat final veux-tu exactement ? Ce n'est pas très clair.

    Pour finir, même si la table ne contient que 5 colonnes, pense à respecter la charte de postage (et donc à poster le code DDL pour cette table).

  4. #4
    Membre averti
    Homme Profil pro
    Directeur technique
    Inscrit en
    Février 2012
    Messages
    26
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Boutique - Magasin

    Informations forums :
    Inscription : Février 2012
    Messages : 26
    Par défaut
    Bonjour,

    Effectivement je ne suis pas très clair dans ma demande car je me rend compte que ce que j'ai effectué en utilisant le curseur m'ouvre d'autre possibilités.

    A la base je voulais obtenir le stock final de chaque référence se trouvant dans mouvstk01.

    cette table est remplie par un import de fichiers récupéré d'un autre logiciel.

    structure de la table :
    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
     
    CREATE TABLE [dbo].[MOUVSTK01](
    	[CODE_ORIGINE] char(10)  NULL,
    	[DATE] [smalldatetime] NULL,
    	[CODE_CLIFOUR] char(8) default '' NULL,
    	[CODE_MAGASIN] [numeric] NULL,
    	[REFERENCE] char(13) NULL,
    	[QTE_ENTREE] [numeric] default 0 NULL,
    	[QTE_SORTIE] [numeric] default 0 NULL,
    	[PUHT] decimal(11,5) default 0 NULL,
    	[PUHTNET] decimal(11,5) default 0 NULL,
    	[PUTTC] decimal(11,5) default 0 NULL,
    	[PUTTCNET] decimal(11,5) default 0 NULL,
    	[TAUX_REMISE] [decimal] default 0 NULL,
    	[PRHT] decimal(11,5) default 0 NULL,
    	[TYPE_VENTE] [numeric] default 0 NULL,
    	[CODE_TARIF] [numeric] default 0 NULL,
    	ACH_TOTALHT as (QTE_ENTREE)*PUHT,
    	ACH_TOTALHTNET as (QTE_ENTREE)*PUHTNET,
    	ACH_TOTALTTC as (QTE_ENTREE)*PUTTC,
    	ACH_TOTALTTCNET as (QTE_ENTREE)*PUTTCNET,
    	ACH_TOTALPRHT as (QTE_ENTREE)*PRHT,
    	VTE_TOTALHT as (QTE_SORTIE)*PUHT,
    	VTE_TOTALTTC as (QTE_SORTIE)*PUTTC,
    	VTE_TOTALTTCNET as (QTE_SORTIE)*PUTTCNET,
    	VTE_TOTALPRHT as (QTE_SORTIE)*PRHT,
    	VTE_TOTALMARGE as ((QTE_SORTIE*PUHTNET)-(QTE_SORTIE*PRHT)) 
    )
    Le problème qu'il y a avec la ligne 'INV' c'est quelle fixe une nouvelle valeur du stock.

    N'arrivant pas a faire de requette "simple" j'ai décidé d'utiliser un curseur.

    J'ai donc modifier ma table mouvstk01 de cette facon:
    rajout d'un champ ID_LIGNE et d'un champ STOCK

    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
     
    CREATE TABLE [dbo].[MOUVSTK01](
    	ID_LIGNE int IDENTITY(1,1),
    	[CODE_ORIGINE] char(10)  NULL,
    	[DATE] [smalldatetime] NULL,
    	[CODE_CLIFOUR] char(8) default '' NULL,
    	[CODE_MAGASIN] [numeric] NULL,
    	[REFERENCE] char(13) NULL,
    	[QTE_ENTREE] [numeric] default 0 NULL,
    	[QTE_SORTIE] [numeric] default 0 NULL,
    	[PUHT] decimal(11,5) default 0 NULL,
    	[PUHTNET] decimal(11,5) default 0 NULL,
    	[PUTTC] decimal(11,5) default 0 NULL,
    	[PUTTCNET] decimal(11,5) default 0 NULL,
    	[TAUX_REMISE] [decimal] default 0 NULL,
    	[PRHT] decimal(11,5) default 0 NULL,
    	[TYPE_VENTE] [numeric] default 0 NULL,
    	[CODE_TARIF] [numeric] default 0 NULL,
    	ACH_TOTALHT as (QTE_ENTREE)*PUHT,
    	ACH_TOTALHTNET as (QTE_ENTREE)*PUHTNET,
    	ACH_TOTALTTC as (QTE_ENTREE)*PUTTC,
    	ACH_TOTALTTCNET as (QTE_ENTREE)*PUTTCNET,
    	ACH_TOTALPRHT as (QTE_ENTREE)*PRHT,
    	VTE_TOTALHT as (QTE_SORTIE)*PUHT,
    	VTE_TOTALTTC as (QTE_SORTIE)*PUTTC,
    	VTE_TOTALTTCNET as (QTE_SORTIE)*PUTTCNET,
    	VTE_TOTALPRHT as (QTE_SORTIE)*PRHT,
    	VTE_TOTALMARGE as ((QTE_SORTIE*PUHTNET)-(QTE_SORTIE*PRHT)), 
    	[STOCK] [numeric] default 0 NULL
     
    constraint pk_presence1
    primary key (ID_LIGNE)
    )
    Voici ma requette avec le curseur qui calcul et affecte le stock

    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
     
    declare @REFERENCE char(13)
    declare @REFERENCEold char(13)
    declare @QTE_ENTREE numeric 
    declare @QTE_SORTIE numeric 
    declare @CODE_ORIGINE char(10)
    declare @stock numeric
    declare @stockold numeric
    declare @id_ligne int
     
     
    DECLARE Mon_Curseur CURSOR FOR
    SELECT  ID_LIGNE,REFERENCE,QTE_ENTREE,QTE_SORTIE,CODE_ORIGINE from MOUVSTK01 order by REFERENCE,ID_LIGNE
    OPEN Mon_Curseur;
     
    FETCH Mon_Curseur INTO @ID_LIGNE,@REFERENCE,@QTE_ENTREE,@QTE_SORTIE,@CODE_ORIGINE
     
    WHILE @@FETCH_STATUS =0
    	BEGIN
    		IF @REFERENCEold<>@REFERENCE OR @CODE_ORIGINE='<INV>'
    			BEGIN
    			set @stock=@QTE_ENTREE-@QTE_SORTIE
    			update mouvstk01 set STOCK=@stock where ID_LIGNE=@ID_LIGNE
     
    			set @stockold=@stock
    			set @REFERENCEold=@REFERENCE
    			END
    		ELSE
    			BEGIN
    			SET @stock=@STOCK+@QTE_ENTREE-@QTE_SORTIE
    			update mouvstk01 set STOCK=@stock where ID_LIGNE=@ID_LIGNE
     
    			set @stockold=@stock
    			set @REFERENCEold=@REFERENCE
    			END
     
    		FETCH Mon_Curseur INTO @ID_LIGNE,@REFERENCE,@QTE_ENTREE,@QTE_SORTIE,@CODE_ORIGINE
     
    	END
     
     
    CLOSE Mon_Curseur
    DEALLOCATE Mon_Curseur
    Je peux maintenant obtenir le stock de mes produits a n'importe quelle date très rapidement.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    select mouvstk01.reference,id_ligne,stock
    from (select mouvstk01.reference,MAX(id_ligne) as maxid_ligne
    		from mouvstk01 where mouvstk01."date"<='01/01/2013'
    			group by reference) temp
     
    join mouvstk01 on mouvstk01.reference = temp.reference and mouvstk01.id_ligne = temp.maxid_ligne

    Y a t'il moyen d’améliorer cette requette avec curseur qui est "trés" longue(1h30).
    car mouvstk01 contient environ 5.5 millions de lignes actuellement pour 1 ans d'historique.
    Elle se lancera la nuit hors production.

    En espérant avoir été plus clair.

  5. #5
    Membre expérimenté
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    Juillet 2006
    Messages
    3 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : Juillet 2006
    Messages : 3 932
    Par défaut
    Que donne ceci ?

    Je ne garantis pas les fautes de syntaxe, j'ai fait ça directement sur le forum.

    L'idée est de prendre la dernière date d'inventaire pour chaque produit et ensuite, de faire la somme des stocks à partir de cette date.

    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
     
    WITH INV(DATE,        ID_PRODUIT)
    AS (
        SELECT
                MAX(DATE) AS 'DATE',
                ID_PRODUIT
        FROM
                MOUVEMENT
        WHERE
                TYPE_MOUVEMENT = 'INV'
        GROUP BY
                ID_PRODUIT
    )
     
    SELECT
            MVT.ID_PRODUIT,
            SUM(
            CASE MVT.TYPE_MOUVEMENT
                WHEN 'INV' THEN MVT.QTE_IN
                WHEN 'ACH' THEN MVT.QTE_IN
                WHEN 'VTE' THEN MVT.QTE_OUT*-1
            END) AS 'QTE_STOCK'
    FROM
            MOUVEMENT MVT
                INNER JOIN INV
                    ON   MVT.ID_PRODUIT = INV.ID_PRODUIT
                    AND MVT.DATE >= INV.DATE
    GROUP BY
            ID_PRODUIT
    EDIT : Je me suis basé sur les noms de colonnes que tu donnes dans ton premier message car je n'ai pas retrouvé la colonne TYPE_MOUVEMENT dans le DDL que tu donnes.

  6. #6
    Membre averti
    Homme Profil pro
    Directeur technique
    Inscrit en
    Février 2012
    Messages
    26
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Boutique - Magasin

    Informations forums :
    Inscription : Février 2012
    Messages : 26
    Par défaut
    Effectivement dans mon 1er post j'avais simplifié la table pour être plus clair
    Type mouvement correspond a CODE_ORIGINE et il y a plusieurs valeurs possible differente de ACH et VTE.

    J'ai compris l'esprit de ta requette , je test ça demain.

    EDIT:Je pense qu'il va y avoir un problème car tous les produits non pas forcement un inventaire et donc ces produits seront exclus du calcul.

    Merci.

  7. #7
    Membre averti
    Homme Profil pro
    Directeur technique
    Inscrit en
    Février 2012
    Messages
    26
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Boutique - Magasin

    Informations forums :
    Inscription : Février 2012
    Messages : 26
    Par défaut
    La requette marche mais comme je le pensai les références qui n'ont pas de ligne
    <INV> ne sont pas prises en compte.

    La requette adapté ma table:

    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
     
    WITH INV("date",reference)
    AS (
        SELECT
                MAX("DATE") AS 'DATE', reference
        FROM
                mouvstk01
        WHERE
                code_origine = '<INV>'
        GROUP BY
                reference
    )
     
    SELECT
            mvt.reference,
            SUM(
            CASE mvt.code_origine
                WHEN '<INV>' THEN mvt.qte_entree
                WHEN 'BRA' THEN mvt.qte_entree
                WHEN 'FAA' THEN mvt.qte_entree
                WHEN 'TIC' THEN mvt.qte_sortie*-1
            END) AS 'QTE_STOCK'
    FROM
            mouvstk01 mvt
                INNER JOIN INV
                    ON   mvt.reference = INV.reference
                    AND mvt.DATE >= INV.DATE
    GROUP BY
            mvt.reference
    serait il possible de faire un truc du style :
    WHEN '<INV>' THEN mvt.qte_entree-QTE_STOCK
    et de faire le calcul sur toute la table?

  8. #8
    Membre expérimenté
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    Juillet 2006
    Messages
    3 932
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : Juillet 2006
    Messages : 3 932
    Par défaut
    C'est triché mais pour simplifier, pourquoi ne pas faire qqch 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
    14
    15
     
    INSERT INTO MOUVEMENT
    SELECT
                '19000101',
                ID_PRODUIT,
                0,
                0,
                'INV'
    FROM
                MOUVEMENT
    WHERE
                ID_PRODUIT NOT IN 
                        (
                         SELECT ID_PRODUIT FROM MOUVEMENT WHERE TYPE_MOUVEMENT = 'INV'
                        )
    De cette manière, tous tes produit auront une ligne d'inventaire.

  9. #9
    Membre averti
    Homme Profil pro
    Directeur technique
    Inscrit en
    Février 2012
    Messages
    26
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Directeur technique
    Secteur : Boutique - Magasin

    Informations forums :
    Inscription : Février 2012
    Messages : 26
    Par défaut
    Effectivement je n'y avait pas pensé.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Demande d'aide pour réalisation d'une requête SQL
    Par etiennegaloup dans le forum Langage SQL
    Réponses: 3
    Dernier message: 14/10/2013, 08h54
  2. [11g] Besoin d'aide pour écriture d'une requête
    Par telchargement dans le forum SQL
    Réponses: 3
    Dernier message: 19/04/2013, 11h05
  3. Demande d'aide pour l'optimisation d'une requête
    Par Menoto dans le forum Optimisations
    Réponses: 4
    Dernier message: 04/04/2008, 12h36
  4. Aide pour établissement d'une requête
    Par Virgile59 dans le forum Access
    Réponses: 2
    Dernier message: 28/02/2006, 18h55
  5. besoin d'aide pour optimiser une requête
    Par jisse dans le forum Langage SQL
    Réponses: 4
    Dernier message: 27/01/2006, 09h41

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