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