Performance issue with Recordset Helper class

[Prasad]

Hi Team,

We recently converted our VB6 application to C# using the mobilize.net tool. After the conversion we are seeing issues in the performance as compared to how it was with VB6. All DB related function of select, insert, update or calling SP's seems to be taking more time. One reason which we felt was because of the recordset helper, which iterates through a number of Open() function and then finally does the execute. Any idea or changes which might help improve the performance.

Thanks in advance.

Prasad

[OlmanQuesada]

Hello Prasad,

 

Sorry for the late response. You can use the Profiler tool that comes in VS.NEt to determine bottlenecks and performance issues.

In regards with the RecordsetHelper, this is an enhanced .NET Dataset component that emulates the classic ADO recordset used in VB6.

Performance issues may be related to the querie itself, infrastructure issues of the machine where either the .NET app or the DBMS are running. Also, there's a specific feature in the RecordsetHelper may affect the performance, so I'm going to elaborate deeper on this:

In VB6, when a recordset is populated, the database schema for that sql operation is retrieved from the database, this is useful when the recordset data is modified (adding new rows for instance). To get the same behavior in NET, you need two database operations: 

- get data. This is achieved by executing DbAdapter,Fill() to populate the DataSet (the RecordsetHelper is a dataset that extends the dataset functionality)

- get schema. This is achieved by executing DbAdapter,FillSchema() method (used by the recordsethelper)

This addicional database operation (getting the schema) may affect the performance:

If the schema is not used for an operation you can deactivate it by setting ADORecordSetHelper.LoadSchema property to false. Being a static property, be careful to set it back to true after opening the ADORecordSetHelper.

C# code snippet

ADORecordSetHelper RcdSet = null;
RcdSet = new ADORecordSetHelper("");
RcdSet.CursorLocation = intUseClient;

ADORecordSetHelper.LoadSchema = false;
RcdSet.Open(strSQLStatement, m_conAdoConnection, intLockType);
ADORecordSetHelper.LoadSchema = true;
Recommendation

If possible, identify ADORecordsetHelpers opened for reading purposes only.
For each of the previous identified ADORecordsetHelpers apply the suggested modification described above:
Before the <RecordsetHelper>.Open) method call, set the LoadSchema flag to false, and then set it back to true.
 

 

Please let me know if this help you out to solve the problem.

 

OlmanQ