Slow Recordset Performance

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.
deferredAsked:
Who is Participating?
 
deepakgConnect With a Mentor 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.
0
 
deferredAuthor 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.
0
All Courses

From novice to tech pro — start learning today.