Hello All,
Although I had registered long back but I never used this forum.
I have one table where in there are about 100 Million records, also there are 7 to 8 indexes on that table. Now the problem is, when I am trying to update a record in this table using recordset.update method it does fast where as when I do the same using connection.execute("Update query") then it becomes very slow. I don't know why is this beheviour, since what I know is, if any insert, update or Delete opration on table is executed fast when it is executed using connection and not .Addnew or .update or .delete op recordset. Please let me know if anybody has done any reaserch on this.

Regards,
MaheshB

When you use the update method you are already positioned on the record, so you don't need to scan the table to retrieve your entry.
On the other hand an UPDATE query will scan your table (which is large by the way) and that could take time (in some cases), for example if the statement from WHERE clause does not take profit of one of 8 indexes you already have. Check your execution plan (in Query Analyser) for the UPDATE query to see if the indexes are used in your particular case.

If that is the case then the same thing should happen to Insert query also, there I am not geting this behaviour. Do you have any statistics with you convince me, really it will help me a lot.

Thanks,
Mahesh

With INSERT is another story...
Inserting via AddNew followed by an Update, or INSERT query should not make any difference, because in both cases the insertion is made without scaning the table.

DELETE should give you (like INSERT) a difference.
Unfortunately a don't have any statistics on that.

Hello Steve,

I think you are wrong, because what I got on "http://www.mcswiz.com/Routines/ADOProblems.asp" this site is "Insert into" is more faster than ".AddNew" method. Even they have given the analysis of the activity with given set or records. So, can the same thing should apply to .update method and the "update Query". Correct me if I am wrong.

Thanks,
Mahesh