CODEWITHSUNDEEP
c++oracle-databaseadoc++builder
Buzz
Buzz

Reputation: 21

borland builder c++ oracle question

I have a Borland builder c++ 6 application calling Oracle 10g database. Operating over a LAN. When the application in question makes a simple db select e.g.

select table_name from  element_tablenames where element_id = 10023842

the following is recorded as happening in Oracle (from the performance logs)

select table_name 
  from element_tablenames 
 where element_id = 10023842

then immediately (and not from C++ source code but perhaps deeper)

select table_name, element_tablenames.ROWID 
  from element_tablenames 
 where element_id = 10023842

The select statement is only called once in the TADODbQuery object, yet two queries are being performed - one to parse and the other adds the ROWID for executon.

Over a WAN and many, many queries this is obviously a problem to the user.

Does anyone know why this might be happening, can someone suggest a solution?

Upvotes: 2

Views: 872

Answers (2)

Robert Love
Robert Love

Reputation: 12581

I don't know exactly where the RowID is coming from, it could be either the TAdoQuery implementation or the Oracle Driver. But I am sure I found the reason.

From the Oracle docs:

If the database table does not contain a primary key, the ROWID must be selected explicitly when populating DataTable.

So I suspect your Table does not have a primary key, either add one or add the rowid. Either way this will solve the duplicate query problem.

Since you are concerned about performance. In general

Using TAdoQuery you can set the CursorType to optimize different behaviors for performance. This article covers this from a TAdoQuery perspective. MSDN also has an article that covers it from from a general ADO Perspective. Finally the specifications from the Oracle Driver can be useful.

I would recommend setting the Cursor to either as they are the only supported by Oracle

  • ctStatic - Bi-directional query produced.
  • ctOpenForwardOnly - Unidirectional query produced, fastest but can't call Prior

You can also play with CursorLocation to see how it effects your speed.

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

Agree with Robert.

The ROWID uniquely identifies a row in a table so that the returned record can be applied back to the database with any changes (or as a DELETE).

Is there a way to identify a particular column (or set of columns) as a primary key so that it can be used to identify a row without using a ROWID.

Upvotes: 3

Related Questions