0 Replies Latest reply on May 30, 2005 6:47 AM by Simon Greener

    Speed accessing OraObjects and OraCollections in VB6

    Simon Greener
      Folks,

      I am using OO4O (10.1) to access Sdo_Geometry
      from within a VB application.

      We have the code running quite fast in terms
      of query speed and client/server buffering
      (via CreateCustomDynaset) but in fine tuning
      the application we have discovered that it
      seems to cost a lot in terms of speed to access
      elements of an OraObject.

      For example, the SDO_GEOMETRY object is defined as
      being:

      ' MDSYS.SDO_GEOMETRY is an OraObject
      '
      ' SQL> describe mdsys.sdo_geometry
      ' Name Type
      ' ---------------------- -----
      ' SDO_GTYPE Number
      ' SDO_SRID Number
      ' sdo_point SDO_POINT_TYPE
      ' SDO_ELEM_INFO sdo_elem_info_array
      ' SDO_ORDINATES sdo_ordinate_array

      With:

      ' MDSYS.SDO_POINT is an OraObject
      '
      ' SQL> describe mdsys.sdo_point_type
      ' Name Type
      ' --------------- -----
      ' X Number
      ' Y Number
      ' Z Number

      And...
      ' MDSYS.SDO_ELEM_INFO is an OraCollection
      '
      ' SQL> describe mdsys.sdo_elem_info_array
      ' mdsys.sdo_elem_info_array VARRAY(1048576) OF NUMBER

      And...
      ' MDSYS.SDO_ORDINATES is an OraCollection
      '
      ' SQL> describe mdsys.sdo_ordinate_array
      ' mdsys.sdo_ordinate_array VARRAY(1048576) OF NUMBER

      Now when I access the parts of the sdo_geomety object
      via code like this...

      uShapeType = CVar(oGeom.SDO_GTYPE Mod 1000)
      lShapeNumDimensions = Int(oGeom.SDO_GTYPE / 1000)
      ....
      If Not IsNull(oGeom.Item("SDO_SRID").Value) Then
      lShapeSRID = oGeom.SDO_SRID
      ....
      Dim gSdoPoint As OraObject
      If Not IsNull(oGeom.Item("sdo_point").Value) Then
      Set gSdoPoint = oGeom.Item("sdo_point").Value
      ....
      Set eiaShape = oGeom.SDO_ELEM_INFO
      l_ShapeNumberOfElements = Int(eiaShape.Size / 3)
      If (l_ShapeNumberOfElements > 0) Then
      Set oaShape = oGeom.SDO_ORdinates
      ...

      We find that each access to the geometry object's parts
      takes between 1 and 50 milliseconds EACH access regardless
      as to the element being accessed. While this doesn't
      seem much, it adds up dramatically over a query of
      a few hundred objects per query.

      Anyone have any experience with this who can offer
      me any pointers as to what to do to improve performance?

      BTW I am thinking about using server-side SQL to
      break the object into its component parts but it
      requires a bit of rewriting and I am unsure if the
      performance will improve.

      regards
      Simon Greener
      GIS Manager