optimisation calcul distance dans procédure stockée ?

Marsh Posté le 12-07-2006 à 20:01:53

J'ai une table de villes avec leur latitude et leur longitude.

J'ai fait une procédure stockée avec comme paramètre une latitude et une longitude et qui sort toutes les villes qui sont dans le rayon (en km) passé en paramètre.

La table est comme suit.

IdCity
CityName
Longitude
Latitude
PostalCode

Code :

CREATE PROCEDURE [SP_Tmp_Villes_Proches]
-- Les parametres
@latitude As float,
@longitude As float,
@limite As float --rayon de recherche en kilomètre.
AS
BEGIN
-- Table temporaire contenant le resultat de la recherche
CREATE TABLE #TmpVille
(
--_Iidtmp int identity primary key,
_nom char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
_distance float
)
INSERT INTO #TmpVille ( _nom, _distance)
SELECT DISTINCT CityName,9999
FROM Tmp_VillesPC
ORDER BY CityName
UPDATE #TmpVille Set _distance = (SELECT TOP 1 (6366 * acos( cos(radians(@latitude)) * cos(radians(Latitude)) * cos(radians(Longitude)-radians(@longitude)) + sin(radians(@latitude)) * sin(radians(Latitude)))) as DST FROM Tmp_VillesPC WHERE Tmp_VillesPC.CityName=#TmpVille._nom And (6366 * acos( cos(radians(@latitude)) * cos(radians(Latitude)) * cos(radians(Longitude)-radians(@longitude)) + sin(radians(@latitude)) * sin(radians(Latitude)))) <= @limite)
SELECT _nom, _distance
FROM #TmpVille
WHERE _distance <= @limite
ORDER BY _nom
END
RETURN
GO

Le problème c'est que la table à 800 000 enregistrements et donc la requête est assez longue à être éxécutée. (10 secondes et + )

Petite explication. Ce sont des villes canadiennes et pour une même ville, il y a plusieurs codes postaux différents.
Par exemple Montréal apparait 21000 fois dans la table.

L'idée de récupérer tous les noms de villes distincts et de calculer la distance en fonction de leur longitude et de leur latitude.

Si une ville apparait plusieurs fois, on affiche la 1ère occurence qui est dans le rayon de recherche.

Le problème c'est le calcul sur la distance qui est long et comme je le fais deux fois pour lister que ceux qui sont dans le rayon de recherche,ça rallonge encore plus.

Quelqu'un a une idée comment faire pour réduire le temps de recherche ?

Reply

Marsh Posté le 12-07-2006 à 20:01:53

Reply

Marsh Posté le 17-07-2006 à 10:31:59

1) pkoi tu passes par une table temporaire ?
2) niveau perfs, tu gagneras rien, mais c'est plus propre de passer par une FONCTION qui fait un "returns table"

au final, t'auras juste à faire un :

Code :

return (select idcity, cityname, ... from cities where --ta fonction de calcul de la distance par rapport à la latitude/longitude passée en paramètre-- <= @limite)

Ca devrait aller autrement plus vite, puisque tu ne passeras plus par une table temporaire.

Pense bien à créer un index unique sur latitude/longitude. Ca ne sera pas forcément beaucoup plus rapide mais bon.
Pour le calcul, et surtout, te servir de l'index, tu peux faire ce qu'on appelle un "clip" en GDI : tu sais que si les latitudes/longitudes sont en dehors d'un certain carré, ou l'un ou l'autre est a plus de "@limite" d'écart, il sera trop loin. Tu rajoutes donc between sur latitude + @limite et -@limite, et pareil sur longitude, afin d'utiliser l'index unique que je t'ai fait créer, et éviter de faire le calcul de distance pour toutes les villes, ce qui est totalement inutile.

Message édité par Arjuna le 17-07-2006 à 10:32:37

Reply

optimisation calcul distance dans procédure stockée ?

Sujets relatifs:

Leave a Replay