suggestion needed to slove this problem

hi all: I am using 7.31.uc2 on solaris 7.0. I have a database with a bunch of tables, 9 or 10 of them. They are all relationional to each other, foreign keys and all that. Everynight, I need to do an updates of the records, i have some new records to add, some records to modify. Due to the complexity of each record, I don't want to spend time to figure out the difference between the new record and the old one, so I simply delete the old one and add the new one as a new record when I modifty a record. Now, when I delete a record, I use cascade delete, so I delete the record on the main table, and all related records are deleted as well. My problem is, the whole thing is too slow, it added/modified 35000 in 4 hours! Further observation indicates that check point happens every 250 records. I tried to play with the wartermark parameters, but it doesn't increase it much. any suggestions are greatly appreciated, I'll be happy to provide any addtional information. thanks yan

Yan Zhu wrote: > I am using 7.31.uc2 on solaris 7.0. I have a database with a bunch > of tables, 9 or 10 of them. They are all relationional to each other, > foreign keys and all that. Everynight, I need to do an updates of the > records, i have some new records to add, some records to modify. Due > to the complexity of each record, I don't want to spend time to > figure out the difference between the new record and the old one, so > I simply delete the old one and add the new one as a new record when > I modify a record. Now, when I delete a record, I use cascade delete, > so I delete the record on the main table, and all related records are > deleted as well. > > My problem is, the whole thing is too slow, it added/modified 35000 > in 4 hours! Further observation indicates that check point happens > every 250 records. > > I tried to play with the wartermark parameters, but it doesn't > increase it much. > > any suggestions are greatly appreciated, I'll be happy to provide > any addtional information. Hesitant suggestion: there's a program SQLUPLOAD which comes with the SQLCMD program from the IIUG archives. It isn't really ready for prime time, but what it does allow you to do is update pre-existing rows or insert new ones. There are numerous potential problems with it -- it is alpha code, has lots of debug in place, and doesn't handle all error situations as it should. But it might be a starting point. On the other hand, it probably isn't the best code for an inexperienced ESQL/C coder to mess around with; it is definitely pushing the edges of ESQL/C coding techniques, at least in patches. Because it isn't really ready for prime time, I'm a bit hesitant about making this suggestion. Potential benefit: you would not, in general, need to do the cascading operations, thus reducing your workload..if you don't mess around updating the primary key, which is usually a bad idea anyway. This would reduce the amount of work that has to be done. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Just additional points. Yan's server is checkpointing every 10-11 seconds! (350000 / (4*60*60) == 24 rows per second meaning 250 rows take 10-11 seconds and Yan says the engine checkpoints every 250 rows.) This means Yan, that you have to increase the size of your physical log because the physical log is reaching 75% full after only 10 seconds and pausing for several seconds to checkpoint. You are only processing about 50-75% of your runtime the rest is spent waiting for checkpoints (I assume you have CKPTINTVL set to something reasonable like 300 seconds or more). Increase the PHYSFILE by a factor of CKPTINTVL/10 and take 80% of that value for the new PHYSFILE value. So if CKPTINTVL is 300 and PHYSFILE is 2000 you need to multiply by 30 (300/10) to 60000 and take 80% of that which gives a new PHYSFILE of AT LEAST 48000 to insure that checkpoints happen ONLY when scheduled NEVER because the physical log filled. This change alone should increase performance and get the run down to about 2 - 2.25 hours. Now Jonathan's suggestion will gain you much more of course but you may still have to increase PHYSFILE, just by less. However, it need not be as complicated as Jonathan suggests (his SQLUPLOAD utility is general purpose and needs to be everything to everyone so it is naturally more complex than your app will be). Read on. You do not have to determine which columns changed to know which rows to update. Just update all non-key columns! This is trivial programming and VERY fast (not significantly slower than just updating some columns) with a HUGE gain over those hugely expensive cascading deletes just to add it all back which also requires you to FETCH all those related rows in the first place which updating does not require you to do (except to get the parent's primary key if not in the input data of course). You say you also have some new records to add. If there are not 90% new records just attempt the update and if zero rows are updated (see sqlca fields) then do the inserts. This is MUCH cheaper than SELECTing the row first to determine if it is there or trying the INSERT and letting it fail with a duplicate key or constraint violation which must be rolled back. (If 90% or more are new rows then try the INSERT first instead as the extra cost for those 10% or less which must be updated will be minimal.) Art S. Kagel Jonathan Leffler wrote: > > Yan Zhu wrote: > > I am using 7.31.uc2 on solaris 7.0. I have a database with a bunch > > of tables, 9 or 10 of them. They are all relationional to each other, > > foreign keys and all that. Everynight, I need to do an updates of the > > records, i have some new records to add, some records to modify. Due > > to the complexity of each record, I don't want to spend time to > > figure out the difference between the new record and the old one, so > > I simply delete the old one and add the new one as a new record when > > I modify a record. Now, when I delete a record, I use cascade delete, > > so I delete the record on the main table, and all related records are > > deleted as well. > > > > My problem is, the whole thing is too slow, it added/modified 35000 > > in 4 hours! Further observation indicates that check point happens > > every 250 records. > > > > I tried to play with the wartermark parameters, but it doesn't > > increase it much. > > > > any suggestions are greatly appreciated, I'll be happy to provide > > any addtional information. > > Hesitant suggestion: there's a program SQLUPLOAD which comes with > the SQLCMD program from the IIUG archives. It isn't really ready > for prime time, but what it does allow you to do is update pre-existing > rows or insert new ones. There are numerous potential problems with > it -- it is alpha code, has lots of debug in place, and doesn't handle > all error situations as it should. But it might be a starting point. > On the other hand, it probably isn't the best code for an inexperienced > ESQL/C coder to mess around with; it is definitely pushing the edges of > ESQL/C coding techniques, at least in patches. Because it isn't really > ready for prime time, I'm a bit hesitant about making this suggestion. > > Potential benefit: you would not, in general, need to do the cascading > operations, thus reducing your workload..if you don't mess around > updating the primary key, which is usually a bad idea anyway. This > would reduce the amount of work that has to be done. > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN > #include <disclaimer.h>