Professional Web Applications Themes

Very slow execution of a delete statement - IBM DB2

The following statement executes in a few seconds: select count (*) from woerter w where not exists (select wortnr from texts where wortnr = w.nr) But the delete statement delete from woerter w where not exists (select wortnr from texts where wortnr = w.nr) runs several minutes! The whole table woerter has about 15,000 rows, texts has about 350,000. Why is the delete statement so slow? Regards, -- Burkhard Schultheis Tele Data Electronic, Wagnerstr. 10, D-76448 Durmersheim Email: de Phone: +49-7245-9287-21, Fax: +49-7245-9287-30...

  1. #1

    Default Very slow execution of a delete statement

    The following statement executes in a few seconds:

    select count (*) from woerter w where not exists (select wortnr from
    texts where wortnr = w.nr)

    But the delete statement

    delete from woerter w where not exists (select wortnr from texts where
    wortnr = w.nr)

    runs several minutes! The whole table woerter has about 15,000 rows,
    texts has about 350,000.

    Why is the delete statement so slow?

    Regards,
    --
    Burkhard Schultheis
    Tele Data Electronic, Wagnerstr. 10, D-76448 Durmersheim
    Email: de
    Phone: +49-7245-9287-21, Fax: +49-7245-9287-30
    Burkhard Guest

  2. #2

    Default Re: Very slow execution of a delete statement

    PM (pm3iinc-nospam) schrieb:
     

    No, definitely not. Machine has no IO all the time. But it reads several
    billions of rows (we have canceled the delete after more than 1 hour,
    the db2 agent had read about 1.4 billion rows). It seems that db2 walked
    through 15,000 x 350,000 = 5 billion rows. But why?

    In the mean time I made it in a few seconds: Created a temp table with
    all numbers to delete and deleted with the join over this temp table.

    Regards,
    --
    Burkhard Schultheis
    Tele Data Electronic, Wagnerstr. 10, D-76448 Durmersheim
    Email: de
    Phone: +49-7245-9287-21, Fax: +49-7245-9287-30
    Burkhard Guest

  3. #3

    Default Re: Very slow execution of a delete statement

    Possibly logging paramaters and/or io contention .
    (woerter, texts tablespaces and your logs may be on the same device.)


    PM


    PM Guest

  4. #4

    Default Re: Very slow execution of a delete statement

    How do the access plans for the two compare?

    --

    Bob
    IBM Toronto Lab
    IBM Software Services for Data Management
    "Burkhard Schultheis" <de> wrote in message news:bkrl04$3pb16$news.uni-berlin.de...
    The following statement executes in a few seconds:

    select count (*) from woerter w where not exists (select wortnr from
    texts where wortnr = w.nr)

    But the delete statement

    delete from woerter w where not exists (select wortnr from texts where
    wortnr = w.nr)

    runs several minutes! The whole table woerter has about 15,000 rows,
    texts has about 350,000.

    Why is the delete statement so slow?

    Regards,
    --
    Burkhard Schultheis
    Tele Data Electronic, Wagnerstr. 10, D-76448 Durmersheim
    Email: de
    Phone: +49-7245-9287-21, Fax: +49-7245-9287-30
    Bob Guest

  5. #5

    Default Re: Very slow execution of a delete statement

    "Burkhard Schultheis" <de> wrote in message
    news:bkrl04$3pb16$news.uni-berlin.de... 

    The delete has to write all the delete rows to the log buffer and then to
    the active log. It is possible that you can speed up the delete by tuning
    the logging parameters, but it will never be as fast as just the select.

    As previously mentioned, the access paths may be different. The use of
    "count(*)" and "exists" may allow index-only access to be used, where the
    delete must actually read the table data to delete the rows, and then also
    delete each of the index entries for each row deleted.


    Mark Guest

  6. #6

    Default Re: Very slow execution of a delete statement

    If there is RI to other tables, then the delete will take longer.

    For example, if you are trying to delete: State_cd = "CA" from the
    'State' table, the "CA" may be use in many other tables that contain
    millions of rows.
    The RI will ensure check each row to ensure it is ok to delete.
    Create an access plan to visualize what tables are being accessed.

    options:
    1) write a korn shell to loop through and only delete a few rows at a
    time
    2) break deletes up into smaller groups (delete from table where
    primarykey between 0 and 10000; delete from table where primarykey >
    10000)
    3) drop RI (very dangerous)
    Kris Guest

  7. #7

    Default Re: Very slow execution of a delete statement

    Bob [IBM] schrieb:
     

    select shows about 63,000 timerons, and delete about 121,000,000
    timerons! They are quite different. Where the two paths from the two
    tables come together, select has a msjoin, whereas delete has an ixscan.

    But:
    delete from woerter where nr in (select nr from woerter except select
    wortnr from texts)
    costs only about 9,000 timerons without RI, with RI about 17,000
    timerons. Witout it executes in a few seconds, with RI it's still
    running (several minutes).

    Regards,
    --
    Burkhard Schultheis
    Tele Data Electronic, Wagnerstr. 10, D-76448 Durmersheim
    Email: de
    Phone: +49-7245-9287-21, Fax: +49-7245-9287-30
    Burkhard Guest

  8. #8

    Default Re: Very slow execution of a delete statement

    Do you have current statistics?
    "Burkhard Schultheis" <de> wrote in message
    news:bkrl04$3pb16$news.uni-berlin.de... 


    Mark Guest

Similar Threads

  1. SQL error in DELETE statement
    By stillwaiting in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 19th, 04:03 AM
  2. Delete form - Post data to a table and delete uponsubmit.
    By FusionRed in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: July 28th, 01:27 PM
  3. COM object execution unacceptably slow
    By akashaboi in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: February 22nd, 03:29 PM
  4. Replies: 3
    Last Post: September 5th, 04:16 PM
  5. slow global.asa execution
    By Andrew Durstewitz in forum ASP
    Replies: 3
    Last Post: August 4th, 03:01 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139