Re: Extremely slow deletes from table

I have a client with a program which allows them to amend delivery instruction lines on purchase orders. When the user accepted the changes the program deleted all the old lines and inserted the updated lines. This was taking up to three minutes (the time being directly proportional to the number of instruction lines) on SE 4,14. There are 120,000 rows in the table.. I changed the program so that, instead of deleting the lines it updated the order number, setting it to zero (it's an integer field). Now the program takes three seconds. I run an overnight sql to remove the zero order numbers. Richard Moon

In article <380d972d.9726876@news.cix.co.uk>, Richard Moon <richard@dcs.co.uk> writes > >I have a client with a program which allows them to amend delivery >instruction lines on purchase orders. When the user accepted the >changes the program deleted all the old lines and inserted the updated >lines. This was taking up to three minutes (the time being directly >proportional to the number of instruction lines) on SE 4,14. There are >120,000 rows in the table.. > >I changed the program so that, instead of deleting the lines it >updated the order number, setting it to zero (it's an integer field). >Now the program takes three seconds. > >I run an overnight sql to remove the zero order numbers. > Hmm, are all the indexes on the table unique?? I found make ALL indexes unique helps a lot... > >Richard Moon -- David Williams