29 July 2008

Executing a stored procedure in Charles River (CRD) via the Workflow Monitor

My current client has a requirement for updating the Charles River blotter intra-day with a dup/contra indicator. The process for implementing this is easy but not straightforward. The purpose of this post is to enumerate the steps for registering a procedure with the monitor and make some guidelines for a successful implementation.

Step 1: Write the stored procedure

The method for implementing a dynamic procedure to run with a workflow starts with a successful stored procedure. The procedure should have the following attributes:

  • Cache as much data as possible in table variables. My rule of thumb is if I need to scan a table twice, I cache the table. I take care to only retrieve the rows and columns necessary for local processing.
  • Take care to only update the rows necessary. This may seem obvious but extra testing will go a long way to keep table locks to a minimum and performance up.
  • The last statement in the query should return a count(*) statement of the elements that may be updated by your procedure. This number will appear in the Workflow Monitor screen.
  • Choose a blotter field that is not currently being used for processing. For our query, we chose the USR_CLASS_CD field as the field to display our indicator.

Step 2: Register the Stored Query

Open the Charles River System Administration application. Choose Setup > Stored Queries from the menus. Name the stored query and register it with the proper Data window.

In the query field execute the query by typing exec proc_name where proc_name is the name of the procedure to be executed.

Make sure the proper groups can see the procedure and that the description is properly populated.

Step 3: Register the Workflow Monitor

Create a Workflow Monitor in the Charles River Administration from Setup > Workflow Monitor Setup. Create your worklfow monitor.

We added (no click) to the monitor name to indicate to our users that clicking on the monitor would not retrieve any records.

The SQL Query field is populated the same as in the Stored Query field, exec proc_name where proc_name is the name of the procedure to be executed.

The privilege filter should be set to 'NONE'. The value message fields should be left blank.

Set the refresh frequency to the maximum time that allows for traders to work and still see timely information. Remember that Workflow Monitors are client-driven. That is, they run at the refresh frequency of each client. As a result, the greater the number of users, the greater you can (theoretically) set your refresh frequency.

Step 4: Create a shadow Workflow Monitor (optional step)

For our process, we created a view that returns the same order records updated by our procedure. This allows the users to quickly drill down to the updated records.

If you follow the steps above and pay close attention to performance, the procedure should execute automatically and give your users a dynamic blotter.