[Qgis-developer] Why is scrolling in the attribute table so slow?

[Dave.DeHaan at sybase.com]

> >> Maybe my view definition is slowing things down that much. I have to
> >> see if
> >> I can improve it.
> >
> > It would be good to use 'EXPLAIN' to see what is going on in the
> > database - then probably adding some indices on columns of joined
> > tables will speed things up significantly.
>
>  yes - it is quite a complex view. Around 20 tables are involved and for
>  the joins I am also using concatenations and regexp functions.
>
>  Although the view involves a lot of tables with quite a bit of data,
>  the result is actually really small (350 records) so I wonder, if for
>  situations like this, if there couldn't be a user-defined option telling

>  QGIS to load all data at once and cache it.
>

If your view is complex to compute but evaluates to a small extent, you
should consider materializing it on the server side.  Materialization makes
a lot of sense for a view that is read much more often than the underlying
data is updated.  Materialization on the server side rather than in an
application-layer cache allows the cache to be shared both by heterogenous
applications and by concurrent user connections.

Unfortunately, Posgres does not have built-in support for materialized
views.  However, it is possible to roll your own and keep it up to date
either eagerly (incremental maintenance via synchronous triggers) or lazily
(periodic recomputation).  See the two excellent tutorials linked from this
page: http://wiki.postgresql.org/wiki/Materialized_Views


----
David E. DeHaan
SQL Anywhere Query Processing team
Sybase, an SAP Company