search

craue / CraueGeoBundle

Doctrine functions for calculating geographical distances in your Symfony project.
MIT License
124 stars 15 forks source link

Performance comparison #5

Open weyandch opened 11 years ago

weyandch commented 11 years ago

Heya craue,

i've just installed the bundle (Doctrine 2.1.7) and made a few tests. The Queries i'm executing contain 2 joins and are performing much slower than before.

Adding indices on my "poi" table improved performance a little bit (<10%) ... going to try adapting the model to use the long/lat. query, but any hint on the most performant version would be nice in the docs.

-chris

craue commented 11 years ago

To be honest, I haven't done any performance-related tests yet. There are no "tests" at all. ;) Would be nice to have them, though. But what does "much slower" mean in this case?

weyandch commented 11 years ago

Okay hehe..

in the specific case i'm retrieving objects from a table, where i need to join another one which holds location & zip codes..

The query without restricting results by the GEO-Query takes 1-2ms, the one with geo data 150-170. The amount of data is little, just 5 records so far..

craue commented 11 years ago

That's pretty much indeed. Does your joined table have an index for location and zip code?

weyandch commented 11 years ago

Yeah, added an combined index on both fields, increased performance.. but the 150-170ms got this index already.

I'll try to do some query analysis ont he weekend, didn't find the time to investigate it further .. sorry :(

craue commented 11 years ago

@weyandch: Any news on this?

craue commented 10 years ago

/ping @weyandch

weyandch commented 10 years ago

heya, no news on that.. never continued work on that project i used your bundle on, might find some time on the holidays. will give it a try someday, sorry for not providing more help :(

lughino commented 10 years ago

Have you found any solution? Looking around I found this: http://tr.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL starting on page 10. Does see also table of comparative performance and sounds great!

Do you think there is any way to implement this solution?

webdevilopers commented 9 years ago

:+1:

Nice catch @Lughino !

craue commented 8 years ago

This bundle provides doctrine functions which can be used in a query, while the optimization mentioned in the presentation (which I've also taken the formula from when creating the bundle) works by adding conditions to the WHERE part of that query, so I have no way to add them automatically when invoking one of the functions. But you could (or even should) add them yourself.

craue commented 8 years ago

I've added the optimization to the test suite in 15fca003d1ec140799b550d4898520f0c1134143 to show how it could be done.

fnagel commented 3 months ago

Thanks a lot for pointing me to this optimization tweak! Using https://github.com/craue/CraueGeoBundle/blob/718761774d96271c22a823bf18fe64e89d20bfcb/Tests/IntegrationTestCase.php#L148 is a game changer!

In my current use case (big dataset, using STI, lots of joins and complex where conditions) I'm down from ~2850ms to ~75ms! What about a prominent notice about this improvement tweak in the readme?