Disconnected REcordset

[hemraj]

IAm using stored procedures to do database intensive calculations.Millions of records get inserted/updated in a single stored procedure.

But I need to implement same in COM.
We are using disconnected recordset but at the time of batchUpdate its taking huge amount of time.Com is taking almost 3 times as that in Stored Procedures.
CAn you suggest any properties to be set in disconnected recordset.


Regards,

Hemraj

[Cakkie]

There are some reasons that might cause COM to be slower than stored procedures.

First of all (and in my opinion likely the most important), stored procedures are compiled and optimized on the server. If you run a stored procedure, the SQL server already knows what you are about to do, even before you start the procedure (unless the procedure has the option WITH RECOMPILE set, or the procedure is run for the first time, ever).

Also, the data stays where it is, at the server. When using COM, data goes from the server to the client, changes are made, and the changes are sent back to the server. This are two roundtrips you need to make which you don't need to make with a stored procedure.

Another thing, when using disconnected recordsets, all the data must come and go in 1 call. You open the recordset, and all the data comes to you. When you have all the data, you can start processing. When not using disconnected recordsets, you can start processing the second you have your first records, and the data comes in when needed.

Finally, but not always true today anymore, a server is most likely to be a killer machine when compared with the client. If you have a dual 1 gHz processor server with 1 GB ram which does the processing, or you have a single 750 mHz PC with 128 MB ram with Excel Word and some other applications running in the back, the difference will be enormous.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[mpaul]

Even while using com(mts) for businnes logic
usage of stored procedures are suggested for faster
access.Check for Database Locks while transaction when put in mts.