[GRASS-dev] postgresql speed

[Markus Metz]

Moritz Lennert wrote:
> On 06/03/09 10:30, Vincent Bain wrote:
>>> See also some comments of Radim in [2] under "Attributes".
>
> An important point in the above is:
>
> "Another problem is random access to the data in RDBMS
> from an application which is terribly slow (due to communication with
> server)"
>
> Most vector modules loop through the cats of the vector map and access 
> the table cat by cat. This creates the random access problem that 
> Radim speaks about, with significant overhead in terms of connection 
> costs.
>
> The new module d.thematic.area uses a different model, selecting an 
> array of cats and values (db_select_CatValArray see line 243 of 
> display/d.thematic.area/main.c in devel6 branch) and during display 
> loops through the areas in the map, but gets the necessary values from 
> this array, instead of connecting each time to the db (see line 139 in 
> display/d.thematic.area/area.c). Thus the random access is handled by 
> db_CatValArray_get_value and not by a connection to the db, contrary 
> to what is done in d.vect.chart for example (see line 91 of 
> display/d.vect.chart/plot.c). By the way, quite a while I proposed a 
> modified version of d.vect.chart (d.vect.chart2) which implements the 
> dbCatValArray model [3] and shows significant speed gains[4], and an 
> even more general approach where you can launch any arbitrary SQL 
> query as long as it returns a list of category values and display the 
> features which correspond to these values. This might allow a more 
> attribute data-centric management and display of maps (see discussion 
> in [5]).
>
> So, it might be interesting to do an audit of database access in 
> different vector modules to see where a different approach could allow 
> speed gains.
>
> Moritz
>
> [2]http://freegis.org/cgi-bin/viewcvs.cgi/grass6/doc/vector/TODO?rev=HEAD&content-type=text/vnd.viewcvs-markup 
>
> [3]http://geog-pc40.ulb.ac.be/grass/chart/
> [4]http://lists.osgeo.org/pipermail/grass-dev/2006-October/026624.html
> [5]http://lists.osgeo.org/pipermail/grass-dev/2006-October/026625.html
Moritz, could this information be placed somewhere where it does not get 
lost, e.g. in the wiki here [1] or here [2], or in the programmer's 
manual? Using the dbCatValArray model does not break compatibility, it 
would simply speed up modules, right? Maybe we can also have a document 
somewhere describing solutions for the Vector TODO list plus other 
enhancements, or is it better to discuss it in the dev ML?

Markus M

[1] http://grass.osgeo.org/wiki/GRASS_7_ideas_collection#Modules_2
[2] http://grass.osgeo.org/wiki/GRASS_7_ideas_collection#Modules_4