Alpha Video Training
Results 1 to 7 of 7

Thread: Delete Taking Long Time

  1. #1
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    662

    Default Delete Taking Long Time

    I have created a 'Delete' operation for a table. There are 78,000. records. I have created an index on the field that I am basing the delete query on. The table is not open when I run the delete operation.

    Question: Is it normal for Alpha to take 4 - 5 minutes to find the records to be deleted (~10,000) and then another 4 - 5 minutes to actually delete the records?

  2. #2
    "Certified" Alphaholic
    Real Name
    Finian Lennon
    Join Date
    Apr 2000
    Location
    Atlanta, GA
    Posts
    1,805

    Default RE: Delete Taking Long Time

    I don't think it should take that long.How are you doing it? With an index on the field for the delete query, you should use the between function to get the records and delete_range() to delete them. Something like
    t=table.open("tablename")
    t.order("FieldName","Between(FieldName,'"+value1+"','"+value2+"')")
    t.delete_range()
    t.close()
    should work pretty fast if FieldName, value1 and value2 are all character.

    Finian

  3. #3
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    662

    Default RE: Delete Taking Long Time

    Thanks for your response...

    I am trying to delete records that satisfy the following condition:
    ElemlinkLeft(Repslink,14) = .t.

    When I create a 'delete reecords'operation & specify the above criteria (or specify a saved Query with the same criteria) the delete takes forever!

    I have since discovered that if I perform a Query operation (with the above criteria), followed by a 'delete selected' operation it goes so fast that I can hardly see the little worm in the scroll bar.

    This has solved my problem, but I still wonder why I can't do it in 'one swell foop'.

    David

  4. #4
    Member
    Real Name
    Blake Watson
    Join Date
    Jan 2003
    Posts
    961

    Default RE: Delete Taking Long Time

    This has solved my problem, but I still wonder why I can't do it in 'one swell foop'.

    Perhaps the one case exploits an index while the other doesn't?

  5. #5
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    662

    Default RE: Delete Taking Long Time

    Perhaps...
    But... I created an index on the ElemLink field (the field used for in the criteia for the Query & delete).??

    Based on your hypothesis... when the two separate operations execute (Query followed by Delete Selected) the index IS used... BUT when I try to create an operation that deletes records, based on exactly the same criteria used in the Query, the index is not used???

    Is there some way to force the operation that deletes based on a saved query to use an index??

    If Alpha is using an index in the separate Query Operation but not in the combined one (with no delete) I don't know why... any ideas?

  6. #6
    Member
    Real Name
    Blake Watson
    Join Date
    Jan 2003
    Posts
    961

    Default RE: Delete Taking Long Time

    Based on your hypothesis... when the two separate operations execute (Query followed by Delete Selected) the index IS used... BUT when I try to create an operation that deletes records, based on exactly the same criteria used in the Query, the index is not used???

    That's my theory, yes.

    Is there some way to force the operation that deletes based on a saved query to use an index??

    I don't know much about operations yet, but it looks like a pretty closed box.

    If Alpha is using an index in the separate Query Operation but not in the combined one (with no delete) I don't know why... any ideas?

    Again, speculating: While the end result of your two approaches, the internal logic used to get there is NOT the same. The operation may take longer because it works even if there is no index, where your query approach will not. It might not be a simple matter for the operation code to check for indices at all possible points where they might benefit the task at hand.

    This sort of thing happens in computing a lot. Until the mid-'90s, the best way to get the fastest code was to write it in assembler. As compiler technology improved and the underlying CPU got more complex, it got harder and harder to improve by hand on what a compiler could do.

    Think of it this way: A5 provides a solution that works in all cases, and a solution that works faster if you know how to apply it.

  7. #7
    Member
    Real Name
    Blake Watson
    Join Date
    Jan 2003
    Posts
    961

    Default RE: Delete Taking Long Time

    >>end result of your two approaches

    Doh.

    Should've been "end result of your two approaches is the same".

Similar Threads

  1. Takes long time to save a transaction
    By Marlene Brusko in forum Alpha Five Version 5
    Replies: 5
    Last Post: 12-11-2004, 04:05 PM
  2. WAS license not taking
    By seidel1 in forum Web Application Server v6
    Replies: 4
    Last Post: 08-04-2004, 10:26 AM
  3. Calculations in Form taking too long when scrollng
    By kumargroup in forum Alpha Five Version 5
    Replies: 4
    Last Post: 01-15-2004, 06:42 AM
  4. have not played in a long time
    By Tom Turney in forum Alpha Five Version 4
    Replies: 1
    Last Post: 04-02-2003, 01:30 AM
  5. Operations taking FOREVER!
    By GregOnline in forum Alpha Five Version 4
    Replies: 3
    Last Post: 06-26-2002, 03:51 AM

Bookmarks

Posting Permissions

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