On Wed, Aug 11, 2010 at 2:53 PM, Markus Metz <[email protected]> wrote: > Moritz Lennert wrote: >> As a final (at least for today ;-) )follow-up, just for the record: >> >> On 11/08/10 13:42, Moritz Lennert wrote: >>> >>> Then testing the idea from the link Markus N added to your bug report: >>> >>> time v.db.update mygrid col=count value="(SELECT count(*) from mypoints >>> WHERE mygrid.cat=mypoints.cat_municip group by cat_municip)" >>> >>> real 5m28.312s >> >> And to show the magic of database indices: >> >> time echo "create index mypoints_cat_municip on mypoints (cat_municip)" | >> db.execute && time v.db.update mygrid col=count value="(SELECT count(*) >> from mypoints WHERE mygrid.cat=mypoints.cat_municip group by cat_municip)" >> >> real 0m10.113s >> user 0m6.320s >> sys 0m1.300s >> >> real 0m0.668s >> user 0m0.544s >> sys 0m0.124s >> > Thanks a lot! This index was missing here on my sqlite tables. To > throw in another timing with 600 000 random points: > > time v.distance from=randpoints_600K from_layer=1 to=boundary_municp > to_layer=1 to_type=area upload=cat column=to_cat > (no dmax, a nearest area was found for each point) > > real 3m26.308s > user 1m49.736s > sys 1m35.067s
Estimate for grass 6: >6 hours > > same with dmax=0.0 > 558294 categories - no nearest feature found <-- expected > > real 1m24.819s > user 0m42.511s > sys 0m41.384s > > tuned v.distance in trunk r43042 > > Markus M > > >> And, very interestingly, the difference of behaviour of the different db >> backends (previous examples were all with SQLite, the following is with >> PostgreSQL): >> >> time echo "create index mypoints_cat_municip on mypoints_pg (cat_municip)" | >> db.execute && time v.db.update mygrid_pg col=count value="(SELECT count(*) >> from mypoints_pg WHERE mygrid_pg.cat=mypoints_pg.cat_municip group by >> cat_municip)" >> >> real 0m2.905s >> user 0m0.012s >> sys 0m0.004s >> >> real 0m7.948s >> user 0m0.228s >> sys 0m0.128s >> >> So, SQLite takes lot's of time creating the index and then is very fast for >> the update, and the opposite is true for PostgreSQL. Don't know if that's >> anything we can do something about in GRASS... >> >> Moritz >> > _______________________________________________ grass-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/grass-dev
