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 :

Optimiser une procédure stockée [2016]


Sujet :

Développement SQL Server

  1. #1
    Membre averti Avatar de soad
    Homme Profil pro
    Développeur .NET
    Inscrit en
    Février 2004
    Messages
    520
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : Suisse

    Informations professionnelles :
    Activité : Développeur .NET

    Informations forums :
    Inscription : Février 2004
    Messages : 520
    Points : 439
    Points
    439
    Par défaut Optimiser une procédure stockée
    Bonjour tout le monde,

    Je cherche une solution pour optimiser une procédure stockée car elle prend trop de temps (3-4 secondes).

    Cette procédure stockée attaque 2 tables avec un index column store.

    Est-ce que quelqu'un aurait des astuces ou conseils pour améliorer la rapidité ?

    Voici le code :

    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
     
    CREATE PROCEDURE [dbo].[GetOperationResultsTotalGoodBadLast]
    	@SelectionGroupID int = 3469, 
    	@OperationType int = 0,
    	@CaliberID int = NULL,
    	@IcID int = NULL,
    	@ModelID int = NULL,
    	@ProgramNameID int = NULL,
    	@TestID int = NULL,
    	@ProgramID int = NULL,
    	@RecipeID int = NULL,
    	@FactoryID int = NULL,
    	@DeviceGroupID int = NULL,
    	@DeviceID int = NULL,
    	@OrderNumber nvarchar(25) = NULL,
    	@OrderOperation nvarchar(5) = NULL,
    	@DateStart datetime2(7) = '1900.01.01',
    	@DateEnd datetime2(7) = '2999.01.01'
     
    AS
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    BEGIN
     
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
     
     
     
    	-- 
    	-- Create tempory table
    	-- 
    	CREATE TABLE #OperationResultTmp (
    		 [PieceUID] bigint,
    		 [DateStart] datetime2(7)
    	)
     
     
     
    	-- 
    	-- Request
    	-- 
    	INSERT INTO #OperationResultTmp ([PieceUID], [DateStart])
    	SELECT [o].[PieceUID], 
    		   MAX([o].[DateStart]) AS [DateStart]
     
    	FROM [dbo].[OperationResults] [o]
    	INNER JOIN [dbo].[Pieces] [p] ON [o].[PieceUID] = [p].[PieceUID]
     
    	WHERE ([p].[SelectionGroupID] = @SelectionGroupID) AND
    			([p].[Deleted] = 0) AND
    			([o].[Type] = @OperationType) AND
    			(@CaliberID IS NULL OR [o].[SelectionOR10] = @CaliberID) AND
    			(@IcID IS NULL OR [o].[SelectionOR11] = @IcID) AND
    			(@ModelID IS NULL OR [o].[SelectionOR12] = @ModelID) AND
    			(@ProgramNameID IS NULL OR [o].[SelectionOR01] = @ProgramNameID) AND
    			(@TestID IS NULL OR [o].[SelectionOR02] = @TestID) AND
    			(@ProgramID IS NULL OR [o].[SelectionOR03] = @ProgramID) AND
    			(@RecipeID IS NULL OR [o].[SelectionOR04] = @RecipeID) AND
    			(@FactoryID IS NULL OR [o].[SelectionOR07] = @FactoryID) AND
    			(@DeviceGroupID IS NULL OR [o].[SelectionOR08] = @DeviceGroupID) AND
    			(@DeviceID IS NULL OR [o].[SelectionOR09] = @DeviceID) AND
    			(@OrderNumber IS NULL OR [o].[OperationInfoID] IN (SELECT [o1].[OperationInfoID] FROM dbo.OperationInfos [o1] WHERE [o1].[OrderNumber] LIKE @OrderNumber AND (@OrderOperation IS NULL OR [o1].[OrderOperation] LIKE @OrderOperation))) AND
    			(DATEDIFF(minute, @DateStart, [o].[DateStart])) >= 0 AND
    			(DATEDIFF(minute, @DateEnd, [o].[DateStart])) <= 0
     
    	GROUP BY [o].[PieceUID]
    	ORDER BY [o].[PieceUID]
     
     
     
    	-- 
    	-- Select and return results
    	-- 
    	SELECT COUNT(*) AS [Total],
    	            SUM(CASE WHEN [o].[Flag] = 0 THEN 1 ELSE 0 END) AS [Good]
     
    	FROM [dbo].[OperationResults] [o]
    	INNER JOIN #OperationResultTmp [p] ON [o].[PieceUID] = [p].[PieceUID] AND [o].DateStart = [p].DateStart
     
    END
    GO
    Merci d'avance pour votre aide.

  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 736
    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 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    À partir du moment ou vous mettez ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    (@ProgramNameID IS NULL OR [o].[SelectionOR01] = @ProgramNameID)
    ...dans votre clause WHERE, vous aurez beau mettre tous les index de la terre, la condition n'étant par cherchable, aucun index ne sera utilisé.
    Rectifiez avec :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    [o].[SelectionOR01] = COALESCE(@ProgramNameID, [o].[SelectionOR01])
    Si la colonne n'est pas NULLable.

    Lisez l'article que j'ai écrit sur la cherchabilité des prédicats :
    https://blog.developpez.com/sqlpro/p...sql_sargable_c

    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
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Bonjour,


    Cette condition n'est pas cherchable :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    (DATEDIFF(minute, @DateStart, [o].[DateStart])) >= 0 AND
    			(DATEDIFF(minute, @DateEnd, [o].[DateStart])) <= 0
    réecrivez la ainsi :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    [o].[DateStart] >= @DateStart
    AND [o].[DateStart] <= @DateStart

    Par ailleurs, pourquoi passer par une table temporaire ?

    Enfin, si la procédure n'est pas appelée trop souvent, et comme il y a beaucoup de paramètres variables, vous pouvez tenter l'utilisation de l'indicateur RECOMPILE, ou de fournir des paramètres types (OPTIMIZE FOR...) afin de générer un plan de requête plus adapté.

  4. #4
    Membre averti Avatar de soad
    Homme Profil pro
    Développeur .NET
    Inscrit en
    Février 2004
    Messages
    520
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 37
    Localisation : Suisse

    Informations professionnelles :
    Activité : Développeur .NET

    Informations forums :
    Inscription : Février 2004
    Messages : 520
    Points : 439
    Points
    439
    Par défaut
    Extra !!! Merci beaucoup à vous deux pour vos conseils...

    Je suis passé à 200-300ms au lieu de 3-4 sec. Et j'ai réduis la charge de mon serveur de 55% à 15% !


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

Discussions similaires

  1. Réponses: 8
    Dernier message: 10/04/2014, 17h28
  2. optimiser une procédure stockée
    Par ed222 dans le forum Développement
    Réponses: 8
    Dernier message: 15/06/2010, 18h30
  3. [Transact-SQL] Optimisation d'une procédure stockée
    Par Shinn77 dans le forum MS SQL Server
    Réponses: 7
    Dernier message: 25/06/2007, 13h30
  4. [SQL2005] Optimiser une procédure stockée
    Par david_chardonnet dans le forum MS SQL Server
    Réponses: 1
    Dernier message: 20/12/2006, 16h48
  5. Réponses: 5
    Dernier message: 09/05/2005, 13h24

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