Solved

Performance  in disconnected recordset

Posted on 2001-06-07
10
377 Views
Last Modified: 2008-03-17
We are doing highly database intensive calulations.
We are fetching millions of records and doing calculations in loop and updating/inserting records in tables.We have created indexes and views to improve the speed and we are able to achieve 80000 records in 8 minutes.

But now same stored procedures are required to be imlplemented in COM using batch processing (disconnected recordset).

We are calling batchupdate of recordset to updates after calculations are done in COM.BAtch Update is taking more time and as a result its taking huge time. Why batch update takes so much time.



Can we improve performance of disconnected recordset

Regards,

HEmant
0
Comment
Question by:hemant
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 3

Accepted Solution

by:
jrspano earned 50 total points
ID: 6163863
when you disconnect the batch recordset it is separated from the db.  when you issue the batch update it has to look at the db and see what has changed and then marshal your changes to the db and handle any rows that have changed between the get recordset and the update.  to improve performance i would use strait sql.  recordset processing is slow sql would allow the server to do all the work.  I haven't yet found something that sql couldn't do, granted that it will propably be much harder to write than the vb.
0
 
LVL 6

Expert Comment

by:anthony_glenwright
ID: 6163898
I can't see a disconnected recordset with millions of records performing well at all.  All the data will be read to your client machine, which will take *ages*, and will probably use up lots of memory, too.

What is the nature of the processing you need to do?
0
 

Author Comment

by:hemant
ID: 6164322
But why do we use COM then. Why not com perform better.I do know Stored procedures are better  but MSDN says that Disconnected is second option to Stored proc.

Atleast it should not take 3 times the time waht stored procedures is taking.

How do we improve performance in COM.


Disconnected recordset is used that purpose only.


Plz provide more guidace if possible.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 3

Expert Comment

by:jrspano
ID: 6164481
you might want to try a async recordset.  this would allow you to do other things while com is marshaling the recordset.  you would then check to see when it is done
0
 

Author Comment

by:hemant
ID: 6166750
I cannot use Async as my logic is such. I need to process all the records before i do the batch update. After that no other processing is taking place.So no processing can go on when recordset is marshalling.

Regards,

Hemant
0
 

Author Comment

by:hemant
ID: 6166789
Hi,

What about this one. Can we convert Recordset into XML and do modifications to XML file. After doing all the calculations and storing it into XML we can convert it back into recordset and do the batch update

Regrads,

HEmant
0
 
LVL 3

Expert Comment

by:jrspano
ID: 6167359
I think that should work.  ado has methods to save a recordset as xml.  you then modify the file and then reload a ado rs from the file and do your batch update.
0
 

Author Comment

by:hemant
ID: 6169263
Thanx for all u r support.

regards,

hemant
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7133410
Hi hemant@devx,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Accept jrspano's comment(s) as an answer.

hemant@devx, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7167811
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month6 days, 7 hours left to enroll

634 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question