optimisation calcul distance dans procédure stockée ?

optimisation calcul distance dans procédure stockée ? - SQL/NoSQL - Programmation

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 :
  1. CREATE PROCEDURE [SP_Tmp_Villes_Proches]
  2. -- Les parametres
  3. @latitude As float,
  4. @longitude As float,
  5. @limite As float --rayon de recherche en kilomètre.
  6. AS
  7. BEGIN
  8. -- Table temporaire contenant le resultat de la recherche
  9. CREATE TABLE #TmpVille
  10. (
  11.  --_Iidtmp int identity primary key,
  12.  _nom char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  13.  _distance float
  14. )
  15. INSERT INTO #TmpVille ( _nom, _distance)
  16. SELECT DISTINCT CityName,9999
  17. FROM Tmp_VillesPC
  18. ORDER BY CityName
  19. 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)
  20. SELECT _nom, _distance
  21. FROM #TmpVille
  22. WHERE _distance <= @limite
  23. ORDER BY _nom
  24. END
  25. RETURN
  26. 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 :
  1. 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

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed