Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    5

    Unhappy Unanswered: diff between addnew and sql insert statement

    hi all...

    this happen in VB 6

    I tried to create a new record (oracle) by using SQL insert statement and .addnew/update method and found that SQL insert is much faster than .addnew/update mtd. Anybody know why ? If the table size is very big....is it better to use SQL insert than .addnew/update ?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    The short answer is that it is always faster to do a direct sql statement over the addnew/update. Depending on the cursor and other properties, ado has to manage quite a bit when using the addnew/update. When dealing with open recordsets using the addnew method, ado has to manage whether a current record was being edited and if so it call update - and when you use addnew, ado caches the changes locally then on update posts the changes to the database and changes properties for the recordset object ... so there is much more going on behind the scenes than just a straight sql statement that returns a closed recordset object. Use the connection's execute method for non-row returning sql commands - such as insert/update/delete.

  3. #3
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    5

    diff between addnew and sql insert statement

    thanks rnealejr.....got it i like this wensite so much.....fast response...

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Happy to help.

  5. #5
    Join Date
    Oct 2007
    Posts
    1
    Here is my code:

    set rs = server.CreateObject("adodb.recordset"
    rs.CursorType = adOpenKeyset
    rs.LockType = adLockOptimistic
    rs.Open "tblClaimHistory", cnn, , , adCmdTable
    lngOffset = 0
    Do While lngOffset < lngFileSize
    varChunk = leftB(RightB(varFDFContent, lngFileSize - lngOffset), iChunkSize)
    rs.Fields("ClaimContent").AppendChunk varChunk
    lngOffset = lngOffset + iChunkSize
    Loop
    rs.Update
    rs.Close
    set rs = nothing

    ----
    I got timeout expired, and need to make this update goes faster.

    So how do you replace:

    rs.Fields("ClaimContent").AppendChunk varChunk

    with update query?

    Thanks.

Posting Permissions

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