Discussion :

[pikamo]

Bonjour,
mon objectif est de recupère la dernier enregistrement de chaque device.
exp

Code :

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

1
2
3
SELECT iddevice,longitude, latitude FROM markers ORDER BY id DESC LIMIT 1 where iddevice = 1 ===>  la dernier enregistrement  de device1
SELECT iddevice,longitude, latitude FROM markers ORDER BY id DESC LIMIT 1 where iddevice = 2 ===>  la dernier enregistrement  de device2
etc

iddevice est un clé étrangère.

SVP comment je peut réaliser ..merci d'avance

[StringBuilder]

Si comme le laisse penser tes ORDER BY, la façon de déterminer le "dernier", c'est "celui qui a le plus grand id", alors voici une requête qui devrait fonctionner, et qui est on ne peut plus standard (ça évite les mots clés réservés de MySQL qui ne sont pas portables) :

Code sql :

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

1
2
3
4
5
6
 
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
LEFT OUTER JOIN markers m2 on m2.iddevice = m1.iddevice and m2.id > m1.id
group by m1.id, m1.iddevice, m1.longitude, m1.latitude
HAVING count(m2.id) = 0;

[pikamo]

merci StringBuilder.Est ce que vous pouvez m'expliquer plus la requête?!

[StringBuilder]

On prend toutes les lignes de la table (m1), et on leur associe toutes les lignes (m2) dont l'ID est plus grand pour le même IDDEVICE.
Et on ne conserve que celles où il n'y a pas de lignes avec plus grand ID (m2) => Donc il ne reste plus que les lignes (m1) qui ont le plus grand ID pour un même IDDEVICE.

[tatayo]

Bonjour,
On peut simplifier la requête ainsi:

Code sql :

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

1
2
3
4
5
 
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
LEFT OUTER JOIN markers m2 ON m2.iddevice = m1.iddevice AND m2.id > m1.id
where m2.iddevice is null

Tatayo.

[pikamo]

cette requête marche aussi.
Comment je peux choisir la bonne requête(bonne rendement , courte durée de traitement etc)?!!

Code :

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

1
2
3
4
 
select m.longitude , m.iddevice , m.latitude
from markers m , ( select max(id) id,iddevice from markers group by iddevice ) m_v
where m.id = m_v.id;

[StringBuilder]

Bonjour,
On peut simplifier la requête ainsi:

Code sql :

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

1
2
3
4
5
 
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
LEFT OUTER JOIN markers m2 ON m2.iddevice = m1.iddevice AND m2.id > m1.id
where m2.iddevice is null

Tatayo.

Grmpf, c'est bien ce qu'il me semblait, mais je devais être fatigué hier, ça marchait pas

[StringBuilder]

cette requête marche aussi.
Comment je peux choisir la bonne requête(bonne rendement , courte durée de traitement etc)?!!

Code :

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

1
2
3
4
 
select m.longitude , m.iddevice , m.latitude
from markers m , ( select max(id) id,iddevice from markers group by iddevice ) m_v
where m.id = m_v.id;

La version de Tatayo et la meilleure.
Et celle que tu viens de montrer la pire.

Dans l'ordre de ce qu'il faut éviter (du pire au moins pire) :
- Sous-requêtes
- Agrégats
- Jointures ouvertes (et encore, les SGBD récents n'ont plus de problème avec)

Bon, après c'est pas une science exacte, mais dans les grandes lignes c'est vrai la plupart du temps.

[tatayo]

Pour avoir une première idée, on peut aussi examiner le plan d'exécution des différentes requêtes avec EXPLAIN.

Tatayo.

[aieeeuuuuu]

Bonjour

Dans l'ordre de ce qu'il faut éviter (du pire au moins pire) :
- Sous-requêtes
- Agrégats
- Jointures ouvertes (et encore, les SGBD récents n'ont plus de problème avec)

Bon, après c'est pas une science exacte, mais dans les grandes lignes c'est vrai la plupart du temps.

Non, cela dépend de trop de facteur, comme la présence d'index, la répartition des données,...

Typiquement dans le cas présent, une sous requete pourrait se montrer bien plus performante (surtout s'il y a un index sur [iddevice, id desc]) :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
WHERE NOT EXISTS(
    SELECT 1
    FROM markers m2 
    WHERE m2.iddevice = m1.iddevice 
    AND m2.id > m1.id
)

Comme l'a indiqué Tatayo, le meilleur (seul ?) moyen de choisir entre différentes requêtes, c'est de les tester, dans des conditions les plus proches de la réalité (en volume et répartition des données)

[StringBuilder]

S'il y a un tel index, la jointure aussi sera plus rapide, non ?

[StringBuilder]

Je viens de faire quelques tests (bon, avec un jeu de données très restreint et forcément pas représentatif), et finalement, c'est la pire des solutions qui est la plus performante une fois l'index posé (arf) : (SQL Server 2014 Express x64)

Avant index :

Code sql :

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
 
-- 14% du lot
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
LEFT OUTER JOIN markers m2 ON m2.iddevice = m1.iddevice AND m2.id > m1.id
WHERE m2.iddevice IS NULL;
 
-- 13% du lot
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
WHERE NOT EXISTS(
    SELECT 1
    FROM markers m2 
    WHERE m2.iddevice = m1.iddevice 
    AND m2.id > m1.id
);
 
-- 33% du lot
SELECT m.longitude , m.iddevice , m.latitude
FROM markers m , ( SELECT max(id) id,iddevice FROM markers GROUP BY iddevice ) m_v
WHERE m.id = m_v.id;
 
-- 14% du lot
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
LEFT OUTER JOIN markers m2 ON m2.iddevice = m1.iddevice AND m2.id > m1.id
GROUP BY m1.id, m1.iddevice, m1.longitude, m1.latitude
HAVING count(m2.id) = 0;
 
-- 27% du lot
with t (iddevice, longitude, latitude, rang)
as
(
SELECT m1.iddevice, m1.longitude, m1.latitude, rank() over(partition by m1.iddevice order by m1.id desc)
FROM markers m1
)
select iddevice, longitude, latitude
from t
where rang = 1;

Après création de l'index :

Code sql :

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

1
2
 
create unique index uix_markers on markers (iddevice, id);

Code sql :

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
 
-- 11% du lot
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
LEFT OUTER JOIN markers m2 ON m2.iddevice = m1.iddevice AND m2.id > m1.id
WHERE m2.iddevice IS NULL;
 
-- 11% du lot
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
WHERE NOT EXISTS(
    SELECT 1
    FROM markers m2 
    WHERE m2.iddevice = m1.iddevice 
    AND m2.id > m1.id
);
 
-- 10% du lot
SELECT m.longitude , m.iddevice , m.latitude
FROM markers m , ( SELECT max(id) id,iddevice FROM markers GROUP BY iddevice ) m_v
WHERE m.id = m_v.id;
 
-- 21% du lot
SELECT m1.iddevice, m1.longitude, m1.latitude
FROM markers m1
LEFT OUTER JOIN markers m2 ON m2.iddevice = m1.iddevice AND m2.id > m1.id
GROUP BY m1.id, m1.iddevice, m1.longitude, m1.latitude
HAVING count(m2.id) = 0;
 
-- 36% du lot
with t (iddevice, longitude, latitude, rang)
as
(
SELECT m1.iddevice, m1.longitude, m1.latitude, rank() over(partition by m1.iddevice order by m1.id desc)
FROM markers m1
)
select iddevice, longitude, latitude
from t
where rang = 1;

[tatayo]

Le problème avec ce genre de test, c'est que potentiellement la première requête récupère les données du disque, et les autres du cache...
L'idéal serait de vider le cache entre chaque requête.

Tatayo.

[aieeeuuuuu]

Code :

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

1
2
3
4
 
select m.longitude , m.iddevice , m.latitude
from markers m , ( select max(id) id,iddevice from markers group by iddevice ) m_v
where m.id = m_v.id;

D'ailleurs, cette requête est fausse : il manque AND m.iddevice = m_v.iddevice

[StringBuilder]

Sauf si ID est la clé primaire.

Dan mon jeu de test, c'était le cas, du coup ça posait pas de problème