[whiteadi]

Hi,

I have a vb6 application that uses the OraOLEDB.Oracle provider, the oledb driver, to connect to an Oracle db.

Now the db is 11g. I am curious if there is any way to improve performance, maybe using some settings on the connection,

like using FetchSize or CacheType (now none of these are used), or using other provider?

Do not think on improving the queries, the code, because that is a different project / job and I am working on that together with a plsql developer.

Any ideas?

Thanks,
Adrian

[maj3091]

I'm no expert in this area, but I think you will find that the FetchSize and CacheType are used but with default values, so if you don't specify them in the connectionstring, the defaults will be used.

This is from the documentation:

Quote

OraOLEDB-specific connection string attributes which affect the performance of the rowset are:

CacheType - specifies the type of caching used by the provider to store rowset data. OraOLEDB provides two caching mechanisms:

Memory - The provider stores all the rowset data in-memory. This caching mechanism provides better performance at the expense of higher memory utilization. The default is Memory.

File - The provider stores all the rowset data on disk. This caching mechanism limits the memory consumption at the expense of performance.
ChunkSize - This attribute specifies the size, in bytes, of the data in LONG and LONG RAW columns fetched and stored in the provider cache. Providing a high value for this attribute improves performance, but requires more memory to store the data in the rowset. Valid values are 1 to 65535. The default is 100.

FetchSize - specifies the number of rows the provider will fetch at a time (fetch array). It must be set appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.

Not sure if that helps in anyway or not.

[whiteadi]

maj3091, on 01 February 2012 - 09:20 AM, said:

I'm no expert in this area, but I think you will find that the FetchSize and CacheType are used but with default values, so if you don't specify them in the connectionstring, the defaults will be used.

This is from the documentation:

Quote

OraOLEDB-specific connection string attributes which affect the performance of the rowset are:

CacheType - specifies the type of caching used by the provider to store rowset data. OraOLEDB provides two caching mechanisms:

Memory - The provider stores all the rowset data in-memory. This caching mechanism provides better performance at the expense of higher memory utilization. The default is Memory.

File - The provider stores all the rowset data on disk. This caching mechanism limits the memory consumption at the expense of performance.
ChunkSize - This attribute specifies the size, in bytes, of the data in LONG and LONG RAW columns fetched and stored in the provider cache. Providing a high value for this attribute improves performance, but requires more memory to store the data in the rowset. Valid values are 1 to 65535. The default is 100.

FetchSize - specifies the number of rows the provider will fetch at a time (fetch array). It must be set appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.

Not sure if that helps in anyway or not.

Thanks for the info, I think a better provider cannot be used to connect from vb6 to Oracle and I just have to identify the most time consuming queries and see if it can be improved.

[maj3091]

I think you could get more performance increases from tuning your queries, if you haven't done so already.

Good luck either way and sorry I couldn't be of more assistance.

[whiteadi]

maj3091, on 02 February 2012 - 01:58 AM, said:

I think you could get more performance increases from tuning your queries, if you haven't done so already.

Good luck either way and sorry I couldn't be of more assistance.

No problem, thanks anyway.
Changing the queries will take some time is a big old application...
Cheers!

[BobRodes]

well, "big old applications" often have some pretty interesting SQL code. I put in a couple of hours on one recently and changed the execution time from about 3 minutes to about 15 seconds.

[whiteadi]

3 minutes! The users really had to wait then. I guess that makes them now very happy
Nice.