SP Insert cost to high?

Hi, I'm trying to troubleshoot a performance problem with an application that uses lots of triggers and stored procedures. I set explain on before all of the stored procedures run. The only things that come back with high costs are inserts. Is this normal or is there something I could do to decrease the cost? I have run update statistics and dostats many different ways with no change. Since the procedures never change, I also set optimization to high when creating the procedures then set to low before executing (per the performance guide). ---------- Procedure: root.cps_add_info insert into "root".info_segment (nn_key,segment_key,info_desc_key,info_k ey,exp_key) values (? ,? ,? ,? ,? ) QUERY: (LOW) ------ Estimated Cost: 866691 Estimated # of Rows Returned: 21142788 Maximum Threads: 0

gps@lucent.com wrote: > I'm trying to troubleshoot a performance problem with an application > that uses lots of triggers and stored procedures. I set explain on > before all of the stored procedures run. The only things that come back > with high costs are inserts. Is this normal or is there something I > could do to decrease the cost? I have run update statistics and dostats > many different ways with no change. Since the procedures never change, > I also set optimization to high when creating the procedures then set to > low before executing (per the performance guide). > > ---------- > Procedure: root.cps_add_info > > insert into "root".info_segment > (nn_key,segment_key,info_desc_key,info_k > ey,exp_key) values (? ,? ,? ,? ,? ) > > QUERY: (LOW) > ------ > > Estimated Cost: 866691 > Estimated # of Rows Returned: 21142788 > Maximum Threads: 0 AFAICR, the cost on INSERT statements with a VALUES clause has always been essentially meaningless -- and ridiculously high. Again, AFAICR, if you have a SELECT statement in place of the VALUES clause, it produces a plausible answer. This was certainly the case back in the days of yore when 5.00 was a shiny new product. It might, depressingly, still be true. So IMO, yes, it is normal, and probably not avoidable. And it is probably ignorable. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>

gps@lucent.com wrote: > Hi, > > I'm trying to troubleshoot a performance problem with an application > that uses lots of triggers and stored procedures. I set explain on > before all of the stored procedures run. The only things that come back > with high costs are inserts. Is this normal or is there something I > could do to decrease the cost? AFAIK, this is normal. Insert costs may be ignored. You could compare this to the costs returned by a dbaccess insert (should be similar) and then determine the "actual" cost by seeing how long the insert actually takes. If, in fact, the insert "actually" takes some time, you will need to delve into the triggers/SPs that the insert invokes. Additionally, keep in mind that costs returned are only estimates. Even with the best statistics, the optimizer may be taking a path that it thinks has a low cost, but that actually isn't the most efficient. This is prone to happen in joins in which a parent table is being joined with a child table with row-constraining conditions on both tables (because statistics provide little information about relationships). Which, of course, means that even though everything else in your explain file looks hunky-dory, it may not actually be so. Bottom line : Sometimes you need to know enough of your data to be able to look at an explain file and home in on queries that have estimated low costs but use inefficient paths. One good way of determining such situations is the "proof of the pudding" method - actually run the darn thing while monitoring where it is taking inordinately long. Rudy > I have run update statistics and dostats > many different ways with no change. Since the procedures never change, > I also set optimization to high when creating the procedures then set to > low before executing (per the performance guide). > > ---------- > Procedure: root.cps_add_info > > insert into "root".info_segment > (nn_key,segment_key,info_desc_key,info_k > ey,exp_key) values (? ,? ,? ,? ,? ) > > QUERY: (LOW) > ------ > > Estimated Cost: 866691 > Estimated # of Rows Returned: 21142788 > Maximum Threads: 0