[gdal-dev] why ogr oracle connection is so slow?

[Ivan]

Mateusz Loskot wrote:
> Moskovitz, Bob wrote:
>> Speaking of Oracle drivers.  Maybe there is something in orclib 
>> (http://orclib.sourceforge.net/ ) that the gdal devs can use to solve
>>  the speed problem.  -Bob
> 
> "encapsulates OCI (Oracle Call Interface)"
> 
> So, there would be obviously no difference related to what
> Oracle API is used.
> 
> I doubt that the matter of which Oracle API is used causes any
> performance bottleneck. It could be OCI or OCCI, which in fact are APIs
> of lowest level available to end user, and there was no difference.
> orclib is an abstraction on top of OCI, so it potentially
> could make things even slower :-)
> 
> Best regards,

Mateusz,

That is right. Using somebody else wrapper or writing your own just help to make your code cleaner. 
But that could introduce a little overhead. One thing that OCILIb does, and maybe that is what Bob 
is looking for, is the OS authentication stuff. We don't have that in GDAL/GeoRaster or OGR/OCI.

But I was looking at the OCI driver code and I noticed that it uses OCIBindObject() to link the 
sdo_geometry objects. I am not sure that is the best alternative. If I remember correctly Bind are 
used when you don't know much about the column you are reading/writing and Define is used otherwise.

The OCI driver is responsible to read attribute data in several different types, like varchar, 
number, date and other previously unknown types. That makes Bind the best option but I double it is 
the best when you know that what you are reading/writing is a SDO_GEOMETRY.

In the GeoRaster driver I use OCIDefineByPos() to retrieve a list of all the LOB at once. That 
doesn't mean that it reads all the data blocks to memory. It means that you don't need to issue 
another SQL and the Oracle client/server will take care of caching the data for you as needed. That 
solution wouldn't work for OGR/OCI because sdo_geometries are not LOB. Works for LibLAS as far as I 
know because the point cloud data is stored is LOB.

So, here is what I found on the "Oracle® Call Interface Programmer's Guide". A better alternative to 
fetch complex object is to use the "Complex Object Retrieval (COR) handle":

"Application performance when dealing with objects may be increased through the use of complex 
object retrieval (COR). This is a prefecthing mechanism in which an application specifies a criteria 
for retrieving a set of linked objects in a single operation.
...
If an application needs to manipulate a graph of objects (inter-related by object
references) then it is more effective to use the OCI interface rather than the SQL
interface for accessing objects. Retrieving a graph of objects using the SQL interface
may require executing multiple SELECT statements which would mean multiple
network round trips. Using the complex object retrieval capability provided by the
OCI, the application can retrieve the graph of objects in one OCIObjectPin() call."

It looks like there is a lot of alternatives to explorer on OCI itself, that is where the meet touch 
the bones. No wrapper is going to help with that :)

Sounds like a lot of fun.

Regards,

Ivan