TableLevel Scripts and SQL TimeOuts

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

TableLevel Scripts and SQL TimeOuts

  • Comments 1
  • Likes
Any SQL statement may take time to run and result in a timeout error. In Sage CRM the general timeout period is controlled within the

Administration -> System -> Database

screen by setting the Query Timeout field value. This is expressed in seconds and represents the maximum amount of time that a query is allowed run on the database server before a timeout error is displayed in CRM. There is no other way of controlling the Timeout for TableLevel scripts

Within a TableLevel script the SQL interaction is chiefly going to be via the eWareRecord object or via the eWareQuery Object.

If the table level script is fired as part of user driven change within a screen then the overall time that it takes for the screen to be returned to the user includes the time spent executing the SQL Query. For example if you have a table level script that writes into an table in an external database when a Company is updated then the edit screen will not return to the View mode until the data has been saved and a success message returned from the external database.

Errors within the execution of a TableLevel script will be caught and may be displayed to screen (if they have occurred in the context of a user driven request) and in the tablescriptslog table. You may also be able to handle some errors within try, catch statements.

TableLevel scripts when running SQL statements may interact with other features within CRM interacting with the database. If the scripts are starting to demonstrate particular problems with TimeOut issues then you may wish to consider the following:

Use the SQL logs to establish the exact SQL fired. It can be a worthwhile expenditure of time to investigate whether the SQL in a eWareQuery object can be tuned.

Because the SQL fired from TableLevel scripts may find itself interacting with other features consider changing any scripts causing a problem to a Detached TableLevel Script. By setting the script to be Detached you avoid the script having to attempt execution as the main screen driven data change is being committed. You can also change the script to retry on error.

There is an internal cycle that causes the Detached TableLevel scripts to be fired every 5 minutes. As discussed in the article "Controlling the Detached TableLevel Script Interval" this interval is not easily changeable.  The details of the scripts waiting to fire are held in the DelayedTableScripts table.

If you suspect that the script may be conflicting with escalation rules operating on the same table then experiment with changing the escalation behaviour. In the

Administration -> Advanced Customization -> Workflow & Escalation Configuration

screen. Change the setting of the field "Escalation engine tuned for" away from Immediate to System Performance. This will stop the immediate checking of the rule as the data record is inserted or updated. The check will then only occur within the regular Notification Interval. The Escalation rule Notify Interval may also be increased from the default 300 seconds to a greater period. Another check can be made on the performance of the individual Escalation rules. You may need to tune the SQL used. A very good tip is to make sure that Escalation rules are based an Views rather than directly on Tables. This means that the rule will be checking fewer records when it fires. Also if certain escalation rules are taking longer to fire than others you may wish to set their own individual intervals. That is done within the definition screen of the escalation rule itself.
Comments
  • Jeff, this article was very helpful. I was growing frustrated with a timeout which had no apparent cause as the commands were simple. Changing the script to a detached one resolved the issue for me. Thanks!