Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unhappy Unanswered: error occur while delete large volum records

    I had write a ActiveX service to delete several tables and those records are more than 100000. When I test it by deleted 1000 records it is ok, but once the volum is increase until 100000, it will give me a error message said timeout operation fail.

    how can i overcome this problem. please!!!!

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Are you deleting the entire table or only a part of it.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Oct 2003
    Posts
    6
    only part of it

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by yawhum80
    only part of it
    Can you post the delete query.. i believe an index would help a lot over here.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Oct 2003
    Posts
    6
    Sub DeleteByReqID(TableName, myConn)
    dim rsResult
    Set rsResult = CreateObject ("ADODB.Recordset")

    strSQL = "DELETE * FROM " & Trim(TableName) & " WHERE ReqID >= " & getFirstReqID & "AND ReqID <= " & getLastReqID
    Set rsResult = myConn.Execute(strSQL)

    Set rsResult = Nothing

    End Sub

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Is reqid the primary key in this table .. if not ... a clustered index on the same would be my recomendation
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Oct 2003
    Posts
    6
    not all the table is using reqid as a primary key, may i know what is the problem?

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    i mean to say that if you create a clustered index on the reqid column .. it might help speed up your query . The reason i asked whether reqid was the primary key was coz a clustered index is automatically created on the primary key column in case one doesn't exist already...

    Can you post the ddl for the table
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Oct 2003
    Posts
    6
    i had test it in isql and all the datas are successful to deleted but once i using DTS ActiveX script it will pop up the error message.

    no matter i using primary index, cluster index or non primary and non cluster index.

    is this because the DTS ActiveX script has a time limit for a operation?

Posting Permissions

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