Insert Custom Order Rule on the fly

[joe.mag]

(Background for non-SoftPro Support readers) In an effort to make every order essentially a self-contained snapshot SoftPro maintains,among other things, a copy of Custom Order Rules for each order that is independent of any other order. As mentioned in another post (see "Manage Custom Order Rules (COR) from API"), we have to have a COR on every order to get our custom rate calculator to work. The problem is our attempts to do a bulk update of the CORs for our 50K+ orders actually made our SoftPro Server service unstable to the point where it crashed. As a result we're reconsidering an approach that SoftPro support originally warned us away from--inserting CORs, when they are missing on an order, on the fly.

In the past we did this check and insertion of the COR in response to the event that invokes our custom RC. I gather SoftPro was concerned that a race condition existed that might mean the COR wouldn't fire when the RC needed it to.

Our new proposal is this: If a user initiates a rate computation and the needed COR is missing, abort the initial RC computation request, insert the COR into the order, pop up a warning to the user to save the order, then allow the user to fire the RC compute event again.

What are your thoughts, SoftPro support?

[John Morris]

One concern with inserting COR modules on the fly via a shell package: If the order is created via the API by non-shell applications (spssvc.exe, IIS, etc.), your custom code will not be executed.

Can you share the details of our prior attempts that crashed the server? I'm curious what the root cause of the crash was.

[joe.mag]

At present the only way orders are created is through manual initiation--we don't create orders via the API or any other means. Does that lessen the concern? Our biggest worries are a) introducing instability or corruption into the individual orders and/or server process and b) a race condition that prevents the COR from being applied in time to enable our custom RC to operate properly.

In the past we've tried a multi-threaded component coded into our custom shell package (but not accessible to anyone but admins) that would visit each order via the API, check for the presence of our one standard COR, and insert it as needed. After testing on our Staging server, we ran this over a weekend on our Production server, numerous errors were thrown from the API (mostly about issues loading portions of the object model) and come Monday morning the server was incredibly slow, no one could run Select effectively so we called SoftPro support. After some missteps support did a full reinstall of Select, attempted to clear out the caches of the SQL Server Service Broker, and finally got things back to a working state. Six hours later. On a work day. So we're not inclined to go down a road that appears to over-stress SoftPro and expose us to considerable down time!

My understanding is a custom server package would work more efficiently but since the problem appears to be exhibited in the data tier I'm not sure it would make a difference other than to speed the overall process of updating CORs on 50K+ records.

I guess what I don't understand is this--why didn't SoftPro implement a simple "default COR set" approach that could be overridden only when needed? Sure, you'd want to version the default sets to maintain the ability to accurately reconstitute historical orders but you'd buy a much more manageable system! If we have to visit 50K orders to correct a typo or add a tweak to the COR script it's going to kill us!