Harvest SQL Performance

Products

• CA Harvest Software Change Manager

Issue/Introduction

There is a big difference between the SQL query speed performed by the Harvest server and the speed when the same SQL statement is executed in TOAD.

The SQL statement speed executed in TOAD is fast (0.3 seconds), but the SQL statement speed displayed in Harvest is very slow (2 seconds).

Why does the query execution speed differ?

Environment

CA Harvest Change Manager 

13.x and 14.x

All Supported DBMS

Resolution

TOAD's data access component communicates with Oracle database using the Oracle Call Interface (OCI) which is the most performant interface of Oracle. 
 
Harvest on the other hand, communicates with Oracle through ODBC which is built on top of OCI and hence adds a certain level of performance overhead on top of OCI. 
 
Harvest is built on top of ODBC because it supports multiple databases, besides Oracle.

It would not be possible to do away with the ODBC layer in the Harvest product at this point of time as the product architecture is designed to work with ODBC.

To match the performance of toad would not be possible with the present design of the Harvest architecture.