developer (#13) - Optimizing a function (#74) - Message List

Optimizing a function

Hi,

I added this function to find the nearest hospital using the distance covered on the route itself. What I am after is to optimize this function to make it take less time, since I am seeing that almost 2 minutes is way too much. Every pass of the shootingstar_sp is taking about 30-40seconds.

My reasoning was this :

- Find the 3 most near hospitals using distance() function

- Iterate the 3 hospitals and find the one which is the shortest distance taking into considerations one-ways et al. using shootingstar_sp() pgRouting Function.

Using Pastebin I have pasted my current function and also inline commented more on the function

The paste bin can be found here :  http://yancho.pastebin.com/f2f986b8c

At the end of the paste you can find the EXPLAIN ANALYZE of the query.

As you can see I have highlighted in yellow some RAISE NOTICEs to time the query and these are the results :

Below please find the result : - filling up the hospital_location with that query took : 32646ms - the IF section took 994 ms - returning back to the FOR loop took : 104 ms - Shooting Star execution took : 25079ms - If statement took : 614ms - Shooting star took : 37927 ms - If took : 300 ms

Total query runtime: 114250 ms. 2 rows retrieved. Execution plan : NOTICE: Entering the FOR IN Thu Dec 06 21:02:46.267488 2007 CET NOTICE: entering shooting star Thu Dec 06 21:02:46.300164 2007 CET NOTICE: before IF Thu Dec 06 21:03:24.307906 2007 CET NOTICE: value of nearest.dist is 585.966565014107 NOTICE: value of hospital.gid is 12712 NOTICE: value of nearest.gid is NOTICE: value of 2ND nearest.gid is POINT(640607.6248615 224673.124400562) NOTICE: after if Thu Dec 06 21:03:24.308900 2007 CET NOTICE: before end of loop Thu Dec 06 21:03:24.309029 2007 CET NOTICE: entering shooting star Thu Dec 06 21:03:24.309133 2007 CET NOTICE: before IF Thu Dec 06 21:04:02.024339 2007 CET NOTICE: value of nearest.dist is 91.0322939509899 NOTICE: value of hospital.gid is 38600 NOTICE: value of nearest.gid is POINT(640607.6248615 224673.124400562) NOTICE: value of 2ND nearest.gid is POINT(640952.4998615 224309.563400563) NOTICE: after if Thu Dec 06 21:04:02.024953 2007 CET NOTICE: before end of loop Thu Dec 06 21:04:02.025093 2007 CET NOTICE: entering shooting star Thu Dec 06 21:04:02.025178 2007 CET NOTICE: before IF Thu Dec 06 21:04:39.952201 2007 CET NOTICE: after if Thu Dec 06 21:04:39.952510 2007 CET NOTICE: before end of loop Thu Dec 06 21:04:39.952598 2007 CET NOTICE: after end of loop before returning Thu Dec 06 21:04:39.952846 2007 CET

Total query runtime: 114250 ms. 2 rows retrieved.

Hope this information can help you give me a better idea on how I can improve this query.

Thanks and regards

Matthew

yancho
12/07/07 17:28:10 (3 years ago)
Tree View Flat View (newer first) Flat View (older first)
  • Message #275

    Hi again,

    I managed to get this Function to compile and actually work. What I changed were the values in the expand to pass to the functions give_source() and give_target().

    I have made a quick OpenLayers? + Mapserver script so as to test the outcome and unfortunately this function is giving me really awkward results!

    The more the accident happens near to a hospital / firestation, the less chance it will be chosen as the nearest hospital / firestation.

    As one can test for himself, here :  http://yancho.no-ip.org/~yancho/rescue.html the nearer (I even tried on the same street) the lesser the chance to be chosen as the nearest hospital.

    The new function can be found here :  http://yancho.pastebin.com/d37f3b903

    The PHP script I am using for routing can be found here :  http://yancho.pastebin.com/f6cff7908

    Any help how I can fix this problem will be extremely appreciated!

    Thanks and regards

    Matthew

    yancho
    12/08/07 01:44:25 (3 years ago)
    • Message #345

      Hello,

      what if you compute the direct distance instead of path distance? (i.e. distance between centroid of starting location and centroid of hospital) instead of computing path distance? because i think no matter how we optimize, finding nearest hospital based on road length is slow.

      another option i think is precompute nearest hospital for each section of a map. that way retrieving the info is just simple retrieval.

      jontan6
      12/29/07 23:53:03 (3 years ago)
      • Message #346

        Thanks jontan.

        sorry for replying a bit late. I had found a work around earlier on using this :  http://yancho.pastebin.com/f27ed0cd8

        It works extremely fast .. talking about less than 50ms

        yancho
        01/07/08 19:31:09 (3 years ago)
Tree View Flat View (newer first) Flat View (older first)