Performance comparison

[weyandch]

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]

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]

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]

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

[weyandch]

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]

@weyandch: Any news on this?

[craue]

/ping @weyandch

[weyandch]

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]

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]

👍

Nice catch @lughino !

[craue]

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]

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

[fnagel]

Thanks a lot for pointing me to this optimization tweak! Using

CraueGeoBundle/Tests/IntegrationTestCase.php

Line 148 in 7187617

private function addRadiusOptimization(QueryBuilder $qb, $latOrigin, $lngOrigin, $maxRadiusInKm) {

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?