Mesure de performance

**olibara** · 19/08/2014, 22h48

Bonjour
Sur un serveuer, je fais tourner un service cSharp dont la tache est d'ecouter un flux d'entrée provenant de Traceurs GPS / GPRS de traiter l'information et de sauver en BD

toutes les 500ms +/-, j'insere un record dans une table de 6 colones

J'essaye d'optimiser au maximum mais je n'ai rien pour evaluer la charge necessaire a l'insert en DB
J'ai déja modifié le traitement pour faire l'insert a partir d'une procedure stockée a laquelle je passe les parametres plutot que de creer la commande d'insert en cSharp

Je n'ai malheureusement aucune idée de comment je pourrais mesurer le gain
Accessoirement j'aimerais ajouter a mon insert un Id resultat d'une requere basée sur les parametres donnés mais j'ai peur que cela alourdise fortement et j'aimerais pouvoir le mesuer

Aves vous des conseils ou suggestion pour mesurer le service proprement dit ?

Merci de votre aide

**SQLpro** · 20/08/2014, 09h55

utilisez le profiler SQL pour mesurer.

A +

**olibara** · 21/08/2014, 06h50

Merci SQL Pro

Le serveur que je veux monitorer a une version xPress
Mais j'ai trouvé le express Profiler sur Codeplex qui semble deja fort utile

**SQLpro** · 21/08/2014, 11h44

Tu peut installer une version Enterprise à l'essai pendant 180 jours et utiliser le profiler SQL de cette version pour monitorer l'instance Express. Tu peut aussi utiliser une quelconque instance dans ton parc, pourvue d'un profiler et accéder en distant (par exemple un profiler d'une instance SQL Developper).

A +

**olibara** · 23/08/2014, 10h02

Merci SQL Pro

Pour etre plus concret j'essaye d'optimiser la procédure suivante

Comme l'action s'execute +/- toutes les 500ms je l'ai mis en procedure stockée pour ne passer que les parametres plutot que le passage de toute la commande en ADO

La partie qui me chiffone encore, c'est la recuperation d'un ID via mon petit Select

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
	(SELECT ChauffeurPkid 
               from vehicle V
               INNER JOIN GPSDevice GPS ON GPS.pkid=V.DeviceID
               where GPS.DeviceSN=@imei)
              )

Sachant que les deux tables concernées sont tres petites < 100 records et rarement mise a jour : n'existe-t-il pas un moyen d'optimiser le Lookup sur ces tables

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
 
/****** Object:  StoredProcedure [dbo].[sp_InsertTrace]    Script Date: 22/08/2014 18:45:05 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
ALTER PROCEDURE [dbo].[sp_InsertTrace]
  	@latitude float,
             @longitude float,
             @Date DateTime,
	@speed float,
	@imei varchar(12)
 
--	@ID AS INT OUTPUT
	AS
BEGIN
 
SET NOCOUNT ON
 
DECLARE @CHpkID int;
/*
SELECT @CHpkID=	(SELECT ChauffeurPkid 
from vehicle V
INNER JOIN GPSDevice GPS ON GPS.pkid=V.DeviceID
where GPS.DeviceSN=@imei)
*/
INSERT INTO GPSTRACE
(
  latitude,
  longitude,
  date,
  speed,
  imei,
  Tour
)
VALUES
(
  	@latitude,
             @longitude,
             @Date,
	@speed,
	@imei,
	(SELECT ChauffeurPkid 
               from vehicle V
               INNER JOIN GPSDevice GPS ON GPS.pkid=V.DeviceID
               where GPS.DeviceSN=@imei)
              )
 
--SET @ID = (SELECT SCOPE_IDENTITY())
 
RETURN (SELECT SCOPE_IDENTITY())
 
END
GO

**SQLpro** · 23/08/2014, 11h49

3 horreurs dans cette procédure :
1) on utilise pas un INSERT VALUES avec une sous requête. On utilise une sous requête d'insertion
2) on utilise JAMAIS RETURN pour renvoyer explicitement une valeur, car RETURN renvoi le code d'erreur !On passe un argument en OUTPU ou bien on fait un SELECT final
3) on préfixe les objets par leur schéma, même si c'est toujours dbo
Ajoutons accessoirement qu'un type GEOGRAPHY aurait été plus judicieux que d'utiliser deus FLOAT pour la longitude et la latitude !
Enfin si tu est en version 2008 ou supérieure, utilise le type DATETIME2. Le type DATETIME est "deprecated"

Enfin, si tu veut plus d'aide, donne le DDL des tables et de tous les index des deux tables.

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
ALTER PROCEDURE [dbo].[sp_InsertTrace]
   @latitude float,
   @longitude float,
   @Date DateTime,
   @speed float,
   @imei varchar(12)
   @ID AS INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @CHpkID int;
 
INSERT INTO dbo.GPSTRACE
(
  latitude,
  longitude,
  date,
  speed,
  imei,
  Tour
)
SELECT 
   @latitude,
   @longitude,
   @Date,
   @speed,
   @imei,
   ChauffeurPkid 
FROM dbo.vehicle AS V
     INNER JOIN dbo.GPSDevice AS GPS 
            ON GPS.pkid = V.DeviceID
WHERE GPS.DeviceSN = @imei)
 
SET @ID = SCOPE_IDENTITY();
 
END
GO

A +

**iberserk** · 26/08/2014, 09h24

Au passage il existe un profiler EXPRESS téléchargeable qui tient la route...

**StringBuilder** · 26/08/2014, 10h02

Bonjour,

Quelques petites remarques :

1/

Vous faite une insertion toutes les 0,5 secondes, soit deux par seconde.

Il y a 31 536 000 secondes dans une année, c'est à dire que vous allez insérer environ 63 000 000 lignes par an.

Même pour la version Express de SQL Server, c'est "rien du tout" (quand vous aurez 1000 fois plus de lignes, vous pourrez commencer à vous poser des questions de performances).

En attendant, suivez simplement les recommandations "classiques" de modélisation et d'écriture des requêtes, cela sera parfaitement suffisant.

2/

Pour récupérer l'ID créé lors d'une requête INSERT, il existe aussi la commande "OUTPUT" de INSERT :

http://msdn.microsoft.com/fr-fr/library/ms177564.aspx

Ceci est bien mieux dans la mesure où SCOPE_IDENTITY() est limité à à la dernière valeur identity insérée, là où OUTPUT permet de récupérer une table virtuelle (inserted) contenant l'ensemble de toutes les lignes insérées.

Ceci permettra éventuellement de bufferiser vos insertions pour non pas exécuter un INSERT toutes les 0,5 secondes, mais un lot de 60 lignes toutes les 30 secondes par exemple, ce qui correspond à l'interval de consultation (ça évite d'avoir des locks inutiles et incessant qui pourrissent ensuite les requêtes de sélection) mais aussi de maintenance (recalcul des index par exemple).

**olibara** · 27/08/2014, 08h20

Bonjour

Je reviens sur la modification proposée par SQLPro
Ci celle ci est peut etre plus "propre" en terme de design, le résultat ne sera pas le meme
Le INNER JOIN aura pour effet de filtrer les insertions dans le cas ou un Chauffeur n'a pas été asssigné a un camion

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
ALTER PROCEDURE [dbo].[sp_InsertTrace]
   @latitude float,
   @longitude float,
   @Date DateTime,
   @speed float,
   @imei varchar(12)
   @ID AS INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @CHpkID int;
 
INSERT INTO dbo.GPSTRACE
(
  latitude,
  longitude,
  date,
  speed,
  imei,
  Tour
)
SELECT 
   @latitude,
   @longitude,
   @Date,
   @speed,
   @imei,
   ChauffeurPkid 
FROM dbo.vehicle AS V
     INNER JOIN dbo.GPSDevice AS GPS 
            ON GPS.pkid = V.DeviceID
WHERE GPS.DeviceSN = @imei)
 
SET @ID = SCOPE_IDENTITY();
 
END
GO

**aieeeuuuuu** · 27/08/2014, 11h40

Envoyé par olibara

dans le cas ou un Chauffeur n'a pas été asssigné a un camion

Des google trucks ???

Dans ce cas, vous pouvez faire une jointure externe

**olibara** · 27/08/2014, 13h05

Tout a fait
Et c'est ce que j'ai fait en modifiant un peu la proposition de SQLPro

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 SELECT
    @latitude,
    @longitude,
    @Date,
    @speed,
    @imei,
    V.ChauffeurPkid 
    FROM dbo.GPSDevice AS GPS
    LEFT OUTER JOIN dbo.Vehicle AS V ON GPS.pkid = V.DeviceID 
    WHERE GPS.DeviceSN = @Imei

**iberserk** · 14/09/2014, 14h58

D'un autres côté vous estimez que même si le GPS n'est pas connu il convient d'ajouter la ligne, soit mais vous perdez alors la source de l'information (nom du GPS) pourquoi ne pas automatiser l'ajout préalable du GPS inconnu dans la table des GPS avant de faire un insert avec INNER JOIN?

J'ai ce même mécanisme sur une table de trace des actions sur un site d'EBUSINESS avec l'information navigateur+version navigateur qui peut de la même manière ne pas être connu.

Pour éviter NULL j'ai donc un mécanisme ajoutant tout navigateur actuellement inconnu dans la base de données.

Message personnel: COME ON VALE ROSSI!

**iberserk** · 16/09/2014, 10h53

Que tonnes ceci?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
  	@latitude,
    @longitude,
    @Date,
	@speed,
	@imei,
    V.ChauffeurPkid,
    @IOSTAT,
	V.deviceID,
COUNT(*)
    FROM dbo.Vehicle V  
    LEFT OUTER JOIN dbo.GPSDevice AS GPS ON GPS.pkid = V.DeviceID AND GPS.DeviceSN @Imei
  GROUP BY V.deviceID

**aieeeuuuuu** · 16/09/2014, 11h04

Envoyé par iberserk

Que tonnes ceci?

A priori, je dirai autant de null qu'il y a de lignes dans la table véhicule puisque l'imei n'existe pas dans la table GPS...

**hmira** · 16/09/2014, 13h46

D'après moi, une requête qui serait, en terme d'algébrisation, équivalente à la requête initiale fournie par olibara, et qui donnerait exactement les mêmes résultats serait la suivante :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT A.prm_latitude, A.prm_longitude, A.prm_Date, A.prm_speed, A.prm_imei, B.ChauffeurPkid        
FROM (  SELECT @latitude AS prm_latitude,
               @longitude AS prm_longitude,
               @Date AS prm_Date,
               @speed As prm_speed,
	           @imei  AS prm_imei
	   )  A  		 		  
OUTER APPLY  ( SELECT TOP(1) V.ChauffeurPkid 
               FROM vehicle V
               INNER JOIN GPSDevice GPS 
		ON GPS.pkid=V.DeviceID 
	      WHERE GPS.DeviceSN=A.prm_imei  ) B

PS : N'ayant pas de données significatives dans les tables afférentes, je ne peux en mesurer la pertinence. Même sur des tables vides, j'ai constaté, néanmoins une légère différence dans les plans d'exécution des 2 requêtes (requête Initiale d'olibara et la requête ci-dessus). Les 2 plans sont presques identiques, ils présentent toutefois des différences dans la préséance des opérations (Table Scan, Index Seek, Constant Scan, etc.).

A+

**iberserk** · 16/09/2014, 16h57

A priori, je dirai autant de null qu'il y a de lignes dans la table véhicule puisque l'imei n'existe pas dans la table GPS...

oups je voulais mettre le prédicate dans le WHERE pas dans le ON

**hmira** · 16/09/2014, 18h07

Envoyé par iberserk

oups je voulais mettre le prédicate dans le WHERE pas dans le ON

Si vous déplacez le prédicat vers le WHERE, votre requête ne serait plus équivalente à la requête initiale de olibara et donc ne retournera pas forcément les mêmes résultats.

En effet, dès lors que la clause WHERE n'est vérifiée, votre requête ne ramènera aucun enregistrement et donc aucune lignes ne sera insérée dans la table GPSTRACE, contrairement à la requête initiale qui elle insère systématiquement une ligne quitte à avoir la colonne Tour à NULL.

Donc votre requête n'est pas algébriquement équivalente à la requête initiale. En d'autres termes, elle ne générera pas le même arbre algébrique, et donc ne produira pas les mêmes résultats !

A+

**olibara** · 16/09/2014, 18h31

Merci a tous

Merci pour tes remarques pertinentes Hmira tu a tout a fait raison
A choisir, ma requete initiale a l'avantage d'etre simple et finalement pas tres gourmande en ressource

Merci Ibersek d'avoir essayé de resoudre la quadrature du cercle mais on revient au meme point

En ce qui concerne l'enregistrement de trace liées a rien, ce n'est pas tout a fait vrai, j'enregistre toujours le numero unique du GPS (IMEI) qui sert de clef, et ce numero permettra dounc toujours a l'aplication de visualiser la trace liée a ce GPS et le cas ecéant lier cette clef aux information manquantes qui peuvent etre liée apres coup

Et dans la réalité il peut y avoir de situation ou le Numero du GPS ne sera révellé que lors de l'apparition d'une trace !

**iberserk** · 16/09/2014, 20h57

En effet, dès lors que la clause WHERE n'est vérifiée, votre requête ne ramènera aucun enregistrement et donc aucune lignes ne sera insérée dans la table GPSTRACE, contrairement à la requête initiale qui elle insère systématiquement une ligne quitte à avoir la colonne Tour à NULL.

JE ne peux qu'admettre mon erreur... voilà ce qui se passe quand on réponds à la hates et sans vérifier sous ce bon vieux SSMS

Mesure de performance

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager