[GRASS-dev] postgresql speed

[Moritz Lennert]

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