Relational Integrity - To use/Not to use - Hints and Tips

Author: i.sharp@pcisystems.co.uk (Iain Sharp)

We currently have an app deployed in MSSQL, and have not, until now, used the relational integrity setup in the model, nor created any via scripts.  I am considering doing this, as we are more and more having to switch to doing bulk updates and such external to uniface for speed purposes, and we also discover we can't guarantee to have all the required entities painted on all the services (the warning messages about such seem to get lost in the mass of identical messages about entities not updated from this service).  Does anyone currently using it have any sage advice they'd like to pass me before I bite the bullet and spend a week setting it up on our database to see what happens? 

4 Comments

  1. Iain, if you have not yet applied referential integrity to your db you are currently at max speed during bulk updates... What are you really aiming for? Putting referencial integrity into DB has pro and cons: On one side you FORCE all users and all application (not only Uniface ones) to stick to rules defined. On another there are disadvantages: - bulk updates are generally slower when all integrity rules are applied. Different databases could have different reactions under this stress action; often is faster delete all integrity rules, do the bulk update on the db, reapply integrity rules. - I've found sometimes application need to be (partially) reviewed, also the existing U one:   A) because of the newer applied referential integrity...rows were sometimes 'de facto' inserted before head of a document.   B) broken transactions sometimes somehow leave orphans rows; this wrong behaviour should obviously be corrected - DB restore, when needed after a failure, are on same trouble path as bulk updates Gianni


    Author: gianni (gianni.sandigliano@unifacesolutions.com)
  2. Actually you make a good point in A. I think we have several occasions where the items are inserted before the order is updated. That would require a rewrite.  We do have a long term plan of switching over to using xml to transfer between client and server, and the disciplines I was required to work out to cope with this would be a reasonable rewrite for the above, but that is a hell of a rewrite.  In terms of the bulk updates, it was more that however much slowing down sql server does because of R.I. it still won't be as slow as mass updates via uniface, but it is more prone to someone accidentally deleting records when it shouldn't.  Also, it would allow us to do some stuff with cascading updates on primary keys, which Uniface doesn't allow.  On the whole it's looking more aspirational that effective though. 


    Author: Iain Sharp (i.sharp@pcisystems.co.uk)
  3. Pro: It helps to keep your database clean. Con: Your current Uniface applications and maybe non-Uniface applications may already sin against the integrity rules so need to be tested and adjusted. Speed: Not a big issue. But if a certain batch job is really too slow you can make it temporary disable the procedures and triggers


    Author: Theo Neeskens (tneeskens@itblockz.nl)
  4. Just an addon to Theo's comment about speeding things up when bulk loading; Remove all indexes from the tables before the update (as long as you know that the data has unique keys!) Once the update is complete - recreate the indexes. I've seen million+ row updates / inserts go from 12+hrs to less than 2 hrs this way... Knut


    Author: Knut (knut.dybendahl@gmail.com)