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 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
