Re: SET EXPLAIN - why are INSERT costs high???

My 0.02c worth. if you have a unique index on a table you are inserting into then the index must be checked as part of the insert and there is an additional cost involved with that. Also update statistics has an effect on that. Especially if you load a large number of rows into an empty table or have not run update statistics. That is because the statistics indicate a sequential scan is the most effiecient way of checking the index becuase the optimiser still thinks the table is empty. So indexes can have an effect on insert performance. Jason On Tue, 24 Feb 1998 00:00:32 +0000, David Williams <djw@smooth1.demon.co.uk> wrote: >In article <34edb916.0@news.snip.net>, David K. Killough ><killougd@ix.netcom.com> writes >> >>We are stress testing our application and have been pouring over the SET >>EXPLAIN OUTPUT to optimize things. Everything looks good but the INSERTs >>are still reporting high costs. My theory is that it is handling the INSERT >>like a SELECT without a WHERE clause. Our hardware vendor has said that we > > The costs for insert are complete crap. They are meaningless. > >>need to reduce these costs before they will tune our database. I don't >>think it is possible. I don't think the high costs represent a failure to >>optimize. I am very sure about this. Does anybody know the scoop. If so, > > Agreed. The costs also expect a cpu to disk performance ratio of 100 > to 1 which will almost never bo correct.. > > Ignore them and just look at selects/updates/deletes and may sure they > use indexes. > >>is there any reference in the Informix documentation that I can go to. It >>seems like indexes should have some impact on the efficiency of doing >>inserts. Does SET EXPLAIN help cost this at all????? > > Not really. Indexes do slow things down slightly but without indexes > your selects will take forever... > >>thanks, dave k >> >> > >-- >David Williams > >Maintainer of the Informix FAQ > Primary site (Beta Version) http://www.smooth1.demon.co.uk > Official site http://www.iiug.org/techinfo/faq/faq_top.html > >I see you standin', Standin' on your own, It's such a lonely place for you, For >you to be If you need a shoulder, Or if you need a friend, I'll be here >standing, Until the bitter end... >So don't chastise me Or think I, I mean you harm... >All I ever wanted Was for you To know that I care