Discussion :

[olibara]

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]

utilisez le profiler SQL pour mesurer.

A +

[olibara]

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]

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]

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]

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 +

[olibara]

Merci de tes Conseils SQL Pro


Et j'apprends des choses :

1- J'etais persuadé que le subselect dans le cas d'un insert unique et dans une procedure stockée serait plus performant
2- Je pensais aussi que le Return serait plus efficace mais je me suis clairement trompé
3- Pour la mention du Schema tu a tout a fait raison, c'est une paresse de ma part

4- Je vais me documenter a propos de Datetime2

Voici mes DDL (extrait avec NAVICAT qui est mieux que SSMS pour la cause)

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
-- ----------------------------
-- Table structure for GPSDevice
-- ----------------------------
DROP TABLE [dbo].[GPSDevice]
GO
CREATE TABLE [dbo].[GPSDevice] (
[pkId] int NOT NULL IDENTITY(1,1) ,
[DeviceSN] varchar(32) NOT NULL ,
[DeviceName] varchar(50) NULL ,
[Comment] varchar(200) NULL ,
[M2MCardID] int NULL 
)
 
 
GO
DBCC CHECKIDENT(N'[dbo].[GPSDevice]', RESEED, 59)
GO
 
-- ----------------------------
-- Indexes structure for table GPSDevice
-- ----------------------------
CREATE UNIQUE INDEX [IX_UniqueM2M] ON [dbo].[GPSDevice]
([M2MCardID] ASC) 
WHERE ([M2MCardID] IS NOT NULL)
WITH (IGNORE_DUP_KEY = ON)
GO
CREATE UNIQUE INDEX [IX_DeviceSN] ON [dbo].[GPSDevice]
([DeviceSN] ASC) 
WITH (IGNORE_DUP_KEY = ON)
GO
 
-- ----------------------------
-- Primary Key structure for table GPSDevice
-- ----------------------------
ALTER TABLE [dbo].[GPSDevice] ADD PRIMARY KEY ([pkId])
GO
 
 
-- ----------------------------
-- Table structure for Vehicle
-- ----------------------------
DROP TABLE [dbo].[Vehicle]
GO
CREATE TABLE [dbo].[Vehicle] (
[pkId] int NOT NULL IDENTITY(1,1) ,
[VehicleNum] int NULL ,
[DeviceID] int NULL ,
[Matriculation] varchar(50) NOT NULL ,
[ChauffeurPkID] int NULL ,
[Model] varchar(200) NULL ,
[TraceColor] int NULL ,
[M3] decimal(8,2) NULL ,
[MTM] int NULL ,
[Chassis] varchar(32) NULL ,
[Motor] varchar(32) NULL ,
[Brand] varchar(32) NULL ,
[Olie] varchar(16) NULL ,
[Euronorm] varchar(24) NULL ,
[DateConstruction] datetime NULL ,
[DateBatterie] datetime NULL ,
[DateNextKeuring] datetime NULL ,
[TotKm] int NULL ,
[KmNextMaintenance] int NULL ,
[nbKeys] int NULL ,
[DateEurovignet] datetime NULL ,
[DateAssurance] datetime NULL ,
[DateTax] datetime NULL ,
[TankKaartRef] varchar(50) NULL ,
[TankKaartCode] varchar(50) NULL 
)
 
 
GO
DBCC CHECKIDENT(N'[dbo].[Vehicle]', RESEED, 51)
GO
 
-- ----------------------------
-- Indexes structure for table Vehicle
-- ----------------------------
CREATE UNIQUE INDEX [IX_DeviceID] ON [dbo].[Vehicle]
([DeviceID] ASC) 
WHERE ([deviceId]>=(0))
WITH (IGNORE_DUP_KEY = ON)
GO
CREATE INDEX [IX_ChauffeurID] ON [dbo].[Vehicle]
([ChauffeurPkID] ASC) 
GO

--

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
----------------------------
-- Primary Key structure for table Vehicle
-- ----------------------------
ALTER TABLE [dbo].[Vehicle] ADD PRIMARY KEY ([pkId])
GO
 
-- ----------------------------
-- Triggers structure for table Vehicle
-- ----------------------------
DROP TRIGGER [dbo].[CamChaufChanged]
GO
CREATE TRIGGER [dbo].[CamChaufChanged]
ON [dbo].[Vehicle]
AFTER INSERT, UPDATE, DELETE
AS
 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	BEGIN 
UPDATE [dbo].[Event]
 SET Date=GETDATE()
 WHERE event='CAMION'
	END
 
END
 
GO
 
-- ----------------------------
-- Foreign Key structure for table [dbo].[Vehicle]
-- ----------------------------
ALTER TABLE [dbo].[Vehicle] ADD FOREIGN KEY ([pkId]) REFERENCES [dbo].[Vehicle] ([pkId]) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

[SQLpro]

Votre déclencheur me parait totalement foiruex : il met à jour à chaque INSERT, UPDATE et DELETE toutes les lignes de la table (sans aucune exception) dont la colonne "event" = 'CAMION'...

A mon avis :
1) seule les lignes en cours de mise à jour devrait être modifiées
2) dans le cas de la suppression, ceci me parait totalement inutile !

Donc, récrit :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TRIGGER [dbo].[CamChaufChanged]
ON [dbo].[Vehicle]
AFTER INSERT, UPDATE
AS
BEGIN
	SET NOCOUNT ON;
   UPDATE T
   SET Date = GETDATE()
   FROM [dbo].[Event] AS T
        INNER JOIN inserted AS i
              ON T.???lacClef??? = i.???lacClef???
   WHERE event = 'CAMION';
END
GO

--> remplacer ???LaClef??? par la colonne PK

D'autre part, appeler des colonnes par un nom réservé de SQL est une bêtise. Exemple : Date, Event... Vous allez au devant d'emmerdes !

A +

[olibara]

Bonjour SQL Pro

Je pensais que tu t'étais trompé de sujet mais je réalise que NAVICAT fait tellement bien les choses que en extrayant le DDL d'une table il extrait aussi les trigger, ce qui était hors propos ici

Cela etant, un petit mot d'explication pour expliquer la pertinence de ce trigger qui est en réalité tres efficace !!

J'ai une application cSharp qui peut afficher (entre autre) une fiche d'affectation de camions et chauffeur
Plusieurs utilisateur consultent cette fiche sont suceptible de changer les affectations

pour ne pas surcharger les requetes, l'application consulte toutes les 30 secondes une petite table EVENT qui permet de determiner si un rafraichissement de la vue est nécessaire

Donc la table EVENT ne contient qu'un et un seul enregistrement par type d'event (definis pour l'application) et la date de derniere modification

[SQLpro]

Il serait beaucoup plus judicieux sans doute de créer une vue indexée plutôt que cette usine à gaz !
En effet :
1) moins de calculs et d'appel
2) toujours synchrone et disponible

A +

[olibara]

Bonjour SQL Pro

Je ne suis pas tout a fait d'accord avec toi
L'aspect mecanique DB est une chose
L'aspect logique metier ou exploitation en est une autre

Et je trouve d'ailleurs que trop souvent les DB Admin oublient ce second aspect (mais c'est un autre débat)
(Rassure toi l'inverse est vrai aussi, les développeurs négligent aussi souvent l'aspect Design DB)

Dans le cas present il est fort utile de pousoir disposer de l'information que quelque chose a été modifié dans une table

Cela n'exclut pas l'intéret d'une vue indexée je te l'accorde
Mais celle ci n'exclut pas non plus l'intérêt de cette information !

[iberserk]

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

[StringBuilder]

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]

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]

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]

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

[olibara]

Re-Bonjour

Je reviens sur le sujet pour invalider une declaration péremptoire de SQL Pro qui m'a fait faire une GROSSE betise
Comme quoi il faut toujours reflechir a une situation avant de vouloir imposer un predicat d'expert !

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

Mon code original etait le suivant :

Code Olibara :

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
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
 
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)
              )

SqlPro m'a fait ecrire ceci : (ci-dessous)
Mais si par malheur la trace reçue provient d'un GPS qui n'est pas encore enregistré dans la DB (situation du monde réel tout a fait possible et inévitable) le résultat est qu'on n'enregistrera rien dans la DB et ca c'est inadmissible !
Donc la seul solution simple qui peut etre confrontée au monde réel est la mienne ou alors il faut préalablement faire un test d'existence du GPS et ecrire deux methode distincte, mais je n'y vois aucun intérêt !

Code SQLPro :

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

[iberserk]

Vous confondez...
SQL PRO ne vous a fait qu'une proposition pour utiliser une insertion propre c'est a dire un INSERT INTO XXX SELECT XXX
VOus avez simplement à changer le INNER JOIN en LEFT OUTER JOIN, sans remettre votre requète initiale à base d'INSERT INTO XXX VALUES avec un sous select

[olibara]

VOus avez simplement à changer le INNER JOIN en LEFT OUTER JOIN, sans remettre votre requète initiale à base d'INSERT INTO XXX VALUES avec un sous select .

Merci Ibersek

Mais tu te trompe !
Car :
1- Memme avec un LEFT OUTER JOIN Le WHERE de la suggestion de SQLPro va de toute façon filtrer le résultat si l'IMEI n'est pas connu : donc aucune action
2- En admettant que j'inclue ce where dans une deuxieme condition de l' OuterJoin je vais recuperer a chaque coup l'ensemble des véhicules, donc c'est foireux !

Comme je le disait il faut etre prudent avant de faire une contre-proposition a quelque chose de fonctionnel
Le mieux est souvent l'ennemi du bien !

Cela etant je reste ouvert a une proposition meilleure que la mienne, sachant que la mienne contrairement au autre a l'avantage pour le moment de faire le travail comme on l'attends :-)

[iberserk]

Dans le cas du INNER JOIN, le prédicat du WHERE élimine en effetl'instertion, mais dans le cas d'un LEFT OUTER JOIN, il vous suffit de le mettre au niveau de la jointure (ON ...) et vous obtenez le résultat escompté.