Slow Recordset Performance

deferred
deferred used Ask the Experts™
on
Can anybody give me quick hints as to how I can increase the performance of my transactions. What I do is I open e recordset, which fetches around 500-600 records. I have heavy processing to do on these records, which  are primarily extensive calculations. Because of these heavy calculations, the whole process takes a lot of time. I loop thru the recordset and process/updat each record. Can anybody throw some light on how I can increase my performance? Any help is appreciated..
Me.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,
It seems like while U are looping thru the recordset..
You process each record,
Then, you save the record and then go for the next record..
U can try one thing..
In ADO, there is a new feature called disconnected recordsets. Here is how U implement it.
1) Establish a connection to your database with Client-Side cursors.
2) Then, Open Ur recordset using this connection with Batch Optimistic Cursor & Locking..
3) After this recordset is successfully opened, disconnect the recordset from the connection like this..
Set ObjRs.ActiveConnection = Nothing
4) Now, perform your manipulations on this data as usual. When U say .Update for each record, the updated record will be held in the cursor.
5) After completing the process, connect the recordset again like this.
Set ObjRs.ActiveConnection = ObjCn
and then use
ObjRs.UpdateBatch
So, what's actually happening is that all the records will be updated as one single batch. U fetch 500-600 records once and update then in 1 go. Save a lot on network traffic. This should increase your performance...
Get back in case of anything..
Another thing I would suggest is that if U are using high-end databases like SQL Server/Oracle, try using a stored procedure & then calling it from Ur VB App.
Cheers,
Deepak.

Author

Commented:
Gee, that's great ..
I never knew that ther's such a feature in ADO. It really works great... The performance has drastically increased..
Thanx,
Bye.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial