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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

deepakgCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.