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

[G. Allegri]

Very intereseting discussion. I don't have much experience neither in
C++ nor in OCI programming, but the topic is very important and
interesting and I will try to contribute somehow. I will start to
deepen the argument making a comparison between the various adopted
solutions (FDO, etc.).

Thanks everyone for sharing ideas.
Giovanni

2010/2/27 Ivan <ivan.lucena at pmldnet.com>:
> 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
>