[gdal-dev] Re: Optimizing random access in SQL result set of OGR DB drivers

[Even Rouault]

Selon Frank Warmerdam <warmerdam at pobox.com>:

> Even Rouault wrote:
> > Martin,
> >
> > I take the liberty to CC the list, as it is an interesting issue and it's
> better
> > to go public if we want to make progress on that.
> >
> > In a few words, this is about how we could speed up GetFeature() on the
> layers
> > returned when issuing a SQL request on OGR drivers that rely on databases :
> PG
> > driver, MySQL driver, SQLite driver (... ?). For people wanting to take
> part,
> > you might be interested in the below email exchange first.
> >
> > After examining Martin's patches and reading its explanations, I cann see 2
> > possibilities if we want to implement such optimization :
>
> Even,
>
> I haven't seen Martin's patches, but the normal way to provide indexed
> access to resultsets is the OGRLayer::SetNextByIndex() method.  You
> pass the index into the current result set and then GetNextFeature()
> should read the indicated feature.
>
> Currently only very few providers implement this in a customized way,
> but it seems it would be better to expand that rather than introduce
> a new mechanism.
>
> Does this make sense?

Yes absolutely ! I didn't realize this existed at all... Apparently, this is
only used in the Memory and Shape driver. But that's exactly what should be
implemented in the explained use cases.

An implementation difficulty I imagine is that on those SQL layers, calling once
SetNextByIndex() and then GetNextFeature() probably makes it
difficult/impossible to use the sequencial API of the DB library (the Step()
method of libpq for example) for the following calls to GetNextFeature(), until
the next call to ResetReading(). Well, that's just an implementation detail that
must be solved.

While looking again at Martin's patch, I think that they could easily be adapted
for the PG and MySQL case. But for the SQLite, I don't see how to do that
correctly as it relies on the OGC_FID, which doesn't necessary match with the
ordinal. But perhaps there's an equivalent of the "mysql_data_seek( hResultSet,
iNextShapeId )" or "FETCH ABSOLUTE %d in %s", nFeatureId, pszCursorName"".

>
> PS. there are reasonably good reasons why we want the FID to be the
> primary key where practical.  I would hate to break that.
>
> Best regards,
> --
> ---------------------------------------+--------------------------------------
> I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
> light and sound - activate the windows | http://pobox.com/~warmerdam
> and watch the world go round - Rush    | Geospatial Programmer for Rent
>
>