[postgis-users] SQLite and postGIS

[Richard Greenwood]

On Sun, Apr 13, 2008 at 8:29 PM, Frank Warmerdam <warmerdam at pobox.com> wrote:
> Martin Chapman wrote:
>
> > Rich,
> >
> > One thing you should note about OGR performance when accessing certain
> > databases like MySQL, PostgreSQL and SQLite is that OGR uses a strategy
> for
> > these drivers that gets progressively worse for large datasets.  Query
> > execution is always pretty quick in any database, it's accessing the rows
> > quickly that is the true test of database performance in my opinion.
> > In OGR, the SQLite driver becomes progressively slower over time when
> > iterating over a large resultset from 0 to N.  Note that doesn't mean that
> > SQLite is slow though, it's actually a really fast database if used
> > optimally.
> > For instance:
> >
> > For each record that is requested after a query is executed, OGR starts at
> > the beginning of the resultset (row zero) and searches forward until it
> > finds the matching FID.  This pattern is repeated for every request.  This
> > will execute very quickly for the first few thousand FID's or so but the
> > further you iterate through the resultset, and the higher the index of
> each
> > FID becomes, the slower each record request will become.  This is
> obviously
> > a strategy of diminishing returns over any resultset of any size.  The
> > solution to this problem is to use random access.  See my other postings
> to
> > see how to make this change to OGR for MySQL, PostgreSQL and SQLite.
> >
>
>  Folks,
>
>  I would like to stress this behavior is only the case when attempting
>  to fetch features by fid.  The more common access pattern is to set
>  query parameters (spatial and attribute), and then to iterate through
>  those features matching using GetNextFeature().  In this situation there
>  is none of this "search by fid" O(n*n) performance.
>
>  Martin is implementing valuable optimizations for the GetFeature() by
>  FID case but the other alternative that performs well for all OGR
>  datasources is to use the GetNextFeature() iterator.

So in a MapServer itemquery, is GetFeature() or GetNextFeature() being
used? I'm guessing the latter. I'm trying to picture, from a MapServer
perspective, where the senarios you describe would be invoked. Like
when would you do a GetFeature() by FID?

I guess I should look at the code. My questions are purely academic,
so don't waste an undue amount of time trying to enlighten me.

Rich

-- 
Richard Greenwood
richard.greenwood at gmail.com
www.greenwoodmap.com