Moritz L: > As a final (at least for today ;-) )follow-up, just for the record: On 11/08/10 13:42, Moritz L:
> > 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: so, this is an "extra" step? i.e. not done by grass' modules. > 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 > > 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... This is very interesting, indeed. Nikos _______________________________________________ grass-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/grass-dev
