Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    8

    Unanswered: Difference between recordset.update and connection.Execute("update query").

    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

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Difference between recordset.update and connection.Execute("update query").

    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.

    Originally posted by mpbalshetwar
    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
    Steve

  3. #3
    Join Date
    Jul 2002
    Posts
    8

    Re: Difference between recordset.update and connection.Execute("update query").

    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

  4. #4
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: Difference between recordset.update and connection.Execute("update query").

    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.


    Originally posted by mpbalshetwar
    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
    Steve

  5. #5
    Join Date
    Jul 2002
    Posts
    8

    Re: Difference between recordset.update and connection.Execute("update query").

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •