I am using ActiveX Data Objects to

[FontanaS]

I am using ActiveX Data Objects to access SQL 6.5 tables. The data is contained in a total of 18 intermediate tables (used primarily as an import baseline for data retrieved from another source). The component utilizes a data parser and ADO to populate these intermediate tables. The component then utilizes ADO to populate permanent tables from the intermediate tables. There is a mix of implicit and explicit recordset object creations that are used throughout the component and the intermediate tables are never purged of its data. The component has a class module for each intermediate table and each permanent table, which when called, creates a recordset object temporarily to effect the manipulation of the data for each table. When the class modules loses focus, the recordset objects are destroyed. This occurs both in the importing of the data to the intermediate tables, and storing of the data to the permanent tables. The Business rules are handled by the class modules and there are functions that also effect business rules. We are seeing a significant performance hit each time this component is executed and recently since the data link files being imported have increased in size. Is this a case where utilizing stored procedures instead of ADO recordsets to manipulate the data would effect a performance gain? Or would maintaining static recordsets be more appropriate?

I am leaning towards stored procedures as a solution, but that would mean a MAJOR re-writing of the component.

Your thoughts are greatly appreciated!

 

[TerpFan2001]

FontanaS:

I have found that using stored procedures noticeably increases the performance of my VB apps. I think the best way to determine the performance difference would be to write two procedures that perform the same task. One would use your normal ADO recordsets and the other would use stored procedures.

At the beginning of each procedure, capture the time that the procedure was started. At the end of each procedure, capture the time the procedure ended. Then have the procedure calculate the difference between the start and end times and return that value in a msgbox. Run both of your procedures using the same data and then compare the time that each required to complete the task. Hopefully, this will make your decision a little easier.

 

[phinoppix]

Hi,

Try to make a keyword search on forum222 and search for stored procedures. I've read comparative discussions on using ADO recordset conventions and stored procedures, and satisfying conclusions, too.