ORDER BY qui plombe une requête de 4k lignes [Résolu]

[Pol63]

bonjour

j'ai une requete qui retourne 4200 lignes
celle ci mettant 2'30" à s'exécuter j'ai regardé le plan d'exécution, et il en retourne que l'order by prend 85% du temps
en effet si je retire l'order by la requête s'exécute en moins d'une seconde

cette requête est devenue longue suite à l'ajout de cette colonne :

Code :

1
2
3
4
5
COALESCE(  MontantForce,
             SUM(DevisVersionCoeffChapitres.TotalBrut) + MargeForcee,  
             SUM(DevisVersionCoeffChapitres.TotalBrut) / (1 - PctMargeForcee / 100), 
             SUM(DevisVersionCoeffChapitres.TotalNet)
           ) AS PrixVente

donc avec cette colonne et l'order by => + de 2 minutes
avec la colonne sans l'order by OU sans la colonne et avec l'order by => moins d'une seconde


pourquoi ce comportement ? où sql server se fait avoir ? comment améliorer le temps ?
l'order by et bien à la fin du plan d'exécution, donc je vois pas quoi ca pourrait le perturber ...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
FROM DevisEntete 
        INNER JOIN Tiers Site   ON DevisEntete.IdSite = Site.IdTiers
        INNER JOIN Tiers ChargeAffaire   ON DevisEntete.ChargeAffaire = ChargeAffaire.IdTiers
        INNER JOIN DevisCommerce   ON DevisEntete.IdCommerce = DevisCommerce.IdDevisCommerce
        INNER JOIN DevisSecteur   ON DevisEntete.IdSecteur = DevisSecteur.IdDevisSecteur
        INNER JOIN DevisStatut   ON DevisEntete.IdStatut = DevisStatut.IdDevisStatut
        INNER JOIN DevisActivite   ON DevisEntete.IdActivite = DevisActivite.IdDevisActivite
        LEFT JOIN Tiers Client   ON Site.TiersLie = Client.IdTiers
        LEFT JOIN Tiers MaitreDOeuvre   ON DevisEntete.MaitreDOeuvre = MaitreDOeuvre.IdTiers
        LEFT JOIN DevisVersion   ON DevisEntete.IdDevis = DevisVersion.IdDevis AND DevisVersion.IsActive = 1
        LEFT JOIN DevisVersionCoeffChapitres   ON DevisVersionCoeffChapitres.IdDevisVersion = DevisVersion.IdDevisVersion
 
 
WHERE (DevisEntete.IdStatut <> 7 OR @suppr = 1)
GROUP BY DevisEntete.IdDevis, DevisEntete.NumDevis, DevisEntete.IdSite, Client.IdTiers, Client.Designation, Site.IdTiers, Site.Designation, DevisEntete.Description, DevisEntete.ChargeAffaire, ChargeAffaire.IdTiers, ChargeAffaire.Designation + ' ' + ChargeAffaire.Code, DevisEntete.DateRealisationTheo, DevisStatut.IdDevisStatut, DevisStatut.LibStatut, DevisActivite.IdDevisActivite, DevisActivite.CodeActivite, DevisActivite.LibActivite, DevisCommerce.IdDevisCommerce, DevisCommerce.LibCommerce, DevisSecteur.IdDevisSecteur, DevisSecteur.CodeSecteur, DevisSecteur.LibSecteur, DevisEntete.MaitreDOeuvre, MaitreDOeuvre.IdTiers, MaitreDOeuvre.Designation,  DevisEntete.IdCommerce, DevisEntete.IdSecteur, DevisEntete.IdActivite, DevisEntete.IdStatut,
DevisStatut.Couleur, DevisEntete.Commentaire, MontantForce, MargeForcee, PctMargeForcee

merci

[zinzineti]

Après l'ajout de la nouvelle colonne, avez-vous mis à jour les statistiques (UPDATE STATISTICS ....) ?
Si non, réessayer après la mise à jour des statistiques.
SQL SERVER utilises les statistiques pour décider des algorithmes à utiliser pour générer le plan d'exécution.

Donc si vous modifier le modèle de données (création de nouvelles colonnes, création d'index, .....) alors il faut mettre à jour les statistiques afin d'aider le moteur SQL SERVER à une prise de décision intelligente.

[Pol63]

je me suis mal exprimé, l'ajout de colonne n'est pas sur la structure, mais sur la requête, avant la requête n'affichait pas cette colonne calculée, j'ai rajouté ce coalesce dans le select et j'ai vu le délai
au début j'ai pensé que c'était cette expression qui plombait le résultat, puis j'ai recherché sur le plan d'exécution pour voir que ce n'est pas le cas mais la combinaison de ce calcul + order by

[mikedavem]

Possibilité d'avoir les 2 plans d'exécution de requêtes et les statistiques d'exécutions via SET STATISTICS TIME ON et SET STATISTICS IO ON ?

++

[Pol63]

sql server 2005 sp3 standard x64

[elsuket]

Bonjour,

Il semble que vous ayez un léger problème d'estimation de cardinalités dans les deux cas.
La requête prend du temps à cause du spool : 500.000+ IOs sur une table de travail, ça veut souvent dire qu'il manque un index pour aider la requête.

Quelle est la définition de la table DevisVersionCoeffChapitre, avec ses index ?

@++

[Pol63]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE [dbo].[DevisVersionCoeffChapitres](
    [IdDevisVersionCoeff] [bigint] IDENTITY(1,1) NOT NULL,
    [IdDevisVersion] [bigint] NOT NULL,
    [IdChapitre] [bigint] NOT NULL,
    [IdLigneBud] [bigint] NOT NULL,
    [TotalBrut] [decimal](19, 5) NOT NULL,
    [Coeff] [decimal](3, 2) NOT NULL,
    [TotalNet]  AS (case when [coeff]=(0) then NULL else [TotalBrut]/[Coeff] end) PERSISTED,
 CONSTRAINT [PK_DevisVersionCoeffChapitres] PRIMARY KEY CLUSTERED 
(
    [IdDevisVersionCoeff] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

2 indexes :
- IdLigneBud
- IdDevisVersion, IdLigneBud, IdChapitre


et j'ai refait un sp_updatestats 'RESAMPLE' pas longtemps avant de fournir les plans d'exécution ...

il me semble logique de mon point de vue que faire un order by ca se fait à la fin et que ca prend un court laps de temps en ram, donc je ne vois pas pourquoi sql server patinerait à faire autre chose quand je lui demande un order by ^^

[elsuket]

La requête longue parallélise, pas la courte ...
Si quand la longue s'exécute vous avez un wait_time long avec le wait_type CX_PACKET, c'est que c'est la parallélisation qui pose problème.
Donc soit vos statistiques ne sont pas à jour (je ne sais plus pourquoi je n'utilise plus sp_updatestats maintenant ... j'utilise UPDATE STATISTICS), soit votre table est fragmentée
SQL Server est donc soit perdu dans son calcul d'équilibrage de la charge de travail des threads, ou attend qu'un thread ait terminé parce qu'il a mal estimé ou que la table est fragmentée.

Aussi, comme TotalBrut n'est pas indexée, et que vous faites un groupement dessus, cela n'aide pas. Donc SQL Server n'a pas d'autre choix que de faire cela dans une table temporaire dans TempDB, et c'est cela qui plombe vos performances.

@++

[SQLpro]

Exécutez la avec une OPTION(MAXDOP 1) pour voir...

A +

[Pol63]

la table DevisVersionCoeffChapitres est fragmentée à 0.9%
msdn disait il me semble que sp_updatestats appelle update statistics

le option maxdop a l'air bon, 10x de suite 2 secondes, on va faire avec

merci bien à tous

[Pol63]

re,

en cherchant des infos sur le maxdop j'ai trouvé des gens qui faisaient un force order
je viens de tester, le force order sans le maxdop est encore mieux, je suis en dessous d'une seconde