django查询和存储过程(MySQL)的性能差异?

I want to use a query that calculates points that are a D distance away from a point (i.e. geolocation stuff), so the query is somewhat complex:

我想使用一个查询来计算距离点D的点(例如地理定位)，所以查询有点复杂:

        DELIMITER //

    CREATE PROCEDURE geodist (IN m_lat double, IN m_lng double, IN dist int, IN lim int)
    BEGIN
    DECLARE lon1 double; DECLARE lon2 double;
    DECLARE lat1 double; DECLARE lat2 double;

    -- calculate lon lat for the rectangle
    SET lon1 = m_lon-dist/abs(cos(radians(m_lat))*69);
    SET lon2 = m_lon+dist/abs(cos(radians(m_lat))*69);
    SET lat1 = m_lat-(dist/69); 
    SET lat2 = m_lat+(dist/69);

    -- run query;
    SELECT post.id, 3956 * 2 * ASIN(SQRT( POWER(SIN((m_lat - post.geo_lat) * pi()/180 / 2), 2) +COS(m_lat * pi()/180) * COS(post.geo_lat * pi()/180) *POWER(SIN((m_lng - post.geo_lng) * pi()/180 / 2), 2) )) as distance 
    FROM post_post as post
    WHERE
    post.geo_lng BETWEEN lon1 AND lon2
    AND post.geo_lat BETWEEN lat1 AND lat2
    HAVING distance < dist ORDER BY Distance limit lim;

    END //

well above is what my stored procedure would be. Since I will be using this to go through thousands if not millions of rows of data I was wondering what the performance hit on using a django query over a stored procedure would be? I realize that django doesn't support stored procedures so that's why I was hoping to avoid using stored procedures. However, then the query won't be compiled in the db and the calculations would have to be done in python ... etc. What's the difference in performance between a django query and a store procedure called from django?

以上就是我的存储过程。由于我将使用它遍历数千行(如果不是数百万行)的数据，我想知道在存储过程中使用django查询会带来什么性能影响?我意识到django不支持存储过程，所以我希望避免使用存储过程。但是，查询不会在db中编译，计算必须在python中完成……等等。django查询和django调用的存储过程在性能上有什么不同?

1 个解决方案

#1

[Edited] There is a huge performance improvement if you use specialized database functions like the ones in Postgis or Mysql Spatial Extensions, compared to calculating everything in python.

如果使用Postgis或Mysql空间扩展中的专用数据库函数，与使用python计算所有内容相比，性能会有很大的提高。

Take a look at geodjango, the setup is somewhat painful but it has everything you may ever need for geolocation.

看一下geodjango，这个设置有点麻烦，但是它有所有你可能需要的地理定位功能。

If geodjango is too much, you can always resort to raw SQL for this particular query.

如果geodjango太多，您总是可以为这个特定查询使用原始SQL。

#1