Microsoft Access Workflow Designer: Tips for Optimizing Team Solution Performance

Microsoft Office Developer Product Team
Microsoft Corporation

September 1, 1999

Summary: This article provides tips for improving performance of a team solution created using the Microsoft® Access Workflow Designer for SQL Server™, a tool provided by Microsoft Office Developer. (7 printed pages)

Introduction

Microsoft Office Developer includes a new component, Microsoft Access Workflow Designer for Microsoft SQL Server, which contains a number of tools designed to build integrated Web- and SQL Server-based solutions that automate business processes easily for teams of knowledge workers.

Because team solutions created using Access Workflow Designer are dependent upon a number of external components, such as Microsoft Windows NT®, SQL Server, and data access pages, there are many options for improving performance in your solution.

Performance issues should be considered throughout the development cycle—not at the end when the system is implemented. Many performance issues that result in significant improvements are achieved by careful design from the outset. To most effectively optimize solution performance, you must identify the areas that will yield the largest performance increases over the widest variety of situations and focus analysis on those areas.

For additional tips for optimizing performance, consult the Windows NT, SQL Server, Access 2000, and Microsoft FrontPage^® Server Extensions documentation.

Database Tips

It is important to correctly design the database to model your business requirements and to take advantage of hardware and software features early in the development cycle of a database application, because it is difficult to make changes to these components later.

When designing your database and your solution, there are several things you can do to optimize database performance and make your solution work more efficiently. For additional information about optimizing database performance, consult the Access and SQL Server online documentation.

Optimizing the SQL Server Database

The following are general guidelines for optimizing SQL Server database performance:

• Design tables without redundant data. A well-designed database is a prerequisite for fast data retrieval and updates.
  
  
• Enforce referential integrity, which preserves the defined relationships between tables when you add, update, or delete the rows in those tables by defining primary key and foreign key constraints in the related tables.
  
  
• Choose an appropriate data type for columns. You can save space in your database and improve join operations. When defining a column, choose the smallest data type or column size that is appropriate for the data in the column.
  
  
• Create indexes for columns you sort or join or for which you set criteria. You can make dramatic improvements in the speed of a query by indexing columns that are on both sides of joins and columns that are used to set criteria for the query. Indexes, however, can slow down the speed of record insertions, updates, and deletes.

Processing Data on the Server

To process data on the server, you can use stored procedures and triggers, and you can sort data on the server before retrieving it.

• Stored procedures are a precompiled collection of one or more SQL statements and optional control-of-flow statements that can accept parameters and are processed as a single unit by SQL Server. A stored procedure resides in the SQL Server database. Because a stored procedure is compiled on the server when it is created, it executes faster than individual SQL statements. In addition, because a stored procedure runs on the server, it takes advantage of the extra processing power of the server.
  
  
• Triggers are a special kind of stored procedure that execute automatically when you update, insert, or delete data. Use triggers to enforce business rules and restrictions that are more complex than those defined with check constraints. Unlike check constraints, triggers can reference columns in other tables. For example, a trigger can roll back updates that attempt to apply a discount (stored in a discounts table) to books (stored in a titles table) with a price of less than $10.00.
  
  
• Sort records on the server by using a stored procedure or SQL statement stored in the RecordSource property of a form or report (which executes on the server). Sorting records on the server is another way to take advantage of the extra processing power of the server.

Workflow Process Tips

You can disable specific Microsoft Office Developer workflow features to improve solution performance. Depending on the requirements of your solution, there are three features that can be turned off to improve transaction performance: row-level permissions; running workflow in a separate process; and making deleted rows available to script.

Row-level Permissions

In Access Workflow Designer, security is designed around the main table and detail table base views. When row-level permissions are enabled, these views perform a join to the modPermissions table to determine which rows to return. Based on the number of rows on which you have set permissions and the number of roles for which you have set permissions on that row, performance can be impacted.

If you are not using row-level permissions in your solution, it is best to disable this feature by finding the table in the Access Workflow Designer Object list and clearing the Enable row permissions option.

Security issues can arise if you disable row-level permissions and have no workflow process defined for a table. Be aware that, for data access pages to work correctly, Select permissions on the primary-key columns, as well as full Insert and Delete permissions, are granted to the Public role during table registration. If you do not have a workflow process enabled or row-level permissions defined, anyone with Public access to your database could run an insert or delete query against the main table with success.

Running Workflow in a Separate Process

By default, workflow is run in a process separate from your SQL Server. If you must have further performance increases, you can define workflow to run in the same process as SQL Server by clearing the Run workflow in a separate process option, found on the General tab of the Workflow Process pane.

You can expect a 20- to 30-percent increase in performance. Be cautious, however, because workflow scripting problems can cause your server to hang, or even crash, if workflow is not run in a separate process. It is recommended you run out of process and fully test your workflow scripts before disabling this option.

Making Deleted Rows Available

Workflow scripting has a method that may be useful in certain situations—the Session.OriginalItem method. This method makes it possible for you to evaluate the previous values in a record before the current values (Session.Item) are set. This method is not available by default.

To make this method available, you select the Make deleted rows available to script option, found on the General tab of the Workflow Process pane.

Be aware that after you make this change, during trigger execution, the values are made available to script by copying the original values to a temporary table. Thus a performance hit is taken when you use this feature. Unless you absolutely must have OriginalItem in scripting, leave this option cleared to increase performance.

Offline Replication Tips

Implementing replication requires careful planning to minimize the demands on network resources and maximize data consistency. While you plan your offline publications, it is important to remember both the size and the number of publications included in your solution can impact performance.

In addition to the database tips provided in an earlier section, there are several design issues that can affect the success of replication. Before implementing replication, you should consider the following design guidelines:

• Use the Not for replication option on the Identity property.
  
  
• Use the uniqueidentifier data type.
  
  
• Filter data to improve efficiency.

As a general rule, create a minimal number of publications, and include only the tables, objects, and filtered data required for the solution to work offline. Offline publications impact performance in a few ways—when the original publication is created, during creation of the solution from a template, when the user takes a publication offline, and when a user synchronizes the offline database.

The more publications your solution has and the larger the publications, the longer creation of the solution will take.

Use the Replication Monitor

Replication Monitor is a component of SQL Server Enterprise Manager designed for viewing the status of replication agents and troubleshooting potential problems at a Distributor. You can use Replication Monitor in SQL Server Enterprise Manager to:

• View a list of publishers, publications, and subscriptions to the publications that are supported by the distributor.
  
  
• View scheduled replication agents and to monitor real-time “heartbeat” status and history for each agent.
  
  
• Set up and to monitor alerts related to replication events.

  If the event occurs, SQL Server will respond automatically, either by executing a task that you have defined or by sending an e-mail or a pager message to the specified operator.

After replication has been set up, you can use the Microsoft Windows NT Event Viewer to view SQL Server messages. For information about Event Viewer, see Windows NT Help.

If you are running Server on a Microsoft Windows NT platform, you can use Windows NT Performance Monitor to monitor the rate at which various replication processes take place. See the SQL Server Books Online for more details.

Data Access Page Tips

In general when you design your Web page, determine the data and functionality required, and delay asking the server for this data and functionality until the user requests the information. For example, create a page that does not require data to be retrieved from the server while the page is loading. Instead, you can add a command button to the page to load data on request.

Provide fields that take a long time to retrieve data from the server, such as text and image fields, only when requested. You can use the following techniques:

• Place text and image fields on a subform that uses the same record source. Access only retrieves the text or image values for the current record. For example, the Issue Tracking solution uses a summary form with a separate detail form.
  
  
• If your solution is data-entry intensive, such as an order entry system, create a specific form for adding records that has the DataEntry property set to Yes. When you open the form to enter new data, Access does not display any existing records. This saves time, because Access does not have to retrieve all the records from the server.
  
  
• If a form or report is based on a parameterized stored procedure, use the Input Parameters property to supply the parameter values.
  
  
• Use SQL UPDATE and DELETE statements with the appropriate WHERE clause to update or delete multiple records that meet the same criteria. This is more efficient than opening a recordset and performing the updates or deletions one record at a time.

There are several things you can do to make your data access pages load faster in Page view or in Microsoft Internet Explorer 5:

• Avoid overlapping controls.
  
  
• Use bitmaps and other graphic objects sparingly. Use black-and-white rather than color bitmaps.
  
  
• Make sure the ExpandedByDefault property in the Sorting And Grouping box is set to No for highest group level. Setting it to No for lower group levels will speed up other interactions after the page is opened.
  
  
• Make sure the DataPageSize property in the Sorting And Grouping box, which determines the number of records displayed in a group on a page, is set to a low number, rather than a high number, or is set to All. The lower the number, the faster the records are displayed.
  
  
• Use bound HTML controls instead of text boxes to display any data that is not updateable—for example, to display autonumbered values, values derived from expressions, and hyperlinks.
  
  
• When using a data access page within Microsoft Access, close all database objects that are not being used.
  
  
• When using a data access page in Internet Explorer, close all windows that are not being used.
  
  
• On data access pages that are used for data entry, set the DataEntry property of the page to Yes, so the page opens to a blank record.
  
  
• Index any fields you sort or group or on which you filter.
  
  
• On grouped data access pages that contain records with a one-to-many relationship, group records by table rather than by a field or expression.
  
  
• On grouped data access pages based on tables with a one-to-many relationship, bind each section to a table rather than to a query. Microsoft Access retrieves the records from a table only when it must display them on the page. With a query, Access retrieves all the records before it displays any records on the page. Use a query only when you want to limit the data used on the page, calculate aggregate values, or perform a task that is possible only in a query.

For More Information

For the latest information about Microsoft Access Workflow Designer for SQL Server, see the Microsoft Office Developer Web site at http://msdn.microsoft.com/OfficeDev/.

To access Knowledge Base information, consult the Product Support section of the Microsoft Office Developer Web site.

For information about developing team solutions, consult the Access Workflow Designer Developer’s Guide online documentation.