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 :

Problèmes avec une procédure SQL server 2000


Sujet :

MS SQL Server

  1. #1
    Membre averti
    Profil pro
    Inscrit en
    Octobre 2010
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Octobre 2010
    Messages : 14
    Par défaut Problèmes avec une procédure SQL server 2000
    Bonjour à tous,

    Novice en SQL server, je travail actuellement sur une application SQL Server 2000. J'ai un problème avec ma procédure stockée SQL server. De toute évidence, elle paraît contre - performante car elle consomme beaucoup de temps CPU.

    J’ai développé une procédure qui permet de calculer les indicateurs financiers d’un tableau de bord et de mettre à jour ces données dans ma table principale.

    Cette procédure utilise une fonction qui permet de calculer différentes quantités nécessaires à la mise à jour de mon tableau de bord.
    Je l’ai testé et elle marche. Le problème, c’est qu’elle met un certains temps pour s’exécuter.

    Aussi, j’ai utilisé l’analiseur de requêtes (SQL Query Analyzer) afin d’obtenir une trace de ma procédure. J’ai constaté qu’elle utilisait un temps CPU assez important, ce qui pourrait justifier la lenteur constatée lors de son exécution.
    - Pour la procédure : CPU : 517, Lectures (Reads) : 977
    - Pour la fonction : CPU : 2499, Lectures (Reads) : 5504


    L’application gérant des niveaux différents, le niveau 0 (zéro) correspond à la racine, le niveau 2 au projet et le niveau 3 au sous projet qui est le tableau de bord.
    Au niveau de la racine, on affiche l’ensemble des programmes (niveau 1). Dans un programme, on a au moins un projet (niveau 2). Dans un projet, on a au moins un sous projet (niveau 3).

    Aussi, je souhaiterai avoir vos conseils, vos orientations et vos propositions pour que la procédure consomme moins de CPU afin qu’elle s’exécute plus rapidement. Je passe probablement à côté de quelque chose.

    Ci dessous, la procédure:

    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
     
    CREATE PROCEDURE update_tableau_de_bord
    -- Cette procédure calcule les indicateurs financiers globaux de tableausx de bord
    --   This procedure computes the aggregated financial indicators from the scorecard
    	@entry_id  int,
    	@level_id  int
    AS
    SET NOCOUNT ON  
    DECLARE
    @total_fin_ind1 decimal(38,14), --row_id=1
    @total_fin_ind2 decimal(38,14), --row_id=14
    @total_fin_ind3 decimal(38,14), --row_id=99
    @total_fin_ind1_percent float, 
    @total_fin_ind2_percent float, 
    @total_fin_ind3_percent float, 
    @total_fin_ind1_current decimal(38,14), 
    @total_fin_ind2_current decimal(38,14), 
    @total_fin_ind3_current decimal(38,14), 
    @today_ctime int,
    @current_date datetime
    SET @current_date = DATEADD(quarter,-1,GETDATE())
    IF @level_id = 0  -- le niveau 0 correspond à la racine 
    	RETURN
    -- calcul des indicateurs financiers
    IF @level_id=3  -- le niveau 3 correspond au sous projet et le niveau 2 correspond au projet
    BEGIN
    	-- Obtenir l'indicateur financier 1
    	SELECT
    	    @total_fin_ind1   =   ISNULL( SUM(ISNULL(col_0001, 0)), 0)
    	FROM 	Tableau_de_bord_euro  
    	WHERE
    	    entry_id3 = @entry_id AND row_id = 1 
    	-- Obtenir l'indicateur financier 2
    	SELECT
    	    @total_fin_ind2   =   ISNULL( SUM(ISNULL(col_0001, 0)), 0)
    	FROM
    	    Tableau_de_bord_euro  
    	WHERE
    	    entry_id3 = @entry_id AND row_id = 14
    	-- Obtenir l'indicateur financier 3
    	SELECT
    	    @total_fin_ind3   =   ISNULL( SUM(ISNULL(col_0001, 0)), 0)
    	FROM
    	      Tableau_de_bord_euro  
    	WHERE
    	    entry_id3 = @entry_id AND row_id = 99      
    	-- calculer la valeur actuelle pour les indicateurs financiers
    	EXEC @total_fin_ind1_current	= 	calculate_qtd @entry_id, 1,@current_date
    	EXEC @total_fin_ind2_current	= 	calculate_qtd @entry_id,14,@current_date
    	EXEC @total_fin_ind3_current	= 	calculate_qtd @entry_id,99,@current_date
    END
    ELSE 
    BEGIN
    	SELECT
    	    @total_fin_ind1   		  =   ISNULL( SUM(ISNULL(col_fin_ind1, 0)), 0),
    	    @total_fin_ind2   		  =   ISNULL( SUM(ISNULL(col_fin_ind2, 0)), 0),
    	    @total_fin_ind3   		  =   ISNULL( SUM(ISNULL(col_fin_ind3, 0)), 0),
    	    @total_fin_ind1_current   =   ISNULL( SUM(ISNULL(col_fin_ind1_current, 0)), 0),
    	    @total_fin_ind2_current   =   ISNULL( SUM(ISNULL(col_fin_ind2_current, 0)), 0),
    	    @total_fin_ind3_current   =   ISNULL( SUM(ISNULL(col_fin_ind3_current, 0)), 0)
    	FROM
    	TableDonnees
    	WHERE
    	    parent_entry_id = @entry_id AND deleted=0 
    END	     
    IF @total_fin_ind1 = 0
    	SET @total_fin_ind1_percent = 0
    ELSE
    	SET @total_fin_ind1_percent = ROUND ((@total_fin_ind1_current/@total_fin_ind1)*100 ,0)
    IF @total_fin_ind2 = 0
    	SET @total_fin_ind2_percent = 0
    ELSE
    	SET @total_fin_ind2_percent = ROUND ((@total_fin_ind2_current/@total_fin_ind2)*100 ,0)
    IF @total_fin_ind3 = 0
    	SET @total_fin_ind3_percent = 0
    ELSE	
    	SET @total_fin_ind3_percent = ROUND ((@total_fin_ind3_current/@total_fin_ind3)*100 ,0)
    IF @total_fin_ind1_percent>201 SET @total_fin_ind1_percent = 201
    IF @total_fin_ind2_percent>201 SET @total_fin_ind2_percent = 201
    IF @total_fin_ind3_percent>201 SET @total_fin_ind3_percent = 201
    UPDATE TableDonnees
    SET    
        col_fin_ind1 				= @total_fin_ind1,
        col_fin_ind2 				= @total_fin_ind2,
        col_fin_ind3 				= @total_fin_ind3,
        col_fin_ind1_current 	= @total_fin_ind1_current,
        col_fin_ind2_current 	= @total_fin_ind2_current,
        col_fin_ind3_current 	= @total_fin_ind3_current,
        col_fin_ind1_percent 	= @total_fin_ind1_percent,
        col_fin_ind2_percent 	= @total_fin_ind2_percent,
        col_fin_ind3_percent 	= @total_fin_ind3_percent
    WHERE  
        entry_id = @entry_id AND deleted = 0
    GO
    Ci- dessous la fonction exécutée dans la procédure:
    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
     
    CREATE FUNCTION calculate_qtd (@entry_id int,@row_id int,@today_ctime datetime)
    -- Cette fonction permet de calculer les quantités....
    RETURNS float
    AS
    BEGIN
    	DECLARE
    	@currentyear int,
    	@currentquarter int,
    	@sort_id int,
    	@qtd float,
    	@qtd_tmp float,
    	@sc_col_id varchar(100),
    	@year_tmp int,
    	@previous_year int,
    	@qtd_year float
    	SET @qtd = 0
    	SET  @currentyear  = YEAR  (@today_ctime)
    	SET  @currentquarter = DATEPART(quarter, @today_ctime) 
    	SELECT @sort_id=sort_id
    	FROM SC_COLUMN_MAP
    	WHERE year=@currentyear and quarter=@currentquarter
    	DECLARE sc_column_cursor  CURSOR READ_ONLY
    	FOR 
    	    SELECT sc_col_id,year FROM SC_COLUMN_MAP where sort_id<=@sort_id and sort_id%5<>0 order by sort_id
    	OPEN sc_column_cursor
    	FETCH NEXT FROM sc_column_cursor INTO @sc_col_id,@year_tmp
    	SET @previous_year = @year_tmp
    	WHILE @@FETCH_STATUS = 0
    	      BEGIN
    	      		SELECT 
    	      			@qtd_tmp=
    	      			CASE @sc_col_id 
    	      				WHEN 'COL_0006' THEN COL_0006
    		              WHEN 'COL_0016' THEN COL_0016
    		              WHEN 'COL_0026' THEN COL_0026
    		              WHEN 'COL_0036' THEN COL_0036
    		              WHEN 'COL_0040' THEN COL_0040
    		              WHEN 'COL_0056' THEN COL_0056
    		              WHEN 'COL_0066' THEN COL_0066
    		              WHEN 'COL_0076' THEN COL_0076
    		              WHEN 'COL_0086' THEN COL_0086
    		              WHEN 'COL_0090' THEN COL_0090
    		              WHEN 'COL_0106' THEN COL_0106
    		              WHEN 'COL_0116' THEN COL_0116
    		              WHEN 'COL_0126' THEN COL_0126
    		              WHEN 'COL_0136' THEN COL_0136
    		              WHEN 'COL_0140' THEN COL_0140
    		              WHEN 'COL_0156' THEN COL_0156
    		              WHEN 'COL_0166' THEN COL_0166
    		              WHEN 'COL_0176' THEN COL_0176
    		              WHEN 'COL_0186' THEN COL_0186
    		              WHEN 'COL_0190' THEN COL_0190
    		              WHEN 'COL_0206' THEN COL_0206
    		              WHEN 'COL_0216' THEN COL_0216
    		              WHEN 'COL_0226' THEN COL_0226
    		              WHEN 'COL_0236' THEN COL_0236
    		              WHEN 'COL_0240' THEN COL_0240
    		              WHEN 'COL_0256' THEN COL_0256
    		              WHEN 'COL_0266' THEN COL_0266
    		              WHEN 'COL_0276' THEN COL_0276
    		              WHEN 'COL_0286' THEN COL_0286
    		              WHEN 'COL_0290' THEN COL_0290
    		              WHEN 'COL_0306' THEN COL_0306
    		              WHEN 'COL_0316' THEN COL_0316
    		              WHEN 'COL_0326' THEN COL_0326
    		              WHEN 'COL_0336' THEN COL_0336
    		              WHEN 'COL_0340' THEN COL_0340
    		              WHEN 'COL_0356' THEN COL_0356
    		              WHEN 'COL_0366' THEN COL_0366
    		              WHEN 'COL_0376' THEN COL_0376
    		              WHEN 'COL_0386' THEN COL_0386
    		              WHEN 'COL_0390' THEN COL_0390
    		              WHEN 'COL_0406' THEN COL_0406
    		              WHEN 'COL_0416' THEN COL_0416
    		              WHEN 'COL_0426' THEN COL_0426
    		              WHEN 'COL_0436' THEN COL_0436
    		              WHEN 'COL_0440' THEN COL_0440
    		              WHEN 'COL_0456' THEN COL_0456
    		              WHEN 'COL_0466' THEN COL_0466
    		              WHEN 'COL_0476' THEN COL_0476
    		              WHEN 'COL_0486' THEN COL_0486
    		              WHEN 'COL_0490' THEN COL_0490
    		              WHEN 'COL_0506' THEN COL_0506
    		              WHEN 'COL_0516' THEN COL_0516
    		              WHEN 'COL_0526' THEN COL_0526
    		              WHEN 'COL_0536' THEN COL_0536
    		              WHEN 'COL_0540' THEN COL_0540
    		              WHEN 'COL_0556' THEN COL_0556
    		              WHEN 'COL_0566' THEN COL_0566
    		              WHEN 'COL_0576' THEN COL_0576
    		              WHEN 'COL_0586' THEN COL_0586
    		              WHEN 'COL_0590' THEN COL_0590
    		              WHEN 'COL_0606' THEN COL_0606
    		              WHEN 'COL_0616' THEN COL_0616
    		              WHEN 'COL_0626' THEN COL_0626
    		              WHEN 'COL_0636' THEN COL_0636
    		              WHEN 'COL_0640' THEN COL_0640
    		              WHEN 'COL_0656' THEN COL_0656
    		              WHEN 'COL_0666' THEN COL_0666
    		              WHEN 'COL_0676' THEN COL_0676
    		              WHEN 'COL_0686' THEN COL_0686
    		              WHEN 'COL_0690' THEN COL_0690
    		              WHEN 'COL_0706' THEN COL_0706
    		              WHEN 'COL_0716' THEN COL_0716
    		              WHEN 'COL_0726' THEN COL_0726
    		              WHEN 'COL_0736' THEN COL_0736
    		              WHEN 'COL_0740' THEN COL_0740		             
    	              ELSE 0
    	      			END
    	      		FROM Tableau_de_bord_euro  
    WHERE row_id = @row_id and entry_id3 = @entry_id
    	          IF @previous_year <> @year_tmp
    	          	BEGIN
    		            SET @qtd = @qtd + ISNULL(@qtd_year,0)
    		            SET @qtd_year = @qtd_tmp
    		            SET @previous_year = @year_tmp
    		          END
    	          ELSE
    	            --Si la variable @qtd_tmp n'est pas NULL
    		    --IF @qtd_tmp IS NOT NULL
    	               SET @qtd_year = @qtd_tmp
    	          FETCH NEXT FROM sc_column_cursor INTO @sc_col_id,@year_tmp
    	      END     
    	CLOSE sc_column_cursor
    	DEALLOCATE sc_column_cursor
    	SET @qtd = @qtd + ISNULL(@qtd_year,0)              
    	return @qtd 
    END
    Merci par avance pour vos retours.

  2. #2
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    Bonjour,

    Il y a plusieurs choses qui sont contre-performantes dans l'expression de vos requêtes :

    - les fonctions scalaires sont connues pour être contre-performantes parce qu'elles ne sont pas traitée de façon ensembliste
    - un curseur est le pire ennemi d'une requête performante
    - vous êtes visiblement la victime d'une mauvaise modélisation de la base de données sur laquelle vous travaillez, et c'est définitivement l'ennemi d'une base de données relationnelle SQL performante.

    Rappelons que SQL est un langage :

    - Déclaratif, c'est à dire que le développeur ne doit pas se soucier de la façon dont est obtenu le résultat de la requête; il ne guide pas le moteur de base de données : c'est ce dernier qui fait le choix de la meilleure façon de résoudre la requête. La plupart du temps il fait ce choix proprement, mais il a parfois besoin de l'aide d'un DBA.

    - Ensembliste, c'est à dire qu'il est conçu et optimisé pour traiter de façon non-itérative un grand nombre de données très rapidement

    Donc en supposant que vous ne pouvez pas remettre en question le modèle de données, il faut donc arriver à se passer du curseur, et déplacer le code de la fonction dans la procédure stockée.

    La définition de votre curseur étant :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT	sc_col_id
    	, year
    FROM	dbo.SC_COLUMN_MAP
    WHERE	sort_id <= @sort_id
    AND	sort_id % 5 <> 0
    ORDER	BY sort_id
    et comme vous avez besoin d'effectuer IF @previous_year <> @year_tmp, c'est qu'il y a un ordre dans le traitement.
    Or encore une fois, l'ordre n'existe pas en SQL, puisque c'est un langage ensembliste.

    Est-il possible de réaliser une auto-jointure sur le sort_id, ou year, du style :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT		A.sc_col_id
    		, A.year AS previous_year
    		, B.year AS next_year
    FROM		dbo.SC_COLUMN_MAP AS A
    INNER JOIN	dbo.SC_COLUMN_MAP AS B
    			ON A.sort_id = B.sort_id + 4
    WHERE		A.sort_id <= @sort_id
    AND		A.sort_id % 5 <> 0
    ORDER BY	A.sort_id
    ?

    @++

  3. #3
    Membre averti
    Profil pro
    Inscrit en
    Octobre 2010
    Messages
    14
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Octobre 2010
    Messages : 14
    Par défaut
    Bonjour,

    Merci pour vos retours.

    Effectivement, j'ai un souci avec la modélisation de la base de données. De toute évidence, elle a été mal modélisée. Le fait est que je ne peux la remettre en question. Aussi vos retours me sont d'une grande aide.

    Je vais suivre vos orientations, vos conseils et voir ce que cela donnera.

    Merci encore.

    @++

Discussions similaires

  1. [JDBC] Problème avec une procédure SQL
    Par Hanagumi dans le forum JDBC
    Réponses: 1
    Dernier message: 18/02/2012, 12h20
  2. [Vxi3] Problème avec une table SQL SERVER 2005
    Par ahmed_amine dans le forum Designer
    Réponses: 2
    Dernier message: 14/06/2011, 18h10
  3. problème avec bulk insert sql server 2000
    Par siyassine dans le forum Administration
    Réponses: 1
    Dernier message: 25/08/2010, 08h31
  4. Erreur de connexion à une BDD SQL Server 2000 avec BDE
    Par SchpatziBreizh dans le forum Bases de données
    Réponses: 3
    Dernier message: 17/06/2005, 11h22

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