Re: esql/c Batch Updates
UPSERT built into the engine would be useful and if properly implemented would tend to improve performance over all but the most carefully optimized code. UPSERT is one of the feature requests included in the IIUG Features Survey so if you think that this would be a good feature then PLEASE fill out a survey on the IIUG Web site (http://www.iiug.org/2008_survey/). FYI, I made a misstatement below where I said: "This is because a failed update on a table with any indexes ..." which should read: "This is because a failed *insert* on a table with any indexes ...". Hopefully everyone understood. Art On Fri, Oct 17, 2008 at 8:36 PM, InDeep <indeep@indeep.com> wrote: > Art, > > I can't remember the last time c.d.i actually had a discussion on update > strategies. This is really good stuff. Seems like this kind of mechanics > is going to become more important to understand for big database operations > where huge volumes of data are being worked on in a short amount of time. > > Do you or anyone else here have any comments regarding 'UPSERT' kinds of > schemes? > > Just curious. > > See also: > > http://en.wikipedia.org/wiki/Upsert > > http://lists.mysql.com/mysql/159795 > > -- > > Build a man a fire, and he'll be warm for a day. Set a man on fire, and > he'll be warm for the rest of his life. > Terry Pratchett > > > Art Kagel wrote: > > <SNIP> > > > > OOO That's a different question! PLEASE everyone ask the question you > > want to ask and describe the problem you are having DO NOT present the > > solution you think you need and ask us how to make it work. You will > > get the wrong solution EVERY TIME! > > > > OK, insert -- failure -- update logic is sub-optimal if more than about > > 25-30% of the rows that need to be affected already exist. In that case > > it will be far faster to UPDATE -- no rows updated -- INSERT. This is > > because a failed update on a table with any indexes (and the more > > indexes the more this is true) has to insert the row, update all of the > > indexes then fail on the unique index, unique key, or primary key and > > rollback. An update of a non-existent row is detected immediately and > > cheaply in which case you can then insert the missing row. > > > > Just be careful! Updating a row that does not exist in NOT AN ERROR > > (except in an ANSI LOGGED database), so you have to check the number of > > rows affected (sqlca.sqlerrd[2] in ESQL/C or sqlca.sqlerrd[3] in 4GL) > > byt the update. If that is zero then the update "failed" and you have > > to insert the row. > > > > I've submitted a proposal to do a session on this very subject > > (Developing better IDS applications) at the next IIUG Conference. You - > > and every other Informix developer, DBA, and manager should plan to > > attend the Conference in Lenexa KS this year the week of April 26, 2009 > > - and not just to attend my sessions. There will be many IBM Informix > > developers and managers there so attendence will give you access to > > people and training that you cannot get any other way and much that you > > would have to pay much more for. > > > > Art > > > > > > > > Yip that was the intent of my question. I am trying to improve the > > loads times of some 4gl apps that use an "insert -> fails -> update" > > logic. I have been playing around with the load2 library (loadinc > > tool) from the iiug software repository with some good results. > During > > my testing I noticed the library also has a load tool which has a > > cursor switch which makes a big difference in load times. I was > > wondering if I could do the same for updates too hence my post. > > > > On that note is feasible/recommend to create a load app that has this > > logic: bulk insert (or update depending on stats) via cursor -> fails > - > > > processing failed batch without cursor? Our environment is such > that > > we have base source data arriving for the same table (different > > columns) in overlapping time slots so there is no way that when your > > load starts you can determine update/insert buckets as during the > load > > process another load may have started. The obvious solution would be > > to stage the data but that's out of my control, I'm purely trying to > > optimize load times within the current paradigm. > > > > Cheers guys and thank for our input > > Angus > > > > > > _______________________________________________ > > Informix-list mailing list > > Informix-list@iiug.org <mailto:Informix-list@iiug.org> > > http://www.iiug.org/mailman/listinfo/informix-list > > > > > > > > > > -- > > Art S. Kagel > > Oninit (www.oninit.com <http://www.oninit.com>) > > IIUG Board of Directors (art@iiug.org <mailto:art@iiug.org>) > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on my employer, Oninit, the IIUG, nor any other > > organization with which I am associated either explicitly or implicitly. > > Neither do those opinions reflect those of other individuals affiliated > > with any entity with which I am affiliated nor those of the entities > > themselves. > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.