Discussion :

[olibara]

Bonjour
J'aimerais dans une procedure stockée parcourir le résultat d'un select

Le select de base :

Code :

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

SELECT DISTINCT Code FROM Results

Ensuite pour chaque Code dans le resultat de mon select j'aimerais faire un TOP

Dans le genre :

Code :

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

1
2
    SELECT TOP 10 * FROM  Result WHERE Code=@UnCode 
    ORDER BY Date DESC

Bien sur je peux faire ca en programmation a coup de requete ADO depuis cSharp mais ca m'intéresserait le le maitriser en SQL

Je suis asser novice en SP, je demande un peu de compréhension pour mon ignorance et toute suggestion est bienvenue

Merci

[SQLpro]

Code :

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

1
2
3
4
5
6
WITH T0 AS (
SELECT Code, ROW_NUMBER() OVER (PARTITION BY Method ORDER BY "Date" DESC) AS N
FROM   Result)
SELECT Code
FROM   T0
WHERE  NCHAR <= 10;

avec le bon index :

Code :

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

CREATE INDEX XXX ON Result ("Date", Method) INCLUDE (Code);

A +

[olibara]

Merci SQL Pro

Mais avec ou sans index, ce n'est pas asser performant sur une table de plusieurs millions de record

Il me semble par contre que le SELECT TOP 10 * WHERE SerialNR='xxxxx' ORDER BY DATE DESC est beaucoup plus rapide ! (et ca me semble logique)

Donc je vais m'orienter sur la solution Requete en boucle depuis cSharp et traitement des resultats intermédiares

[mikedavem]

Et avec cet index ?

CREATE INDEX XXX ON Result (Method, [Date] DESC) INCLUDE (Code);

Est-ce que la CTE est toujours moins rapide ?

Tu peux poster le plan d'exécution ?

++

[olibara]

Merci MikeDavem

Je pense avoir l'index qu'il faut sur la table

Code :

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

1
2
3
4
5
CREATE NONCLUSTERED INDEX [IX_ImeiDate] ON [dbo].[GPSTrace] 
(
    [imei] ASC,
    [date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Et voici l'execution plan (joint) : j'ai renommé car le forum n'aime pas l'extension sqlplan

[mikedavem]

Je pense que ton index n'est pas le bon en réalité. Il faudrait tester bien sûr.
Sur ton plan d'exécution on voit qu'il y a une opération de tri coûteuse provoquée par ROW_NUMBER() OVER (PARTITION BY Method ORDER BY "Date" DESC). En effet l'optimiseur est obligé de trier dans l'ordre descendant les dates avant d'utiliser son opérateur de segmentation. Il y a de grandes chances que cette opération de tri se fasse dans tempdb ... donc au niveau performance ...

Pour éviter cette opération de tri tu peux essayer de recréer ton index avec un ordre descendant sur ta colonne [date] pour voir.

Code :

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

1
2
3
4
5
CREATE NONCLUSTERED INDEX [IX_ImeiDate] ON [dbo].[GPSTrace] 
(
[imei] ASC,
[date] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

++

[olibara]

Merci MikeDavem

Theoriquement tu as tout a fait raison
Pratiquement j'ai changé l'index et ca ne change rien (j'ai joint le nouveau plan)!

Et le nouvel index (j'ai modifié l'autre)

Code :

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

1
2
3
4
5
CREATE NONCLUSTERED INDEX [IX_ImeiDate] ON [dbo].[GPSTrace] 
(
	[imei] ASC,
	[date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Je vais essayer mon TOP en boucle
Je pense que c'est beaucoup beaucoup plus rapide
Et c'est normal parce que chaque TOP ne porte que sur le nombre d'enregistrement demande
Tandis que que le select over partition porte sur la totalité et ce n'est qu'apres que le WHERE RowNumber<N filtre le resultat

[iberserk]

Quel est ton but côté applicatif?

[iberserk]

A la vue de votre plan d’exécution je serais curieux de voir votre requête?


Le top induit en général un tri... ce qui est votre cas apparemment puisque vous souhaitez voir les résultats par triés par date décroissante?

[olibara]

Bonjour Ibersek

Mon select est dans un autre post dont le lien est ci dessous

http://www.developpez.net/forums/d13...over-group-by/

Le but coté applicatif est de visualiser les N dernieres position d'une serie de chauffeurs dont les position GPS sont stockées toutes les 30 secondes en BD

[iberserk]

Je ne vois aucun filtre sur les chauffeur?

De même l'utilisation des LEFT OUTER JOIN est contre-performant surtout si comme je le pense le chauffeur doit ressortir dans votre requète vous pouvez les remplacer par des INNER JOIN...

Le problème de lenteur vient du manque de prédicat (filtre): vous retournez les 10 dernières positions pour tout les IMEI...

[mikedavem]

Theoriquement tu as tout a fait raison

En fait je n'ai pas vu ta requête complète .. du coup effectivement l'index ne sera pas utilisé. Il faudrait ajouter les colonnes supplémentaires de ton SELECT dans ce cas afin qu'il devienne un index couvrant mais bon cela revient à mettre toutes les colonnes de ta table finalement ...

Une autre solution serait de changer ton plan d'indexation pour cette table (sous réserver de ne pas impacter tes requêtes existantes bien évidemment) :

- Tu mets un index non cluster unique sur la colonne id
- Tu mets un index cluster comme ceci {imei, date DESC}

En principe tu devrais pouvoir éviter l'opération de tri dans ces 2 cas.
Tiens nous au courant plus les propositions faites par iberserk

++

[olibara]

Merci MikeDavem et Iberserk

Vos remarques m'ont donné une idée dont les resultat sont remarquables !

Avant tout pour le LEFT OUTER JOIN Chauffeurs, j'en ai besoin car tout les traceurs n'ont pas forcément un chauffeur lié
Mais ce n'est pas ca qui tue ! AU CONTRAIRE : si je le remplace par un inner join je remultiplie par 4 le temps de resultat de la requete optimisée ci-dessous

Le post de MikeDavem concernant les index m'a mis la puce a l'oreille

Du coup j'ai mis mon OVER Partition en sub select d'un inner join c'a a l'air plus compliqué et pas ebident au premier coup d'oeil mais je passe de +/- 40 secondes a 3 secondes

Pas photo !

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
   SELECT
   ta.ID,
   ta.latitude,
   ta.longitude,
   ta.date,
   ta.speed,
   ta.imei,
   Vehicle.ChauffeurID,
   '('+CAST(Vehicle.ChauffeurID as VarChar)+')-'+CH.naam AS ChauffeurName
   FROM [bcTracking].[dbo].[GPSTrace] Ta
INNER JOIN (SELECT
      ROW_NUMBER() OVER ( PARTITION BY IMEI ORDER BY Date DESC ) AS 'RowNumber',
      ID
  FROM [bcTracking].[dbo].[GPSTrace]
	) dt ON dt.id=Ta.id
 LEFT JOIN GPSDevice ON GPSDevice.DeviceSN = Ta.imei 
 LEFT JOIN Vehicle ON Vehicle.DeviceID = GPSDevice.pkId 
 LEFT OUTER JOIN Chauffeurs CH ON Vehicle.ChauffeurID=CH.chauffeurID
WHERE dt.RowNumber <= 50

[mikedavem]

Un petit plan d'exécution peut être ?

Je pense que là l'index doit être utilisé correctement et du coup éviter une opération de tri coûteuse

++

[olibara]

Voici le nouveau plan

J'ai aussi ajouté

Code :

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

ORDER BY ta.IMEI,TA.ID

[olibara]

Bonjour

A la lumiere des résultat apres amélioration de l'index et de la requete
Je me demande si je n'ai pas dans le cas présent intéret a definir un index IMEI + ID Desc

Sachant que le ID est une primary key auto incrément il suit forcément l'ordre chronologique des date

Et en faisant ca et si j'ai bien compris MikeDavem le subselect over partition qui sort cet ID sera encore plus performant

Je change la requete initiale

Code :

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

1
2
3
4
5
INNER JOIN (SELECT
      ROW_NUMBER() OVER ( PARTITION BY IMEI ORDER BY Date DESC ) AS 'RowNumber',
      ID
  FROM [bcTracking].[dbo].[GPSTrace]
	) dt ON dt.id=Ta.id

Je fais un nouvel index IMEI,Pkid Desc et la nouvelle requete sera

Code :

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

1
2
3
4
5
INNER JOIN (SELECT
      ROW_NUMBER() OVER ( PARTITION BY IMEI ORDER BY ID DESC ) AS 'RowNumber',
      ID
  FROM [bcTracking].[dbo].[GPSTrace]
	) dt ON dt.id=Ta.id

Un avis sur l'idée ?
Merci de votre aide

[olibara]

Pour revenir sur le sujet abordé dans ce Post qui concerne in fine une optimisation sur la requete des n dernier elements d'un groupe

La table se remplit en permanence a raison de +/- 2 insert a la seconde et doit conserver un historique de +/- 6 mois ca fait donc +/- 30M de record pour 40 groupes (40 traceurs en activité)

Parallelement des postes clients "surveillent" l'activité en temps réel des traceurs,
Une dizaine de clients executent de maniere asynchone toutes les 30 secondes une requete pour obtenir la derniere activité de chaque "groupe". Ca fait je pense pas mal de charge pour le SGBD

Je viens de penser que plutot que d'effectuer ces requetes sur la table principale, il serait peut etre intéressant d'utiliser un trigger qui mettrait automatiquement a jour une table secondaire limitée soit
- au DERNIER insert de chaque groupe (un simple update dans ce cas)
- aux n derniers insert de chaque groupe (sans doute plus compliqué a mettre en oeuvre)

Mes requetes de surveillance seraint donc beaucoup plus rapides et certainement moins couteuses en ressources pour le SGBD

Qu'en pensez-vous ?

Merci de partager votre savoir