Re: Extremely slow deletes from table
On Thu, 21 Oct 1999 00:50:32 +0100, David Williams <djw@smooth1.demon.co.uk> wrote: >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... > >> No. If I made them unique how might that affect query times ? Presumably you are suggesting adding a unique key as the last item on each index ? Richard Moon
Richard Moon wrote: > > On Thu, 21 Oct 1999 00:50:32 +0100, David Williams > <djw@smooth1.demon.co.uk> wrote: > > >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... > > > >> > > No. If I made them unique how might that affect query times ? > Presumably you are suggesting adding a unique key as the last item on > each index ? Making indexes that have a high average duplication count "more unique" to make them more efficient in use is a recommendation from the Informix Performance Guide. It does make a positive difference. You see Informix duplicate indexes are Inversion Lists, ie a key node which contains a list of rowids that contain that key value, rather than multiple nodes containing the same key. The Inversion List method is more efficient IF the Inversion list fits on a single leaf page otherwise it becomes very inefficient. In addition updating an index with large Inversion Lists can be very time consuming. So if you can add another column to a relatively non-unique key to lower the average duplication count this is a good thing. Further, an Informix UNIQUE index does not have the Inversion lists at all. Instead of a pointer to the inversion list's slot on a leaf page the node contains the target row's rowid. This saves a page access and perhaps an I/O for that key so you can further improve the index's efficiency if you can append column(s) to the key to make each key unique and then declare the new index UNIQUE in the CREATE INDEX statement so the more efficient structure can be used. Obviously if you have to append 20bytes to a 2byte index to make it unique there will be many fewer keys in each node and the tradeoff may not be there, but even adding a 4byte serial number to a 2byte index should still improve things and it may be enough to add 5 of those 20 bytes to the index so the duplication count is 2 or 3 per key value instead of hundreds or thousands. Art S. Kagel
In article <38107975.8DDC2A31@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes > >Making indexes that have a high average duplication count "more unique" >to make them more efficient in use is a recommendation from the Informix >Performance Guide. It does make a positive difference. You see Informix >duplicate indexes are Inversion Lists, ie a key node which contains a list >of rowids that contain that key value, rather than multiple nodes >containing the same key. The Inversion List method is more efficient IF >the Inversion list fits on a single leaf page otherwise it becomes very >inefficient. In addition updating an index with large Inversion Lists can >be very time consuming. So if you can add another column to a relatively >non-unique key to lower the average duplication count this is a good thing. >Further, an Informix UNIQUE index does not have the Inversion lists at all. >Instead of a pointer to the inversion list's slot on a leaf page the node >contains the target row's rowid. This saves a page access and perhaps an >I/O for that key so you can further improve the index's efficiency if you >can append column(s) to the key to make each key unique and then declare >the new index UNIQUE in the CREATE INDEX statement so the more efficient >structure can be used. Obviously if you have to append 20bytes to a 2byte >index to make it unique there will be many fewer keys in each node and the >tradeoff may not be there, but even adding a 4byte serial number to a >2byte index should still improve things and it may be enough to add 5 of >those 20 bytes to the index so the duplication count is 2 or 3 per key >value instead of hundreds or thousands. > >Art S. Kagel Good answer, I've explain this several times on c.d.i after hearing it in the Database Optimisation course at Informix UK in June/July 1997! Another one for the FAQ... -- David Williams